Shulou(Shulou.com)06/01 Report--

This article mainly shows you "what is the difference between # {} and ${} in mybatis". The content is easy to understand and clear. I hope it can help you solve your doubts. Let the editor lead you to study and learn the article "what are the differences between # {} and ${} in mybatis".

1. The difference between ${} and # {} in MyBatis 1.1 ${} and # {} demonstration

Database data:

Dao interface:

List findByUsername (String username); List findByUsername2 (String username)

Mapper.xml:

Select * from user where username like # {username} select * from user where username like'% ${value}%'

Execute the test code:

@ Testpublic void findByUsername () throws Exception {InputStream in = Resources.getResourceAsStream ("SqlMapConfig.xml"); SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder (); SqlSessionFactory factory = builder.build (in); / / true: autocommit SqlSession session = factory.openSession (true); UserDao userDao = session.getMapper (UserDao.class); List userList = userDao.findByUsername ("% small%"); List 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 the execution result:

All discoveries can be queried.

1.2 SQL injection issu

${} will generate SQL injection, while # {} will not cause SQL injection problem

Let's do a test:

List userList2 = userDao.findByUsername2 ("aaa' or 1--"); System.out.println ("userList2:"); for (User user: userList2) {System.out.println (user);}

SQL statement generated by the query:

The parameter we passed is aaa' or 1--, which results in all the data being queried.

You can imagine what if I want to delete it according to id?

Delete from user where id='$ {value}'

If what I pass is: 1'or 1: 1;-- what the result will be like, I think you already know.

Here id is Integer type. If it is not good to test, I will not take you to test it. If you are interested, you can test it yourself in private.

If # {} was used above, there would be no SQL injection problem.

1.3 the difference between ${} and # {}

# {} matches a placeholder, which is equivalent to one of the JDBC, filters some sensitive characters and adds double quotes to the values passed 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, which does not prevent sql injection problems.

View the SQL statements generated by # {} and ${}:

String abc= "123"

# {abc} = "123"

${value} = 123

1.4 # {} how does the underlying layer prevent SQL injection? 1.4.1 answers on the Internet

There are a lot of questions about this kind of questions on the Internet, summed up for two reasons:

1) # {} the underlying layer uses PreparedStatement and is precompiled, so there is no SQL injection problem.

In fact, precompilation is a function of MySQL itself, which has nothing to do with PreparedStatement; and precompilation is not the precompilation that we understand, and the bottom layer of PreparedStatement does not use precompilation at all by default (we have to turn it on manually)! Read down in detail

2) # {} will not produce string concatenation, but ${} will produce string concatenation, so ${} will have the problem of SQL injection

Neither of these answers can stand deep research, and in the end, the answer is only superficial, and no one knows exactly why.

1.4.2 Why can SQL injection be prevented?

Let's take a look at the source code of the MySQL driver.

Open the setString () method of the PreparedStatement class (MyBatis does this with the help of the setString () method when passing parameters in # {}, but not ${}):

All the source code of the 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) { //遍历字符串，获取到每个字符 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; } } } 我们执行#{}的查询语句，打断点观察： 最终传递的参数如下： 最终传递的参数为：'aaa\' or 1=1 -- 咱们在数据库中执行如下SQL语句（肯定是查询不到数据的）： select * from user where username like 'aaa\' or 1=1 -- '

What if the "/" added by PreparedStatement is removed? Let's try SQL:

Select * from user where username like 'aaa' or 1 -'

We can also analyze the problem by observing the SQL statements generated by # {} and ${} through the log of MySQL:

1) enable MySQL log:

Add the following configuration under [mysqld] in the MySQL configuration file:

# whether to enable mysql log 0: disable (default) 1: enable the location where general-log=1# mysql log is stored general_log_file= "D:/query.log"

2) restart the MySQL service (to run as an administrator):

Net stop mysqlnet start mysql

Use mybatis to execute the following two SQL statements:

View the MySQL log:

1.5 # {} and ${} application scenarios

If # {} is so much better than ${}, why does ${} still exist? Why not just use # {} and everything will be all right?

In fact, it is not. ${} also has the opportunity to display its talents. We all know that ${} will generate string concatenation to generate a new string.

1.5.1 differences in usage between ${} and # {}

For example, now we need to make 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%'

If the parameter passed in is "Zhang" at this time

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 passed parameter 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%'

From the SQL statement above, we can see that # {} is enclosed in double quotes, while ${} matches the real value.

Another point is that if you use ${}, you must enter value, that is, ${value}, and # {} at will

1.5.2 under what circumstances do I use ${}?

An example of a scenario:

Code testing:

After execution, it is found that the execution is successful.

We can switch and change ${} to # {}. There will be an exception of SQL syntax error.

1.6 summarize 1.6.1 SQL injection issues

The reason why MyBatis's # {} can prevent SQL injection is that the underlying layer uses the setString () method of the PreparedStatement class to set parameters. This method takes each character of the passed parameter and compares it in a loop. If sensitive characters (such as single quotation marks, double quotation marks, etc.) are found, the symbol will be escaped with a'/'in front of it, making it a normal string and will not participate in the generation of SQL statements. To achieve the effect of preventing 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 value passed in, so when we pass parameters, we usually pass them directly without double quotation marks, but not ${}. We need to add them manually.

2) when passing a parameter, we said that any value can be written in # {}, and ${} must use value; that is: ${value}

3) # {} filters characters for SQL injection, while ${} is only passed as a normal value and does not take into account these problems

4) the application scenario of # {} is to pass conditional values to where sentences of SQL statements, and the application scenario of ${} is to pass some values that need to participate in the syntax generation of SQL statements.

The above is all the content of this article entitled "what is the difference between # {} and ${} in mybatis". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.