Ibatis 动态映射（基本用法,Iterate实现in查询，Or查询,Sql注入例子)

1. 配置文件

<!-- 动态映射 -->
 
  <!--  基本用法 -->
  <select id="dynamicSelect" parameterClass="user" resultClass="user">
   <![CDATA[
    select
     loginid,
     username,
     loginpassword
    from users
  
    ]]>
     <dynamic prepend="WHERE">
    
     <!-- 注意like的两种用法 oracle连接字符串符合为||，SqlServer为+-->
    <isNotEmpty prepend="AND" property="username">
     (username like '%'+#username#+'%')
    </isNotEmpty>
 
    <!--  此种方式，会引起Sql注入攻击,传入参数"ad' or '%'='" -->
    <isNotEmpty prepend="AND" property="loginid">
     loginid like '%$loginid$%'
    </isNotEmpty>
 </dynamic>
  </select>
 
  <!-- Iterate用法 ,实现In查询-->
  <select id="dynamicIterate" parameterClass="java.util.Map" resultClass="user">
   <![CDATA[
    select
     loginid,
     username,
     loginpassword
    from users
  
    ]]>
    <iterate prepend=" where loginid in " property="userlist" open="(" close=")" conjunction=",">
     '$userlist[].loginid$'
     
    </iterate>
   
   
    
  </select>
 
 
  <!-- 动态映射实现or查询 -->
  <select id="dynamicOr" parameterClass="user" resultClass="user">
  <![CDATA[
    select
     loginid,
     username,
     loginpassword
    from users
   where 1=1
    ]]>
   
    <!-- 当username和loginid都不为空时，需要再追加and (1=1 -->
     <dynamic>
     <isEmpty property="username">
      <isEmpty prepend="and"  property="loginid">
    (1=1
     </isEmpty>
     </isEmpty>
   
    </dynamic>
  
   
    <dynamic prepend=" and (">
     <isNotEmpty prepend="or" property="username">
   username like '%'+#username#+'%'
    </isNotEmpty>
    <isNotEmpty prepend="or" property="loginid">
   loginid=#loginid#
    </isNotEmpty>
    </dynamic>
   
  

 

 ) order by loginid desc
  </select>

2.生成测试类

/**
  * 基本查询，模仿Sql注入攻击
  * @return
  * @throws IOException
  * @throws SQLException
  */
 List<Users> DynamicSelect() throws IOException, SQLException
 {
  Users user=new Users();
  //user.setLoginid("ad");
  //模拟Sql注入攻击，查询出所有的用户
  user.setLoginid("ad' or '%'='");
  user.setUsername("理员");
  
  SqlMapClient client=SqlClientFactory.getClientMap();
  client.startTransaction();
  List<Users> list=(List<Users>)client.queryForList("user_space.dynamicSelect",user);
  client.commitTransaction();
  return list;
 }
 
 
 /**
  * 动态映射实现Or查询
  * @return
  * @throws IOException
  * @throws SQLException
  */
 List<Users> DynamicSelectOr() throws IOException, SQLException
 {
  Users user=new Users();
  user.setLoginid("");
  user.setUsername("");
  
  SqlMapClient client=SqlClientFactory.getClientMap();
  client.startTransaction();
  List<Users> list=(List<Users>)client.queryForList("user_space.dynamicOr",user);
  client.commitTransaction();
  return list;
 }
 
 /**
  * Iterate实现in查询
  * @return
  * @throws IOException
  * @throws SQLException
  */
 List<Users> DynamicSelectIterate() throws IOException, SQLException
 {
  Map<String,List<Users>> map=new HashMap<String,List<Users>>();
  
  List<Users> list=new ArrayList<Users>();
  
  Users user=new Users();
  user.setLoginid("admin");
  list.add(user);
  
  user=new Users();
  user.setLoginid("leon");
  list.add(user);
  
  map.put("userlist", list);
  
  SqlMapClient client=SqlClientFactory.getClientMap();
  client.startTransaction();
  List<Users> resultList=(List<Users>)client.queryForList("user_space.dynamicIterate",map);
  client.commitTransaction();
  return resultList;
 }