0

My problem: when creating a database using python's (2.x) MySQLdb module with parameters I get either access denied or invalid syntax depending on how I quote the database name.

Example (without parameters, to show it's working):

    $ python
Python 2.7.5 (default, Jun 24 2015, 00:41:19) 
[GCC 4.8.3 20140911 (Red Hat 4.8.3-9)] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> import MySQLdb
>>> MySQLdb.version_info
(1, 2, 5, 'final', 1)
>>> db = MySQLdb.connect(user='user', passwd='pass')
>>> c = db.cursor()
>>> c.execute("CREATE DATABASE IF NOT EXISTS foo")
>>> c.fetchall()
()

With parameters and no quotes:

>>> c.execute("CREATE DATABASE IF NOT EXISTS %s", ('foo', ))
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "build/bdist.linux-x86_64/egg/MySQLdb/cursors.py", line 205, in execute
  File "build/bdist.linux-x86_64/egg/MySQLdb/connections.py", line 36, in defaulterrorhandler
_mysql_exceptions.ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ''foo'' at line 1")

With parameters and double quotes (I've tried various combinations):

>>> c.execute('''CREATE DATABASE IF NOT EXISTS %s''', ('foo', ))
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "build/bdist.linux-x86_64/egg/MySQLdb/cursors.py", line 205, in execute
  File "build/bdist.linux-x86_64/egg/MySQLdb/connections.py", line 36, in defaulterrorhandler
_mysql_exceptions.ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ''foo'' at line 1")

With backticks it gives me an access denied which really puzzles me:

>>> c.execute('''CREATE DATABASE IF NOT EXISTS `%s` ''', ('foo', ))
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "build/bdist.linux-x86_64/egg/MySQLdb/cursors.py", line 205, in execute
  File "build/bdist.linux-x86_64/egg/MySQLdb/connections.py", line 36, in defaulterrorhandler
_mysql_exceptions.OperationalError: (1044, "Access denied for user 'user'@'localhost' to database ''foo''")

What am I doing wrong?

This is how I create the user:

    $ mysql -u root
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 1485
Server version: 5.5.44-MariaDB MariaDB Server

Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> grant usage, create, drop on foo.* to 'user'@'localhost' identified by 'pass' with grant option;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)
lorenzog
  • 3,483
  • 4
  • 29
  • 50
  • I'm not sure you can use MySQLdb's "safe" substitution for things like database creation. It's really only meant for substituting values, not meta-information like table names. You can't, for instance do something like `.execute("SELECT * from %s", (tablename,))`. You may have to do the table-name substitution yourself. – BrenBarn Nov 03 '15 at 18:38
  • @BrenBarn looks like you are right, still the error is really annoying and I was hoping in something simpler as I'm using this in a test so I'd like to avoid duplicate information – lorenzog Nov 03 '15 at 18:40
  • I marked as duplicate of another question that was also about substituting table names. (The type of SQL statement being run is different there, but the problem is the same.) – BrenBarn Nov 03 '15 at 18:43
  • @BrenBarn Learned something about security today.. :-) – Minnow Nov 03 '15 at 18:44
  • @BrenBarn my question duplicates the one you indicated (thanks!) but that one does not explain the access denied problem. Or is it related to the table name and how it is escaped? – lorenzog Nov 03 '15 at 18:48
  • @lorenzog: Hmmm, I'm not sure. It could be related to the escaping. But I think the point is more or less moot since you can't use that type of substitution anyway. If you still get an access error after switching to straight-up string substitution, maybe you can ask a separate question about that (since it will then no longer involve this parameter-substitution aspect). – BrenBarn Nov 03 '15 at 18:56
  • @BrenBarn fair enough. In fact, switching syntax doesn't return an access denied when creating the database. – lorenzog Nov 03 '15 at 19:07

0 Answers0