Python/MySQL: Escaping strings used as parameters in raw database queries

Question

I am using a connection/cursor to migrate data from an old database/schema into a new one built with Django models. I encounter a problem with names that have an apostrophe

to simplify

business = "Tom's Diner"
cursor.execute("select * from businesses where name = '" + business + "'")

This would obviously fail as I'm forcing a single quote which causes an SQL syntax problem. It would work if I did this:

business = "Tom''s Diner"

But as this is an automated process that deals with migrating millions of rows. I am looking for a way to escape my string before applying it to the direct MySQL query.

My question: is that something I have to do manually, or is there some function in Django/Python that escapes strings, and may handle cases I haven't even thought of yet, like double quotes in the string, etc.

Charif DZ · Accepted Answer · 2019-09-06 19:25:41Z

6

Try this:

cursor.execute("select * from businesses where name = %s ", (business ,))

make sure the second parameter is a tuple: (business,) not (business)

edited Sep 6, 2019 at 19:25

answered Sep 6, 2019 at 19:23

Charif DZ

14.8k3 gold badges25 silver badges42 bronze badges

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

2 Comments

JasonGenX Over a year ago

hmm interesting. will try.

Simon Notley Over a year ago

+1, the equivalent module for Postgres (psycopg) stresses in it's documentation that you should use this method rather than constructing your own query strings.

Collectives™ on Stack Overflow

Python/MySQL: Escaping strings used as parameters in raw database queries

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related