Use Parameterized Query with Python and MySQL

Question

I'm using Python + MySQL and want to use parameterized query. I'm stuck. I've encountered an error and can't figure out how to solve it. I've spent a day, checked dozens of articles, used various options (sinle quotes, double quotes, prepared statements) and still no luck.

Requirements: use Parameterized Query

Here is basic demo of the issue:

#!/usr/bin/python3
import mysql.connector as mysql

conn = mysql.connect(host=server, user=username, passwd=password, autocommit=True)
try:
    create_database_query = "CREATE DATABASE %s;"
    db_name = "BOOKS"

    cursor = conn.cursor()

    print(f"Creating {db_name} database... ", end='')
    cursor.execute(create_database_query, (db_name,))
    print("Success")

except mysql.Error as error:
    print("Parameterized query failed {}".format(error))

Output: Creating BOOKS database... Parameterized query failed 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''BOOKS'' at line 1

So it looks like it uses too many quotes (2 single quotes on each side). The code above works fine if I change the following line: create_database_query = "CREATE DATABASE %s;" and put backtick around %s

The problem that now it creates a database but with invalid chars - 'BOOKS' (quotes are now part of db name). Duh...

If I use prepared statements then the same issue occurs but slightly different error message: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '?' at line 1

Environment:

MacOS Catalina
Python 3.8
PyCharm 2019.3 IDE
MySQL 8.0.19
mysql-connector-python module 8.0.19

What is going on? Any ideas? Thanks

Bill Karwin · Accepted Answer · 2020-03-30 14:46:55Z

3

You can't use query parameters for identifiers (like a database name or table name or column name).

Query parameters can be used only in place of a constant value — a quoted string, quoted date/time, or a numeric value. Not identifiers, expressions, SQL keywords, etc.

To combine a database name with your CREATE DATABASE statement, you have to format it into the string in a way that forms the full statement before it is sent to MySQL.

db_name = "BOOKS"
create_database_query = "CREATE DATABASE %s;" % db_name

cursor.execute(create_database_query)

Because this creates a risk of SQL injection when you format variables into your string, it's up to you to make sure the db_name is safe.

Update: Thanks to @Parfait for the reminder about current best practices of string-formatting.

Prefer:

db_name = "BOOKS"
create_database_query = "CREATE DATABASE {};".format(db_name)

Or F-strings:

db_name = "BOOKS"
create_database_query = f"CREATE DATABASE {db_name};"

(In other words, Python has become Ruby ;-)

edited Mar 30, 2020 at 14:46

answered Mar 29, 2020 at 17:56

Bill Karwin

567k87 gold badges709 silver badges869 bronze badges

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

4 Comments

Parfait Over a year ago

I know you are not a Pythoner but the modulo operator, %, for string interpolation is de-emphasized (not deprecated yet) in Python. Recommendation is for str.format (Python 2.6+) or as OP uses, F-strings (Python 3.6+).

Gary Over a year ago

Hmm... interesting, I wasn't aware of that. Good to know. Maybe I missed it somewhere in the documentation. Thank you Bill and Parfait!

baggiponte Over a year ago

How do I make it safe? is using an f-string enough?

Bill Karwin Over a year ago

No, using an f-string does nothing to make it safe. What I mean is that you need to check the content of your variable db_name to make sure it is a legal identifier that matches the name of your new schema, doesn't conflict with an SQL reserved keyword, etc.

Collectives™ on Stack Overflow

Use Parameterized Query with Python and MySQL

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related