sql语句中#{}和${}的区别(The difference between #{} and ${} in SQL statements)

进行参数绑定时,我们常使用 #{},因为它可以防止SQL注入

#{} 是预编译处理,它会先将SQL中的#{}替换为?号编译,然后再取值

原始SQL:select * from user where user_name = #{name} 
预编译后:select * from user where user_name = ?
然后调用set方法来赋值‘李四’

将传入的数据都当成一个字符串,会对自动传入的数据加一个双引号。如:order by #user_id#,如果传入的值是111,那么解析成sql时的值为order by “111”, 如果传入的值是id,则解析成的sql为order by “id”。

${} 会先去变量值,再去编译SQL语句

原始SQL:  select * from user where user_name = #{name}
编译后SQL:select * from user where user_name = ‘李四’
这样很容易会遭到恶意SQL的拼接来非法操作数据

${}将传入的数据直接显示生成在sql中。如:order by userid,如果传入的值是111,那么解析成sql时的值为order by user_id, 如果传入的值是id,则解析成的sql为order by id。

#{} 能防止SQL注入的原因:

因为采用预编译机制,预编译完成后,SQL的结构已经固定,
这时候,即使用户输入非法的参数,也不会对SQL语句的整体结构造成影响,从而避免了SQL注入的危险

————————

When binding parameters, we often use #{}, because it can prevent SQL injection

#{} is a precompiled process. It will first replace #{} in SQL with? No. compile and then take value

原始SQL:select * from user where user_name = #{name} 
预编译后:select * from user where user_name = ?
然后调用set方法来赋值‘李四’

Treat the incoming data as a string, and add a double quotation mark to the automatically incoming data. For example: order by #user_ ID #, if the value passed in is 111, the value parsed into SQL is order by “111”. If the value passed in is ID, the value parsed into SQL is order by “Id”.

${} will remove the variable value first, and then compile the SQL statement

原始SQL:  select * from user where user_name = #{name}
编译后SQL:select * from user where user_name = ‘李四’
这样很容易会遭到恶意SQL的拼接来非法操作数据

${} directly displays the incoming data and generates it in SQL. For example: order by userid. If the value passed in is 111, the value when parsing into SQL is order by user_ ID. if the value passed in is ID, the SQL parsed is order by ID.

#{} reasons for preventing SQL injection:

Because the precompiling mechanism is adopted, the structure of SQL has been fixed after precompiling,
At this time, even if the user enters illegal parameters, it will not affect the overall structure of the SQL statement, thus avoiding the danger of SQL injection