18

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)"
Improve this question

6 Answers 6

Reset to default
18

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

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

5 Comments

Thanks. I actually typed my example code correctly. I've edited it to show my problem.
And what if you have to generate insert statement that contains 1000 (10,000, 1,000,000) values, you have to hardcode it with %s? What if you don't know how much values you have to insert, for example, what if you read it from external source (file, HTTP request), how to use "automatic escaping"?
There is another exception: mysql-connector-python does not support list or tuple parameters at the moment. Say, you cannot form the IN clause provided a list of strings. Therefore, library caller is responsible of escaping the list.
If the table name contains a hyphen, you have to escape with backticks or you get mysql.connector.errors.ProgrammingError
@Vitalii I had the same issue. I discovered that there is an alternate version of execute called executemany that will accept a list of value tuples for inserting multiple values. You get the same auto-escape semantics as execute and if an INSERT command is used, it will also automatically optimize the command to do all the inserts in one go dev.mysql.com/doc/connector-python/en/…
17

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")
Improve this answer

5 Comments

This didn't work for me, I get AttributeError: 'NoneType' object has no attribute 'escape'
Hi @IvanIvković, which version are you using? Just tested again and it works :) I'm using 2.1.4 Are you using mysql.connector from mysql website?
Hi @Luciano Barcaro, I'll try rolling back to 2.1.4 cause I'm using 2.1.6 at the moment. Thanks for your help, especially on my birthday!
New versions does not work :( But, I found another way: import _mysql_connector db = _mysql_connector.MySQL() db.connect(host...., user....) escaped_string = db.escape_string('test string') It's from low level C-API. Editing main answer.
One leading underscore means implementation detail that is not supposed to be used outside the library itself. It can change name or semantics at any new release without notice.
0

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

Improve this answer

4 Comments

It's not working with the buffered cursor, though.
Probably, but even if you use buffered cursor, you can create a "usual" one to mimic mogrify() =)
I would appreciate a fully working example that works with buffered cursors without using other mysql packages like pymysql.
Just create a usual cursor (MySQLCursor), use it to get the escaped string, then create a MySQLCursorBuffered` and execute your SQL with it. What's the problem?
0

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.

Improve this answer

Comments

-1

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"))
Improve this answer

3 Comments

Using both pymysql and mysql.connector is sort of werid.
@KlasŠ. I agree, it's probably better to use pymysql instead of mysql.connector. This is just a quick solution for a quick problem and the question is about mysql.connector.
The question is "Escaping strings with mysql.connector", so suggesting completely another module isn't quite the answer.
-1

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
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.