If you're using the official mysql-connector-python package from MySQL and you're satisfied with printing the exact SQL after it was executed, then print(cursor._executed).
They don't seem to expose a simple processing function to display what would be executed, you'd have to lift some code from around here: https://github.com/mysql/mysql-connector-python/blob/master/lib/mysql/connector/cursor.py#L539 and make your own function if you need that.
You could approximate it somewhat with code like this:
def render_sql(sql, args):
quoted = list()
for arg in args:
if type(arg) in (int, float):
quoted.append(str(arg))
elif type(arg) is str:
quoted.append(f"'{arg}'")
else:
raise Exception(f"got an unknown type: {type(arg} for SQL param: {arg}")
return sql % tuple(quoted)
sql = "SELECT column FROM table WHERE id=%s and name=%s"
print(render_sql(sql, (1, 1)))
# SELECT column FROM table WHERE id=1 and name=1
print(render_sql(sql, (1, 1.5)))
# SELECT column FROM table WHERE id=1 and name=1.5
print(render_sql(sql, (1, "test")))
# SELECT column FROM table WHERE id=1 and name='test'
print(render_sql(sql, (1, "text with spaces")))
# SELECT column FROM table WHERE id=1 and name='text with spaces'
You can extend that with additional types. But, as jarmod said, use this only for debugging, do not use it to execute on the database without reviewing the rendered SQL first.
Update:
I came up with this, but it's specific to the PyMySQL implementation and not general:
from pymysql.connections import Connection
def render_sql(operation, params):
connection = Connection(defer_connect=True)
connection.server_status = 0
return connection.cursor().mogrify(operation, params)
So this:
render_sql(
"SELECT * FROM Users WHERE id = %s and name = %s and password = %s",
(1, "user", "I'm past")
)
yields:
SELECT * FROM Users WHERE id = 1 and name = 'user' and password = 'I\'m past'
Security Demo:
For those that aren't familiar with SQL Injection attacks.
I used the above function and a naive implementation where the %s placeholders are simply replaced with the values passed in.
def naive_sql(operation, params):
return operation % params
Two "parameterized" queries:
good = "SELECT * FROM Users WHERE name = %s AND password = %s"
bad = "SELECT * FROM Users WHERE name = '%s' AND password = '%s'"
Example 1:
params = ("bobby", "PaSsWoRd")
print("Good:", render_sql(good, params))
print(" Bad:", naive_sql(bad, params))
print("so far so good")
Output:
Good: SELECT * FROM Users WHERE name = 'bobby' AND password = 'PaSsWoRd'
Bad: SELECT * FROM Users WHERE name = 'bobby' AND password = 'PaSsWoRd'
so far so good
Example 2:
params = ("bobby", "' OR 'a' = 'a")
print("Good:", render_sql(good, params))
print(" Bad:", naive_sql(bad, params))
print("just granted access without a password")
Output:
Good: SELECT * FROM Users WHERE name = 'bobby' AND password = '\' OR \'a\' = \'a'
Bad: SELECT * FROM Users WHERE name = 'bobby' AND password = '' OR 'a' = 'a'
just granted access without a password
Example 3:
params = ("bobby", "' ; UPDATE USERS SET password = 'letmein' WHERE name = 'bobby' ; -- ")
print("Good:", render_sql(good, params))
print(" Bad:", naive_sql(bad, params))
print("updated bobby's password")
Output:
Good: SELECT * FROM Users WHERE name = 'bobby' AND password = '\' ; UPDATE USERS SET password = \'letmein\' WHERE name = \'bobby\' ; -- '
Bad: SELECT * FROM Users WHERE name = 'bobby' AND password = '' ; UPDATE USERS SET password = 'letmein' WHERE name = 'bobby' ; -- '
updated bobby's password
Example 4:
params = ("bobby", "' ; DROP TABLE Users ; -- ")
print("Good:", render_sql(good, params))
print(" Bad:", naive_sql(bad, params))
print("your site is now down")
Output:
Good: SELECT * FROM Users WHERE name = 'bobby' AND password = '\' ; DROP TABLE Users ; -- '
Bad: SELECT * FROM Users WHERE name = 'bobby' AND password = '' ; DROP TABLE Users ; -- '
your site is now down
Obligatory XKCD: https://xkcd.com/327/
