Invalid syntax in executing SQL query using python

Question

I am trying to run the below SQL query using pymysql in python

cursor.execute(""SELECT count(1) from user_login WHERE user_id="+username + "and password="+password"")

Here username=abc and password=xyz are variables with values

I know there is a problem with quotation marks.Can someone suggest me the right way to do this?

You have an extra `"` in the beginning and the end of the query. — Guy, Jan 09 '20 at 05:31

score 0 · Accepted Answer · answered Jan 09 '20 at 05:32

The immediate problem is that your username and password inputs are not being properly escaped in single quotes. But, don't do that manually; instead, use a prepared statement:

sql = "SELECT COUNT(1) FROM user_login WHERE user_id = %s AND password = %s"
cursor.execute(sql, (username, password,))

A side problem: I see that you are comparing some password input directly against the password column of your user table, which would imply that you are storing clear text passwords. This is a big security vulnerability, and you should instead be hashing all passwords, and then storing the hash only in the table.

Invalid syntax in executing SQL query using python

1 Answers1