I am using mysql.connector to execute some simple SQL statements. In this example I am creating a database schema. I want to pass the database as a variable and use substitution in the SQL.
I believe the following is proper technique to avoid SQL injection.
It seems that mysql.connector is flawed when you try to substitute database or table names which normally would be wrapped with backticks like this mydb in case of spaces.
If I don't put backticks around %s, then it fails with a syntax error.
If I do put backticks around %s, then it creates a database with single quotes in the name like 'mydb'. I just want mydb as the name.
The only viable method I have found is to use standard python substitution to create the entire SQL statement and pass that to cursor.execute, but I assume that technique is frowned upon.
I have already used mysqldb and that works successfully, but I'd like to use mysql.connector.
Can this be done with mysql.execute substitution?
import mysql.connector
import sys
dbname = "mydb"
#========================
try:
db = mysql.connector.connect(host="localhost", user="alan", password="***")
except mysql.connector.Error as e:
raise SystemExit("MySQL Error %d at line %d: %s" % (e.errno, sys.exc_info()[2].tb_lineno, e.msg.decode("utf8")))
cursor = db.cursor()
try:
cursor.execute("""
CREATE DATABASE IF NOT EXISTS %s
""", (dbname,) )
except mysql.connector.Error as e:
raise SystemExit("MySQL Error %d at line %d: %s" % (e.errno, sys.exc_info()[2].tb_lineno, e.msg.decode("utf8")))
db.commit()
cursor.close()
db.close()
