Search code examples
pythonmysqlsqlmysql-pythonpymysql

Invalid syntax in executing SQL query using python

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?

Solution

  • 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.