CODEWITHSUNDEEP
pythonmysql
user5528951
user5528951

Reputation:

Escaping quotes for MySQL in python

Following the advice on this thread, I am storing my list as string type in MySQL database, but, I'm facing this error: 

_mysql_exceptions.ProgrammingError: (1064, '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 \'foo_bar" but I can\\\'t bar.\', u\'high\', 0]]")\' at line 1')

on some list entries like this one:

var1 = 'Name'
var2 = 'Surname'
var3 = 15
var4 = [u'The Meter', [[u'Black', u'foo foo bar bar "foo_bar" but I can\'t bar', u'high', 0]]]

I figured that's because there's a double quote at the beginning of foo_bar and I am using the following code to make entries in the database:

SQL = 'INSERT INTO test_table (col1, col2, col3, col4) VALUES ("{}", "{}", "{}", "{}")'.format(var1, var2, var3, var4)
cursor.execute(SQL)

And the double quotes are not being escaped. If i remove the double quotes from:
("{}", "{}", "{}", "{}"), I get the same error, I tried using a different string formatting, (using %s) but that didn't work either.
Any ideas?

Upvotes: 3

Views: 5706

Answers (2)

Agnel Vishal
Agnel Vishal

Reputation: 572

SQL = 'INSERT INTO test_table (col1, col2, col3, col4) VALUES ("{!a}", "{!a}", "{!a}", "{!a}")'.format(var1, var2, var3, str(var4))
cursor.execute(SQL)

{!a} applies ascii() and hence escapes non-ASCII characters like quotes and even emoticons.

Check out Python3 docs

Upvotes: 0

Martijn Pieters
Martijn Pieters

Reputation: 1121914

Do not use string formatting to interpolate SQL values. Use SQL parameters:

SQL = 'INSERT INTO test_table (col1, col2, col3, col4) VALUES (%s, %s, %s, %s)'
cursor.execute(SQL, (var1, var2, var3, var4))

Here the %s are SQL parameters; the database then takes care of escaping the values (passed in as the 2nd argument to `cursor.execute) for you.

Exactly what syntax you need to use depends on your database adapter; some use %s, others use ? for the placeholders.

You can't otherwise use Python containers, like a list, for these parameters. You'd have to serialise that to a string format first; you could use JSON for that, but then you'd also have to remember to decode the JSON into a Python string again when you query the database. That's what the answers to the other question tried to convey.

For example, if var4 is the list, you could use:

cursor.execute(SQL, (var1, var2, var3, json.dumps(var4)))

Upvotes: 5

Related Questions