Completely ban SQL injection attacks in PHP Part 3

1. Create a security abstraction layer

We do not recommend that you manually apply the techniques described above to every instance of user input, but we strongly recommend that you create an abstraction layer for this purpose. A simple abstraction is to add your validation plan to a function and call this function for each item entered by the user. Of course, we can also create a more complex, higher-level abstraction - encapsulating a safe query into a class that can be used by all applications. There are many such ready-made free classes available online; in this article, we are going to discuss some of them.

　 There are at least three advantages to making this abstraction (and each will improve the security level):

　 1. Localized code.

　2. Make the structure of the query faster and more reliable - because this can offload part of the work to abstract code.

　　3. When built with security features in mind and applied appropriately, 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 simply 'sanitizes' any user input you collect might look like this:

function safe( $string ) {
　return ''' . mysql_real_escape_string( $string ) . '''
}
　【Note】We have built single quotes corresponding to value requests 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 attempts to perform an injection attack - by entering the following as the value of the variable $variety:

lagrein' or 1=1;
Note that if the 'liquidation' above is not performed, then The final query will look like this (which will lead to unpredictable results):

SELECT * FROM wines WHERE variety = 'lagrein' or 1=1;'
But now, since the user's input has been cleared, the query The statement becomes the following harmless form:

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

Since there is no variety field corresponding to the specified value in the database (this is malicious What the user enters -lagrein' or 1=1;), then the query will not return any results and the injection will fail.

　 3. Protect a new application

If you are creating a new application, then you can create a security abstraction layer from scratch. Now, PHP 5's newly improved support for MySQL (which is mainly reflected in the new mysqli extension) provides strong support for this security feature (both procedural and object-oriented). You can get information about mysqli from the site http://php.net/mysqli. Note that this mysqli support is only available if you compile PHP with the --with-mysqli=path/to/mysql_config option. Here is a procedural version of this code, used to secure a mysqli-based query:

＜?php
　 //Retrieve the user's 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 to the statement
　mysqli_stmt_bind_param( $stmt, 's', $animalName );
　 //Execute the 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 intelligence.n';
　 } else {
　　print 'Sorry, no records found.';
　　}
　　//Clear statement source
　　mysqli_stmt_close( $stmt );
　}
　mysqli_close( $connect );
?＞
　 This mysqli extension provides a set of functions for structuring and executing queries. Moreover, it also provides very accurately the functionality achieved previously using our own safe() function.

　In the above snippet, the input content submitted by the user is first collected and the database connection is established. Then, use the mysqli_prepare() function to create a query source - here named $stmt to reflect the name of the function that uses it. This function takes two parameters: the connection resource and a string (whenever you use expansion to insert a value, the '?' mark is inserted into it). In this case, you only have one such value - the name of the animal.

Note that in a SELECT statement, the only valid place to place the '?' mark is in the value comparison part. This is exactly why you don't 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 case, 's' stands for string. Other possible types are: 'I' for an integer, 'd' for a double (or float), and 'b' for a binary string.

　The functions mysqli_stmt_execute(), mysqli_stmt_bind_result() and mysqli_stmt_fetch() are responsible for executing queries and retrieving results. If search results exist, display them; if no results exist, display a harmless message. Finally, you need to close the $stmt resource and database connection - free them from memory.

　 Assuming a legitimate user enters the string 'lemming', then this routine will (assuming the appropriate data in the database) output the message 'A lemming has very low intelligence.' Assuming there is a tentative injection - for example 'lemming' or 1=1;', then this routine will print the (harmless) message 'Sorry, no records found.'.
In addition, the mysqli extension also provides an object-oriented version of the same routine. Below, we would like to clarify how this version is applied.

＜?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 intelligence .n';
　} else {
　print 'Sorry, no records found.';
　}
　$stmt-＞close();
　}
　$mysqli-＞close();
?＞
　Actually, this part The code is a copy of the code described earlier - it uses an object-oriented syntax and organization rather than strictly procedural code.

　IV. Higher-level abstraction

　If you use the external library PearDB, then you can fully abstract the security protection module of the application.

On the other hand, there is a prominent shortcoming in using this library: you can only be limited by the ideas of certain people, and a lot of work is also added to code management. For this reason, you need to think carefully before deciding whether to apply them. If you decide to do this, then at least make sure they actually help you 'clean up' your users' input.

5. Test your injection protection capabilities

As we discussed earlier, an important part of ensuring your scripts are secure is testing them. The best way to do this is to create SQL code injection tests yourself.
Here, we provide an example of such a test. In this example, we test an injection attack on a SELECT statement.

＜?php
//Tested protection function
function safe( $string ) {
　return ''' . mysql_real_escape_string( $string ) . '''
}
//Connect to the database
///// //////////////////
//Attempting to inject
///////////////////////
$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 various different SQL commands based on Injection, you will quickly detect any holes in your protection strategy. Once you correct these headers, you can be confident that you have established true protection against injection attacks.

　VI. Summary

　At the beginning of this series of articles, we analyzed a specific threat to your scripts through a SQL injection discussion - caused by inappropriate user input. After that, we described how SQL injection works and analyzed exactly how PHP is easily injected. We then provide a real-life injection example. Afterwards, we recommend a series of measures to render an attempted injection attack harmless - this would be by ensuring that all submitted values ​​are enclosed in quotes, by checking the type of user-submitted values, and by filtering out your user input This is achieved through advanced methods such as ambushing dangerous characters. Finally, we recommend that you abstract your validation routines and provide example scripts for changing an existing application. We then discussed the pros and cons of third-party abstraction solutions.

The above is the third content of completely prohibiting SQL injection attacks in PHP. For more related content, please pay attention to the PHP Chinese website (m.sbmmt.com)!