如何在PostgreSQL中匹配模式时转义字符串 [英] How to escape string while matching pattern in PostgreSQL

问题描述

我想查找文本列以用户给定字符串开头的行，例如 SELECT * FROM用户WHERE名称LIKE'rob％'但rob是未验证的用户输入。如果用户编写一个包含特殊模式字符（如rob_）的字符串，它将匹配robert42和rob_the_man。我需要确保字符串是字面匹配的，我该怎么做？我是否需要在应用程序级别处理转义，或者它是一种更美丽的方式？我使用PostgreSQL 9.1和 go-pgsql for Go。

解决方案

_和％字符必须被引用来在LIKE语句中进行字面匹配，这是无法解决的。选择是在做客户端还是服务器端（通常使用SQL替换（）），请参见下文）。为了在一般情况下100％正确，还有几件事情需要考虑。

默认情况下，在_或％之前使用的引号字符是反斜杠（\），但可以在LIKE子句后立即使用ESCAPE子句进行更改。
在任何情况下，引号字符都必须在模式中重复两次，才能作为一个字符匹配。

示例： ... WHERE字段像'john^%node1^^node2.uucp@%'ESCAPE'^'会匹配 john％node1 ^ node2.uccp @ 后跟任何内容。

反斜杠的默认选择存在问题：当 standard_conforming_strings 是关闭的（PG 9.1默认开启，但以前的版本仍在广泛使用，这是一个值得考虑的问题）。

另外，如果LIKE通配符的引用是在用户输入注入场景中的客户端完成的，那么它另外到到用户输入中已经需要的普通字符串引用。

查看一个go-pgsql示例，告诉它它使用$ N样式的占位符s用于变量...因此，下面试图以某种通用的方式编写它：它可以与ON或OFF的standard_conforming_strings一起使用，使用服务器端替换[％_]，替代引号字符，引用引号字符，并避免SQL注入：

$ $ p $ db.Query（SELECT * from USERS where name like replace（replace（replace（$ 1，' ^'，'^^'），'％'，'^％'），'_'，'^ _'）||'％'ESCAPE'^'，
variable_user_input）;


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.

解决方案

The _ and % characters have to be quoted to be matched literally in a LIKE statement, there's no way around it. The choice is about doing it client-side, or server-side (typically by using the SQL replace(), see below). Also to get it 100% right in the general case, there are a few things to consider.

By default, the quote character to use before _ or % is the backslash (\), but it can be changed with an ESCAPE clause immediately following the LIKE clause. In any case, the quote character has to be repeated twice in the pattern to be matched literally as one character.

Example: ... WHERE field like 'john^%node1^^node2.uucp@%' ESCAPE '^' would match john%node1^node2.uccp@ followed by anything.

There's a problem with the default choice of backslash: it's already used for other purposes when standard_conforming_strings is OFF (PG 9.1 has it ON by default, but previous versions being still in wide use, this is a point to consider).

Also if the quoting for LIKE wildcard is done client-side in a user input injection scenario, it comes in addition to to the normal string-quoting already necessary on user input.

A glance at a go-pgsql example tells that it uses $N-style placeholders for variables... So here's an attempt to write it in a somehow generic way: it works with standard_conforming_strings both ON or OFF, uses server-side replacement of [%_], an alternative quote character, quoting of the quote character, and avoids sql injection:

   db.Query("SELECT * from USERS where name like replace(replace(replace($1,'^','^^'),'%','^%'),'_','^_') ||'%' ESCAPE '^'",
     variable_user_input);

这篇关于如何在PostgreSQL中匹配模式时转义字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！