Handle Apostrophe In Query String Using Python

I am trying to query Redshift using Python. I am generating a query string which looks like the one below: I am using psycopg2 as the library to establish the connection. Select l

Solution 1:

Use a parametrized query as strongly suggested in http://initd.org/psycopg/docs/sql.html

# somethin along the lines of this should work:from psycopg2 import sql

names = [ "A", "McDiff", "Old'McDonal"]

for n in names:
    cur.execute(sql.SQL("Select lat, lon, gender from {} where x_name = %s")
                .format(sql.Identifier('table_x')),[n])

This avoids the problem of self-quoting dur to using parametrized query construction instead of string concattenation.

---

See Little Bobby Tables / Exploit of a Mom and google sql injection fo rother reasons not to string-concattenate.

Solution 2:

You can use single quotes in the string and use the parametrized string to substitute with your values.

QUERY = """select lat, lon, gender from table_x where x_name = '{namestring}'"""for namestring in list_of_namestrings:
    cur.execute(QUERY.format(namestring=namestring) 

This should solve your purpose, you can make the QUERY as complex as you desire, and make the required substitutions using .format()