I have problems with Python sqlite3 library, for protections against SQL Injections

I’m trying to prevent SQL injections with code in Python, using library sqlite3.

I went accross all websites in existence concerning this issue, but none of them are fixing my problem.

This statement is here is executed via an email_in_use(email:str) function, to check if an email does exist in the creditentials table (I know I could use count to do this, or even anything else, but the actual problem here is about protecting the statement, and avoiding code to be injected into it, by malicious users).

cursor.execute("SELECT email FROM creditentials WHERE email = %s;", (email,))

The %s is supposed to make the email str : literal, avoiding any injection.

However, it does not work as intended and raises the following OperationalError :

An exception occured : OperationalError near "%": syntax error File "...serverserverSystemplayers.py", line 175, in email_in_use
    cursor.execute("SELECT email FROM creditentials WHERE email=%s;", (email,))   File "...serverserverSystemplayers.py", line 377, in <module>
    print(email_in_use("email@e.g"))

Also, when adding apostrophes surrounding the %s like this :

cursor.execute("SELECT email FROM creditentials WHERE email = '%s';", (email,))

A different exception then occurs :

    An exception occured : ProgrammingError
Incorrect number of bindings supplied. The current statement uses 0, and there are 1 supplied.
  File "...serverserverSystemplayers.py", line 175, in email_in_use
    cursor.execute("SELECT email FROM creditentials WHERE email = '%s';", (email,))
  File "...serverserverSystemplayers.py", line 377, in <module>
    print(email_in_use("email@e.g"))

These are unexcepted errors, and I’d like to know why are they still raising. Does it have something to do with the library I’m using ? Maybe sqlite3 is obsolete.

Thank y’all for your answers !

PS : This is my first question of stack overflow, so please be lenient and do not hesitate to improve this post :)

Answer

%s is not a valid sqlite placeholder when using parameterized SQL queries.

You may either use the qmark style:

cursor.execute("SELECT email FROM creditentials WHERE email = ?", (email,))

or the named style:

cursor.execute("SELECT email FROM creditentials WHERE email = :myvar", {"myvar": email})

More details can be found in the python docs: https://docs.python.org/3/library/sqlite3.html

and in the sqlite docs: https://www.sqlite.org/c3ref/bind_blob.html

Advertisement

Answer