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

Question

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:\Users\artun\PycharmProjects\DENEMELER\venv\lib\site-packages\mysql\connector\cursor.py", line 569, in execute
    self._handle_result(self._connection.cmd_query(stmt))
  File "C:\Users\artun\PycharmProjects\DENEMELER\venv\lib\site-packages\mysql\connector\connection.py", line 553, in cmd_query
    result = self._handle_result(self._send_cmd(ServerCmd.QUERY, query))
  File "C:\Users\artun\PycharmProjects\DENEMELER\venv\lib\site-packages\mysql\connector\connection.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"

If they are strings you still have to enclose them in quotes inside the SQL query like this `"SELECT * FROM users WHERE username = '%s' AND password = '%s'"%(username,password)` — Matthew Barlowe, May 18 '19 at 20:50
I tried but it didn't work. Even if I enter the information correctly, "failure" is the result that should actually be true "Logged In" when entered incorrectly "Failure". the program now really gives the wrong output — H4X, May 18 '19 at 21:24

score 1 · Accepted Answer · answered May 18 '19 at 21:10

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))

score 1 · Answer 2 · edited Jan 19 '21 at 14:37

1

Try this code below:

 """SELECT * FROM `users` WHERE `username` '{}' AND `password` '{}'""".format(username,password))

edited Jan 19 '21 at 14:37

William Baker Morrison

1,642
4
21
33

answered Jan 19 '21 at 09:39

babiya

11
1

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

2 Answers2

Linked