Python: get escaped SQL string

Question

When I have a cursor, I know I can safely execute a query as follows:

cur.execute("SELECT * FROM foo WHERE foo.bar = %s", (important_variable,))

Is there any way to just get the string safely without executing the query? For example, if important_variable is a string, like "foo 'bar' \"baz", I'd want the appropriately escaped one:

"SELECT * FROM foo WHERE foo.bar = "foo \'bar\' \"baz"

(or whatever the appropriate escaping is, I'm not even sure).

I'm using psycopg, and sqlobject.

If `important_variable` is `datetime.date(2000,1,1)` then I think Claudiu wants a function which returns the string `"SELECT * FROM foo WHERE foo.bar = '2000-1-1'"` — unutbu, Jun 24 '10 at 16:17
Actually there is no escaping involved in this case - the driver sends the parametrized statement and the arguments separately. OTOH check [psycopg2.extensions.QuotedString](http://initd.org/psycopg/docs/extensions.html?highlight=escape#psycopg2.extensions.QuotedString) if you're using psycopg2. — Milen A. Radev, Jun 24 '10 at 16:21

score 4 · Accepted Answer · answered Jun 24 '10 at 17:22

4

Look at the mogrify method for cursors -- it will return the string after variable binding and show any quoting it does

cur.mogrify("SELECT * FROM foo WHERE foo.bar = %s", ("foo 'bar' \"baz",))

answered Jun 24 '10 at 17:22

Rob Kruus

56
1

Also note that any approach you find will be connector-specific. The DB API does not define a standard way to generate escaped SQL strings. This is appropriate because connectors don't necessarily build up the whole string on the client side; some are capable of sending the query template and the values to the server separately. – ʇsәɹoɈ Jun 24 '10 at 17:35
but.. what if i want to transmogrify instead? – Claudiu Jun 24 '10 at 18:33

score 1 · Answer 2 · edited May 23 '17 at 11:54

1

You haven't told us what library or DB you are using, but I think your question is answered here: How to quote a string value explicitly (Python DB API/Psycopg2)

edited May 23 '17 at 11:54

Community

1
1

answered Jun 24 '10 at 16:14

spookylukey

6,380
1
31
34

seems to be just what i need! – Claudiu Jun 24 '10 at 16:28

score 1 · Answer 3 · answered May 07 '17 at 21:27

SQLObject handles escaping/quoting itself, but if you want to use that functionality:

from sqlobject.converters import sqlrepr
print(sqlrepr('SELECT * FROM foo WHERE foo.bar = "foo \'bar\' \"baz', 'postgres'))

Result:

'SELECT * FROM foo WHERE foo.bar = "foo ''bar'' "baz'

Python: get escaped SQL string

3 Answers3