MySQL & Python: Not all parameters were used in the SQL statement

Question

I've created the following database using MySQL:

CREATE TABLE tutors (
id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
email VARCHAR(320) NOT NULL,
description VARCHAR(400) NOT NULL,
image VARCHAR(150) NOT NULL, 
applyDate DATE NOT NULL)

where the image column stores the absolute paths to images I store in my file system.

However, when I try to insert user-inputted data such as

arguments = ("Name", "[email protected]", "desc", "C:\\Users\\path\\to\\image.png", "2020-08-23")

using Python with the code

cursor.execute("INSERT INTO tutors (name, email, description, image, applyDate) VALUES (?, ?, ?, ?, ?)", arguments)
conn.commit()

it gives me the following error:

mysql.connector.errors.ProgrammingError: Not all parameters were used in the SQL statement.

It works when I replace each ? with %s, but I've read that %s is vulnerable to SQL injection attacks, so I'd rather stay away from it if possible. Is there a reason ? isn't working?

You've read wrong. For prepared statements with the mysql.connector, %s is the placeholder, not ?. %s is subject to SQL Injection when it is not used as a placeholder for a prepared statement but instead used for textual substitution as in cursor.execute("SELECT y from t where x = '%s'" % some_value) instead of cursor.execute("SELECT y from t where x = %s", (some_value,)) — Booboo
– Booboo, Commented Aug 23, 2020 at 10:55

Nuno Mariz · Accepted Answer · 2020-08-25 09:29:42Z

0

The ? placeholder only works with prepared statement cursors, such as cursor = conn.cursor(prepared=True).

If you don't want or need to use prepared statements, use %s as placeholder. It's safe since their values are escaped and quoted before the query is executed.

answered Aug 25, 2020 at 9:29

Nuno Mariz

5993 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL & Python: Not all parameters were used in the SQL statement

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related