Escaping strings with python mysql.connector

Question

I am trying to insert a bunch of strings into mysql using python and mysql.connector. My current code looks something like this:

db = mysql.connector.Connect('config blah blah')
cursor = db.cursor()
data = (somestring1, somestring2)
sql = "INSERT INTO mytable (col1, col2) VALUES ('%s', '%s')"
cursor.execute(sql, data)

How should I go about escaping my strings? I could try doing it in python but I know this isn't the right way.

Note: I realise mysql.connector is still in development.

update:

Line 4 should read:

sql = "INSERT INTO mytable (col1, col2) VALUES (%s, %s)"

Answer 1

The answer from infrared is the best approach.

But, if you really need to escape some arbitrary string, you can do this (before 2.1.6):

db = mysql.connector.connect(......)

new_str = db.converter.escape('string to be escaped')

Newer versions (use lowlevel C-API):

db = mysql.connector.connect(......)

new_str = db._cmysql.escape_string('string to be escaped')

Another option is to use mariadb python connector (pip install mariadb).

db = mariadb.connector(....)
new_str = db.escape_string("quote ' this")

Answer 2

Indeed, the best approach would be to let module escape values by itself. If you absolutely need to do it by hand (I, for example, want to only print SQL in my script's debug mode, and mysql.connector doesn't seem to implement mogrify()), there's also another option:

>>>> import mysql.connector
>>>> cnx = mysql.connector.connect()
>>>> cur = cnx.cursor()
>>>> cur._connection.converter.escape("tic ' toc")
"tic \\' toc"

Admittedly, it still uses "non-public API", but at least it is consistent between recent versions (so far; tested on 2.0.4, 2.1.3, 2.2.9, 8.0.16).

Answer 3

Here is what worked for me:

import mysql.connector
db = mysql.connector.connect(host="HOST",    # your host, usually localhost
                     user="USERNAME",         # your username
                     passwd="PASSWORD",  # your password
                     db="DATABASE")        # name of the data base

query_string = "Geor'ge" 
escaped_string = db.converter.escape(query_string)

The first step is to import mysql.connector, followed by creating a connection by using the connect function. After that, you can call the db.converter.escape function.

Answer 4

You can do this with the pymysql package:

import pymysql
from pymysql.converters import escape_string
from mysql.connector import connect, Error

then you can do:

with connect(
    host="localhost",
    user="root",
    password="",
    database="mydb",
) as connection:
    with connection.cursor(buffered=True) as cursor:
        cursor.execute(escape_string("YOUR_SQL_STATEMENT"))