(如何)我可以使用“喜欢" MyBatis进行SQL查询时是否安全且与数据库无关? [英] (How) can I use "LIKE" in SQL queries with MyBatis safely and DB-agnostic?

问题描述

在 MyBatis 中，您标记应在SQL中插入参数的位置，如下所示:

In MyBatis, you mark the places where parameters should be inserted into your SQL like so:

选择*来自id为#{id}

此语法可激活适当的转义等，以避免SQL注入攻击.如果您具有可靠的输入并且想跳过转义，则可以逐字插入参数:

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:

选择*来自 {tableName} ，其中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:

选择*来自名称类似的人#{beginningOfName} || '％'

不幸的是，但是重要的数据库服务器不要支持连接的||语法:

Unfortunately, however, important DB servers don't support the || syntax for concatenation:

MSSQL-通过使用'+'运算符而不是'||'来破坏标准.

MSSQL - Breaks the standard by using the '+' operator instead of '||'.

...

MySQL-通过重新定义||严重违反了标准表示或".

MySQL - Badly breaks the standard by redefining || to mean OR.

所以，我可以做

选择*来自名称类似的人 CONCAT(#{beginningOfName}，'％')

，并且仅限于MySQL，否则我可以做

and be confined to, in this case, MySQL, or I could do

选择*来自姓名类似的人'{beginningOfName}％'

，并且必须自己清理输入内容.

and would have to sanitize input myself.

有没有更优雅的解决方案?

Is there a more elegant solution?

推荐答案

通常，这是通过在传递参数之前将%添加到参数本身来完成的，而使用的是您在SQL之外使用的任何语言.但是请注意，如果您的搜索词中可能包含_或%，则可能仍然需要执行转义步骤.参见例如此问题作为背景.)

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 question for background.)

要解决一般的串联问题，请将MySQL放入 ANSI sql_mode ，您将获得对||运算符的适当支持，以及对架构名称而不是字符串文字的双引号的正确处理.

To fix the concatenation problem in general, put MySQL into ANSI sql_mode and you get proper support for the || operator, as well as correct handling of double quotes for schema names rather than string literals.

(如果不能这样做，则必须构建一个函数以从||或CONCAT()中构建语句，以抽象出差异.)

(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.)

这篇关于(如何)我可以使用“喜欢" MyBatis进行SQL查询时是否安全且与数据库无关?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！