CODEWITHSUNDEEP
pythonsqlpostgresqlpsycopg2
Alex S
Alex S

Reputation: 26051

How to manually escape SQL literals in Python

I'm attempting to write a very high level query builder specific to my application. I'm currently using psycopg2 which doesn't seem to have a way to escape one literal without it being part of a larger, already written query.

Is there a generic module for doing this? I don't seem to have many (if any) options.

Upvotes: 0

Views: 1885

Answers (1)

Craig Ringer
Craig Ringer

Reputation: 324285

You shouldn't need to escape literals; that's what parameterised statements are for. psycopg2 supports them for both plannable statements (insert/update/delete/select) and non-plannable statements (create table, create index, ...).

You should always be using parameterised statements in preference to directly substituting literals into query strings.

It looks like psycopg2 exposes some quoting functions too - particularly psycopg2.extensions.adapt.

That said, for modern PostgreSQL it's pretty trivial; you can follow the same rules as for identifiers, just with ' instead of ":

  • If there's a zero byte (\x00) in the string, truncate the string before the zero byte;
  • For every ' in the string, replace it with ''; and
  • Prepend and append '.

That's it. Backslashes have no special meaning, so there's nothing else to escape.

You just need to make sure that standard_conforming_strings is enabled (SELECT current_setting('standard_conforming_strings') and throw an exception if it isn't, because these rules are completely wrong for old-style strings and E'' strings.

It'd be very nice to have escape_literal and escape_identifier functions in psycopg2. Consider submitting a patch.

See also:

Upvotes: 2

Related Questions