Escape SQL “LIKE” value for Postgres with psycopg2

by

Yeah, this is a real mess. Both MySQL and PostgreSQL use backslash-escapes for this by default. This is a terrible pain if you’re also escaping the string again with backslashes instead of using parameterisation, and it’s also incorrect according to ANSI SQL:1992, which says there are by default no extra escape characters on top of normal string escaping, and hence no way to include a literal % or _.

I would presume the simple backslash-replace method also goes wrong if you turn off the backslash-escapes (which are themselves non-compliant with ANSI SQL), using NO_BACKSLASH_ESCAPE sql_mode in MySQL or standard_conforming_strings conf in PostgreSQL (which the PostgreSQL devs have been threatening to do for a couple of versions now).

The only real solution is to use the little-known LIKE...ESCAPE syntax to specify an explicit escape character for the LIKE-pattern. This gets used instead of the backslash-escape in MySQL and PostgreSQL, making them conform to what everyone else does and giving a guaranteed way to include the out-of-band characters. For example with the = sign as an escape:

# look for term anywhere within title
term= term.replace('=', '==').replace('%', '=%').replace('_', '=_')
sql= "SELECT * FROM things WHERE description LIKE %(like)s ESCAPE '='"
cursor.execute(sql, dict(like="%"+term+'%'))

This works on PostgreSQL, MySQL, and ANSI SQL-compliant databases (modulo the paramstyle of course which changes on different db modules).

There may still be a problem with MS SQL Server/Sybase, which apparently also allows [a-z]-style character groups in LIKE expressions. In this case you would want to also escape the literal [ character with .replace('[', '=['). However according to ANSI SQL escaping a character that doesn’t need escaping is invalid! (Argh!) So though it will probably still work across real DBMSs, you’d still not be ANSI-compliant. sigh…

More Related Contents:

  1. psycopg2: insert multiple rows with one query
  2. Parameterized queries with psycopg2 / Python DB-API and PostgreSQL
  3. Passing table name as a parameter in psycopg2
  4. DatabaseError: current transaction is aborted, commands ignored until end of transaction block?
  5. error installing psycopg2, library not found for -lssl
  6. Error: pg_config executable not found when installing psycopg2 on Alpine in Docker
  7. How to set up a PostgreSQL database in Django
  8. psycopg2.OperationalError: FATAL: unsupported frontend protocol 1234.5679: server supports 2.0 to 3.0
  9. Psycopg2 image not found
  10. Error Installing Psycopg2 on MacOS 10.9.5
  11. sqlalchemy.exc.NoSuchModuleError: Can’t load plugin: sqlalchemy.dialects:postgres
  12. Create a Postgres database using python
  13. Python/postgres/psycopg2: getting ID of row just inserted
  14. How to set connection timeout in SQLAlchemy
  15. Mac + virtualenv + pip + postgresql = Error: pg_config executable not found
  16. “Failed building wheel for psycopg2” – MacOSX using virtualenv and pip
  17. Psycopg2, Postgresql, Python: Fastest way to bulk-insert
  18. Insert Python Dictionary using Psycopg2
  19. Use binary COPY table FROM with psycopg2
  20. How Postgresql COPY TO STDIN With CSV do on conflic do update?
  21. How to install psycopg2 with “pip” on Python?
  22. Can’t subtract offset-naive and offset-aware datetimes
  23. Composite primary key in django
  24. psycopg2 installation error – Library not loaded: libssl.dylib
  25. How do you join two tables on a foreign key field using django ORM?
  26. Strange SQLAlchemy error message: TypeError: ‘dict’ object does not support indexing
  27. Case insensitive unique model fields in Django?
  28. Pandas update sql
  29. SQLAlchemy: filtering on values stored in nested list of the JSONB field
  30. How does COPY work and why is it so much faster than INSERT?

Leave a Comment