java - ibatis 内联参数 order by 子句中带有 #

Question

下面是我的ibatis map 配置，

    <?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE sqlMap 
PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN"
"http://ibatis.apache.org/dtd/sql-map-2.dtd">

<sqlMap namespace="Contact">
<!--- Showing all data of table -->
<select id="getAll" resultClass="com.nik.Contact">
  select * from contact
  <dynamic prepend="where ">
        salary like '%'
    <isNotNull  property="orderby" >
        order by #orderby#, #orderby2#
    </isNotNull>
    </dynamic>
</select>
</sqlMap>

这是我的pojo

package com.nik;

public class Contact {
      private String firstName; 
      private String lastName; 
      private String email; 
      private String salary;
      private String mobile;
      private String orderby;
      private String orderby2;
      private int id;

      public Contact() {}

      public Contact(
      String firstName,
        String lastName,
        String email) {
      this.firstName = firstName;
      this.lastName = lastName;
      this.email = email;
      }

      public String getEmail() {
      return email;
      }
      public void setEmail(String email) {
      this.email = email;
      }
      public String getFirstName() {
      return firstName;
      }
      public void setFirstName(String firstName) {
      this.firstName = firstName;
      }
      public int getId() {
      return id;
      }
      public void setId(int id) {
      this.id = id;
      }
      public String getLastName() {
      return lastName;
      }
      public void setLastName(String lastName) {
      this.lastName = lastName;
      }

    public String getSalary() {
        return salary;
    }

    public void setSalary(String salary) {
        this.salary = salary;
    }

    public String getMobile() {
        return mobile;
    }

    public void setMobile(String mobile) {
        this.mobile = mobile;
    }

    public String getOrderby() {
        return orderby;
    }

    public void setOrderby(String orderby) {
        this.orderby = orderby;
    }

    public String getOrderby2() {
        return orderby2;
    }

    public void setOrderby2(String orderby2) {
        this.orderby2 = orderby2;
    } 
    }

这是我的测试课，

package com.nik;

import com.ibatis.common.resources.Resources;
import com.ibatis.sqlmap.client.SqlMapClient;
import com.ibatis.sqlmap.client.SqlMapClientBuilder;
import java.io.*;
import java.sql.SQLException;
import java.util.*;

import org.apache.log4j.Logger;

public class IbatisExample{
  public static void main(String[] args)
  throws IOException,SQLException{
    // get a logger instance named "com.foo"
       Logger  logger = Logger.getLogger("com.nik");
  Reader reader = Resources.getResourceAsReader("ibatis-config.xml");
  SqlMapClient sqlMap = 
  SqlMapClientBuilder.buildSqlMapClient(reader);
  //Output all contacts
  System.out.println("All Contacts");
  Contact c1 = new Contact();
  c1.setOrderby("salary");
  c1.setOrderby2("mobile");
  List<Contact> contacts = (List<Contact>)
  sqlMap.queryForList("Contact.getAll",c1);
  Contact contact = null;
  for (Contact c : contacts) {
  System.out.print("  " + c.getId());
  System.out.print("  " + c.getFirstName());
  System.out.print("  " + c.getLastName());
  System.out.print("  " + c.getEmail());
  System.out.print("  " + c.getSalary());
  System.out.print("  " + c.getMobile());
  contact = c; 
  System.out.println("");
  }  
  }
}

这里的问题是 order by 子句不起作用......

这是我们的输出

All Contacts
DEBUG [main] - Created connection 71786792.
DEBUG [main] - {conn-100000} Connection
DEBUG [main] - {pstm-100001} PreparedStatement:    select * from contact   where      salary like '%'        order by ?, ?     
DEBUG [main] - {pstm-100001} Parameters: [salary, mobile]
DEBUG [main] - {pstm-100001} Types: [java.lang.String, java.lang.String]
DEBUG [main] - {rset-100002} ResultSet
DEBUG [main] - {rset-100002} Header: [id, firstName, lastName, email, salary, mobile]
DEBUG [main] - {rset-100002} Result: [1, abc, 111, abc@hyahoo.com, 5000, 400]
DEBUG [main] - {rset-100002} Result: [2, def, 222, def@yahoo.com, 2000, 100]
DEBUG [main] - {rset-100002} Result: [3, xyz, 333, xyz@yahoo.com, 3000, 300]
DEBUG [main] - Returned connection 71786792 to pool.
  1  abc    111  abc@hyahoo.com  5000  400
  2  def   222  def@yahoo.com  2000  100
  3  xyz  333  xyz@yahoo.com  3000  300

如果我在 map 配置中将“#”更改为“$”(例如按 $orderby$、$orderby2$ 排序)，那么它就可以工作，

All Contacts
DEBUG [main] - Created connection 71786792.
DEBUG [main] - {conn-100000} Connection
DEBUG [main] - {pstm-100001} PreparedStatement:    select * from contact   where      salary like '%'        order by salary, mobile     
DEBUG [main] - {pstm-100001} Parameters: []
DEBUG [main] - {pstm-100001} Types: []
DEBUG [main] - {rset-100002} ResultSet
DEBUG [main] - {rset-100002} Header: [id, firstName, lastName, email, salary, mobile]
DEBUG [main] - {rset-100002} Result: [2, def, 222, def@yahoo.com, 2000, 100]
DEBUG [main] - {rset-100002} Result: [3, xyz, 333, xyz@yahoo.com, 3000, 300]
DEBUG [main] - {rset-100002} Result: [1, abc, 111, abc@hyahoo.com, 5000, 400]
DEBUG [main] - Returned connection 71786792 to pool.
  2  def   222  def@yahoo.com  2000  100
  3  xyz  333  xyz@yahoo.com  3000  300
  1  abc  111  abc@hyahoo.com  5000  400

关于为什么带 # 的内联参数不能按顺序工作的任何线索？我不能使用 $，因为根据 fortify 360，这存在安全风险:(

Answer 1

$var$ 语法是告诉 iBATIS 在构造准备语句之前将 var 属性的值按原样替换到 SQL 中的唯一方法。 Fortify 警告其存在安全风险的原因是，如果直接来自不受信任来源的数据未经事先验证就按原样插入到 SQL 中，则可能会为 SQL 注入(inject)攻击打开大门。

要防止此类 SQL 注入(inject)，您可以修改 Contact POJO，以便 setOrderBy 和 setOrderBy2 方法测试其参数并仅允许如果这些参数在可接受值集中，则要设置对象的相应属性。类似于以下内容:

public void setOrderBy(String orderBy) {
   if (orderBy.equals("name") or orderBy.equals(" salary") or orderBy.equals("dept")) {
      this.orderBy = orderBy;
   } else {
      this.orderBy = NULL;
   }     
}

只要像这样验证所有替换值，使用 $var$ 语法应该是完全可以接受且安全的。