CODEWITHSUNDEEP
pythonmysql
ivorytux
ivorytux

Reputation: 359

python inserting single quotes (') around MySQL table name

I have a database called project1 with the following tables:

_systbl1
_systbl2
_systbl3
dataset1
dataset2
dataset3

MySQL user odbc will need to be granted SELECT permissions on dataset% tables whenever a new one is added.

To accomplish this, I'm using a simple python script, like so:

#!/usr/bin/python
import MySQLdb

db = MySQLdb.connect(
    host="localhost",
    user="user",
    passwd="pass",
    db="project1"
    )

    # Create Cursor Object
cur = db.cursor()

# get list of tables beginning with dataset
cur.execute("SHOW TABLES FROM project1 LIKE 'dataset%';")

# run GRANT statement for each table
for row in cur.fetchall() :
    cur.execute("GRANT SELECT ON `project1`.`%s` TO `odbc`@`localhost`;", row)
    cur.execute("GRANT SELECT ON `project1`.`%s` TO `odbc`@`%`;", row)

Unfortunately, it gives me the following error:

Traceback (most recent call last):
  File "mysql_query.py", line 20, in <module>
    cur.execute("GRANT SELECT ON `project1`.`%s` TO `odbc`@`localhost`;", row)
  File "/usr/lib/python2.7/dist-packages/MySQLdb/cursors.py", line 174, in execute
    self.errorhandler(self, exc, value)
  File "/usr/lib/python2.7/dist-packages/MySQLdb/connections.py", line 36, in defaulterrorhandler
    raise errorclass, errorvalue
_mysql_exceptions.ProgrammingError: (1146, "Table 'project1.'dataset1'' doesn't exist")

As you can see in the last line of the error, the problem is that python is putting a single quote around the table names when generating the query.

What am I missing here?

Upvotes: 4

Views: 2598

Answers (3)

user2521546
user2521546

Reputation: 23

sql = """CREATE TABLE IF NOT EXISTS `""" + project +  """` ( `id` INT(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id`))"""

Upvotes: 1

Martijn Pieters
Martijn Pieters

Reputation: 1123970

Do not use SQL parameters for table names. SQL parameters are escaped by the database adapter to not be interpreted as anything but literal values.

You'll have to interpolate those yourself instead, but be absolutely certain that your table name does not hold untrusted data (prevent SQL injection attacks):

cur.execute("GRANT SELECT ON `project1`.`%s` TO `odbc`@`localhost`;" % row)
cur.execute("GRANT SELECT ON `project1`.`%s` TO `odbc`@`%%`;" % row)

(where the % character in the grant has been escaped by doubling it to %%).

Upvotes: 2

Explosion Pills
Explosion Pills

Reputation: 191789

Instead use:

cur.execute("GRANT SELECT ON `project`.`%s` TO `odbc`@`localhost`" % row)

This will not use the normal escaping of the input. Beware of a backtick in any of your table names, though.

Upvotes: 1

Related Questions