What is? SQL Inject , How to prevent SQL Inject ？

So-called SQL Inject , It’s through SQL Command insert into Web Form submit or Enter query string for domain name or page request , Finally, to cheat the server to execute malicious SQL command . say concretely , It’s about leveraging existing applications , take （ malice ） Of SQL Ability of command injection into background database engine , It can be Web Input in form （ malice ）SQL Statement to get a database on a website with a security vulnerability , Not according to the designer’s intention SQL sentence .

How to prevent SQL Inject , Use stored procedures to execute all queries ; Check the validity of user input ; Put the user’s login name 、 The password and other data are encrypted and saved . 

At present, before operating on the database , Use prepareStatement precompile , Then fill in the data according to the wildcard , It’s a common practice , The benefit is to improve the efficiency of execution , And it’s guaranteed to be excluded SQL Inject holes .

One 、prepareStatement Precompiling and prevention of SQL Injection function

Everybody knows ,java in JDBC in , There’s a preprocessing function , One of the advantages of this function is that it can improve the execution speed, especially when the database is operated multiple times , Another advantage is prevention SQL Inject , Strictly speaking , It should be to prevent the vast majority of SQL Inject .

       The usage is as shown below ：

String sql="update cz_zj_directpayment dp"+
"set dp.projectid = ? where dp.payid= ?";
try {
PreparedStatement pset_f = conn.prepareStatement(sql);
pset_f.setString(1,inds[j]);
pset_f.setString(2,id);
pset_f.executeUpdate(sql_update);
}catch(Exception e){
//e.printStackTrace();
logger.error(e.message());
}

Why does it prevent SQL Injection to improve security ？ Because SQL Statement is precompiled before the program runs , Before the first operation of the database while the program is running ,SQL Statements have been analyzed by the database , Compile and optimize , The corresponding execution plan is also cached and allows the database to query in parameterized form , When the runtime dynamically passes parameters to PreprareStatement when , Even if there are sensitive characters such as or ‘1=1’ Also, the database will be treated as a parameter, the attribute value of a field, not as a SQL Instructions , such , That’s it SQL The effect of injection is ！

Two 、Statement and PreparedStatement The difference between

First to speak , What is? java Medium Statement：Statement yes java An important way to perform database operations , Used on the basis of established database connection , Send to the database what to execute SQL sentence . Specific steps ：

1. First, import. java.sql.*; This package .

2. And then load the driver , Create connection , obtain Connection The implementation object of the interface , For example, the object is called do conn.

3. And then use conn Object to create Statement Example , The method is ：Statement stmt = conn.creatStatement(“SQL Statement string “);

Statement Object is used to SQL Statements are sent to the database . There are actually three kinds of Statement object , They are all executed as on a given connection SQL Statement’s package container ：Statement、PreparedStatement（ It is from Statement inherited ） and CallableStatement（ It is from PreparedStatement inherited ）. They are all dedicated to sending specific types of SQL sentence ：Statement Object is used to perform simple without parameters SQL sentence ;PreparedStatement Object is used to perform precompiling with or without parameters SQL sentence ;CallableStatement Object is used to execute calls to stored procedures in the database .

in summary , Summarized below ：Statement Every time you execute sql sentence , Databases are executed sql Statement compilation , It is best used when a query is executed only once and results are returned , Efficiency is higher than PreparedStatement. But there is sql Injection risk .PreparedStatement It’s precompiled . Executes one of the mutable arguments SQL when ,PreparedStatement than Statement The high efficiency , because DBMS Precompile a SQL Of course, it’s better than compiling one more time SQL The high efficiency . Better security , Effectively prevent SQL Injection problem . For statements that are executed repeatedly , Use Prepared

Statement It’s going to be a little more efficient . perform SQL Statements can have parameters , And support batch execution SQL. Because of the adoption of Cache Mechanism , Then precompiled statements , It’ll be in Cache in , Do the same next time SQL When the sentence is , You can go directly from Cache To remove .

PreparedStatement pstmt = con.prepareStatement("UPDATE EMPLOYEES SET name= ? WHERE ID = ?");
pstmt.setString(1, " Li Si ");
pstmt.setInt(2, 1);
pstmt. executeUpdate();

that CallableStatement Expanded PreparedStatement The interface of , Used to call stored procedures , It provides support for input and output parameters ,CallableStatement The interface is also for PreparedStatement Interface provided by the input parameters sql Query support .

PreparedStatement: The database will sql Statement precompiling , Do the same next time sql When the sentence is , There will be no more precompiling on the database side , And use the database buffer directly , Improve the efficiency of data access （ But try to use ？ Pass parameters in the form of ）, If sql Statement is executed only once , No reuse in the future . In terms of security ,PreparedStatement It’s through ? To pass parameters , Avoid spelling sql And it appears sql Injection problem , So it’s safe .

In development , Recommended PreparedStatement

mybatis Medium #{} and ${} difference  

1. # Treat the incoming data as a string , A double quotation mark will be added to the automatically passed in data . Such as ：order by #user_id#, If the value passed in is 111, So it’s resolved into sql The value of time is order by “111”, If the value passed in is id, Then it resolves to sql by order by “id”.

2. $ Generate the incoming data directly in the sql in . Such as ：order by $user_id$, If the value passed in is 111, So it’s resolved into sql The value of time is order by user_id,  If the value passed in is id, Then it resolves to sql by order by id.

3. # To a great extent sql Inject .

4.$ Method cannot prevent Sql Inject .

5.$ Method is generally used for incoming database objects , For example, pass in the table name .

6. Generally available # Don’t use it. $.

MyBatis Use when sorting order by Pay attention to dynamic parameters , use $ instead of #

String substitution
By default , Use #{} The grammar of the form leads to MyBatis Create a preprocessing statement property and set a safe value against it （ such as ?）. It’s safe to do this , It’s also the first choice very quickly , Sometimes you just want to be directly in SQL Insert an unchanged string into the statement . such as , image ORDER BY, You can use ：
ORDER BY ${columnName}
here MyBatis Strings will not be modified or escaped .

important ： Accept the output from the user and provide the invariant string in the statement , It’s not safe to do so . This can lead to potential SQL Injection attack , So you should not allow users to enter these fields , Or usually escape and check .

1. Use #{} The grammar of format is in mybatis Use in Preparement Statement to set values safely , perform sql Similar to the following ：

PreparedStatement ps = conn.prepareStatement(sql);
ps.setInt(1,id);

The advantage of this is ： More secure , More quickly , It’s usually the first choice .

and #{} Format sql Be able to precompile , It can be saved in memory sql grammar , No need to reassemble sql grammar .

2. But sometimes you just want to be directly in SQL Insert an unchanged string into the statement . such as , image ORDER BY, You can use ：

ORDER BY ${columnName}

here MyBatis Strings will not be modified or escaped .

This way is similar to ：

 Statement st = conn.createStatement();
ResultSet rs = st.executeQuery(sql);

The disadvantage of this way is ： It’s not safe to accept the output from the user in this way and provide the invariant string in the statement , Will lead to potential SQL Injection attack , So either the user is not allowed to enter these fields , You can either escape it yourself and test it .