Python mySQL - escaping quotes

python mysql mysql-python
Nupur picture Nupur · Oct 15, 2012 · Viewed 20.2k times · Source

I have seen this question asked in various ways on this website, but none of them exactly addressed my issue.

I have an sql statement with single quotes inside it, and am trying to use recommended practices before making database queries with it. So the statement is like

val2="abc 'dostuff'" 
sql="INSERT INTO TABLE_A(COL_A,COL_B) VALUES(%s,'%s')" %(val1, val2)
a_cursor.execute(sql)

However, when I run this, I get..

ProgrammingError: (1064,"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'dostuff'.

What am I doing wrong? Thanks very much Nupur

Answer

Martijn Pieters picture Martijn Pieters · Oct 15, 2012

Use parameters instead of string interpolation to ensure that your values are properly escaped by the database connector:

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

The mysqldb sql parameter style uses the same syntax as used by the python string formatting operator, which is a little confusing.

Related questions

Installing specific package versions with pip

I'm trying to install version 1.2.2 of the MySQL_python adaptor, using a fresh virtualenv created with the --no-site-packages option. The current version shown in PyPi is 1.2.3. Is there a way to install the older version? I found an article stating …

Read More
ImportError: No module named MySQLdb

I am referring the following tutorial to make a login page for my web application. http://code.tutsplus.com/tutorials/intro-to-flask-signing-in-and-out--net-29982 I am having issue with the database. I am getting an ImportError: No module named MySQLdb when I execute …

Read More
Install mysql-python (Windows)

I've spent hours trying to make Django work on my computer. The problem is that I can't install the mysql-python package. I'm running Windows 7 64bit. This is what I've tried: I have downloaded easy_install I have downloaded Cygwin64 to …

Read More