mysql.connector.errors.ProgrammingError: 1054 (42S22): Unknown column ‘X’ in ‘where clause’

This is my Script:

import mysql.connector
def loginsystem():
    db = mysql.connector.connect(host="127.0.0.1",
                                 user="root",
                                 passwd="",
                                 db="dbpython")

    cursor = db.cursor()

    loop = 'true'
    while (loop == 'true'):
        username = str(input("Username : "))
        password = str(input("Password : "))

        if (cursor.execute("SELECT * FROM users WHERE username = %s AND password = %s"%(username,password))):

            print("Logged İn")
        else:
            print("Failure")
    db.commit()
loginsystem()

I am installing an online system, but I have encountered a problem with the login system. How to Fix “Unknown column ‘x’ in where clause” or do you have any other code suggestions?

if (cursor.execute("SELECT * FROM users WHERE (username =?  password = ?) VALUES(?,?)"(username,password))):

I tried to do it with this method but didn’t

Traceback (most recent call last):
  File "C:/Users/artun/PycharmProjects/DENEMELER/Login System.py", line 21, in <module>
    loginsystem()
  File "C:/Users/artun/PycharmProjects/DENEMELER/Login System.py", line 15, in loginsystem
    if (cursor.execute("SELECT * FROM users WHERE username = %s AND password = %s"%(username,password))):
  File "C:UsersartunPycharmProjectsDENEMELERvenvlibsite-packagesmysqlconnectorcursor.py", line 569, in execute
    self._handle_result(self._connection.cmd_query(stmt))
  File "C:UsersartunPycharmProjectsDENEMELERvenvlibsite-packagesmysqlconnectorconnection.py", line 553, in cmd_query
    result = self._handle_result(self._send_cmd(ServerCmd.QUERY, query))
  File "C:UsersartunPycharmProjectsDENEMELERvenvlibsite-packagesmysqlconnectorconnection.py", line 442, in _handle_result
    raise errors.get_exception(packet)
mysql.connector.errors.ProgrammingError: 1054 (42S22): Unknown column 'oziboran123' in 'where clause'

Process finished with exit code 1

this is the output of the code, but I expected the output of the code to be “Logged In” or “Failure”

Answer

Consider parameterization and not string interpolation which involves a two-step process of prepared statement and then execution that binds parameters. Below uses two arguments of execute call: cursor.execute(query, params). Also, user and password are reserved words in MySQL which should be escaped with backticks.

Please note the parameter placeholder, %s, for the mysql.connector API should not be confused with Python’s modulo string format symbol (which by the way is the less preferred string formatting method in Python for more preferred str.format).

# PREPARED STATEMENT
sql = """SELECT * FROM `users`
         WHERE `username` = %s AND `password` =%s
      """

# EXECUTE WITH PARAMS
cursor.execute(sql, (username, password))