-2

I am running a Python bot which connects to a SQL database and updates values in tables.

I have the following code:

mycursor.execute("SELECT * FROM reputation WHERE username = '" + str(critiquer) + "'")

I am receiving the error "...check the manual that corresponds to your MySQL server version for the right syntax to use near ''' at line 1"

I've done some debugging and there is no problem with the string "critiquer," and the row exists in the table.

Would appreciate some insight into this problem.

quibblify
  • 395
  • 2
  • 6
  • 13
  • `critiquer` may contain a single quote, thus the error. – Eduard Sep 06 '16 at 01:47
  • @EduardDaduya `critiquer` does not contain a single quote. I've also double checked this by executing the query without a variable, and I am still receiving the error message. – quibblify Sep 06 '16 at 01:48
  • 4
    You should be using parameterized queries to avoid this sort of problem. – Gordon Linoff Sep 06 '16 at 01:48
  • 2
    Paramaterized queries would both solve the problem of dangling quotes and protect you from SQL injection. – Tim Biegeleisen Sep 06 '16 at 01:49
  • Similar: http://stackoverflow.com/questions/775296/python-mysql-parameterized-queries – Eduard Sep 06 '16 at 01:50
  • As a very simple debugging step, did you try assembling the query string and then printing it, before executing it? – John Gordon Sep 06 '16 at 02:08
  • @JohnGordon yes, it seems to print fine. – quibblify Sep 06 '16 at 02:09
  • And did you try executing the printed sql statement? – Eduard Sep 06 '16 at 02:09
  • When you printed the statement, critiquer didn't have any quotes or other odd characters in it? Did you try pasting the query into a SQL client program and executing it there? – John Gordon Sep 06 '16 at 02:11
  • @EduardDaduya yes, I did try that. – quibblify Sep 06 '16 at 02:12
  • @JohnGordon I checked it with an SQL client program and it worked fine. There were no weird characters in the critiquer string. – quibblify Sep 06 '16 at 02:13
  • Can you post the assembled query? I don't see how it can execute fine inside a SQL client but have trouble inside the python code. – John Gordon Sep 06 '16 at 02:14
  • @JohnGordon [Here is a picture of the execution and error message I receive](http://imgur.com/HTKeQKh) If you don't want to look at the picture, the assembled query is `SELECT * FROM reputation WHERE username = 'ryanbtw'` I've also tested this with different usernames that have rows and I am getting the same error message. – quibblify Sep 06 '16 at 02:19
  • Does MySQL expect commands to be terminated with a semicolon? Is `username` a reserved word? – John Gordon Sep 06 '16 at 02:35
  • What is the MySQL server version? And what's your Python version? – Stefan Pochmann Sep 06 '16 at 02:38

1 Answers1

1

Try using a parametrized query:

mycursor.execute("SELECT * FROM reputation WHERE username = '?'", critiquer)
Rushy Panchal
  • 16,979
  • 16
  • 61
  • 94