CODEWITHSUNDEEP
pythonmysqlstringpython-3.xquotes
BowPark
BowPark

Reputation: 1460

Python and MySQL query with quotes

With a script in Python3, after extracting some strings from a file, they should be used as data to be inserted into a MySQL database as follows:

query1 = """INSERT INTO {:s} VALUES ({:s}, {:s}, {:s}, {:s});""".format(table1,"""0""",string1,string2,string3)
cursor1.execute(query1)

Some of the strings contain different and unpleasant quotes, like:

a "'double quoted'" example string

If I define some example strings with triple quotes delimiters

string1 = """a "'double quoted'" example string"""

the above query is successful. If instead the strings are returned by a function after parsing an external file, the query generates an error:

_mysql_exceptions.ProgrammingError: (1064, '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 \'first string, "\'Quoted part\'" of second string, , Third string\' at line 1')

I tried also with:

query1 = """INSERT INTO {:s} VALUES ('{:s}', '{:s}', '{:s}', '{:s}');""".format(table1,"""0""",string1,string2,string3)

but the same error is generated.

Also

query1 = """INSERT INTO %s VALUES (%s, %s, %s, %s);"""
data1 = ("""table1"""","""0""",string1,string2,string3)
cursor1.execute(query1,data1)

and

query1 = """INSERT INTO %s VALUES ('%s', '%s', '%s', '%s');"""
data1 = ("""table1"""","""0""",string1,string2,string3)
cursor1.execute(query1,data1)

generate the same error.

How to fix this? Maybe, once the strings have been returned by the function, is it possible to re-define them with triple quotes?

Upvotes: 0

Views: 5132

Answers (1)

khelwood
khelwood

Reputation: 59096

This is how you add parameters to a statement.

sql = "INSERT INTO my_table VALUES (%s, %s, %s);"

cursor.execute(sql, [string1, string2, string3])

See MySQLCursor.execute().

In this example you don't have to explicitly quote the values because you're not gluing them into your SQL. Also, this is safer, because if the string contains an end quote and them some malicious SQL, it will not be executed.

You can't add the table name as a parameter, so if that was in a variable you would have to glue that into your SQL:

sql = "INSERT INTO {} VALUES (%s, %s, %s);".format(table_name)

Upvotes: 4

Related Questions