Escaping user input for use in a PostgreSQL regex using PHP?

Question

I'm creating a regular expression pattern to be matched with ~ or ~*, where part of the pattern is created by the application and part is taken from user input:

$userInput = "t ' e * \\s * t ( i \n g";

$pattern = "(a|b|c)" . preg_quote($userInput) . "(x|y|z)";
$pattern = pg_escape_literal($conn, $pattern);

$result = pg_query($conn, "SELECT * FROM example WHERE name ~* $pattern");

This works, so far, but I'm not completely sure about its safety:

• Does PHP's preg_quote() escape (at least) all the special characters in a PostgreSQL regular expression?
• Is there any possible $userInput value that could break the escaping?

(There is some generic processing for $userInput before it reaches this stage - encoding, charset, accepted character ranges, etc. Query parameters are used for most of the (actual) query, but this part of the SQL is created manually.)

Answer 1

Yes. From the docs, ~ and ~* use POSIX regular expressions, which means that these characters are treated specially:

()[]{}^$|?:.\

preg_quote, from the docs, just blindly goes through and puts a backslash (i.e. escapes) all of these characters:

.\+*?[^]$(){}=!<>|:-

In other words, it escapes all of the characters that need escaping.

However, you should still be careful. If you decide to use LIKE or SIMILAR TO at some point in the future, _ and % won't be escaped. Also, you have to remember not to use a different escape character; if you do, all that careful validation will go out the window, and you'd get a host of weird bugs.