Error fromatting parameter for Pandas read_sql_query

Question

How do I fix this error on passing a string parameter into pd.read_sql_query? Using Python 2.7.

table = 'mytable1'
query = (
        "SELECT * "
        "FROM ? "
    )
df = pd.read_sql_query(sql=query, con=conn, params=(table))

pandas.io.sql.DatabaseError: Execution failed on sql 'SELECT * FROM ? ': near "?": syntax error

I have tried replacing ? with % and %s but it returns the same error.

The following equality example works as expected:

query = (
        "SELECT * "
        "FROM mytable1 "
        "WHERE name = ? "
    )
df = pd.read_sql_query(sql=query, con=conn, params=('cat',))

Note that the comma in params appears to be required, otherwise this error is returned: Incorrect number of bindings supplied. The current statement uses 1, and there are 7 supplied.

I have also tried params=(table,) in my problem but no luck. I know that alternatively I can do this with "FROM '{t}' ").format(t=table), but I would like to understand how to use Pandas' built-in parameters option.

There is a difference between (table) and (table,) in Python. The latter is a tuple, while the formal is a string, which is table. — Quang Hoang
– Quang Hoang, Commented Apr 2, 2021 at 0:48
@QuangHoang yes, but the docs say it can take a list or tuple! — a11
– a11, Commented Apr 2, 2021 at 0:49

JL Peyret · Accepted Answer · 2021-04-02 01:16:49Z

2

The problem is that params is intended to replace values, not SQL keywords (FROM, SELECT, etc...) or tables or columns.

You can't specify table that way, you have to use a string substitution.

query = (
        f"SELECT * "
        "FROM {table} "
    )

However, be very, very, very careful. Doing this, rather than using params opens you up to a very big family of vulnerabilities, SQL Injections.

Don't do it if you get the table names from external sources.

(Oh, and the likely reason for this limitation is that, besides their absolute necessity for security reasons, parametrized queries allow the database engine to examine the query, look at indices, statistics and all that and draw up an "execution plan". Successive calls then can reuse that execution plan and just substitute in the new variables. That could not be done if the query was changing in terms of what tables and columns were being accessed.)

edited Apr 2, 2021 at 1:16

answered Apr 2, 2021 at 0:49

JL Peyret

12.2k4 gold badges67 silver badges97 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

a11 Over a year ago

That makes sense, I was starting to suspect that. Does string format alleviate injection concerns, notwithstanding the * ? In other words, is query = ("SELECT * FROM '{table}' ").format(table=t) ok?

JL Peyret Over a year ago

Not really, no. You could specify a regex test ahead, like re.match('[A-Za-z0-9_]+') on table. That should keep you safe-ish as it avoids little tricks like embedded quotes. But there are libraries to do this. Still, whenever you can, use parametrized queries rather than testing user input for safety. It so happens you can't here.

JL Peyret Over a year ago

@QuangHoang Yes, as I have already noted twice in the answer. That doesn't change the fact that you can't specify either SQL keywords or schema objects using parametrized queries. Whether the OP's use case justifies the risk is for them to decide.

a11 Over a year ago

Rest assured, negligible risk :) trying my hand at a script that matches ingredients to recipes and a relational database seemed to be the way to go. I don't use databases professionally, but I like knowing the difference between the right and wrong way (e.g., SQL injection concerns) out of general curiosity.

Collectives™ on Stack Overflow

Error fromatting parameter for Pandas read_sql_query

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related