CODEWITHSUNDEEP
pythonmysqlmariadbprepared-statement
William Osborne
William Osborne

Reputation: 71

Unicode and incorrect number of arguments errors with Python 3 and MySQL connector

I'm trying to execute a simple SELECT query on a MySQL(MariaDB) database in Python 3.

I'm getting very weird errors

        query = (
            'SELECT user_id, user_password_hash, user_password_salt '
            'FROM users '
            'WHERE user_username = "%s"'
        )

        print(f'Trying login, username {username}, password {password}')

        try:
            cursor = self.connection.cursor(prepared=True)
            cursor.execute(query, (username,))

            results = cursor.fetchone()

            print(results)
            ...

        except Error as e:
            print('Error in login query: ' + str(e))
            raise e

Running this code gives me the following error: Error in login query: 1210: Incorrect number of arguments executing prepared statement

I don't understand - it requires one argument, and I've given it one argument. Removing the comma in the tuple in execute() doesn't fix this problem.

Now, if I remove the quotes from the query, I get an entirely different error:

        query = (
            'SELECT user_id, user_password_hash, user_password_salt '
            'FROM users '
            'WHERE user_username = %s'
        )

UnicodeDecodeError: 'utf-8' codec can't decode byte 0x9b in position 0: invalid start byte

The above exception was the direct cause of the following exception:

SystemError: <class 'UnicodeDecodeError'> returned a result with an error set

The table is using utf8mb4, so that shouldn't be a problem.

Also, running SELECT * FROM users WHERE user_username="jeff" in the MySQL console works fine.

This should be a really trivial query, but I've got absolutely no idea what's going on - any help would be greatly appreciated.

EDIT: When experimenting in an isolated Python process, I reproduced the problem by running this: 

import mysql.connector

conn = mysql.connector.connect(host="localhost",
                               database="lucidlab",
                               user="lucidlab",
                               password="lucidlab")

cur = conn.cursor()
cur.execute("SELECT * FROM users WHERE user_username='jeff'")

results = cur.fetchall() # ERROR
print(results)

This makes me think that perhaps it's a database encoding problem?

EDIT: I've solved the problem. Please see my answer below.

Upvotes: 1

Views: 227

Answers (3)

William Osborne
William Osborne

Reputation: 71

I've found the problem. Fixing it required looking at the schema, which I unfortunately didn't post - lesson learned.

My schema contained the following:

CREATE TABLE users (
...
  user_password_hash BINARY(20) NOT NULL,
  user_password_salt BINARY(20) NOT NULL,
...
);

Running a SELECT * FROM users... query of any kind retrieves this binary data, which Python helpfully(!) immediately converts to a UTF-8 string... which fails, as the salt is bytes produced from os.urandom().

I fixed this by instead using sha256_crypt from the passlib library, and storing my encoded hash+salt combination as a CHAR(77).

Thanks to those who helped - unfortunately, I was thinking about the problem too narrowly, and had therefore phrased my question as such.

Upvotes: 1

Aniket singh
Aniket singh

Reputation: 138

query = """SELECT user_id, user_password_hash, user_password_salt FROM users WHERE user_username = %s"""

use this it will work..

Upvotes: 0

Nox
Nox

Reputation: 1

Try using double quotation marks:

    query = ("SELECT user_id, user_password_hash, user_password_salt "
        "FROM users "
        "WHERE user_username = %s"
    )

or tripple quotations:

    query = ("""SELECT user_id, user_password_hash, user_password_salt 
        FROM users 
        WHERE user_username = %s"""
    )

Upvotes: 0

Related Questions