PHP prevents SQL injection implementation code
- 2020-03-31 21:36:41
- OfStack
I. type of injection attack
There may be many different types of motivations for attacks, but at first glance, there seem to be many more. This is very true-if a malicious user finds a way to execute multiple queries. We'll discuss this in more detail later in this article.
Such as
If your script is executing a SELECT instruction, an attacker can force each row in a table to be displayed - by injecting a condition such as "1=1" into the WHERE clause, as shown below (WHERE the injection section is bold) :
SELECT * FROM wines WHERE variety = 'lagrein' OR 1=1; '
As we discussed earlier, this in itself can be useful information because it reveals the general structure of the table (which is not possible with an ordinary record) and potentially displays records that contain confidential information.
An update directive potentially poses a more immediate threat. By putting other attributes into the SET clause, an attacker can modify any field in the record that is currently being updated, such as the following example (where the injection section is shown in bold) :
UPDATE wines SET type='red', 'vintage'='9999' WHERE variety =' lagrein'
This modification scope can be extended to each record by adding a constant condition such as 1=1 to the WHERE clause of an update instruction, such as the following example (WHERE the injection section is shown in bold) :
UPDATE wines SET type='red', 'vintage'='9999 WHERE variety =' lagrein' OR 1=1; '
The most dangerous instruction may be DELETE- this is not hard to imagine. The injection technique is the same as we have already seen - extend the scope of affected records by modifying the WHERE clause, such as the following example (WHERE the injection section is shown in bold) :
DELETE FROM wine WHERE variety = 'lagrein' OR 1=1; '
Two, multiple query injection
Multiple query injections will exacerbate the potential damage that an attacker can cause - by allowing multiple destructive instructions to be included in a single query. When using a MySQL database, an attacker can easily do this by inserting an unexpected terminator into the query - an injected quote (single or double) marks the end of the expected variable; The instruction is then terminated with a semicolon. Now, an additional attack instruction may be added to the end of the now terminated original instruction. The resulting destructive query might look like this:
This injection will create a new user BadGuy and give him network privileges (all privileges on all tables); There is also an "ominous" password added to the simple SELECT statement. If you follow our advice in a previous article to strictly limit the privileges of users of this process, then this should not work because the web server daemon no longer has the GRANT privileges you withdrew. But in theory, such an attack might give BadGuy the freedom to do whatever he wants with your database.
As to 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 in most cases it is due to the way multiple queries exist. MySQL's monitor fully allows such a query. The commonly used MySQL GUI, phpMyAdmin, copies everything before the final query and does nothing more than that.
However, most multiple queries in an injection context are managed by PHP's mysql extension. Fortunately, by default, it does not allow multiple instructions to be executed in a single query; Attempting to execute two instructions (such as the injection shown above) will simply cause failure - no errors are set and no output is generated. In this case, PHP does protect you from most simple injection attacks, even though it does "behave" by implementing its default behavior.
The new mysqli extension in PHP5 (see http://php.net/mysqli), like mysql, doesn't inherently support multiple queries, but does provide a mysqli_multi_query() function to support multiple queries if you really want to.
However, the case for SQLite- an embeddable SQL database engine bundled with PHP5 (see http://sqlite.org/ and http://php.net/sqlite) is even more frightening, attracting a lot of user interest because of its ease of use. In some cases, SQLite allows such multi-instruction queries by default because the database can optimize batch queries, especially very efficient batch INSERT statement processing. However, the sqlite_query() function does not allow multiple queries to be executed if the result of the query is used by your script (for example, if the record is retrieved using a single SELECT statement). INVISION Power BOARD SQL injection vulnerability
The Invision Power Board is a well-known forum system. On May 6, 2005, an SQL injection vulnerability was found in the login code. Their findings
James Bercegay of GulfTech Security Research.
The login query looks like this:
$DB - > Query ("SELECT * FROM ibf_members WHERE id=$mid AND password='$pid'");
Where, the member ID variable $mid and password ID variable $pid are retrieved from the my_cookie() function using the following two lines of code:
Here, the my_cookie() function retrieves the required variable from the cookie using the following statement:
Return urldecode ($_COOKIE [$ibforums - > vars [' cookie_id] $name]);
[note] the value returned from the cookie is not processed at all. Although $mid is cast to an integer before being used in the query, $pid remains unchanged. As a result, it is vulnerable to the injection type of attack we discussed earlier.
Therefore, this vulnerability is exposed by modifying the my_cookie() function as follows:
After this correction, the key variables are returned after "passing" the global clean_value() function, while the other variables are not checked.
Now that we have a general understanding of what SQL injection is, how it works, and how vulnerable it is, let's explore how to prevent it effectively. Fortunately, PHP provides us with rich resources, so we have full confidence in predictions, a thoroughly by carefully using our recommended technique to build the application from your script will fundamentally eliminate any possibility of SQL injection - through before it may cause any damage to "clean up" your user data.
Define each value in your query
We recommend that you make sure you define every value in your query. String values first and foremost, and those you would normally expect to use "single "(not" double ") quotes. On the one hand, if you use double quotes to allow PHP to substitute variables within the string, it makes it easier to enter queries. On the other hand, this (admittedly, only a tiny amount) also reduces the amount of analysis that goes into PHP code later on.
Now, let's illustrate this with the same non-injection query we started with:
SELECT * FROM wines WHERE variety = 'lagrein'
Or expressed as a PHP statement:
$query = "SELECT * FROM wine WHERE variety = '$variety'";
Technically, quotation marks are not required for numeric values. However, if you don't mind enclosing the corresponding value of a field such as wine in quotation marks and if your user enters a null value into your form, you will see a query similar to the following:
SELECT * FROM wines WHERE vintage =
Of course, the query is grammatically invalid; However, the following syntax is valid:
SELECT * FROM wines WHERE vintage = ''
The second query will (probably) return nothing, but at least it won't return an error message.
Check the type of value submitted by the user
As we've seen from the previous discussion, the main source of SQL injection so far has been an unexpected form entry. However, when you offer the user the opportunity to submit certain values via a form, you should have a considerable advantage
Decide what type of input you want to get - this makes it easier for us to check the validity of the user entry. In previous articles, we have discussed this validation problem; So, here, we will simply summarize the main points that we discussed at that time. If you're expecting a number, you can use one of the following techniques to make sure you're getting a real number type:
, use the is_int() function (or is_integer() or is_long()).
, use the gettype() function.
, use the intval() function.
, use the settype() function.
To check the length of the user's input, you can use the strlen() function. To check whether an expected time or date is valid, you can use the strtotime() function. It almost certainly ensures that a user's entry does not contain semicolons (unless punctuation can be included legally). You can do this easily with the strpos() function, as shown below:
If (strpos($variety, '; ')) exit ("$variety is an invalid value for variety!" );
As we mentioned earlier, if you carefully analyze your user input expectations, you should be able to easily detect many of these problems.
Filter every suspect character from your query
Although in previous articles, we've discussed how to filter out dangerous characters; But here, again, let's briefly highlight and summarize the problem:
, do not use the magic_quotes_gpc directive or its "behind the scenes "-addslashes() function, which is restricted in application development and requires additional steps - use the stripslashes() function.
, in contrast, the mysql_real_escape_string() function is more commonly used, but it has its own disadvantages.
There may be many different types of motivations for attacks, but at first glance, there seem to be many more. This is very true-if a malicious user finds a way to execute multiple queries. We'll discuss this in more detail later in this article.
Such as
If your script is executing a SELECT instruction, an attacker can force each row in a table to be displayed - by injecting a condition such as "1=1" into the WHERE clause, as shown below (WHERE the injection section is bold) :
SELECT * FROM wines WHERE variety = 'lagrein' OR 1=1; '
As we discussed earlier, this in itself can be useful information because it reveals the general structure of the table (which is not possible with an ordinary record) and potentially displays records that contain confidential information.
An update directive potentially poses a more immediate threat. By putting other attributes into the SET clause, an attacker can modify any field in the record that is currently being updated, such as the following example (where the injection section is shown in bold) :
UPDATE wines SET type='red', 'vintage'='9999' WHERE variety =' lagrein'
This modification scope can be extended to each record by adding a constant condition such as 1=1 to the WHERE clause of an update instruction, such as the following example (WHERE the injection section is shown in bold) :
UPDATE wines SET type='red', 'vintage'='9999 WHERE variety =' lagrein' OR 1=1; '
The most dangerous instruction may be DELETE- this is not hard to imagine. The injection technique is the same as we have already seen - extend the scope of affected records by modifying the WHERE clause, such as the following example (WHERE the injection section is shown in bold) :
DELETE FROM wine WHERE variety = 'lagrein' OR 1=1; '
Two, multiple query injection
Multiple query injections will exacerbate the potential damage that an attacker can cause - by allowing multiple destructive instructions to be included in a single query. When using a MySQL database, an attacker can easily do this by inserting an unexpected terminator into the query - an injected quote (single or double) marks the end of the expected variable; The instruction is then terminated with a semicolon. Now, an additional attack instruction may be added to the end of the now terminated original instruction. The resulting destructive 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 him network privileges (all privileges on all tables); There is also an "ominous" password added to the simple SELECT statement. If you follow our advice in a previous article to strictly limit the privileges of users of this process, then this should not work because the web server daemon no longer has the GRANT privileges you withdrew. But in theory, such an attack might give BadGuy the freedom to do whatever he wants with your database.
As to 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 in most cases it is due to the way multiple queries exist. MySQL's monitor fully allows such a query. The commonly used MySQL GUI, phpMyAdmin, copies everything before the final query and does nothing more than that.
However, most multiple queries in an injection context are managed by PHP's mysql extension. Fortunately, by default, it does not allow multiple instructions to be executed in a single query; Attempting to execute two instructions (such as the injection shown above) will simply cause failure - no errors are set and no output is generated. In this case, PHP does protect you from most simple injection attacks, even though it does "behave" by implementing its default behavior.
The new mysqli extension in PHP5 (see http://php.net/mysqli), like mysql, doesn't inherently support multiple queries, but does provide a mysqli_multi_query() function to support multiple queries if you really want to.
However, the case for SQLite- an embeddable SQL database engine bundled with PHP5 (see http://sqlite.org/ and http://php.net/sqlite) is even more frightening, attracting a lot of user interest because of its ease of use. In some cases, SQLite allows such multi-instruction queries by default because the database can optimize batch queries, especially very efficient batch INSERT statement processing. However, the sqlite_query() function does not allow multiple queries to be executed if the result of the query is used by your script (for example, if the record is retrieved using a single SELECT statement). INVISION Power BOARD SQL injection vulnerability
The Invision Power Board is a well-known forum system. On May 6, 2005, an SQL injection vulnerability was found in the login code. Their findings
James Bercegay of GulfTech Security Research.
The login query looks like this:
$DB - > Query ("SELECT * FROM ibf_members WHERE id=$mid AND password='$pid'");
Where, the member ID variable $mid and password ID variable $pid are 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 retrieves the required variable from the cookie using the following statement:
Return urldecode ($_COOKIE [$ibforums - > vars [' cookie_id] $name]);
[note] the value returned from the cookie is not processed at all. Although $mid is cast to an integer before being used in the query, $pid remains unchanged. As a result, it is vulnerable to the injection type of attack we discussed earlier.
Therefore, this vulnerability is exposed by modifying the my_cookie() function as follows:
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 this correction, the key variables are returned after "passing" the global clean_value() function, while the other variables are not checked.
Now that we have a general understanding of what SQL injection is, how it works, and how vulnerable it is, let's explore how to prevent it effectively. Fortunately, PHP provides us with rich resources, so we have full confidence in predictions, a thoroughly by carefully using our recommended technique to build the application from your script will fundamentally eliminate any possibility of SQL injection - through before it may cause any damage to "clean up" your user data.
Define each value in your query
We recommend that you make sure you define every value in your query. String values first and foremost, and those you would normally expect to use "single "(not" double ") quotes. On the one hand, if you use double quotes to allow PHP to substitute variables within the string, it makes it easier to enter queries. On the other hand, this (admittedly, only a tiny amount) also reduces the amount of analysis that goes into PHP code later on.
Now, let's illustrate this with the same non-injection query we started with:
SELECT * FROM wines WHERE variety = 'lagrein'
Or expressed as a PHP statement:
$query = "SELECT * FROM wine WHERE variety = '$variety'";
Technically, quotation marks are not required for numeric values. However, if you don't mind enclosing the corresponding value of a field such as wine in quotation marks and if your user enters a null value into your form, you will see a query similar to the following:
SELECT * FROM wines WHERE vintage =
Of course, the query is grammatically invalid; However, the following syntax is valid:
SELECT * FROM wines WHERE vintage = ''
The second query will (probably) return nothing, but at least it won't return an error message.
Check the type of value submitted by the user
As we've seen from the previous discussion, the main source of SQL injection so far has been an unexpected form entry. However, when you offer the user the opportunity to submit certain values via a form, you should have a considerable advantage
Decide what type of input you want to get - this makes it easier for us to check the validity of the user entry. In previous articles, we have discussed this validation problem; So, here, we will simply summarize the main points that we discussed at that time. If you're expecting a number, you can use one of the following techniques to make sure you're getting a real number type:
, use the is_int() function (or is_integer() or is_long()).
, use the gettype() function.
, use the intval() function.
, use the settype() function.
To check the length of the user's input, you can use the strlen() function. To check whether an expected time or date is valid, you can use the strtotime() function. It almost certainly ensures that a user's entry does not contain semicolons (unless punctuation can be included legally). You can do this easily with the strpos() function, as shown below:
If (strpos($variety, '; ')) exit ("$variety is an invalid value for variety!" );
As we mentioned earlier, if you carefully analyze your user input expectations, you should be able to easily detect many of these problems.
Filter every suspect character from your query
Although in previous articles, we've discussed how to filter out dangerous characters; But here, again, let's briefly highlight and summarize the problem:
, do not use the magic_quotes_gpc directive or its "behind the scenes "-addslashes() function, which is restricted in application development and requires additional steps - use the stripslashes() function.
, in contrast, the mysql_real_escape_string() function is more commonly used, but it has its own disadvantages.