Ryan Dooley Ryan Dooley - 10 months ago 51
MySQL Question

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

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)")





Answer Source

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 = """

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

Note how the query parameters are put into a tuple.