1, The difference between ${} and #{} in MyBatis
1.1 ${} and #{} demo
Database data:
dao interface:
List<User> findByUsername(String username); List<User> findByUsername2(String username);
Mapper.xml:
<!-- use#{} -->
<select id="findByUsername" parameterType="java.lang.String" resultType="com.lscl.entity.User">
select * from user where username like #{username}
</select>
<!-- use ${},be careful ${}The value in must be filled in value -->
<select id="findByUsername2" parameterType="java.lang.String" resultType="com.lscl.entity.User">
select * from user where username like '%${value}%'
</select>
Execute test code:
@Test
public void findByUsername() throws Exception {
InputStream in = Resources.getResourceAsStream("SqlMapConfig.xml");
SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
SqlSessionFactory factory = builder.build(in);
// true: Auto submit
SqlSession session = factory.openSession(true);
UserDao userDao = session.getMapper(UserDao.class);
List<User> userList = userDao.findByUsername("%Small%");
List<User> userList2 = userDao.findByUsername2("Small");
System.out.println("userList: ");
for (User user : userList) {
System.out.println(user);
}
System.out.println("userList2: ");
for (User user : userList2) {
System.out.println(user);
}
session.close();
in.close();
}
View execution results:
You can find out everything you find
1.2 SQL injection
${} will generate SQL injection and #{} will not generate SQL injection
Let's do a test:
List<User> userList2 = userDao.findByUsername2(" aaa' or 1=1 -- ");
System.out.println("userList2: ");
for (User user : userList2) {
System.out.println(user);
}
SQL statement generated by query:
The parameter we passed is AAA 'or 1 = 1 --, resulting in the query of all data.
You can imagine, what if I want to delete according to id?
delete from user where id='${value}'
If I pass: 1 'or 1 = 1; --, I think you should already know what the result will be.
My id here is of Integer type. If it's not easy to test, I won't test it with you. If you are interested, you can test it privately.
If #{} is used above, the problem of SQL injection will not occur
Difference between 1.3 ${} and #{}
#{} matches a placeholder, which is equivalent to a?, in JDBC?, Some sensitive characters will be filtered. After compilation, double quotes will be added to the passed values, so SQL injection problems can be prevented.
${} matches the real value passed. After passing, it will be spliced with the sql statement$ {} will splice strings with other sql, which cannot prevent sql injection problems.
To view SQL statements generated by #{} and ${}:
String abc="123";
#{abc}="123"
${value}=123;
1.4 #{} how does the bottom layer prevent SQL injection?
1.4.1 online answers
There are many such problems on the Internet, which can be summarized for two reasons:
1) The #{} bottom layer adopts PreparedStatement, which will be precompiled, so there will be no SQL injection problem;
In fact, precompiling is MySQL's own function, which has nothing to do with PreparedStatement; Moreover, precompiling is not what we understand. In addition, precompiling is not used at the bottom of PreparedStatement by default (we need to start it manually)! Look down in detail
2) #{} will not produce string splicing, and ${} will produce string splicing, so ${} will have SQL injection problems;
Neither of these two answers can stand in-depth study. In the end, the answer just stays on the surface, and no one knows why.
1.4.2 why can SQL injection be prevented?
Let's open the source code of MySQL driver and have a look;
Open the setString() method of PreparedStatement class (MyBatis uses the setString() method to #{} pass parameters, but ${} is not):
All source codes of setString() method:
public void setString(int parameterIndex, String x) throws SQLException {
synchronized(this.checkClosed().getConnectionMutex()) {
if (x == null) {
this.setNull(parameterIndex, 1);
} else {
this.checkClosed();
int stringLength = x.length();
StringBuilder buf;
if (this.connection.isNoBackslashEscapesSet()) {
boolean needsHexEscape = this.isEscapeNeededForString(x, stringLength);
Object parameterAsBytes;
byte[] parameterAsBytes;
if (!needsHexEscape) {
parameterAsBytes = null;
buf = new StringBuilder(x.length() + 2);
buf.append('\'');
buf.append(x);
buf.append('\'');
if (!this.isLoadDataQuery) {
parameterAsBytes = StringUtils.getBytes(buf.toString(), this.charConverter, this.charEncoding, this.connection.getServerCharset(), this.connection.parserKnowsUnicode(), this.getExceptionInterceptor());
} else {
parameterAsBytes = StringUtils.getBytes(buf.toString());
}
this.setInternal(parameterIndex, parameterAsBytes);
} else {
parameterAsBytes = null;
if (!this.isLoadDataQuery) {
parameterAsBytes = StringUtils.getBytes(x, this.charConverter, this.charEncoding, this.connection.getServerCharset(), this.connection.parserKnowsUnicode(), this.getExceptionInterceptor());
} else {
parameterAsBytes = StringUtils.getBytes(x);
}
this.setBytes(parameterIndex, parameterAsBytes);
}
return;
}
String parameterAsString = x;
boolean needsQuoted = true;
if (this.isLoadDataQuery || this.isEscapeNeededForString(x, stringLength)) {
needsQuoted = false;
buf = new StringBuilder((int)((double)x.length() * 1.1D));
buf.append('\'');
for(int i = 0; i < stringLength; ++i) { //Traverse the string to get each character
char c = x.charAt(i);
switch(c) {
case '\u0000':
buf.append('\\');
buf.append('0');
break;
case '\n':
buf.append('\\');
buf.append('n');
break;
case '\r':
buf.append('\\');
buf.append('r');
break;
case '\u001a':
buf.append('\\');
buf.append('Z');
break;
case '"':
if (this.usingAnsiMode) {
buf.append('\\');
}
buf.append('"');
break;
case '\'':
buf.append('\\');
buf.append('\'');
break;
case '\\':
buf.append('\\');
buf.append('\\');
break;
case '¥':
case '₩':
if (this.charsetEncoder != null) {
CharBuffer cbuf = CharBuffer.allocate(1);
ByteBuffer bbuf = ByteBuffer.allocate(1);
cbuf.put(c);
cbuf.position(0);
this.charsetEncoder.encode(cbuf, bbuf, true);
if (bbuf.get(0) == 92) {
buf.append('\\');
}
}
buf.append(c);
break;
default:
buf.append(c);
}
}
buf.append('\'');
parameterAsString = buf.toString();
}
buf = null;
byte[] parameterAsBytes;
if (!this.isLoadDataQuery) {
if (needsQuoted) {
parameterAsBytes = StringUtils.getBytesWrapped(parameterAsString, '\'', '\'', this.charConverter, this.charEncoding, this.connection.getServerCharset(), this.connection.parserKnowsUnicode(), this.getExceptionInterceptor());
} else {
parameterAsBytes = StringUtils.getBytes(parameterAsString, this.charConverter, this.charEncoding, this.connection.getServerCharset(), this.connection.parserKnowsUnicode(), this.getExceptionInterceptor());
}
} else {
parameterAsBytes = StringUtils.getBytes(parameterAsString);
}
this.setInternal(parameterIndex, parameterAsBytes);
this.parameterTypes[parameterIndex - 1 + this.getParameterIndexOffset()] = 12;
}
}
}
The query statement we executed #{} was interrupted by the following observation:
The parameters finally passed are as follows:
The final parameter passed is: 'aaa\' or 1=1--
Let's execute the following SQL statements in the database (the data cannot be queried):
select * from user where username like 'aaa\' or 1=1 -- '
What if you remove the "/" added to the PreparedStatement? Let's execute SQL:
select * from user where username like 'aaa' or 1=1 -- '
We can also observe the SQL statements generated by #{} and ${} through MySQL logs to analyze problems:
1) Open MySQL log:
Add the following configuration under [mysqld] in MySQL configuration file:
# Enable mysql log 0: off (default) 1: on general-log=1 # Storage location of mysql logs general_log_file="D:/query.log"
2) Restart MySQL service (to run as administrator):
net stop mysql net start mysql
Use mybatis to execute the following two SQL statements:
View MySQL logs:
Application scenarios of 1.5 #{} and ${}
Since #{} is so much better than ${}, why does ${} exist? Just use it #{} and everything will be all right?
In fact, ${} is also useful. We all know that ${} will produce string splicing to generate a new string
1.5.1 differences between ${} and #{} usage
For example, now you need to perform a fuzzy query to query the information of all employees surnamed Zhang in the user table
The sql statement is: select * from user where name like 'Zhang%'
At this time, if the parameter passed in is Zhang
If you use ${}: select * from user where name like '${value}%'
Generated sql statement: select * from user where name like 'Zhang%'
If you use #{}: select * from user where name like #{value} "%"
Generated sql statement: select * from user where name like 'Zhang' "%"
If the parameter passed in is "Zhang%"
Use #{}: select * from user where name like #{value}
Generated sql statement: select * from user where name like 'Zhang%'
Use ${}: select * from user where name like '${value}'
Generated sql statement: select * from user where name like 'Zhang%'
Through the above SQL statement, we can find that #{} is enclosed in double quotes, and ${} matches the real value.
Another point is that if ${} is used, value must be filled in, that is, ${value}, #{} is optional
1.5.2 under what circumstances do you use ${}?
Scenario example:
Code test:
After execution, it is found that the execution is successful
We can switch from ${} to #{}, and an exception with SQL syntax error will appear
1.6 summary
1.6.1 SQL injection problem
MyBatis #{} is able to prevent SQL injection because the underlying layer uses the setString() method of PreparedStatement class to set parameters. This method will obtain each character of the passed parameter, and then conduct cyclic comparison. If sensitive characters (such as single quotation mark, double quotation mark, etc.) are found, a '/' will be added in front to represent the escape of this symbol, Let it become an ordinary string and do not participate in the generation of SQL statements, so as to prevent SQL injection.
Secondly, ${} itself is designed to participate in the syntax generation of SQL statements, which will naturally lead to the problem of SQL injection (character filtering will not be considered).
1.6.2 #{} and ${} usage summary 1) #{} when using, we will choose whether to add double quotation marks according to the passed in value. Therefore, when we pass parameters, we generally pass them directly without double quotation marks, ${} will not, and we need to add them manually
2) When passing a parameter, we said that #{} can write any value, and ${} must use value; I.e. ${value}
3) #{} performs character filtering for SQL injection, and ${} only passes values as normal, without considering these problems
4) The application scenario of #{} is to pass the condition value to the where clause of the SQL statement, and the application scenario of ${} is to pass some values that need to participate in the syntax generation of the SQL statement.