Handle apostrophe in query string using python

Question

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 lat, lon, gender from table_x where x_name = "namestring"

If my name string contains an ', the query string fails to execute.

Can someone tell me a way to avoid this error? I have a list of approximately 25k names and hence escaping each of them with a \' is not an option.

If you're iterating over your namestrings, can't you just escape the quote mark there, before executing the query? — Nebel22
– Nebel22, Commented Oct 9, 2018 at 4:35
The ANSI standard way to escape a single quote is to double it up, i.e. use '' inside the query string. — Tim Biegeleisen
– Tim Biegeleisen, Commented Oct 9, 2018 at 4:39

Patrick Artner · Accepted Answer · 2018-10-09 05:32:50Z

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.

answered Oct 9, 2018 at 5:32

Patrick Artner

51.9k10 gold badges50 silver badges79 bronze badges

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

2 Comments

Sumedha Nagpal Over a year ago

Can you please help with the scenario when the table name is a join clause? How does the sql.identifier work in the case of a join?

Patrick Artner Over a year ago

@SumedhaNagpal No, I can not. Please refer to the documentation. What you are asking (about ' destroying your query) is common over any number of Sql-Like queries - using parametrized queries is the solution to that. As for joint querys, use the API provided by psycopg and research it.

Shailesh · Accepted Answer · 2018-10-09 10:47:08Z

0

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

answered Oct 9, 2018 at 10:47

Shailesh

2,2964 gold badges31 silver badges53 bronze badges

Collectives™ on Stack Overflow

Handle apostrophe in query string using python

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related