CODEWITHSUNDEEP
pythonmysqlescaping
Laurențiu Dascălu
Laurențiu Dascălu

Reputation: 2069

Escape string Python for MySQL

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.

Upvotes: 83

Views: 172666

Answers (7)

Saurav Panda
Saurav Panda

Reputation: 566

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.

Upvotes: 0

ELOUAJIB Imad
ELOUAJIB Imad

Reputation: 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)

Upvotes: -3

Agnel Vishal
Agnel Vishal

Reputation: 572

{!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

Upvotes: -5

Martin Thoma
Martin Thoma

Reputation: 136865

>>> 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'

Upvotes: 21

User
User

Reputation: 24759

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

Upvotes: 86

Eric Leschinski
Eric Leschinski

Reputation: 154101

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

Upvotes: 4

miku
miku

Reputation: 188194

conn.escape_string()

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

Upvotes: 105

Related Questions