Detailed explanation of mybatis method to prevent SQL injection
SQL injection is a very simple attack, but it is still very common today. The reason is no patch for stupid. Why? Let's take Java as an example:
Suppose there are such tables in the database:
Then use the JDBC operation table:
The above code is often used by some developers. Imagine this situation when the userid parameter passed in is "3; drop table user;" The SQL statements executed are as follows:
After the database was compiled and executed, the user table was deleted. Look, a simple SQL injection attack has taken effect! This is because the above code does not meet the programming specification.
When we program according to the specification, SQL injection does not exist. This is also the first way to avoid SQL injection: precompiled statements. The code is as follows:
Why doesn't SQL injection exist in the above code? Because the precompiled statement is used, the precompiled statement will "select name from user where id =?" The statement is compiled in advance, so that when executed, it only needs to be replaced with the passed parameters? Placeholder. For the first case that does not conform to the specification, the program will generate SQL statements and compile them with the contents passed in by the user, which is exactly the problem.
In addition to using precompiled statements, there is a second way to avoid SQL injection attacks: stored procedures. Stored procedure is a set of SQL statements that complete specific functions. It is compiled and stored in the database. Users can execute it by calling the stored procedure and giving parameters (if the stored procedure has parameters), and can also avoid SQL injection attacks
The corresponding stored procedure in the above code is as follows:
Of course, the user can also check the character at the front end, which is also a way to avoid SQL injection: for example, for the above userid parameter, the user will prompt an error if it contains a semicolon.
However, from the most fundamental reason, the SQL injection attack exists because the app does not use the minimum permission when accessing the database. It seems that everyone has been using the root account to access the database.
So how does mybatis avoid SQL injection attacks? Take the above table user as an example: suppose the mapper file is:
The corresponding java file is:
You can see that the input parameter is a userid of string type. When we pass in userid = "34; drop table user;" After printing, the printed statement is as follows:
No matter what userid is entered, his SQL statement is like this. This is due to the use of precompiled statements in the underlying implementation of mybatis. When the database executes the statement, it directly uses the precompiled statement, and then replaces the placeholder with the incoming userid? It's running. Does not exist replace placeholder first? Then compile, so SQL injection has no room for survival.
So how does MySQL precompile SQL? In fact, the Preparedstatement class is used at the bottom of the framework. Preparedstaement class can not only avoid SQL injection, because it has been precompiled. When the same SQL statement is executed n times, the compilation time is saved (n-1) times, which can improve efficiency.
If the above statement is changed to:
When we enter userid = "34; drop table user;" After printing, the printed statement is as follows:
At this time, mybatis does not use precompiled statements. It will splice strings before compiling. This process is the process of SQL injection taking effect.
Therefore, when writing the mapping statement of mybatis, try to use the format of "#{XXX}". If you have to use parameters such as "${XXX}", you should filter manually to prevent SQL injection attacks.
summary
The above is an example of how to prevent SQL injection in mybatis introduced by Xiaobian. I hope it will help you. If you have any questions, please leave me a message, and Xiaobian will reply to you in time. Thank you very much for your support for the programming tips website!