While I'm able to update my mysql database to an int but I can't input a string

Question

Edit => Problem resolve : I changed my query to this and now it's working thank you yall:

    query_update = ("UPDATE comments SET label = '%s' WHERE id = %s"%(new_label, id))

I'm working on a school project to update a mysql database thru a form in a flask app.

While I run my query updating a column to an integer it works, but when my query is a string it doesn't.

I only know a little about mysql and even less about mysql workench.

Thanking in advance.

Here is my code :

cur_update = cnx.cursor(buffered=True)

#working
query_update = ("UPDATE comments SET label = %s WHERE id = %s"%(1, id))
#working
query_update = ("UPDATE comments SET label = %s WHERE id = %s"%("1", id))
#not working
query_update = ("UPDATE comments SET label = %s WHERE id = %s"%("test", id))
#returing the following error
mysql.connector.errors.ProgrammingError: 1054 (42S22): Unknown column 'test' 
in 'field list'

cur_update.execute(query_update)
cnx.commit()
cur_update.close()
cnx.close()

I tried several datatype for the concern column but every one of them return the same error.

You should pass your variables separately as query parameters instead of trying to glue them into your SQL. https://xkcd.com/327/ — khelwood, Jul 07 '18 at 22:27

John Gordon · Accepted Answer · 2018-07-08T00:30:06.803

0

SQL queries generally require that strings be enclosed in single quotes.

Your UPDATE statement evaluates to this:

UPDATE comments SET label = test WHERE id = 1

Since test isn't in quotes, SQL expects it to be the name of a column.

Put quotes around the value and it will work:

query_update = ("UPDATE comments SET label = '%s' WHERE id = %s" % ("test", id))

edited Jul 08 '18 at 00:30

answered Jul 07 '18 at 22:27

John Gordon

29,573
7
33
58

seems we got a troll getting fun with proper answers. – Alex Jul 07 '18 at 22:31
`c.execute("UPDATE comments SET label = '%s' WHERE id = %s", ("test", id))` is much more recommended – PRMoureu Jul 08 '18 at 05:57

score -2 · Answer 2 · answered Jul 07 '18 at 22:25

-2

just quote it. string values must be quoted in SQL

query_update = ("UPDATE comments SET label = %s WHERE id = %s"%("'test'", id))

please notice the single quote before and after the value.

and by the way. doing it this way is insecure. use prepared statement with binding parameter.

answered Jul 07 '18 at 22:25

Alex

189
1
7

well got -2 so long for answering such question. – Alex Jul 07 '18 at 22:29
"doing it this way is insecure. use prepared statement with binding parameter." ? – PRMoureu Jul 07 '18 at 22:50
@PRMoureu improper handling of SQL query parameter can lead to SQL Injection security flaws, you can have a description here https://en.wikipedia.org/wiki/SQL_injection the principle of prepared statement can be found here too https://en.wikipedia.org/wiki/Prepared_statement – Alex Jul 08 '18 at 11:29
1

so why not suggesting this way rather than a simple string interpolation ? – PRMoureu Jul 08 '18 at 11:31
@PRMoureu simple, the level of the question indicated that the person wasn't really aware or the security implications nor the 'usual' SQL API, so i wanted him to at least understand why it didn't work and didn't want to directly add security concern, lastly tying it on my phone was tedious, that's why i edited it when i had time on my desktop. anyway, case close as it seems to have worked for him :) – Alex Jul 23 '18 at 11:54

While I'm able to update my mysql database to an int but I can't input a string

2 Answers2