Fully disable SQL injection attacks in PHP III

On the other hand, applying this inventory has a major problem: you can only be limited by the ideas of some people, and a lot of work has been added to code governance. Therefore, you need to carefully consider before deciding whether to apply them. If you decide to do this, you should at least make sure that they can actually help you 'clear' the content your users enter.

1. create a security abstraction layer

We recommend that you create an abstraction layer for this instead of using the prefix skills manually in the instances that every user enters. A simple abstraction is to add your verification plan to a function, and call this function for each item you enter. Of course, we can also create a more complex and high-level abstraction-encapsulate a secure query into a class, so as to take full advantage of the program. There are already many free classes available online. in this article, we will discuss some of them.

This abstraction has at least three strengths (and each improves the security level ):

1. localized code.

2. make the query structure faster and more reliable-as this can be done by the abstract code.

3. when built based on security features and appropriate applications, this will effectively prevent the various injection attacks we discussed earlier.

II. improvement of existing exploitation procedures

If you want to improve an existing application, it is most appropriate to apply a simple abstraction layer. A function that can simply 'clear' any user input you collect may look like this:

Function safe ($ string ){
Return '''. mysql_real_escape_string ($ string ).'''
}
[Note] we have constructed the single quotation marks corresponding to the value request and the mysql_real_escape_string () function. Next, you can apply this function to structure a $ query variable, as shown below:

$ Variety = safe ($ _ POST ['variety']);
$ Query = 'select * FROM wines WHERE variety = '. $ variety;
Now, your user tries to initiate a injection attack-by inputting the following content as the value of the variable $ variety:

Lagrein 'or 1 = 1;
Note that if you do not perform the 'clear' above, the final query will be as follows (this will lead to unexpected results ):

SELECT * FROM wines WHERE variety = 'lagrein' or 1 = 1 ;'
However, since the user's input and output have been cleared, the query statement has become the following situation without persecution:

SELECT * FROM wines WHERE variety = 'lagrein \ 'or 1 = 1 \;'

Since the database does not have the variety field corresponding to the specified value (this is the content entered by a malicious user-lagrein 'or 1 = 1;), then, this query will not return any results, and the injection will fail.

3. protect a new Exploitation Program

If you are creating a new application, you can create a security abstraction layer from the beginning. Now, PHP 5's new support for MySQL (which is embodied in the expansion of mysqli) provides strong support for this security feature (both procedural and, is also object-oriented ). You can obtain information about mysqlies from the website http://php.net/mysqli. Note that this mysqli support is only available when you use the -- with-mysqli = path/to/mysql_config option to compile PHP. The following is a procedural version of the code used to protect a query based on mysqli:

<? Php
// Retrieve user input
$ AnimalName = $ _ POST ['animalname'];
// Connect to the database
$ Connect = mysqli_connect ('localhost', 'username', 'password', 'database ');
If (! $ Connect) exit ('connection failed: '. mysqli_connect_error ());
// Create a query statement source
$ Stmt = mysqli_prepare ($ connect, 'Select intelligence FROM animals WHERE name =? ');
If ($ stmt ){
// Bind the exchange statement
Mysqli_stmt_bind_param ($ stmt,'s, $ animalName );
// Execute this statement
Mysqli_stmt_execute ($ stmt );
// Retrieve results...
Mysqli_stmt_bind_result ($ stmt, $ intelligence );
// And display it
If (mysqli_stmt_fetch ($ stmt )){
Print 'a $ animalName has $ intelligence. \ n ';
} Else {
Print 'Sorry, no records found .';
}
// Clear the statement source
Mysqli_stmt_close ($ stmt );
}
Mysqli_close ($ connect );
?>
The mysqli extension provides a set of functions for structure and query execution. In addition, it also provides a very accurate supply of the effects achieved by the previous application of our own safe () function.

In the above section, first collect the input content submitted by the user and establish a database connection. Then, use the mysqli_prepare () function to create a query statement source-name it $ stmt here to reflect the name of the function applied to it. This function applies two parameters: connect the resource and a string (whenever your application expands and inserts a value ,'? 'Mark is inserted into it ). In this example, you have only one such value-the animal name.

Note: In a SELECT statement, place '? 'The unique valid position of the tag is in the value comparison section. This is why you do not need to specify which variable to apply (except in the mysqli_stmt_bind_param () function ). Here, you also need to specify its type-in this example, 'S' represents a string. Other possible types are: 'I represents an integer, 'D' represents a double-precision number (or floating-point number), and' B 'represents a binary string.

The mysqli_stmt_execute (), mysqli_stmt_bind_result () and mysqli_stmt_fetch () functions perform queries and retrieve results. If there are search results, they are displayed; if there is no results, a harmless message is displayed. Finally, you need to close $ stmt resources and database connections-open them from memory.

Assuming that A legitimate user inputs the string 'lemming', this routine will (assuming the appropriate data in the database) output the message 'A lemming has very low intelligence .'. Suppose there is an attempted injection-for example, 'lemming' or 1 = 1; ', then this routine will print the (harmless) message 'Sorry, no records found .'.
In addition, mysqli expansion also provides an object-oriented version of the same routine. Next, we want to clarify the application method of this version.

<? Php
$ AnimalName = $ _ POST ['animalname'];
$ Mysqli = new mysqli ('localhost', 'username', 'password', 'database ');
If (! $ Mysqli) exit ('connection failed: '. mysqli_connect_error ());
$ Stmt = $ mysqli-> prepare ('select intelligence

FROM animals WHERE name =? ');
If ($ stmt ){
$ Stmt-> bind_param ('s ', $ animalName );
$ Stmt-> execute ();
$ Stmt-> bind_result ($ intelligence );
If ($ stmt-> fetch ()){
Print 'a $ animalName has $ intelligence. \ n ';
} Else {
Print 'Sorry, no records found .';
}
$ Stmt-> close ();
}
$ Mysqli-> close ();
?>
In fact, this part of the code is a copy of the code described above-it applies an object-oriented syntax and organization method, rather than strict procedural code.

4. more advanced abstraction

If you apply the external database PearDB, you can thoroughly abstract the security protection modules of the application.

On the other hand, applying this inventory has a major problem: you can only be limited by the ideas of some people, and a lot of work has been added to code governance. Therefore, you need to carefully consider before deciding whether to apply them. If you decide to do so, you should at least make sure that they can actually help you 'clear' the content your users enter.

5. test your injection protection capabilities

As we discussed earlier, an important part of ensuring the security of your scripts is to test them. The best way to do this is to create your own SQL code and inject it to the test.
Here, we provide an example of this test. In this example, we test the injection attack on a SELECT statement.

<? Php
// Protected functions tested
Function safe ($ string ){
Return '''. mysql_real_escape_string ($ string ).'''
}
// Connect to the database
///////////////////////
// Try to add
///////////////////////
$ Exploit = 'lemming' AND 1 = 1 ;';
// Perform liquidation
$ Safe = safe ($ exploit );
$ Query = 'select * FROM animals WHERE name = $ safe ';
$ Result = mysql_query ($ query );
// Test whether the protection is sufficient.
If ($ result & mysql_num_rows ($ result) = 1 ){
Exitt 'protection succeeded: \ n
Exploit $ exploit was neutralized .';
}
Else {
Exit ('protection failed: \ n
Exploit $ exploit was able to retrieve all rows .');
}
?>
If you want to create such a test set and experiment with different injection methods based on different SQL commands, you will soon detect any vulnerabilities in your protection policy. Once you correct these headers, you can be confident that you have established a real injection attack protection mechanism.

VI. Summary

At the beginning of this series of articles, we have discussed and analyzed a specific threat to your script through an SQL injection-caused by improper user input. Later, we described the working principle of SQL injection and accurately analyzed how PHP is easy to be injected. Then, we provide an example of the actual injection. Afterwards, we recommend a series of measures to make the attempted injection attack harmless-this will distinguish by ensuring that all submitted values are enclosed in quotation marks, by checking the type of user submitted values, and by filtering out the characters that your users enter in the ambush danger. Finally, we recommend that you abstract your verification routine and provide script examples for changing an existing exploitation program. Then, we discussed the advantages of a third-party abstract plan.