3

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?

Community
  • 1
  • 1

2 Answers2

5

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)))
Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
  • Now I am getting this error: `TypeError: not all arguments converted during string formatting` :| –  Dec 05 '15 at 17:45
  • @Azazelroman: did you check your database adapter documentation? Yours probably expected `%s` for the placeholders. – Martijn Pieters Dec 05 '15 at 17:45
  • @martijin Oh, but as i said in the question, i got the same error while using `%s`: `mysql_exceptions.ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MyS QL server version for the right syntax to use near '))' at line 1")` –  Dec 05 '15 at 17:48
  • @Azazelroman: you didn't use them as SQL parameters then. I've swapped out the `?` placeholders with `%s` placeholders to illustrate. – Martijn Pieters Dec 05 '15 at 17:49
  • @Azazelroman: where in my answer does the SQL contain double `))` parentheses? You are not executing the same code I posted. :-) – Martijn Pieters Dec 05 '15 at 17:50
  • I apologize, i wasn't able to comprehend your last comment, here's what i did `QUERY = 'INSERT INTO test_table (col1, col2, col3, col4) VALUES (%s, %s, %s, %s)'` `cursor.execute(QUERY, (var1, var2, var3, var4))` And i would like to point out again that `var4` is a list which contains `", '` inside it, which i guess is creating chaos? –  Dec 05 '15 at 17:53
  • Can you update your question to show exactly what each `var*` contains? You can't use a list or other container as one of those values. – Martijn Pieters Dec 05 '15 at 17:55
  • If `var4 = u'foo foo bar bar "foo_bar" but I can\'t bar'`, then that's no problem, that's not a container, that's a string, and it'll be escaped properly. – Martijn Pieters Dec 05 '15 at 17:56
  • I implemented your updated answer. Thanks for the update. I was unable to phrase my question properly. But now i am getting 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 '%s, %s, %s, %s)' at line 1")` –  Dec 05 '15 at 18:08
  • @Azazelroman: can you create a pastie or similar with the code that produces that error? It looks as if you are calling `cursor.execute()` without parameters, thus leaving the placeholders unchanged for MySQL to interpret as SQL code.. – Martijn Pieters Dec 05 '15 at 18:12
  • I am getting 'ValueError: Could not process parameters' and using python 3. What did I do wrong? – Agnel Vishal Jul 07 '18 at 13:24
  • @AgnelVishal sorry, without *all* of the info I asked for, I really can’t help you. – Martijn Pieters Jul 07 '18 at 13:27
  • 'sql=INSERT INTO test (text) VALUES (%s); cursor.execute(sql, (name))' is the statement and I am using mariadb.@Martijn Pieters – Agnel Vishal Jul 07 '18 at 13:31
  • 1
    `(name)` is not a single-element tuple. It’s just a grouped expression. You need to add a comma: `(name,)`, or use a list: `[name]`. – Martijn Pieters Jul 07 '18 at 13:38
0
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

Agnel Vishal
  • 564
  • 6
  • 13