使用mybatis防止sql注入的安全方法

I am using '$' notation in mybatis query:

我在mybatis查询中使用'$'表示法:

<select id="queryOrgs" resultMap="orgLiteMap" parameterType="gov.cbrc.gzbanking.data.QueryRequest" >
        select id, name from sys_orgs
        <if test="filter != null">where id like #{filter, jdbcType=INTEGER} or name like #{filter, jdbcType=INTEGER}</if>
        <if test="order != null">order by ${order}</if>
        limit #{offset, jdbcType=INTEGER},#{fetch, jdbcType=INTEGER}
     </select>

the order parameter can be something like "id desc", do I need to worry about sql injection here? We know that mybatis uses PreparedStatement, if mybatis call PreparedStatement#executeQuery() against "select" statement, or the jdbc driver implementation does't allow multiple statements in one call, then sql injection is impossible, am I right?

order参数可以是“id desc”之类的东西,我需要担心sql注入吗?我们知道mybatis使用PreparedStatement,如果mybatis对“select”语句调用PreparedStatement#executeQuery(),或者jdbc驱动程序实现在一次调用中不允许多个语句,那么sql注入是不可能的,我是对的吗?

If that is possible for sql injection in my case, what's the verified way to prevent it ?

如果在我的情况下可以进行sql注入,那么经过验证的方法是什么呢?

------------------------ edit -----------------------

------------------------编辑-----------------------

Is that enough to check order parameter has a sql delimiter?

是否足以检查order参数是否有sql分隔符?

2 个解决方案

#1

If order comes directly from the user, you can get into trouble. You should never trust user input.

如果订单直接来自用户,您可能会遇到麻烦。你永远不应该相信用户输入。

To respond to your questions:

回答你的问题:

If you don't allow multiple queries you will get an exception if you try running more than one, yes;

如果你不允许多个查询,如果你尝试运行多个查询,你会得到一个例外,是的;

checking for an SQL delimiter might be enough, might not;

检查SQL分隔符可能就足够了,可能不行;

Using ${} exposes you to SQL injection if you send the user input unchanged to the SQL. The above two methods are fragile:

如果将用户输入不变地发送到SQL,则使用$ {}会向您公开SQL注入。以上两种方法都很脆弱:

you can easily forget to configure the connection or a colleague of yours might turn it on because he/she needs to execute multiple queries in one statement and they were not aware about this particular statement

你很容易忘记配置连接,或者你的同事可能会打开它,因为他/她需要在一个语句中执行多个查询而他们不知道这个特定的语句

hackers might be smarter than checks you have in place (e.g have you considered testing for \u003B too? What else might an attacker attempt?).

黑客可能比你现有的支票更聪明(例如你是否考虑过测试?攻击者还可以尝试什么?)。

Always perform your own escapes and checks and send a value you control, not something received from the user. Have all the correct values in your code and send that instead. It's then just a matter of determining which one the user wanted and fallback to a default if you can't determine it, something like:

始终执行自己的转义和检查并发送您控制的值,而不是从用户收到的值。在代码中包含所有正确的值并将其发送。这只是确定用户想要哪一个并且如果你无法确定它而回退到默认值的问题,例如:

String userChoice = order.toLowerCase();
String safeOrder = null;

if ("id desc".equals(userChoice)) {
    safeOrder = "id desc";
} else if ("id asc".equals(userChoice)) {
    safeOrder = "id asc";
....
....
} else if ("name desc".equals(userChoice)) {
    safeOrder = "name desc";
} else {
    // if no clean match then maybe user tried something fishy... 
    // ... go with some default 
    safeOrder = "id desc"; // or null or whatever you like...
}

then in your mapping you do:

然后在你的映射中你做:

<if test="safeOrder != null">order by ${safeOrder}</if>

#2

See https://mybatis.github.io/mybatis-3/sqlmap-xml.html. section 'String Substitution' for offical comment on that.

请参阅https://mybatis.github.io/mybatis-3/sqlmap-xml.html。 “字符串替换”部分对此有正式评论。

It's not safe to accept input from a user and supply it to a statement unmodified in this way. This leads to potential SQL Injection attacks and therefore you should either disallow user input in these fields, or always perform your own escapes and checks.

接受来自用户的输入并以这种方式将其提供给未经修改的语句是不安全的。这会导致潜在的SQL注入攻击,因此您应该禁止在这些字段中输入用户,或者始终执行自己的转义和检查。

#1