What are the differences between ${} and #{} in java?

Preface

${} and #{} are both used to replace parameters in MyBatis. They can both replace the user The passed parameters are replaced into the SQL finally generated by MyBatis, but their differences are very big. Let's take a look at them next.

1. Different functions

${} is to directly replace parameters into SQL,For example, the following code:

<select id="getUserById" resultType="com.example.demo.model.UserInfo">
    select * from userinfo where id=${id}
</select>

The final generated execution SQL is as follows:

As can be seen from the above figure, the previous parameter ${id} is directly replaced with the specific parameter The value is 1. #{} uses placeholders and preprocessing to execute business . We transform the above case into the form of #{}. The implementation code is as follows:

<select id="getUserById" resultType="com.example.demo.model.UserInfo">
    select * from userinfo where id=#{id}
</select>

The final generated SQL is as follows:

##1.1 Problem with ${}

When the parameter is When the parameter type is a numeric type (without considering security issues), the execution effects of

${} and #{} are the same. However, when the parameter type is a character, using ${} will There is a problem, The following code is shown:

<select id="getUserByName" resultType="com.example.demo.model.UserInfo">
    select * from userinfo where name=${name}
</select>

When the above program is executed, the generated SQL statement is as follows:

This will cause the program to report an error, because the parameters passed are of character type, and in SQL syntax, if it is a character type, single quotes need to be added to the value, otherwise an error will be reported, and

${} is a direct replacement and single quotes will not be automatically added, so an error will be reported when executed. The use of #{} uses placeholder pre-execution, so there is no problem. Its implementation code is as follows:

<select id="getUserByName" resultType="com.example.demo.model.UserInfo">
    select * from userinfo where name=#{name}
</select>

The execution SQL finally generated by the above program is as follows:

2. Different usage scenarios

Although using #{} method can handle any type of parameters, when the parameter passed is a SQL command or SQL When the keyword #{} is used, there will be a problem. For example, when we want to query based on price from high to low (reverse order), or from low to high (forward order)

At this time we want to pass the sorting keyword, desc reverse order (price from high to high Low) or asc positive sequence (price from low to high), at this time we use the implementation code of

${} Ryan:

<select id="getAll" resultType="com.example.demo.model.Goods">
  select * from goods order by price ${sort}
</select>

The above code generates the execution SQL and The running results are as follows:

However, if ${} in the code is changed to #{}, an error will be reported during program execution, #{ } The implementation code is as follows:

<select id="getAll" resultType="com.example.demo.model.Goods">
  select * from goods order by price #{sort}
</select>

The execution SQL and running results generated by the above code are as follows:

From the above execution results, we can see: When passing ordinary parameters, you need to use the #{} method, and when passing SQL commands or SQL keywords, you need to use ${ } to directly replace and execute the parameters in SQL.

3. Different security

##${}

and #{} The main difference is reflected in the security aspect. When using ${} There will be security issues, that is, SQL injection issues. However, using #{} will not cause security issues because it is preprocessed. Let's observe the difference between the two through the login function below. 3.1 Use ${} to implement user login

The implementation code in UserMapper.xml is as follows:

<select id="login" resultType="com.example.demo.model.UserInfo">
  select * from userinfo where name=&#39;${name}&#39; and password=&#39;${password}&#39;
</select>

The unit test code is as follows:

@Test
void login() {
    UserInfo userInfo = userMapper.login("java", "java");
    System.out.println(userInfo);
}

The execution SQL and running results generated by the above code are as follows:

As can be seen from the results, when we When the correct username and password are passed in, the data can be successfully queried. However, when we use

${}

, when we do not know the correct password, we can also use SQL injection statements to obtain the user's private information. The implementation code of SQL injection is as follows: <div class="code" style="position:relative; padding:0px; margin:0px;"><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:java;">@Test void login() { UserInfo userInfo = userMapper.login(&quot;java&quot;, &quot;&amp;#39; or 1=&amp;#39;1&quot;); System.out.println(userInfo); }</pre><div class="contentsignin">Copy after login</div></div><div class="contentsignin">Copy after login</div></div>

The execution SQL and running results generated by the above code are as follows:

从上述结果可以看出，当使用 ${} 时，在不知道正确密码的情况下也能得到用户的私人数据，这就像一个小偷在没有你们家钥匙的情况下，也能轻松的打开你们家大门一样，这是何其恐怖的事情。那使用 #{} 有没有安全问题呢？接下来我们来测试一下。

3.2 使用 #{} 实现用户登录

首先将 UserMapper.xml 中的代码改成以下内容：

<select id="login" resultType="com.example.demo.model.UserInfo">
  select * from userinfo where name=#{name} and password=#{password}
</select>

接着我们使用上面的 SQL 注入来测试登录功能：

<div class="code" style="position:relative; padding:0px; margin:0px;"><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:java;">@Test void login() { UserInfo userInfo = userMapper.login(&quot;java&quot;, &quot;&amp;#39; or 1=&amp;#39;1&quot;); System.out.println(userInfo); }</pre><div class="contentsignin">Copy after login</div></div><div class="contentsignin">Copy after login</div></div>

最终生成的 SQL 和执行结果如下： 

 从上述代码可以看出，使用 SQL 注入是无法攻破 #{} 的“大门”的，所以可以放心使用。

The above is the detailed content of What are the differences between ${} and #{} in java?. For more information, please follow other related articles on the PHP Chinese website!