MyBatis中#{}和${}的区别

在MyBatis 的映射配置文件中，动态传递参数有两种方式：

1. #{} 占位符
2. ${} 拼接符

#{} 和 ${} 的区别

区别1

#{} 为参数占位符 ?，即 sql 预编译
${} 为字符串替换，即 sql 拼接

区别2

#{}：动态解析 -> 预编译 -> 执行
${}：动态解析 -> 编译 -> 执行

区别 3

#{} 的变量替换是在 DBMS 中
${} 的变量替换是在 DBMS 外

区别 4

变量替换后，#{} 对应的变量对于String类型会自动加上单引号 ‘’，其他类型不加。
变量替换后，${} 对应的变量都不会加上单引号 ‘’

区别 5

#{} 采用预编译，其后的参数不会再进行SQL编译，所以可以防止SQL注入。
${} 每执行一次都需要重新编译一次，不能防止sql 注入

什么是SQL预编译

数据库接收到sql语句后，需要词法/语法解析，优化sql语句，制定执行计划。多数情况下，相同的sql语句可能只是传入参数不同（如where条件后的值不同…）。

如果每次都需要经过上面的词法/语法解析、语句优化、制定执行计划等，则效率就明显不行了。所以预编译的优势就体现出来了。预编译语句在被MySQL编译器编译后，执行代码会被缓存下来。那么下次调用时，只要是相同的预编译语句就再不需要编译，只要将参数直接传入编译过的语句执行代码中即可。

预编译SQL的用法

以 MySQL 为例，在 MySQL 中，所谓预编译其实是指先提交带占位符的 SQL 模板，然后为其指定一个 key，MySQL 先将其编译好，然后用户再拿着 key 和占位符对应的参数让 MySQL 去执行。

一个标准的预编译 SQL 的用法如下：

# 提交带有占位符的参数化 SQL，也可以理解为 SQL 模板
prepare prepare_query from 'select * from t_user where username = ?';# 指定一个参数
set @name = '王五';# 指定参数化 SQL 的 key 和参数，让 MySQL 自己去拼接执行
execute prepare_query using @name;

先通过 prepare 设置一个 SQL 模板，然后通过 execute 提交参数，MySQL 会自行根据参数替换占位符，到最后执行的 SQL 就是：

select * from t_user where username = '王五';

预编译的原理，为什么说预编译SQL可以防止注入？

按网上的说法，prepare 执行的时候实际上 SQL 已经编译完了，所以可以防止注入，因为后续不管塞什么参数都不可能在调整语法树了，换个角度想，这是不是说明，如果我们一开始就让 prepare 执行的 SQL 模板的关键字变成占位符，是不是应该在这个时候就编译不通过？

比如，可以把查询的表名改成占位符：

prepare prepare_query from 'select * from ? where username = ?';# 执行结果
# > 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 '? where username = ?' at line 1

实际上也确实不行，因为编译时必须确定主表，因此在 from 后面加占位符会导致预编译不通过。

那么如果把查询得字段改成占位符，只在查询字段里面套一个嵌套查询呢？

prepare prepare_query from 'select ? from t_user';
SET @c = '(select * from t_user) as q';
EXECUTE prepare_query using @c;# 执行结果
# (select * from t_user) as q
# (select * from t_user) as q
# (select * from t_user) as q
# ...

查询成功了，不过得到的结果的固定的 (select * from t_user) 这个字符串，我们检查一下 MySQL 的执行日志，看看最终执行的 SQL 变成什么样了：

Prepare	select ? from t_user
Query	SET @c = '(select * from t_user) as q'
Query	EXECUTE prepare_query using @c
Execute	select '(select * from t_user) as q' from t_user # 最终执行的SQL

显然，(select * from t_user) 参数本身被直接转义为了一串普通的字符串，我们试图“注入”的 SQL 片段完全不会生效。

换而言之，对于预编译 SQL 来说，我们作为模板的参数化 SQL 已经完成的编译过程，这段 SQL 包含几条有效语句？查哪张表？查哪些字段？作为条件的字段有哪些？…这些在 prepare 语句执行完后都是固定的，此后我们再通过 execute 语句塞进去的任何参数，都会进行转义，不会再作为 SQL 的一部分。这就是为什么说预编译 SQL 可以防止注入的原因。

JDBC的预编译

直接在驱动的 url 上配上 useServerPrepStmts=true ，这样就会启用 MySQL 的预编译功能。否则不会使用 MySQL 的预编译功能。

测试代码：

String sql = "select * from s_user where username = ?";
PreparedStatement preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1, "王五' union select * from s_user");
ResultSet resultSet = preparedStatement.executeQuery();

设置了 useServerPrepStmts=true 后再执行代码，去数据库查看执行日志有：

Execute select * from s_user where username = '王五\\' union select * from s_user'
Prepare select * from s_user where username = ?

此时 MySQL 的预编译功能就真正的生效了。

#{} 和 ${} 的实例：假设传入参数为 1

实例步骤一

#{}：select * from t_user where uid=#{uid}${}：select * from t_user where uid= '${uid}'

实例步骤二

#{}：select * from t_user where uid= ?${}：select * from t_user where uid= '1'

实例步骤三

#{}：select * from t_user where uid= '1'${}：select * from t_user where uid= '1'

可以看到使用#{}动态传递参数采用了sql预编译，这也是为啥#{}可以防止SQL注入的原因。

#{} 和 ${} 在使用中的技巧和建议

1. 不论是单个参数，还是多个参数，一律都建议使用注解@Param(“”)
2. 能用 #{} 的地方就用 #{}，不用或少用 ${}
3. 表名作参数时，必须用 ${}。如：select * from ${tableName}
4. 列名作参数时，必须用 ${}。如：select * from user where ${column} = #{value}
5. order by 时，必须用 ${}。如：select * from t_user order by ${columnName}
6. 表名处用#{}会直接报错；列名处用#{}会查询不到数据；order by后面用#{}排序不生效
7. 使用${}时，要注意何时加或不加单引号，即 ${} 和 '${}'。一般字段类型为char或者varchar时需要加单引号

${}的使用场景举例

当 SQL 语句中的元数据（如表名或列名）是动态生成的时候，字符串替换将会非常有用。 举个例子，如果你想 select 一个表任意一列的数据时，不需要这样写：

@Select("select * from user where id = #{id}")
User findById(@Param("id") long id);@Select("select * from user where name = #{name}")
User findByName(@Param("name") String name);@Select("select * from user where email = #{email}")
User findByEmail(@Param("email") String email);// 其它的 "findByXxx" 方法

而是可以只写这样一个方法：

@Select("select * from user where ${column} = #{value}")
User findByColumn(@Param("column") String column, @Param("value") String value);

其中 ${column} 会被直接替换，而 #{value} 会使用 ? 预处理。 这样，就能完成同样的任务：

User userOfId1 = userMapper.findByColumn("id", 1L);
User userOfNameKid = userMapper.findByColumn("name", "kid");
User userOfEmail = userMapper.findByColumn("email", "noone@nowhere.com");

这种方式也同样适用于替换表名的情况。

ps： 用这种方式接受用户的输入，并用作语句参数是不安全的，会导致潜在的 SQL 注入攻击。因此，要么不允许用户输入这些字段，要么自行转义并检验这些参数。