-2

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: enter image description here

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?

satan 29
  • 275
  • 2
  • 11
  • Just stop using string formatting to build queries. I'm bored of saying this; it's error-prone and _open to SQL Injection_. Look up parameterization – roganjosh Jan 09 '21 at 22:53
  • I could give you the fixed code if you hadn't posted images, btw. It would be a case of copy/paste and just editing it. But you didn't give your code as text – roganjosh Jan 09 '21 at 22:54
  • @roganjosh if thats the case ill put it in text. – satan 29 Jan 09 '21 at 22:56
  • You should always be posting code as text, for all questions. Otherwise, you're expecting people to type it all out again for you (for no good reason) – roganjosh Jan 09 '21 at 22:57
  • MySQL should not have an issue with the special character, what makes you think its the name that is causing the problem? What are the formats of the other fields, especially recipe["total time"] and image? – Alex Collette Jan 09 '21 at 22:58
  • @AlexCollette everything is varchar, total_time is int and name is nvarchar. – satan 29 Jan 09 '21 at 22:59

2 Answers2

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)
roganjosh
  • 12,594
  • 4
  • 29
  • 46
0

I think the problem is a missing space between "query" and "value" variables. Over this

  • from a pythonic point of view this is the old way to format strings
  • from a security point point of view this is not ok

The actual the method to do it is:

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

Check this out as reference.

Stefano Paviot
  • 130
  • 1
  • 6
  • It's not the "space" (they actually use `%`). They're trying to invoke the old style string formatting, instead of parameterization – roganjosh Jan 09 '21 at 23:05
  • It now gives me an error "not all parameters were used in the sql statement" – satan 29 Jan 09 '21 at 23:08
  • This happens because you are not specifying values for all the fields, I did write less '?' than needed.. Let's modify the sintax to: `query = "INSERT INTO mytable VALUES(?,?,?,?,?,?,?)" ` – Stefano Paviot Jan 10 '21 at 21:12