Python equivalent of mysql_real_escape_string, for getting strings safely into MySQL?

Question

Is there a Python equivalent of PHP's mysql_real_escape_string?

I'm trying to insert some strings into a MySQL db direct from Python, and keep getting tripped up by quotes in the strings.

mysql_string = "INSERT INTO candidate (name, address) VALUES  " 
for k, v in v_dict.iteritems():
    mysql_string += " ('" + v_dict['name'] + "', '" + v_dict['address'] + "'), "
mysql_string += ";"
cursor.execute(mysql_string)

I've tried re.escape() but that escapes every non-alphanumeric character in the strings, which isn't what I need - I just need to escape single quotes in this instance (plus more generally anything else that might trip up MySQL).

Could do this manually I guess, but is there a smarter way to do it in Python?

Do you have some sort of CSV file that you are using python to insert into a MySQL database? And you're using `import csv` to get dictionaries for each row of data? If so, you should look up the MySQL bulk insert utility. http://dev.mysql.com/doc/refman/5.1/en/load-data.html — hughdbrown, Apr 01 '10 at 17:34
Yeah - don't even ask. I host with Webfaction and they don't support mysqlimport :( — AP257, Apr 01 '10 at 19:02
Possible duplicate of [Escape string Python for MySQL](https://stackoverflow.com/questions/3617052/escape-string-python-for-mysql) — Martin Thoma, Aug 03 '17 at 14:18

score 42 · Accepted Answer · answered Apr 01 '10 at 14:51

42

If you are using mysql-python, just try

MySQLdb.escape_string(SQL)

Example

>>> import MySQLdb
>>> MySQLdb.escape_string("'")
"\\'"

answered Apr 01 '10 at 14:51

YOU

120,166
34
186
219

1

Thought it would be something simple. Thanks! – AP257 Apr 01 '10 at 14:56
3

-1 This is the wrong answers in several ways. The OP would be better off using cursor.executemany() or using a bulk load tool if he is starting from a csv file. – hughdbrown Apr 01 '10 at 17:36
11

You may check [MySQLdb's documentation](http://mysql-python.sourceforge.net/MySQLdb.html#id5). its exactly an equivalent of `mysql_real_escape_string`. I know, *My* answer did not cover all, but its not wrong. But ok, you could **disagree**. – YOU Apr 02 '10 at 01:10
@you actually this is the answer that worked for me – Max Oct 20 '21 at 12:53

score 15 · Answer 2 · answered Apr 01 '10 at 14:53

15

cursor.executemany('INSERT INTO candidate (name, address) VALUES (%s, %s)',
                   [(v_dict['name'], v_dict['address'])] * len(v_dict))

should do what your code appears to attempt -- inserting the same identical values N times (you're looping on v_dict.iteritems() but completely ignoring the loop variables, and instad just reusing those specific two values from v_dict each and every time). Of course if you mean something completely different the second line will need to be changed accordingly, but the key idea anyway is to use placeholders, not string formatting, for such tasks.

answered Apr 01 '10 at 14:53

Alex Martelli

854,459
170
1,222
1,395

Alex is right: using executemany() this way avoids the formatting issues and all the possibilities for incorrect SQL. And @AP257 you are doing something crazy with your dictionary, as Alex intimates. – hughdbrown Apr 01 '10 at 17:30
I had same isue, your method is easiest but with """triple double quotes""" you can avoid many bugs. `cursor.executemany("""INSERT INTO candidate (name, address) VALUES (%s, %s)""", [(v_dict['name'], v_dict['address'])] * len(v_dict))` – Leo Aug 13 '16 at 19:47

score 8 · Answer 3 · answered Apr 01 '10 at 16:05

Is there a Python equivalent of PHP's mysql_real_escape_string?

Yes, it's escape_string on the database connection object.

Not escape_string on the MySQLdb module, which is equivalent to mysql_escape_string in PHP, and has the same potential vulnerability with multibyte character sets. You shouldn't use MySQLdb.escape_string.

In any case, you are much better off using parameterised queries as in Alex's answer. This makes it easier to port to other databases (it's part of the DB-API standard, which escape_string is not), and it's generally more convenient. (And much more convenient than parameterisation is in PHP.)

I'm getting `Error writing to database: 'CMySQLConnection' object has no attribute 'escape_string'` — fIwJlxSzApHEZIl, Feb 25 '20 at 20:45

score 3 · Answer 4 · answered Apr 01 '10 at 14:51

3

In this particular case you just need to use executemany method of the cursor.

mysql_string = "INSERT INTO candidate (name, address) VALUES  (%s,%s);"
cursor.executemany(mysql_string, v_dict.iteritems())

answered Apr 01 '10 at 14:51

newtover

31,286
11
84
89

score 3 · Answer 5 · answered Sep 27 '17 at 07:33

3

beside MySQLdb.escape_string(SQL), pymysql python library also provide escape_string() function.

>>> import pymysql
>>> pymysql.escape_string("'")
"\\'"
>>>

answered Sep 27 '17 at 07:33

Yuda Prawira

12,075
10
46
54

2

Does not seem like pymysql has this function. – Corneliu Maftuleac Apr 21 '21 at 23:07

score 2 · Answer 6 · answered Apr 01 '10 at 14:51

2

MySQLdb.escape_string is equivalent. All of that is described in documentation.

answered Apr 01 '10 at 14:51

Łukasz

35,061
4
33
33

Python equivalent of mysql_real_escape_string, for getting strings safely into MySQL?

6 Answers6

Linked