如何在PostgreSQL中匹配模式时转义字符串

Question

I want to find rows where a text column begins with a user given string, e.g. SELECT * FROM users WHERE name LIKE 'rob%' but "rob" is unvalidated user input. If the user writes a string containing a special pattern character like "rob_", it will match both "robert42" and "rob_the_man". I need to be sure that the string is matched literally, how would I do that? Do I need to handle the escaping on an application level or is it a more beautiful way?

I'm using PostgreSQL 9.1 and go-pgsql for Go.

Answer 1

To escape the underscore and the percent to be used in a pattern in like expressions use the escape character:

SELECT * FROM users WHERE name LIKE replace(replace(user_input, '_', '\\_'), '%', '\\%');