SoFunction
Updated on 2025-03-04

Summary of the difference between #{} and ${} in MyBatis

In MyBatis,#{}and${}are two common placeholders, and their functions and usage scenarios are different. Understanding their differences is very important for the correct use of MyBatis.

In Mybatis interviews, the difference between #{} and ${} is often involved.

1. #{} is precompiled processing, and ${} is string replacement.

2. When MyBatis handles #{}, it replaces #{} in SQL with a ? number and uses the PreparedStatement set method to assign values; when MyBatis handles ${}, it replaces ${} with the value of the variable.

3. Using #{} can effectively prevent SQL injection and improve system security.

1. Basic differences between #{} and ${}

#{}: SQL parameter placeholder

effect：#{}Used to securely bind incoming parameters into SQL statements, it will be used automaticallyPreparedStatementof?Placeholder mechanism, and MyBatis preprocesses incoming parameters (such as preventing SQL injection).

Parameter replacement: When generating SQL statements,#{}Will be replaced with one?, then the parameter value is bound to this by the JDBC driver?on placeholder.

Use scenarios: Usually used to pass parameters entered by users, such as query conditions, inserted or updated data, etc.

Example：

<select  resultType="User">
    SELECT * FROM users WHERE id = #{id}
</select>

  • If theidTo 1, the SQL generated by MyBatis is similar to:SELECT * FROM users WHERE id = ?, and then passPreparedStatementWill?Replace with 1.

${}: SQL text placeholder

effect：${}Used to directly replace the passed parameter value into the SQL statement, it does not perform preprocessing, so the parameter value is directly inserted into the SQL statement. This means${}The parameters will be treated as part of SQL, which may cause SQL injection risks.

Parameter replacement: When generating SQL statements,${}The value passed in will be replaced directly into the SQL statement without using it?Placeholder.

Use scenarios: Usually used to dynamically generate SQL fragments, such as sorting field names, table names, column names, etc. parameters that do not come directly from user input.

Example：

<select  resultType="User">
    SELECT * FROM users WHERE ${columnName} = #{value}
</select>

  • If thecolumnNameforusername，valueforJohn, the SQL generated by MyBatis is similar to:SELECT * FROM users WHERE username = ?, and then passPreparedStatementWill?Replace withJohn。

2. Safety and usage recommendations

  • SQL injection protection:because#{}Can usePreparedStatementThe parameter binding mechanism can effectively prevent SQL injection attacks. and${}Directly splicing parameter values ​​into SQL may lead to SQL injection, so it should be used with caution.

  • Dynamic SQL generation：${}It is more suitable for generating dynamic SQL fragments, such as dynamic table names, column names, etc. But make sure that the incoming value is trustworthy, or ensure security through other means.

3. Specific examples

Examples using #{}

<select  resultType="User">
    SELECT * FROM users WHERE id = #{id}
</select>

IncomingidTo be 1, generate SQL as:

SELECT * FROM users WHERE id = ?

Then bind parameter 1 safely to?superior.

Example using ${}

<select  resultType="User">
    SELECT * FROM users WHERE ${columnName} = #{value}
</select>

IncomingcolumnNameforusername，valueforJohn, generate SQL as:

SELECT * FROM users WHERE username = ?

Then put the parametersJohnBind to?superior.

SQL injection risk example

<select  resultType="User">
    SELECT * FROM users WHERE ${columnName} = #{value}
</select>

If thecolumnNameforusername OR '1'='1', the generated SQL might be:

SELECT * FROM users WHERE username OR '1'='1' = ?

This may lead to SQL injection problems.

Summarize

  • #{}: Safely pass parameters to prevent SQL injection, and is often used to pass parameters input by users.

  • ${}: Insert parameter values ​​directly into SQL, which is suitable for dynamically generating SQL fragments (such as table names and column names), but there is a risk of SQL injection and should be used with caution.

In actual development, it is recommended to use it as much as possible#{}to pass parameters to ensure SQL security, while for use${}In scenarios, it is necessary to ensure that the incoming parameters are safe and verified.

This is the end of this article about the difference between /#{} and /${} in MyBatis. For more related contents of MyBatis /#{} and /${}, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!