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

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. xkcd.com/327 — khelwood
– khelwood, Commented Jul 7, 2018 at 22:27

John Gordon · Accepted Answer · 2018-07-08 00:30:06Z

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 8, 2018 at 0:30

answered Jul 7, 2018 at 22:27

John Gordon

33.8k9 gold badges48 silver badges72 bronze badges

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

2 Comments

Alex Over a year ago

seems we got a troll getting fun with proper answers.

PRMoureu Over a year ago

c.execute("UPDATE comments SET label = '%s' WHERE id = %s", ("test", id)) is much more recommended

Alex · Accepted Answer · 2018-07-07 22:25:14Z

-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 7, 2018 at 22:25

Alex

1892 silver badges8 bronze badges

5 Comments

Alex Over a year ago

well got -2 so long for answering such question.

PRMoureu Over a year ago

"doing it this way is insecure. use prepared statement with binding parameter." ?

Alex Over a year ago

@PRMoureu improper handling of SQL query parameter can lead to SQL Injection security flaws, you can have a description here en.wikipedia.org/wiki/SQL_injection the principle of prepared statement can be found here too en.wikipedia.org/wiki/Prepared_statement

PRMoureu Over a year ago

so why not suggesting this way rather than a simple string interpolation ?

Alex Over a year ago

@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 :)

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

5 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

5 Comments

Linked

Related