Python mysql.connector module, Passing data into string VALUES %s

Question

I'm having trouble passing data into %s token. I've looked around and noticed that this Mysql module handles the %s token differently, and that it should be escaped for security reasons, my code is throwing this error.

mysql.connector.errors.ProgrammingError: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '%s)' at line 1

If I do it like this:

sql_insert = ("INSERT INTO `Products` (title) VALUES(%s)"),(data)

I get a tuple error..

import mysql.connector
from mysql.connector import errorcode

cnx = mysql.connector.connect (user='userDB1', password='UserPwd1',
host='somedatabase.com', database='mydatabase1')
cursor = cnx.cursor()

sql_insert = ("INSERT INTO `Products` (title) VALUES(%s)")

data=('HelloSQLWORLD')

cursor.execute(sql_insert,data)

cnx.commit()


cnx.close()

alecxe · Accepted Answer · 2016-09-26 01:06:33Z

2

No, don't do it the way @Jeon suggested - by using string formatting you are exposing your code to SQL injection attacks. Instead, properly parameterize the query:

query = """
    INSERT INTO 
        Products 
        (title) 
    VALUES
        (%s)"""

cursor.execute(query, ('HelloSQLWORLD', ))

Note how the query parameters are put into a tuple.

edited Sep 26, 2016 at 1:06

answered Sep 26, 2016 at 1:01

alecxe

476k127 gold badges1.1k silver badges1.2k bronze badges

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

1 Comment

Ryan Dooley Over a year ago

Thank you, this was the answer I was looking for although @Jeon's solution would work, this method is best due to vulnerabilities.

Jeon · Accepted Answer · 2016-09-26 00:47:59Z

0

Pythonic string formatting is:

str1 = 'hello'
str2 = 'world'
'%s, %s' % (str1, str2)

Use % with tuple, not ,

For your particular case, try:

cursor.execute(sql_insert % (data))

answered Sep 26, 2016 at 0:47

Jeon

4,1467 gold badges31 silver badges78 bronze badges

1 Comment

alecxe Over a year ago

Sorry, have to downvote, , here is perfectly valid - it is a delimiter between arguments to execute(). The second argument to execute() are the query parameters. This is not about Pythonic vs not, this is about a proper way to insert parameters into the query and a wrong way to do it. Thanks for understanding.

Collectives™ on Stack Overflow

Python mysql.connector module, Passing data into string VALUES %s

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related