${} and #{} in MyBatis
1.1 The ${}and# {}demo
Database data:
The dao interface:
List<User> findByUsername(String username);
List<User> findByUsername2(String username);
Copy the codeMapper. XML:
<! ParameterType ="java.lang.String" resultType="com.lscl.entity.User"> select * from user where username like #{username} </select> <! ParameterType ="java.lang.String" ${$} --> <select id="findByUsername2" parameterType="java.lang.String" resultType="com.lscl.entity.User"> select * from user where username like '%${value}%' </select>Copy the codeExecute the test code:
@Test public void findByUsername() throws Exception { InputStream in = Resources.getResourceAsStream("SqlMapConfig.xml"); SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder(); SqlSessionFactory factory = builder.build(in); // true: automatically 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(); }Copy the codeView the execution result:
All discoveries can be queried
1.2 SQL injection Problems
${} causes SQL injection, #{} does not cause SQL injection problems
Let’s take a test:
List<User> userList2 = userDao.findByUsername2(" aaa' or 1=1 -- ");
System.out.println("userList2: ");
for (User user : userList2) {
System.out.println(user);
}
Copy the codeSQL statement generated by query:
We passed aaa’ or 1=1 –, resulting in a full query.
You can imagine, what if I want to delete by ID?
delete from user where id='${value}'
Copy the codeIf I pass: 1′ or 1=1; I think you already know what the result will be.
I have an Integer id here, so I’m not going to test it for you, but if you’re interested, you can test it privately.
The SQL injection problem would not have occurred if #{} had been used above
1.3 The ${}and# {}The difference between
#{} matches a placeholder, equivalent to one in JDBC? , some sensitive characters are filtered, and values passed are double quoted after compilation, thus preventing SQL injection problems.
${} matches the actual passed value, which is then concatenated with the SQL statement. ${} will concatenate strings with other SQL and does not prevent SQL injection problems.
#{} and ${} generate SQL statements:
A String of ABC = "123"; #{abc}="123" ${value}=123;Copy the code1.4 # {}What is the underlying protection against SQL injection?
1.4.1 Online answers
There are so many questions about this on the Internet that I can sum up two reasons:
1)# {}The bottom layer is PreparedStatement, which will be precompiled, so there will be no SQL injection problems;
Preparedstatements are not preparedStatements. PreparedStatement does not use precompilation at all by default in PreparedStatement. Read on for details
2)# {}Does not generate string concatenation,The ${}Will produce string concatenation, soThe ${}SQL injection problems occur;
Neither answer holds up to much scrutiny, and in the end the answer is superficial, and no one knows exactly why.
1.4.2 Why CAN SQL Injection Be Prevented?
Let’s open the MySQL driver source code to see what it is;
${} ${} ${} ${} ${} ${} ${} ${} ${}
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; Char c = x.charat (I); 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; }}}Copy the codeWe execute the query #{} to observe the break point:
The final parameters passed are as follows:
The final argument passed is: ‘aaa\’ or 1=1 —
SQL > select * from database;
select * from user where username like 'aaa\' or 1=1 -- '
Copy the code
What if you removed the “/” in PreparedStatement? Let’s execute SQL:
select * from user where username like 'aaa' or 1=1 -- '
Copy the code
${$} = ${$} = ${$}
MySQL > enable MySQL
Add the following configuration to [mysqld] :
General-log =1 general-log=1 general-log_file ="D:/query.log"Copy the code
MySQL > restart MySQL server (run as administrator)
net stop mysql
net start mysql
Copy the codeExecute the following two SQL statements with Mybatis:
MySQL > select * from ‘MySQL’;
1.5 # {}andThe ${}Application scenarios of
Why does ${} exist when #{} is so much better than ${}? Why not just use #{}?
${} will generate a string concatenation to create a new string
1.5.1 The difference between ${} and #{
For example, now you want to do a fuzzy query to query the information of all employees surnamed Zhang in the user table
Select * from user where name like ‘%’
If you pass in “zhang”
${} : select * from user where name like ‘${value}%’
Select * from user where name like ‘%’
#{} : select * from user where name like #{value}”%”
Select * from user where name like ‘zhang ‘”%”
If the parameter passed is “zhang %”
#{} : select * from user where name like #{value}
Select * from user where name like ‘%’
${} : select * from user where name like ‘${value}’
Select * from user where name like ‘%’
${} matches the actual value. ${} matches the actual value.
${value} = ${value} ${value} = ${value
1.5.2 When is it usedThe ${}?
Scenario Example:
Code test:
The command is successfully executed
${$} = ${$} = ${$
1.6 summarize
1.6.1 SQL Injection Problems
The #{} function of MyBatis prevents SQL injection because it uses the PreparedStatement class setString() method to set parameters. This method retrieves each character of the parameter and performs a loop comparison. If sensitive characters are found (e.g. Single quotation mark, double quotation mark, etc.), and a ‘/’ is added before it to indicate that the symbol is escaped, so that it becomes a common string, which does not participate in the generation of SQL statements and prevents SQL injection.
Second, ${} itself is designed to participate in the syntax generation of SQL statements, which naturally leads to SQL injection problems (regardless of character filtering).
${} = ${{}; ${} = ${}; ${} = ${}
${} (${}); ${} (${}); ${} (${}); Namely: ${value}
3) #{} is used for SQL injection. ${} is just passed as a normal value and does not consider these problems
${} is used to pass conditional values to SQL statements. ${} is used to pass values to SQL statements.