Escape string Python for MySQL

Question

I use Python and MySQLdb to download web pages and store them into database. The problem I have is that I can't save complicated strings in the database because they are not properly escaped.

Is there a function in Python that I can use to escape a string for MySQL? I tried with ''' (triple simple quotes) and """, but it didn't work. I know that PHP has mysql_escape_string(), is something similar in Python?

Thanks.

Do `db_cur.execute('''UPDATE test_table SET field_1="%s" WHERE field_2="%s"''' % (data, condition))` Note the triple single quotes and double quotes around `%s` — zelusp, Oct 29 '16 at 01:33
Old thread but shouldn't the last `%` mark in your code be a `,` comma, otherwise it's just the same? @zelusp — Artemis, Jul 15 '19 at 06:08
Nope, I think that's right - unless you ran my code and I'm wrong. `% (data, condition)` is taking the variables `data` and `condition` and putting it into the two `%s` placeholders. — zelusp, Jul 15 '19 at 20:32
That's just python string formatting, which replaces. If `condition` were, for example, `" or 1 == 1 or" `, for example, you would have a problem. `cur.execute` provides escaping by doing `cur.execute('SOME COMMAND ?', [value])`. `?` is replaced with value. — Artemis, Jul 21 '19 at 01:28

score 105 · Answer 1 · answered Sep 01 '10 at 10:29

105

conn.escape_string()

See MySQL C API function mapping: http://mysql-python.sourceforge.net/MySQLdb.html

answered Sep 01 '10 at 10:29

miku

181,842
47
306
310

2

+1 ... Perfect answer. Surprised to see so many complicated answers out there. Clearly, param'ed queries don't take into account for long strings (text) that is being stored. – Mike Nov 18 '10 at 23:59
2

+1 I love the black and white HTML pages with swear words and code. – yurisich Dec 29 '11 at 01:07
6

_mysql.escape_string("input's " ) can also work to escape an ascii string for mysql. Apparently not for unicode though. _mysql.escape_string( u"input's éh " ) – knowledge_is_power Apr 10 '13 at 12:14
1

What are the parameters for this function? – User Dec 19 '14 at 23:25
our equivalent would be MySQLdb.escape_string("") – possumkeys Apr 05 '16 at 15:28
4

doesnt work... converts string to byte format – zdanman May 29 '21 at 16:52

User · Answer 2 · 2015-01-27T17:55:45.827

83

The MySQLdb library will actually do this for you, if you use their implementations to build an SQL query string instead of trying to build your own.

Don't do:

sql = "INSERT INTO TABLE_A (COL_A,COL_B) VALUES (%s, %s)" % (val1, val2)
cursor.execute(sql)

Do:

sql = "INSERT INTO TABLE_A (COL_A,COL_B) VALUES (%s, %s)"
cursor.execute(sql, (val1, val2))

edited Jan 27 '15 at 17:55

answered Dec 19 '14 at 23:36

User

23,729
38
124
207

3

Somewhat upsetting that it forces quotation marks. For example, if you were inserting into a conditional table (TABLE_A instead of 'TABLE_A'), you couldn't do it completely with this method. – bozdoz May 11 '15 at 19:52
6

bozdoz, this is very much by design as it prevents SQL Injection. If you want to insert into a conditional table, first make sure there is no way a user submitted string can be used for the table name, and then just add it directly to the query. – techdude Oct 08 '16 at 14:38
2

This is definitely the correct answer. Never know what new ways will exist to get around string escaping (Like we currently do over in the PHP world...). Just safer to do prepared statements always – Brian Leishman Jun 09 '17 at 15:56
The builder formats using *exactly the same call* to `db.literal` as `escape` does, so calling it *"safer"* is a bit of a misnomer. Use it because its easier for small queries, but don't be troubled that you're missing something when you have to craft a large query or script by hand. – c z Apr 30 '19 at 13:34

score 19 · Answer 3 · answered Aug 03 '17 at 13:27

19

>>> import MySQLdb
>>> example = r"""I don't like "special" chars ¯\_(ツ)_/¯"""
>>> example
'I don\'t like "special" chars \xc2\xaf\\_(\xe3\x83\x84)_/\xc2\xaf'
>>> MySQLdb.escape_string(example)
'I don\\\'t like \\"special\\" chars \xc2\xaf\\\\_(\xe3\x83\x84)_/\xc2\xaf'

answered Aug 03 '17 at 13:27

Martin Thoma

124,992
159
614
958

Hm. Seems as if StackOverflows highlighting algorithm doesn't know Pythons triple quotes. – Martin Thoma Aug 03 '17 at 13:28
This is especially helpful for use in routines where the SQL string is constructed piecemeal. – Andrew Basile May 13 '18 at 01:24
3

MySQLdb fork for Python3: https://github.com/PyMySQL/mysqlclient-python – Ian Mackinnon Oct 23 '20 at 14:10

Eric Leschinski · Answer 4 · 2014-12-20T02:40:46.437

Use sqlalchemy's text function to remove the interpretation of special characters:

Note the use of the function text("your_insert_statement") below. What it does is communicate to sqlalchemy that all of the questionmarks and percent signs in the passed in string should be considered as literals.

import sqlalchemy
from sqlalchemy import text
from sqlalchemy.orm import sessionmaker
from datetime import datetime
import re

engine = sqlalchemy.create_engine("mysql+mysqlconnector://%s:%s@%s/%s"
     % ("your_username", "your_password", "your_hostname_mysql_server:3306",
     "your_database"),
     pool_size=3, pool_recycle=3600)

conn = engine.connect()

myfile = open('access2.log', 'r')
lines = myfile.readlines()

penguins = []
for line in lines:
   elements = re.split('\s+', line)

   print "item: " +  elements[0]
   linedate = datetime.fromtimestamp(float(elements[0]))
   mydate = linedate.strftime("%Y-%m-%d %H:%M:%S.%f")

   penguins.append(text(
     "insert into your_table (foobar) values('%%%????')"))

for penguin in penguins:
    print penguin
    conn.execute(penguin)

conn.close()

score 0 · Answer 5 · answered Apr 05 '20 at 12:05

One other way to work around this is using something like this when using mysqlclient in python.

suppose the data you want to enter is like this <ol><li><strong style="background-color: rgb(255, 255, 0);">Saurav\'s List</strong></li></ol>. It contains both double qoute and single quote.

You can use the following method to escape the quotes:

statement = """ Update chats set html='{}' """.format(html_string.replace("'","\\\'"))

Note: three \ characters are needed to escape the single quote which is there in unformatted python string.

score -3 · Answer 6 · answered Sep 13 '19 at 18:24

-3

install sqlescapy package:

pip install sqlescapy

then you can escape variables in you raw query

from sqlescapy import sqlescape

query = """
    SELECT * FROM "bar_table" WHERE id='%s'
""" % sqlescape(user_input)

answered Sep 13 '19 at 18:24

ELOUAJIB Imad

3
4

2

Note that sqlescapy doesn't properly escape quotes or double quotes, it simply removes them: https://github.com/elouajib/sqlescapy/blob/master/sqlescapy/sqlescape.py – Jesús A. Álvarez Mar 13 '20 at 15:10

Agnel Vishal · Answer 7 · 2019-02-13T11:21:56.360

-5

{!a} applies ascii() and hence escapes non-ASCII characters like quotes and even emoticons. Here is an example

cursor.execute("UPDATE skcript set author='{!a}',Count='{:d}' where url='{!s}'".format(authors),leng,url))

Python3 docs

edited Feb 13 '19 at 11:21

answered Oct 13 '18 at 14:49

Agnel Vishal

564
6
13

2

Your Sample Program link is broken, and single quotes are ascii – Tim Richardson Feb 13 '19 at 04:24

Escape string Python for MySQL

7 Answers7

Linked