2

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

Gary
  • 143
  • 1
  • 2
  • 6

1 Answers1

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 ;-)

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • 1
    I know you are not a Pythoner but the modulo operator, `%`, for string interpolation is [de-emphasized (not deprecated *yet*)](https://stackoverflow.com/a/13452357/1422451) in Python. Recommendation is for `str.format` (Python 2.6+) or as OP uses, F-strings (Python 3.6+). – Parfait Mar 29 '20 at 18:30
  • Hmm... interesting, I wasn't aware of that. Good to know. Maybe I missed it somewhere in the documentation. Thank you Bill and Parfait! – Gary Apr 01 '20 at 02:53
  • How do I make it safe? is using an f-string enough? – baggiponte Oct 14 '22 at 13:38
  • 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. – Bill Karwin Oct 14 '22 at 14:17