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
– unutbu, Commented Jun 24, 2010 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 if you're using psycopg2. — Milen A. Radev
– Milen A. Radev, Commented Jun 24, 2010 at 16:21

Rob Kruus · Accepted Answer · 2010-06-24 17:22:23Z

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, 2010 at 17:22

Rob Kruus

561 bronze badge

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

2 Comments

ʇsәɹoɈ Over a year ago

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.

Claudiu Over a year ago

but.. what if i want to transmogrify instead?

Community · Accepted Answer · 2017-05-23 11:54:50Z

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, 2017 at 11:54

CommunityBot

11 silver badge

answered Jun 24, 2010 at 16:14

spookylukey

6,6443 gold badges35 silver badges36 bronze badges

1 Comment

Claudiu Over a year ago

seems to be just what i need!

phd · Accepted Answer · 2017-05-07 21:27:45Z

1

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'

answered May 7, 2017 at 21:27

phd

97.3k14 gold badges165 silver badges223 bronze badges

Collectives™ on Stack Overflow

Python: get escaped SQL string

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related