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?

Answer 1

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.