1

I'm trying to execute thi query:

SELECT '23.34.67.0/22' CONCAT(DAY_31, 'hello') DAY_31 FROM Jule

using pymysql. My code is:

cursor.execute("SELECT %s CONCAT(%s, %s) %s FROM Jule", (p, 'DAY_' + _day, as_tmp, 'DAY_' + _day))

But python adds single quote and return a syntax error

"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 '('DAY_31', 'hello') 'DAY_31' FROM Jule' at line 1"

DAY_31 is a column of Jule Schema

Brodario
  • 23
  • 5
  • Did you check string escaping? Does [this](https://stackoverflow.com/questions/3617052/escape-string-python-for-mysql) question point you in the right direction? What I mean by string escaping is that there seems to be an automatic unwanted escaping done on your string and there might be a way to avoid that in the cursor.execute func. – MikeMajara Oct 02 '19 at 11:45
  • it returns "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 '('\\'DAY_31\\'', '6762') '\\'DAY_31\\'' FROM Jule' at line 1" – Brodario Oct 02 '19 at 11:54
  • ok, a comma missing 'cursor.execute("SELECT %s, CONCAT(%s, %s) %s FROM Jule", (p, 'DAY_' + _day, as_tmp, 'DAY_' + _day)) – Brodario Oct 02 '19 at 12:05
  • Shouldn't it be: `SELECT '23.34.67.0/22', CONCAT(DAY_31, 'hello'), DAY_31 FROM Jule`, i.e. aren't you missing commas? – Booboo Oct 02 '19 at 12:31

3 Answers3

1

If memory serves, ? in place of %s might do the trick.

sjp
  • 382
  • 4
  • 15
  • thanks for the answer, but it returns 'TypeError: not all arguments converted during string formatting' – Brodario Oct 02 '19 at 11:43
0

The reason that you end up with quotes is because cursor.execute adds these around values you pass in as arguments. This is totally appropriate for your second argument, because if the value 'hello' was inserted into the query as-is you would end up with a query like this:

 SELECT '23.34.67.0/22' CONCAT(DAY_31, hello) DAY_31 FROM Jule

and you would have errors telling you that MySQL can't identify what hello should refer to.

Obviously though, this is not appropriate for situations where you want to pass in field names, or any other part of the query that is not a primitive string value. In these cases, you will need to splice them into your string before executing the query. One way you could do that is with f-strings, but there are other alternatives as well. Here's your cursor.execute line with the field names spliced in using f-strings:

cursor.execute(f"SELECT %s, CONCAT({'DAY_'+_day}, %s) {'DAY_'+_day} FROM Jule", (p, as_tmp))

Notice that I've removed the 'DAY_'+_day from the arguments list as well.

Important note:

While this should work like this (although I think you also needed an extra comma after SELECT '23.34.67.0/22', which I've added in the example above), it's very important that if day has a value that originates from outside of your application (e.g. passed in by a user in a form field) that you make sure it is exactly in the format you want before splicing it in to your query. Checking that the string value is an integer could be one way to do that. The reason that it is important is that without this, your application could be prone to SQL injection, which would potentially allow users to run arbitrary SQL on your database. If the value of day is calculated solely by your application, you shouldn't need to worry about this.

Rob Streeting
  • 1,675
  • 3
  • 16
  • 27
-1

Actually what is happening has nothing to do with cursor.execute. It is just the way you are formatting the string in python.

We should first note that %s %d can be specific to SQL, and that these can also be used in python for string formatting.

So taking this into account, I think you have no need to use %s for string formatting (which on top does not help with readability). And since you are cursor.executeing straight to your database, you could format your string straight away in python.

Small check to see whats happening:

p = "23.34.67.0/22"
as_tmp = "hello"
_day = "3"
print("SELECT %s CONCAT(%s, %s) %s FROM Jule", (p, 'DAY_' + _day, as_tmp, 'DAY_' + _day))
# output
# SELECT %s CONCAT(%s, %s) %s FROM Jule ('23.34.67.0/22', 'DAY_3', 'hello', 'DAY_3')
# ^^^^ so this is what is being sent in cursor.execute (with all the quotes and so on)

If you format with an f-string you will increase readability, and you should get rid of your problem with the quotes

print(f"SELECT '{p}' CONCAT(DAY_{_day}, '{as_tmp}') DAY_{_day} FROM Jule")
# output
# SELECT '23.34.67.0/22' CONCAT(DAY_3, 'hello') DAY_3 FROM Jule

So the solution could be: 

cursor.execute(f"SELECT '{p}' CONCAT(DAY_{_day}, '{as_tmp}') DAY_{_day} FROM Jule")
MikeMajara
  • 922
  • 9
  • 23
  • Thanks for the answer but the problem was a missing comma. Anyway I'll keep in mind your answer – Brodario Oct 02 '19 at 12:34
  • 1
    This answer leaves the application prone to SQL injection if `as_tmp` could originate from outside the application. The original approach was fine for string data values, but there's no way of avoiding splicing in field names like you suggested. – Rob Streeting Oct 02 '19 at 12:35