escape snippets

Escaping strings in PostgreSQL queries

Tagged escape, postgres, sql  Languages sql

PostgreSQL queries containing, for example, single quotes or semicolons need to be escaped.

This won’t work:

UPDATE jobs SET work=':'';

To make it work, escape the strings using C-style escapes (E’’) and replacing single-quotes with ‘’:

UPDATE jobs SET work=E':''';

See section “ String Constants with C-style Escapes” in the PostgreSQL documentation for details.

Escape and sanitize input and output?

Tagged escape, input, output, sanitize  Languages 

Thinking about escaping and sanitizing input and output? Read this:


  • Always sanitize output

You can fix bugs in the input sanitizer without touching the database by always sanitizing output.

  • Sanitize input, if it makes sense:

For example, if there’s a risk that input becomes invalid after sanitization it’s better to store data raw. Also, when there are multiple output formats sanitizing might break something.