Fully blocking SQL injection attacks in PHP III

1. to create a security abstraction layer, we do not recommend that you manually apply the technology described above to each user input instance. Instead, we strongly recommend that you create an abstraction layer for this. A simple abstraction is to add your verification scheme to a function and call this function for each item entered by the user. Of course, we can also create a more complex and high-level abstraction-encapsulate a secure query into a class and apply it to the entire application. There are already many off-the-shelf free classes on the internet. 1. create a security abstraction layer

We do not recommend that you manually apply the technology described above to each user input instance, but strongly recommend that you create an abstraction layer for this. A simple abstraction is to add your verification scheme to a function and call this function for each item entered by the user. Of course, we can also create a more complex and high-level abstraction-encapsulate a secure query into a class and apply it to the entire application. There are already many such free classes available online; in this article, we are going to discuss some of them.

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

1. localized code.

2. make the query structure faster and more reliable-because it can be implemented by abstract code.

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

2. improve existing applications

If you want to improve an existing application, it is most appropriate to use a simple abstraction layer. A function that can easily "clean up" any user input you collect may look like this:

Function safe ($ string ){
Return "'". MySQL_real_escape_string ($ string )."'"
}

[Note] we have constructed a single quotation mark corresponding to the value requirement and the mysql_real_escape_string () function. Next, you can use this function to construct a $ query variable, as shown below:

$ Variety = safe ($ _ POST ['variety']);
$ Query = "SELECT * FROM wines WHERE variety =". $ variety;

Now, your user tries to launch a injection attack-by entering the following content as the value of the variable $ variety:

Lagrein 'or 1 = 1;

Note: If you do not clean up, the final query will be as follows (this will lead to unexpected results ):

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

However, now that user input has been cleared, the query statement becomes the following harmless form:

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 exactly 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 application

If you are creating a new application, you can create a security abstraction layer from the beginning. Currently, the new MySQL support (mainly reflected in the new mysqli extension) of php 5 provides strong support for this security feature (both procedural and, it also has object-oriented features ). 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 substitution to the statement
Mysqli_stmt_bind_param ($ stmt, "s", $ animalName );
// Execute this statement
Mysqli_stmt_execute ($ stmt );
// Search 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 constructing and executing queries. In addition, it also provides very accurate functions implemented by using 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-named $ stmt here to reflect the name of the function that uses it. This function uses two parameters: connect the resource and a string (whenever you insert a value using extension ,"? "Mark is inserted to 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 use (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" stands for integers, "d" stands for double-precision (or floating-point number), and "B" stands for binary strings.

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

Assuming that A valid user inputs the string "lemming", this routine will (assuming the appropriate data in the database) output the message "A lemming has very low intelligence .". Assuming there is an attempted injection-for example, "lemming 'or 1 = 1;", this routine will print the (harmless) message "Sorry, no records found .".
In addition, the mysqli extension provides the same routines for the object-oriented version. The following describes how to use 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 earlier-it uses an object-oriented syntax and organization method, rather than strict procedural code.
4. more advanced abstraction

If you use the external database PearDB, you can thoroughly abstract the security protection module of the application.

On the other hand, using this inventory has a major disadvantage: you can only be limited by the ideas of some people, and a lot of work has been added to code management. Therefore, you need to carefully consider before deciding whether to use them. If you decide to do so, at least make sure that they can help you "clean" your user input.

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 an SQL code injection test by yourself.
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 inject
///////////////////////
$ Exploit = "lemming 'AND 1 = 1 ;";
// Clear
$ 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 test various injections based on different SQL commands, you will soon detect any vulnerabilities in your protection policy. Once these problems are rectified, 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 discussed and analyzed a specific threat to your script through an SQL injection-caused by inappropriate user input. Then, we described how SQL injection works and analyzed exactly how PHP is easy to be injected. Then, we provide an actual injection example. Afterwards, we recommend a series of measures to make the attempted injection attack harmless-this will respectively by ensuring that all submitted values are enclosed in quotation marks, by checking the type of user submitted values, and by filtering out potentially dangerous characters entered by your users. Finally, we recommend that you abstract your verification routine and provide script examples for changing an existing application. Then, we discussed the advantages and disadvantages of the third-party abstract solution.

Full text