String escape in SQL statement

Question

I am trying to do the following query:

cursor.execute("SELECT DISTINCT(provider) FROM raw_financials 
                WHERE vendor_id="%s" OR title='%'", (vendor_id, title))

My problem is that the title can be anything, for example it can be "Hello, Sir", 'Yes' he responded. So when I do the above, I could get a (bad) return value, such as An Elf\\'s Story (which raises a SQL error).

How would I correctly quote the SQL statement and the title here?

Depends on the database, some want backslashes (A\'B), some want doubled-quotes (A''B), etc... — Marc B
– Marc B, Commented Jan 19, 2012 at 22:29

unutbu · Accepted Answer · 2012-01-19 22:46:08Z

5

Don't quote it yourself; your DB driver will do it for you:

cursor.execute("SELECT DISTINCT(provider) FROM raw_financials 
            WHERE vendor_id = %s OR title = %s", (vendor_id, title))

Edit: Above, %s are placeholders which indicate where the arguments (vendor_id and title) should be inserted. MySQLdb uses %s for placeholders, but some other drivers use ?. You may have to change my code above to use the placeholder appropriate for your driver.

edited Jan 19, 2012 at 22:46

answered Jan 19, 2012 at 22:29

unutbu

886k197 gold badges1.9k silver badges1.7k bronze badges

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

2 Comments

jcollado Over a year ago

Shouldn't be ? instead of %s?

unutbu Over a year ago

It depends on the driver. sqlite3 and oursql use ?, while MySQLdb uses %s placeholders. Thanks for reminding me that my answer was (unwittingly) MySQLdb-specific.

Collectives™ on Stack Overflow

String escape in SQL statement

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