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.

Improve this question
8
  • 2
    The argment to the second format should be field=field_name Commented Jan 19, 2023 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?) Commented Jan 19, 2023 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? Commented Jan 19, 2023 at 21:27
  • I don't understand why you aren't using strings, e.g. field = 'field_name' Commented Jan 19, 2023 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. Commented Jan 19, 2023 at 21:32

1 Answer 1

Reset to default
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.

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

1 Comment

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.