mysql how to prevent sql injection problems
mysql to prevent sql injection methods:
1, open php magic mode, set magic_quotes_gpc=on can be, when some special characters appear in the front-end of the site, it will be automatically converted, converted into some other symbols lead to sql statement can not be The following are some of the reasons why this is a good idea You can also find a professional website security company to deal with, domestic SINE security, green alliance, Qixingchen, are relatively good, the website security reinforcement, server security protection, mysql security deployment.
2, the site code written in the code to filter sql special characters, some special characters are converted, such as single quotes, commas, *, (brackets) AND1=1, backslash, selectunion and other queries of the sql statement are security filtering, to limit the input of these characters, and prohibit the submission of the back-end to the back-end.
3, open the site firewall, IIS firewall, apache firewall, nginx firewall, there are built-in filtering sql injection parameters, when the user enters the parameters get, post, cookies submitted over the way will be detected in advance to intercept.
web security and how to prevent SQL injection
SQL injection, that is, by inserting SQL commands into the Web form to submit or enter the domain name or page request query strings, and ultimately achieve to deceive the server to perform malicious SQL commands, such as many previous film and television sites to leak the VIP member of most of the passwords through the WEB form to submit the query character outbreak, this type of form is particularly vulnerable to SQL injection attacks. For example, many movie and TV websites have compromised their VIP passwords by submitting query characters through WEB forms, which are particularly susceptible to SQL injection attacks.
The principle of SQL injection is that when an application uses input to construct dynamic sql statements to access a database, a sql injection attack occurs. sql injection also occurs when code uses stored procedures that are passed as strings containing unfiltered user input.
Sql injection can result in an attacker using an application login to execute commands in a database. This problem can become serious if the application connects to the database using an over-privileged account. Certain forms where user input is used directly to construct dynamic sql commands, or as input parameters to stored procedures, are particularly vulnerable to sql injection. And many website programs are written without judging the legitimacy of user input or improperly handling variables in the program itself, making the application a security risk. In this way, the user can submit a piece of code for database query and get some sensitive information or control the whole server according to the result returned by the program, so sql injection happens.
How to prevent SQL injection? Never trust user input. Check the user’s input, which can be handled by regular expressions, or limiting the length; then convert sensitive symbols such as single quotes and double “-“, etc. etc. Don’t use dynamic assembly sql, you can use parameterized sql or directly use stored procedures for data query access. Never use database connections with administrator privileges, use separate database connections with limited privileges for each application Don’t store confidential information directly, encrypt or hash away passwords and sensitive information. Application exception messages should give as few hints as possible and it is best to use custom error messages to wrap raw error messages.
Related tutorials: SQL video tutorials
Mybatis is how to achieve the prevention of SQL injection
1# is the incoming value as a string form, eg:selectid,name,agefromstudentwhereid=#{id}, the current end of the value of the id of 1, passed into the background of the equivalent of selectid,name,agefromstudentwhereid=’1′.
2$ is the incoming data will be directly displayed to generate sql statements, eg:selectid,name,agefromstudentwhereid=${id},the current end of the id value of 1, when passed to the background, it is equivalent to selectid,name,agefromstudentwhereid = 1.
3 using # can largely prevent sql injection. (Splicing of statements)
4But if used in orderby you need to use $.
5In most cases # is still often used, but in different cases $. must be used.
I think the biggest difference between # and is that when #{} is passed in for a value, the sql parses it with the parameters in quotes, whereas the biggest difference between #{} is that when #{} is passed in for a value, the sql parses it with the parameters in quotes, whereas ${} wears in for a value, the sql parses it with the parameters without the quotes.
What is SQL injection
Before discussing how to implement it, first understand what is SQL injection, we have a simple query operation: query a user information based on id. Its sql statement should look like this: select*fromuserwhereid=. We fill in the id to query based on the incoming condition.
If we do this normally, passing in a normal id, say 2, then the statement becomes select*fromuserwhereid=2. This statement works fine and meets our expectations.
But if you pass in a parameter of ”or1=1, then the statement becomes select*fromuserwhereid=”or1=1. Let’s think about what the result of this statement would be. How? It will look up all the data in our user table, which is obviously a big mistake. This is SQL injection.
How Mybatis prevents SQL injection
At the beginning, it was said that you can use # to prevent SQL injection, it is written as follows:
<selectid=”safeSelect “resultMap=”testUser”>
SELECT* FROMuserwhereid=#{id}
</select>
There is another way to write a query in mybatis is to use $, which is written as follows:
<selectid=”unsafeSelect “resultMap=” testUser”>
select*fromuserwhereid=${id}
</select>
When we continue to call both methods externally, we find that the two results are not different if we pass in the safe parameter, and if we pass in the unsafe parameter The first method using # does not get results (select*fromuserwhereid=”or1=1), but this parameter will get all the results under the second one which is $.
And if we print the sql, we’ll see that when we add #, the sql executed to the database is :select*fromuserwhereid=’\’\’or1=1′ and it will be in our It will add another layer of quotes outside of our parameters, and when using $, its execution sql is select*fromuserwhereid=”\’or1=1.
Is it okay to discard $?
We can accomplish the role of $ by using #, and there is still a danger in using $, so in the future, let’s don’t use $ at all.
No, it’s just a problem in our scenario, but there are some dynamic query scenarios where it’s irreplaceable, for example, dynamically modifying the table name select*from${table}whereid=#{id}. We will be able to return the same information in the case of dynamic changes to the query table, which is mybatis dynamic powerful place.
How to achieve SQL injection without Mybatis how to achieve
In fact, Mybatis is also through the jdbc to database connection, if we look at the use of jdbc, we can get the reason.
# uses PreparedStatement for preprocessing, and then placeholders are set by means of set, while $ is queried directly through Statement, and when there are parameters, they are directly spliced for querying.
So that we can use jdbc for SQL injection.
Look at the code for both:
publicstaticvoidstatement(Connectionconnection){
System.out.println(“statement—–“);
StringselectSql=”select*fromuser”;
// Equivalent to the use of $ in mybatis, get the parameters and then directly splice
StringunsafeSql=”select*fromuserwhereid=’ ‘ or1=1;”;
Statementstatement=null;
try{
statement=connection.createStatement();
}catch( SQLExceptione){
e.printStackTrace();
}
try{
ResultSetresultSet=statement.executeQuery(selectSql);
print(resultSet);
}catch(SQLExceptione){
e.printStackTrace();
}
System.out.println(“—–****—“);
try{
ResultSetresultSet=statement.executeQuery(unsafeSql);
print(resultSet);
}catch(SQLExceptione){
e. printStackTrace();
}
}
publicstaticvoidpreparedStatement(Connectionconnection){
System.out.println(” preparedStatement—–“);
StringselectSql=”select*fromuser;”;
// Equivalent to # in mybatis, preprocess the sql to be executed first, set the placeholders, and then set the parameters
StringsafeSql=”select*fromuserwhereid=? ;”;
PreparedStatementpreparedStatement=null;
try{
preparedStatement=connection.prepareStatement(selectSql);
< p>ResultSetresultSet=preparedStatement.executeQuery();
print(resultSet);
}catch(SQLExceptione){
e. printStackTrace();
}
System.out.println(“—****—“);
try{
preparedStatement=connection.prepareStatement( safeSql);
preparedStatement.setString(1,””or1=1″);
ResultSetresultSet=preparedStatement. executeQuery();
print(resultSet);
}catch(SQLExceptione){
e.printStackTrace();
}
}
}
publicstaticvoidprint(ResultSetresultSet)throwsSQLException{
while(resultSet.next()){
System.out.print(resultSet. getString(1)+”,”);
System.out.print(resultSet.getString(“name”)+”,”);
System.out.println(resultSet.getString(3));
< p>}
}
Summary:
The use of # in Mybatis prevents SQL injection, $ does not prevent SQL injection
Mybatis implements SQL injection on the principle of preprocessing by calling PreparedStatement in jdbc.
How Mybatis implements the prevention of SQL injection
Label: sql injection shows that the situation is to deal with caughtsizedivext
SQL injection how to prevent
SQL injection attack is very harmful, and the firewall is difficult to intercept the attack, the main SQL injection attack prevention methods, specifically in the following areas:
1, hierarchical management
Hierarchical management of the user, strict control of the user’s privileges for ordinary users, prohibited to give the database to establish, Delete, modify and other related rights, only the system administrator has the authority to add, delete, change, check.
2. Parameter passing
Programmers in the writing of SQL language, prohibit the variables written directly to the SQL statement, you must pass the relevant variables by setting the appropriate parameters. This inhibits SQL injection. Data input cannot be embedded directly into the query statement. At the same time, the input should be filtered to filter out unsafe input data. Or use parameter passing to pass input variables, which can maximize the prevention of SQL injection attacks.
3, basic filtering and secondary filtering
Before the SQL injection attack, the intruder submits and and other special characters by modifying the parameters to determine whether there is a loophole, and then select, update and other kinds of characters to write SQL injection statements. Therefore, to prevent SQL injection to check the user input, to ensure the security of data input, in the specific check input or submitted variables, for single quotes, double quotes, colons and other characters for conversion or filtering, so as to effectively prevent SQL injection.
Of course there are a lot of dangerous characters, in the acquisition of user input to submit parameters, first of all, the basic filtering, and then according to the function of the program and the possibility of user input secondary filtering, in order to ensure the safety of the system.
4, the use of security parameters
SQL database in order to effectively inhibit the impact of SQL injection attacks. In the SQLServer database design set up a special SQL security parameters. In the program writing should try to use security parameters to eliminate the injection attack, so as to ensure the security of the system.
5. Vulnerability scanning
In order to more effectively prevent SQL injection attacks, as the system management in addition to setting up effective preventive measures, it is more important to find the system in a timely manner there are SQL attack security holes. System administrators can purchase some SQL vulnerability scanning tools, through the professional scanning tools, you can scan the system in a timely manner to the existence of the corresponding vulnerabilities.
6, multi-layer verification
Nowadays, the function of the website system is more and more huge and complex. In order to ensure the security of the system, the visitor’s data input must be strictly verified in order to enter the system, the verification did not pass the input directly be denied access to the database, and to the upper level of the system to send an error message. At the same time in the client access program to verify the visitor’s relevant input information, thus more effective to prevent simple SQL injection. However, if the lower tier of multi-layer authentication passes the validation data, then an attacker bypassing the client will be able to access the system at will. Therefore, when performing multi-layer authentication, it is important to have each layer work in conjunction with the other, and only with effective authentication protection on both the client and system side can we better prevent SQL injection attacks.
7, database information encryption
The traditional encryption and decryption methods are roughly divided into three: symmetric encryption, asymmetric encryption, irreversible encryption.