Mybatis SQL传参中的表名问题

问题

我想使用Mybatis，让用户指定表名，然后查询具体的数据总数
凭感觉写了条下面的语句

<select id="getCountByTableName" resultType="Long" parameterType="String">
    select count(*) from tableName=#{tableName};
</select>

ERROR o.a.c.c.C.[.[localhost].[/].[dispatcherServlet] - Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.springframework.jdbc.BadSqlGrammarException: 
### 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 file [E:\java_web\myface\face\target\classes\mapping\RegisterRetMapper.xml]
### The error may involve com.jing.face.mapper.RegisterRetMapper.getCountByTableName
### The error occurred while executing a query
### SQL: select count(*) from tableName=?;
### 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
; bad SQL grammar []; nested exception is 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] with root 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

很尴尬，报错了

<select id="getCountByTableName" resultType="Long" parameterType="String">
    select count(*) from #{tableName};
</select>

ERROR o.a.c.c.C.[.[localhost].[/].[dispatcherServlet] - Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.springframework.jdbc.BadSqlGrammarException: 
### 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 file [E:\java_web\myface\face\target\classes\mapping\RegisterRetMapper.xml]
### The error may involve com.jing.face.mapper.RegisterRetMapper.getCountByTableName
### The error occurred while executing a query
### SQL: select count(*) from ?;
### 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
; bad SQL grammar []; nested exception is 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] with root 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

还是报错了

解决

<select id="getCountByTableName" resultType="Long" parameterType="String" statementType="STATEMENT">
    select count(*) from ${tableName};
</select>

原因

我们先看原生SQL语句中：

[SQL]select count(*) from "user";
[Err] 1064 - 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 '"user"' at line 1

[SQL]select count(*) from 'user';
[Err] 1064 - 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 ''user'' at line 1

可以看到将表名加上单引号或双引号，都会报SQL解析错误
只有不加引号才是正确的

select count(*) from user;

5000000

所以我们使用了#{ },导致字符串被加上了引号导致的，而使用${ }却不会

#{}与${}的区别可以简单总结如下：

#{ } 解析为一个 JDBC 预编译语句（prepared statement）的参数标记符。
${ } 仅仅为一个纯碎的 string 替换，在动态 SQL 解析阶段将会进行变量替换

• #{}将传入的参数当成一个字符串，会给传入的参数加一个双引号
• ${}将传入的参数直接显示生成在sql中，不会添加引号
• #{}能够很大程度上防止sql注入，${}无法防止sql注入

${}在预编译之前已经被变量替换了，这会存在sql注入的风险。如下sql

select * from ${tableName} where name = ${name}

如果传入的参数tableName为user; delete user; --，那么sql动态解析之后，预编译之前的sql将变为：

select * from user; delete user; -- where name = ?;

--之后的语句将作为注释不起作用，顿时我和我的小伙伴惊呆了！！！看到没，本来的查询语句，竟然偷偷的包含了一个删除表数据的sql，是删除，删除，删除！！！重要的事情说三遍，可想而知，这个风险是有多大。

所以我们应该

1. ${}一般用于传输数据库的表名、字段名等
2. 能用#{}的地方尽量别用${}

要实现动态调用表名和字段名，就不能使用预编译了，需添加statementType=“STATEMENT”" 。

statementType：STATEMENT（非预编译），PREPARED（预编译）或CALLABLE中的任意一个，这就告诉 MyBatis 分别使用Statement，PreparedStatement或者CallableStatement。默认：PREPARED。这里显然不能使用预编译，要改成非预编译。

其次，sql里的变量取值是${xxx},不是#{xxx}。

因为${}是将传入的参数直接显示生成sql，如${xxx}传入的参数为字符串数据，需在参数传入前加上引号，如：

    String name = "sprite";
    name = "'" + name + "'";