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

In this issue, the editor will bring you about the injection and prevention of SQL and the basic role of myBaits. The article is rich in content and analyzes and describes for you from a professional point of view. I hope you can get something after reading this article.

SQL injection

In embedded SQL programming, sql statements are usually submitted to the database management system in the form of strings. SQL injection uses SQL syntax to add some malicious code to the string to obtain unauthorized data.

For example, if a user logs in (assuming that the user name is admin and the password is 123456), the following statement is usually used to determine

Select * from user where username='admin' and password='123456'

If the record is obtained, login is allowed, otherwise it prompts "the user name does not exist or the password is incorrect". In addition, I do not know the user password and know that the user name is "admin". When entering the user name, the user name is changed to admin'--, and the sql statement is changed to

Select * from user where username='admin'-- and password='123456'

The login is successful. Enter the password as shown in the figure below. If you don't do anything to prevent SQL injection, you can log in successfully.

Suppose: I typed "admin;drop table user;-" for the user name; what will happen?

Methods to prevent SQL injection

The PreparedStatement provided by JDBC can prevent SQL injection; after PreparedStatement precompiles sql, the parameters in the sql statement need to be used. Instead. Then call the setXX () method to set the parameters in the sql statement. If you pass in special values in this way, there will be no problem of sql injection. The sample code is as follows:

String sql= "select * from user where username='?'" + "and password='?'"; PreparedStatement pstmt = conn.prepareStatement (sql); pstmt.setString (1, "admin"); pstmt.setString (2, "123456"); / / 5, execute statement ResultSet rs=pstmt.executeQuery (sql)

Another way is to write SQL statements to the stored procedure, which can also prevent SQL injection by completing the query through the stored procedure.

The use of ${} and # {} in mybaits

"$" is a splicing character; using "${}" means that sql statements have been concatenated in a high-level language, and variables are not quoted, such as user name admin and parameter variable sname.

Select * from user where username='$ {sname}'- in this case, the SQL statement given to the database by the high-level language is select * from user where username='admin'

It is not possible to prevent SQL injection in this case.

# {} is a placeholder; using "# {}" can only bring the parameters in # {} into the database management system

Select * from user where username=# {sname}-in this case, the following SQL is first precompiled, then the parameter is brought in, and the parameter value is enclosed in single quotation marks select * from user where username=?

In this case, SQL injection can be prevented.

Since # {} can prevent SQL injection and "${}" can't, why does mybaits provide such a symbol? Of course, there is a reason for myBatis. If the database object in the SQL statement needs to pass parameters, you can only use * * ${} * *. For example, if you query the user table (user), the parameter variable is tableName='user',. The code can only be

Select * from ${tableName} / / convert the SQL statement to select * from user

When variables are used as parameters in the where clause in myBatis, # {} is used, and "" is prohibited in order to prevent SQL injection. Only when the SQL statement contains data library objects (such as tables, views, etc.) can "{}" be used to prevent SQL injection. SQL statements contain database objects (such as tables, views, etc.) to use "" to prevent SQL injection; SQL statements contain database objects (such as tables, views, etc.) to use "{}", because # {} automatically quotes variables, such as the above example:

Select * from # {tableName} / / is converted into SQL statement select * from 'user'. This is the basic function of SQL injection and prevention and myBaits shared by Xiaobian. If you happen to have similar doubts, please refer to the above analysis to understand. If you want to know more about it, you are 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.