1

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()
panofish
  • 7,578
  • 13
  • 55
  • 96
  • 2
    Unless the dbname is being provided by an untrusted source, you can just use string substitution for this. – dano Jul 29 '14 at 18:34
  • 1
    @panofish I'm not sure why the Python module doesn't support this, but there's [probably](http://stackoverflow.com/q/15255694/2073595) [six](http://stackoverflow.com/q/6618344/2073595) [or](http://stackoverflow.com/q/5936806/20735950) [seven](http://stackoverflow.com/q/24945874/2073595) dupes of this question on SO, and all of them basically say "use regular string substitution". – dano Jul 29 '14 at 18:40
  • Thanks dano... I didn't see those. Should I delete this question? – panofish Jul 29 '14 at 18:47

0 Answers0