在 PostgreSQL 中，LIKE 语句是一种强大的模式匹配工具，但当用户提供的输入包含特殊字符（如 _ 和 %）时，可能会导致意外的匹配结果。这些字符在 LIKE 语句中具有特殊的含义：_ 匹配任意单个字符，而 % 匹配任意数量的字符（包括零个字符）。因此，如果用户输入的字符串包含这些字符，并且我们希望将其视为字面字符进行匹配，就需要对它们进行转义。
转义特殊字符
要将 _ 和 % 字符视为字面字符进行匹配，必须对它们进行转义。默认情况下，转义字符是反斜杠 \。但是，可以使用 ESCAPE 子句指定不同的转义字符。
默认转义字符（反斜杠）：
SELECT * FROM users WHERE name LIKE 'rob\_%';
在这个例子中，rob\_ 将匹配以 rob 开头，后跟任意单个字符的字符串。要匹配字面上的 rob_，需要将反斜杠自身转义：
SELECT * FROM users WHERE name LIKE 'rob\\_%';
使用 ESCAPE 子句指定转义字符：
SELECT * FROM users WHERE name LIKE 'rob^_%' ESCAPE '^';
在这个例子中，我们使用 ^ 作为转义字符。因此，^% 将匹配字面上的 % 字符。 同样，要匹配字面上的 ^ 字符，需要将其转义：^^。
服务器端转义
虽然可以在客户端应用程序中进行转义，但更安全和方便的方法是在服务器端使用 replace() 函数进行转义。这样可以避免在客户端和服务器之间传递未转义的字符串，从而降低 SQL 注入的风险。
以下是一个使用 replace() 函数进行转义的示例：
SELECT * FROM users WHERE name LIKE replace(replace(replace($1,'^','^^'),'%','^%'),'_','^_') ||'%' ESCAPE '^';
这个 SQL 语句使用三个嵌套的 replace() 函数来转义用户输入字符串中的 ^、% 和 _ 字符。它首先将所有的 ^ 替换为 ^^，然后将所有的 % 替换为 ^%，最后将所有的 _ 替换为 ^_。 || '%' 将用户输入的字符串与 % 连接，以便匹配以用户输入字符串开头的任何字符串。ESCAPE '^' 指定 ^ 作为转义字符。
示例代码（Go）：
db.Query("SELECT * from USERS where name like replace(replace(replace($1,'^','^^'),'%','^%'),'_','^_') ||'%' ESCAPE '^'",
variable_user_input);注意事项
- standard_conforming_strings 配置： 在 PostgreSQL 9.1 及更高版本中，默认情况下，standard_conforming_strings 配置为 ON。这意味着反斜杠 \ 仅在字符串中作为转义字符使用。在早期版本中，或者如果 standard_conforming_strings 配置为 OFF，则反斜杠可能会有不同的含义。因此，为了确保代码的兼容性，建议始终使用 ESCAPE 子句显式指定转义字符。
- SQL 注入： 在处理用户输入时，务必小心防范 SQL 注入。使用参数化查询或预处理语句可以有效地防止 SQL 注入攻击。
- 转义字符的选择： 选择一个在用户输入中不太可能出现的字符作为转义字符。如果用户输入可能包含您选择的转义字符，您需要转义该转义字符本身。
总结
在 PostgreSQL 中使用 LIKE 语句进行模式匹配时，正确转义用户输入字符串至关重要。通过使用 replace() 函数在服务器端进行转义，可以避免意外的匹配结果，并降低 SQL 注入的风险。同时，需要注意 standard_conforming_strings 配置的影响，并选择合适的转义字符。遵循这些最佳实践，可以确保 LIKE 语句的正确性和安全性。
