（如何）在 MyBatis 的 SQL 查询中使用“LIKE”安全且与数据库无关？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7491291/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
(How) can I use "LIKE" in SQL queries with MyBatis safely and DB-agnostic?
提问by Hanno Fietz
In MyBatis, you mark the places where parameters should be inserted into your SQL like so:
在MyBatis 中，您可以像这样标记参数应该插入到 SQL 中的位置：
SELECT * FROM Person WHERE id = #{id}
SELECT * FROM Person WHERE id = #{id}
This syntax activates proper escaping etc to avoid, among other things, SQL injection attacks. If you have trusted input and want to skip escaping, you can insert the parameters verbatim:
此语法激活适当的转义等，以避免 SQL 注入攻击等。如果您有受信任的输入并想跳过转义，您可以逐字插入参数：
SELECT * FROM {tableName}WHERE id = #{id}
SELECT * FROM {tableName}WHERE id = #{id}
Now, I want to do a LIKE search on unsafe input, so what I want to do is this:
现在，我想对不安全的输入进行 LIKE 搜索，所以我想做的是：
SELECT * FROM Person WHERE name LIKE #{beginningOfName} || '%'
SELECT * FROM Person WHERE name LIKE #{beginningOfName} || '%'
Unfortunately, however, important DB servers don't support the ||syntax for concatenation:
然而不幸的是，重要的数据库服务器不支持||连接语法：
MSSQL - Breaks the standard by using the '+' operator instead of '||'.
...
MySQL - Badly breaks the standard by redefining || to mean OR.
MSSQL - 通过使用“+”运算符而不是“||”来打破标准。
...
MySQL - 通过重新定义 || 严重违反了标准 表示或。
So, I could do either
所以，我可以
SELECT * FROM Person WHERE name LIKE CONCAT(#{beginningOfName}, '%')
SELECT * FROM Person WHERE name LIKE CONCAT(#{beginningOfName}, '%')
and be confined to, in this case, MySQL, or I could do
并且仅限于，在这种情况下，MySQL，或者我可以做
SELECT * FROM Person WHERE name LIKE '{beginningOfName}%'
SELECT * FROM Person WHERE name LIKE '{beginningOfName}%'
and would have to sanitize input myself.
并且必须自己清理输入。
Is there a more elegant solution?
有没有更优雅的解决方案？
采纳答案by bobince
Typically this is done by adding the %to the parameter itself before passing it in, in whatever language you're using outside of SQL. However note that either way you might still need to do an escaping step if your search term may have _or %in it. See eg this questionfor background.)
通常，这是通过%在传入参数之前将其添加到参数本身来完成的，无论您在 SQL 之外使用什么语言。但是请注意，无论哪种方式，如果您的搜索词可能有_或%在其中，您可能仍然需要执行转义步骤。参见例如这个问题的背景。）
To fix the concatenation problem in general, put MySQL into ANSI sql_modeand you get proper support for the ||operator, as well as correct handling of double quotes for schema names rather than string literals.
要解决一般的连接问题，请将 MySQL 置于ANSI sql_mode 中，您将获得对||运算符的适当支持，以及正确处理模式名称而不是字符串文字的双引号。
(If you can't do that you'd have to build a function to build the statement out of either ||or CONCAT(), abstracting away the difference.)
（如果你不能这样做，你就必须构建一个函数来从||or 中构建语句CONCAT()，抽象出差异。）
回答by Bartosz Bilicki
You could use bindsyntax
您可以使用绑定语法
Quoting Official documentation
引用官方文档
The bind element lets you create a variable out of an OGNL expression and bind it to the context. For example:
bind 元素允许您从 OGNL 表达式中创建一个变量并将其绑定到上下文。例如：
<select id="selectBlogsLike" resultType="Blog">
<bind name="pattern" value="'%' + _parameter.getTitle() + '%'" />
SELECT * FROM BLOG
WHERE title LIKE #{pattern}
</select>
回答by JORGE ROMERO
the bind include inside the if
绑定包含在 if 中
<select id="select" parameterType="java.util.Map" resultType="ViajeDTO">
SELECT A.ID_VIAJE ID, A.NOMBRE, A.DESCRIPCION, A.FINICIO, A.FFIN, A.LOGO, A.URL,
A.ID_CLIENTE IDCLIENTE, B.NOMBRE CLIENTE
FROM VIAJE A
INNER JOIN CLIENTE B ON (A.ID_CLIENTE = B.ID_CLIENTE)
WHERE A.ESTATUS = 1
<if test="P_NOMBRE != null">
<bind name="pattern" value="'%' + P_NOMBRE + '%'" />
AND A.NOMBRE LIKE #{pattern}
</if>
</select>
回答by Trang NT
if you're using mybatis, you can write this for s
如果你用的是mybatis，你可以为s写这个
SELECT(" * ");
FROM(" student ");
WHERE(" ten LIKE '%' #{ten} '%' ");
回答by Luis Enrique Moreno Gutierrez
We can make use of Bind. The bind element lets you create a variable out of an expression and bind it to the context. For example:
我们可以使用Bind。bind 元素允许您从表达式中创建变量并将其绑定到上下文。例如：
<select id="select" parameterType="java.util.Map" resultType="ViajeDTO">
<bind name="pattern" value="'%' + P_NOMBRE + '%'" />
SELECT A.ID_VIAJE ID, A.NOMBRE, A.DESCRIPCION, A.FINICIO, A.FFIN, A.LOGO, A.URL,
A.ID_CLIENTE IDCLIENTE, B.NOMBRE CLIENTE
FROM VIAJE A
INNER JOIN CLIENTE B ON (A.ID_CLIENTE = B.ID_CLIENTE)
WHERE A.ESTATUS = 1
<if test="P_NOMBRE != null">
AND A.NOMBRE LIKE #{pattern}
</if>
</select>
Taking into account that the parameter to look for is P_NAME.
考虑到要查找的参数是 P_NAME。
回答by changed
||operator worked for me in IBatis but not in Mybatis.
||操作员在 IBatis 中对我来说有效，但不在 Mybatis 中工作。
In MyBatis i had to use +operator.
在 MyBatis 中，我不得不使用+运算符。
SELECT *
FROM Employee
WHERE
name like '%' + #{searchName,jdbcType=NVARCHAR} + '%'
