CODEWITHSUNDEEP
pythonmysql
Mr_Chimp
Mr_Chimp

Reputation: 6907

Escaping strings with python mysql.connector

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

Upvotes: 18

Views: 24435

Answers (6)

Online User
Online User

Reputation: 17618

I'm adding a new answer as my answer is outdated! I've got a working implementation that is for Python V3+ for this issue:

db = mysql.connector.connect(host=DATABASE_IP,  # your host
                              user=DATABASE_USERNAME,  # your username
                              passwd=DATABASE_PASSWORD,  # your password
                              db=DATABASE_NAME)  # name of the data base

cur = db.cursor()
escaped = db._cmysql.escape_string("trying 'to be escaped")
escaped_str = escaped.decode('utf-8')
print(escaped_str) #printing trying \'to be escaped

Upvotes: 0

Online User
Online User

Reputation: 17618

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.

Upvotes: 0

Martin Braun
Martin Braun

Reputation: 12569

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

Upvotes: -1

Luciano Barcaro
Luciano Barcaro

Reputation: 388

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

Upvotes: 17

Nikolaj Š.
Nikolaj Š.

Reputation: 1986

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

Upvotes: 0

infrared
infrared

Reputation: 3626

Since mysql.connector is DB API v2.0 compliant, you do not need to escape the data yourself, it does it automatically for you.

Upvotes: 18

Related Questions