Completely ban SQL injection attacks in PHP Part 2

One of the complete bans on SQL injection attacks in PHP

1. Types of injection attacks

There may be many different types of attack motivations, but at first glance, it seems that there are more types. This is very true - if a malicious user invented a method that could perform multiple queries. We will discuss this in detail later in this article.

If your script is executing a SELECT instruction, then an attacker can force every row in a table to be displayed - by injecting a condition such as '1=1' into the WHERE clause, as shown below (The injected part is shown in bold):

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

As we discussed earlier, this can be useful in itself information, since it reveals the general structure of the table (something a plain record cannot), as well as subtly revealing records that contain confidential information.

　 An updated command ambush has a more direct threat. By placing other attributes in the SET clause, an attacker can modify any field in the currently updated record, such as the following example (in which the injected part is shown in bold):

UPDATE wines SET type= 'red', 'vintage'='9999' WHERE variety = 'lagrein'

　 By adding a true condition such as 1=1 to the WHERE clause of an update instruction, this modified category Can be expanded to each record, such as the following example (where the injection part is shown in bold):

UPDATE wines SET type='red', 'vintage'='9999 WHERE variety = 'lagrein' OR 1=1 ;'

Probably the most dangerous instruction is DELETE - it's not hard to imagine. The injection technique is similar to what we have already seen - by modifying the WHERE clause to expand the scope of affected records, such as the following example (in which the injection part is shown in bold):

DELETE FROM wines WHERE variety = 'lagrein' OR 1=1;'

　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　in a query. When using the MySQL database, an attacker can easily achieve this by inserting an unexpected terminator into the query - an injected quote (single or double) marks the expected variable. at the end; then apply a semicolon to terminate the directive. Now, an additional attack command may be added to the end of the now terminated original command. The ultimate damaging query might look like this:

SELECT * FROM wines WHERE variety = 'lagrein';

GRANT ALL ON *.* TO 'BadGuy@%' IDENTIFIED BY 'gotcha';'

This injection will create a new user BadGuy and give it network privileges (all privileges on all tables); there is also an 'unfortunate' password added to this simple SELECT statement. If you followed our advice in the previous article to strictly limit the privileges of the process user, then this should not work because the web server daemon no longer has the GRANT privileges that you revoked. But in theory, such an attack could give BadGuy free rein to achieve whatever control he has over your database.

As for whether such a multi-query will be processed by the MySQL server, the conclusion is not unique. Some of this may be due to different versions of MySQL, but most of the time it's due to the way multiple queries exist. MySQL's monitoring program fully allows such a query. The commonly used MySQL GUI-phpMyAdmin will copy all previous content before the final query, and only do this.

However, most multiple queries in an injection context are handled by PHP's mysql extension. Fortunately, by default it does not allow executing multiple instructions in a query; trying to execute two instructions (such as the injection shown above) will simply cause failure - no errors are set, and no Output information. In this case, although PHP just implements its default behavior "step by step", it does protect you from most simple injection attacks.

　 The new mysqli extension in PHP5 (refer to http://php.net/mysqli), just like mysql, does not inherently support multiple queries, but it provides a mysqli_multi_query() function to support you in implementing multiple queries. Query - if you really want to do that.

However, for SQLite - the embeddable SQL database engine bundled with PHP5 (see http://sqlite.org/ and http://php.net/sqlite) the situation is even more dire, due to its ease of use The application has attracted the attention of a large number of users. In some cases, SQLite allows such multi-instruction queries by default because the database can optimize batch queries, especially batch INSERT statement processing, which is very efficient. However, if the results of the query are used by your script (for example, when using a SELECT statement to retrieve records), the sqlite_query() function will not allow the execution of multiple queries.

　3. INVISION Power BOARD SQL injection vulnerability

　Invision Power Board is a well-known forum system. On May 6, 2005, a SQL injection vulnerability was discovered in the login code. Its inventor is James Bercegay of GulfTech Security Research.

　This login query is as follows:

$DB->query('SELECT * FROM ibf_members WHERE id=$mid AND password='$pid'');
　Among them, the member ID variable $mid and the password ID variable $pid is retrieved from the my_cookie() function using the following two lines of code:

$mid = intval($std->my_getcookie('member_id'));
$pid = $std->my_getcookie('pass_hash ');
Here, the my_cookie() function uses the following statement to retrieve the requested variables from the cookie:

return urldecode($_COOKIE[$ibforums->vars['cookie_id'].$name]);
　【Note 】The value returned from this cookie is basically not processed. Although $mid is coerced into an integer before being applied to the query, $pid remains unchanged. Therefore, it is vulnerable to the injection type of attacks we discussed earlier.

Therefore, by fixing the my_cookie() function as follows, this vulnerability will be exposed:

if ( ! in_array( $name, array('topicsread', 'forum_read', 'collapseprefs') ) )
{
return $this->
clean_value(urldecode($_COOKIE[$ibforums->vars['cookie_id'].$name]));
}
else
{
return urldecode($_COOKIE[ $ibforums->vars['cookie_id'].$name]);

}
After such correction, the key variables are returned after 'passing' the global clean_value() function, while other variables are not checked.

Now, since we have a general understanding of what SQL injection is, its injection principle and the vulnerability of this injection, let's explore how to effectively prevent it. Fortunately, PHP provides us with abundant resources, so we can predict with full confidence that an application carefully and thoroughly built using our recommended techniques will basically eliminate any possibility of SQL from your scripts. Injection - This is achieved by 'liquidating' your users' data before it can cause any damage.

　 4. Define every value in your query

We recommend that you make sure you define every value in your query. String values ​​are the first to be affected, as well as content where you would normally expect to use 'single' (rather than 'double') quotes. On the one hand, if you use double quotes to allow PHP to exchange variables within a string, this can make entering queries easier; on the other hand, this (admittedly, only a very small amount) will also reduce the need for future PHP Code analysis work.

　Now, let us use the non-injection query we used at the beginning to illustrate this problem:

SELECT * FROM wines WHERE variety = 'lagrein'
　 Or expressed in PHP statement as:

$query = 'SELECT * FROM wines WHERE variety = '$variety'';
Technically speaking, quotes are not required for numeric values. However, if you don't mind using quotes around a value for a field like wine and if your user enters a null value into your form, then you'll see something like this: Query:

SELECT * FROM wines WHERE vintage =
Of course, this query is syntactically invalid; however, the following syntax is valid:

SELECT * FROM wines WHERE vintage = ''
　 The second query Will (presumably) return nothing, but at least it won't return an error message.

5. Check the type of value submitted by the user

From the previous discussion, we can see that so far, the important origin of SQL injection often comes from an unexpected form import. However, when you provide the user with the opportunity to submit certain values ​​through a form, you should have a good idea of ​​what input you want to get - this can make it easier to check the validity of the user's input. We have already discussed such verification headers in previous articles; so here we will only briefly summarize the key points we discussed then. If you're looking at a number, you can use one of these techniques to make sure you're actually getting a numeric type: Use the is_int() function (or is_integer() or is_long()).

　· Apply gettype() function.

　· Apply intval() function.

　· Apply the settype() function.

　To check the length of user input, you can use the strlen() function. To check whether a desired time or date is valid, you can use the strtotime() function. It will almost certainly ensure that a user's import does not contain a semicolon character (unless punctuation can be legitimately included). You can easily achieve this with the help of the strpos() function, as shown below:

if( strpos( $variety, ';' ) ) exit ( '$variety is an invalid value for variety!' );

　 As As we mentioned earlier, as long as you carefully analyze your user input expectations, you should be able to easily detect many problems that exist.

6. Filter out every suspicious character from your query

Although in previous articles, we have discussed how to filter out titles with dangerous characters; but here, let us briefly exaggerate and summarize again Here's a tip:

　· Do not use the magic_quotes_gpc command or its 'behind-the-scenes partner' - the addslashes() function. This function is restricted in application development, and this function also requires the use of an extra step - the use of stripslashes. ()function.

　· In comparison, the mysql_real_escape_string() function is more commonly used, but it also has its own shortcomings.


The above is the second part of the complete prohibition of SQL injection attacks in PHP. For more related content, please pay attention to the PHP Chinese website (www.php.cn)!