-----Original Message----- From: pgsql-general-owner@xxxxxxxxxxxxxx [mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of Ronan Dunklau Sent: Friday, February 24, 2012 6:34 AM To: pgsql-general@xxxxxxxxxxxxxx Subject: Regular expression character escape Hello. I'd like to perform a query using user-submitted input in a regular expression. Something along the lines of: select some_col from some_table where some_col ~ ('^' || user_submitted_input || '\d*$') This query is looking for every value matching the user submitted input with optional trailing decimal characters. My problem is: the user can break this using special regular expression characters (ex: 'test('). I'm looking for a way to escape regular expression characters from the input (maybe a function already exists for that purpose?). I could do it on the client side, but I'm not really sure how python and postgresql flavors of regular expressions differ. I've seen that the (?q) modifier is supported (treat the rest of the regexp as normal characters), but unless I missed something it I can't use it anywhere else than at the beginning of the regexp. >From the docs at http://www.postgresql.org/docs/current/static/functions-matching.html: "Embedded options take effect at the ) terminating the sequence. They can appear only at the start of an ARE." Any idea on how to achieve this ? Thank you. -- Ronan Dunklau ------------------------------------------------------------------------------------------------ How about: WHERE some_col LIKE (user_submitted_input || '%') AND some_col ~ ('^.{' || length_of_user_submitted_input || '}\d*$') I'd have some reservations regarding multi-byte characters however - but this avoids any escaping of the input string. You could (should?) write the escaping routine on the server side in a user-defined function: WHERE some_col ~ ('^' || make_regexp_literal(user_submitted_stringliteral) || '\d*$') David J. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
