[大数据]Mybatis的#{}与${}与和statementType的结合

#{}：以预编译的形式进行查询，所有要传的参数均用？代替，

${}:以传输值的方式查询，有SQL注入的危险。

当指定statementType属性时：

1.#{}与PREPARED

  //#{}与PREPARED
  /*
<select  statementType="PREPARED" id="selectUserById" resultType="org.example.pojo.User">
    select * from t_user WHERE id = #{id}
</select>
*/
    @Test
    public void testSelectUserById() throws Exception{

        SqlSession session = getSqlSession();//自定义方法，返回一个SqlSession对象
        try {
            UserMapper mapper = session.getMapper(UserMapper.class);
            User user = mapper.selectUserById(9);
            System.out.println(user);
        }finally {
            session.close();
        }
    }

结果：正常

?2.#{}与STATEMENT

//#{}与STATEMENT
/*<select  statementType="STATEMENT" id="selectUserById" resultType="org.example.pojo.User">
    select * from t_user WHERE id = #{id}
</select>
*/
    @Test
    public void testSelectUserById() throws Exception{

        SqlSession session = getSqlSessionFactory();
        try {
            UserMapper mapper = session.getMapper(UserMapper.class);
            User user = mapper.selectUserById(9);
            System.out.println(user);
        }finally {
            session.close();
        }
    }

结果：提示SQL语法错误，检查第一行的？号。

Error querying database. ?Cause: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '?' at line 1
### The error may exist in t_user.xml
### The error may involve org.example.dao.UserMapper.selectUserById
### The error occurred while executing a query
### SQL: select * from t_user WHERE id = ?
### Cause: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '?' at line 1

3.${}与PREPARED

//${}与PREPARED
/*<select  statementType="PREPARED" id="selectUserById" resultType="org.example.pojo.User">
    select * from t_user WHERE id = ${id}
</select>
*/
    @Test
    public void testSelectUserById() throws Exception{

        SqlSession session = getSqlSessionFactory();
        try {
            UserMapper mapper = session.getMapper(UserMapper.class);
            User user = mapper.selectUserById(9);
            System.out.println(user);
        }finally {
            session.close();
        }
    }

结果：正常

?4.${}与STATEMENT

  //${}与STATEMENT
/*<select  statementType="STATEMENT" id="selectUserById" resultType="org.example.pojo.User">
    select * from t_user WHERE id = ${id}
</select>
*/
    @Test
    public void testSelectUserById() throws Exception{

        SqlSession session = getSqlSessionFactory();
        try {
            UserMapper mapper = session.getMapper(UserMapper.class);
            User user = mapper.selectUserById(9);
            System.out.println(user);
        }finally {
            session.close();
        }
    }

结果：正常

?从3的结果我们得知，当用${}的方法传参并且指定statementType为PREPARED时，程序成功运行了。那么能不能用这种方式来解决${}的sql注入问题呢？

验证：

1.在UserMapper接口中准备根据姓名查询用户方法

List<User> selectUserByName(String username);

2.在对应的xml文件中准备该方法的select标签。（注意statementType的类型为PREPARED，sql参数为${}）

<select id="selectUserByName" statementType="PREPARED" resultType="org.example.pojo.User">
        SELECT * FROM t_user WHERE username = ${0}
    </select>

3.准备

@Test
    public void testSelectUserByname() throws Exception{
        SqlSession session = getSqlSessionFactory();
        try {
            UserMapper mapper = session.getMapper(UserMapper.class);
            List<User> users = mapper.selectUserByName("admin" + "OR 1=1");
            for(User user:users){
                System.out.println(user);
            }
        }finally {
            session.close();
        }

    }

结果：编译通过

?得出结论：当使用${}并且指定statementType为PREPARED时，并不能解决sql注入问题。mybatis仍是直接将传递的参数拼接进sql中而不是用占位符代替。