python - special characters in MySQL causing error

Question

Below image shows a code snippet:

            query="insert into mytable values ('%s','%s','%s','%s',%s,'%s')"
            value=(str(cr),str(cs),str(srv),str(cl),recipe["total_time"],image,name)
            print(name)
            cursor.execute(query%value)
            mydb.commit()

I have added a print(name) statement to show what exactly the "name" variable is. (its a string). On running, I get an error:

The "paneer ki sabji | .." is the value of name. I am inserting the data in "name" into the mysql table, under a column of type "nvarchar".

Is the error because of the special character "|"? If yes, how do we fix it, and why did the "nvarchar" not take care of it?

question from:https://stackoverflow.com/questions/65648391/special-characters-in-mysql-causing-error

Answer 1

It has nothing to do with special characters. You're making the same mistake as so many before you; the %s is not being used the same as the old-style string formatting. Do not use string formatting to insert parameters into queries - this is open to SQL Injection. It is unfortunate that the DB API also specifies %s as valid: https://www.python.org/dev/peps/pep-0249/#paramstyle. See also MySQL parameterized queries

Now there's another issue:

query="insert into mytable values ('%s','%s','%s','%s',%s,'%s')"

Count the %s. There's 6. Now count:

(str(cr),str(cs),str(srv),str(cl),recipe["total_time"],image,name)

That's a tuple of 7 values. So how do you expect that to work with 6 placeholders? If recipes is a pd.Series then you have even more issues, but hopefully it's not.

My guessed answer:

query="INSERT INTO mytable VALUES ('%s', '%s', '%s', '%s', '%s, '%s', '%s')"
value=(str(cr), str(cs), str(srv), str(cl), recipe["total_time"], image, name)
cursor.execute(query, value)