0

I have a string (sql query) in which I want to pass a variable at one point, then pass another variable at another point (list of variables, but just focusing on one for now).

The expected would be something like this:

sql_query = 'SELECT {{field}} FROM {table} WHERE {{field}} IS NULL'.format(table=table_name)

sql_query should now be: 'SELECT {field} FROM table_name WHERE {field} IS NULL' Then format for field

sql_query = sql_query.format(field_name)

In theory I want sql_query to now be: 'SELECT field_name FROM table_name WHERE field_name IS NULL'

But the above ignores the .format and I get: 'SELECT {field} FROM table_name WHERE {field} IS NULL'

I have tried combining f-strings and .format in multiple ways and the closest to my goal is:

field = field_name
sql_query = f'SELECT {field} FROM {{0}} WHERE {field} IS NULL'.format(table_name)

# Works and I get sql_query : 'SELECT field_name FROM table_name WHERE field_name IS NULL'

The above works but it happens all in the same place and separating where each one happens is the true goal of mine.

EAA
  • 87
  • 1
  • 9
  • 2
    The argment to the second format should be `field=field_name` – Barmar Jan 19 '23 at 21:27
  • 5
    Do not use string formatting to construct SQL queries. Use your SQL library's facilities for parameterized queries. If you *must* use string formatting (not all libraries provide a way to parameterize the query on the table to query), either make sure you have validated the input, or restructure your code. (Do you really need `sql_query` to support *arbitrary* tables, or can you simply define a hard-coded dict of table-specific queries key by table name?) – chepner Jan 19 '23 at 21:27
  • You should have gotten `KeyError: 'field'` when you called `.format`, because you didn't supply the input in the expected form. Did you do this inside a try/except that eats exceptions? – Samwise Jan 19 '23 at 21:27
  • I don't understand why you aren't using strings, e.g. `field = 'field_name'` – President James K. Polk Jan 19 '23 at 21:27
  • Please re-read what @chepner wrote above. Don't do this. String formatting is a powerful tool but should never be used with SQL strings. You are creating a massive security hole. – Frank Yellin Jan 19 '23 at 21:32
  • @FrankYellin - tables and columns can only be be built dynamically in this way, you can't parameterise a table. All that means is that the substitutions should be dictated by the code, not user input. Such as building a query from ORM meta-data (I've done that a lot, safely and securely, including using ORM meta-data as a white-list). – MatBailie Jan 19 '23 at 21:52
  • @MatBailie. If you know *exactly* what you're doing, it's safe. And even then, I've seen "safe" code suddenly used in ways that the original author didn't expect. Don't use construct SQL strings unless there really is no other way. – Frank Yellin Jan 20 '23 at 01:19
  • Thank you to all the comments above, this solve my issue and also made me aware of potential sec issues, although I think those issues don't apply here just by the situation of it. But thank you. – EAA Jan 20 '23 at 14:08

1 Answers1

1
sql = "SELECT {{column}} FROM {table}"

sql = sql.format(table="my_table")

print(sql)

sql = sql.format(column="my_column")

print(sql)

Or...

sql = "SELECT {column} FROM {table}"

sql = sql.format(table="my_table", column="{column}")

print(sql)

sql = sql.format(column="my_column")

print(sql)

That said, I'd recommend not actually passing the string around and doing partial replacements on it.

Instead, pass a dictionary around, add the replacements to the dictionary, and call format just once...

Then you don't need to add arbitrary {} around the token you don't want to replace, or be aware that it exists in order to replace it with itself.

sql = "SELECT {column} FROM {table}"

parts = dict()

parts["table"] = "my_table"

# more code here

parts["column"] = "my_column"

print(sql.format(**parts))

NOTE:

As per other warnings here...

NEVER do this with user supplied text.

  • Literal values should be supplied using parameterisation / prepared statements.

Only ever do this when you are in complete control of the potential values in the dictionary, such as deriving the columns, etc, from ORM meta-data, or some other white list.

MatBailie
  • 83,401
  • 18
  • 103
  • 137
  • Thank you Mat, this answered my question and gave me insights into how to do it in other ways. I am aware of the sec issues, but don't think they apply to this situation. Thank you again. – EAA Jan 20 '23 at 14:09