Anti-SQL injection code to prevent SQL injection 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 finds a way to perform multiple queries. We will discuss this in detail later in this article.
For example
If your script is executing a SELECT instruction, then an attacker can force the display of every row in a table - by injecting a condition such as "1=1" into the WHERE clause, as shown below (where the injection part is shown in 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 showing records containing confidential information.
An updated directive potentially poses a more immediate threat. By placing other attributes in the SET clause, an attacker can modify any field in the record currently being updated, such as the following example (where 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 modification range can be extended to each record, For example, the following example (where the injection part is shown in bold):
UPDATE wines SET type='red', 'vintage'='9999 WHERE variety = 'lagrein' OR 1=1;'
The most dangerous instruction is probably DELETE - It's not hard to imagine. The injection technique is the same as what we've already seen - extending the scope of affected records by modifying the WHERE clause, as in the example below (where the injected part is in bold):
DELETE FROM wines WHERE variety = 'lagrein' OR 1=1;'
2. Multiple query injection
Multiple query injections will exacerbate the potential damage an attacker can cause - by allowing multiple destructive instructions to be included in a single 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 end of the expected variable; Then terminate the directive with a semicolon. Now, an additional attack command may be added to the end of the now terminated original command. The final destructive query might look like this:

Copy code The code is as follows:

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 "ominous" password added to this simple SELECT statement. If you followed our advice in the previous article and strictly limited 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 do whatever he wants with 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 out all previous content before the final query, and only do this.
However, most of the multiple queries within an injection context are managed 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 a failure - no errors are set, and no output is generated information. In this case, although PHP only implements its default behavior "regularly", it can indeed 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. -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 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, the sqlite_query() function does not allow multiple queries to be executed if the results of the query are used by your script (for example, in the case of using a SELECT statement to retrieve records). 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. It was discovered by 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:

Copy code The code is as follows:

$mid = intval($std->my_getcookie('member_id'));
$pid = $std->my_getcookie('pass_hash');

Here, the my_cookie() function retrieves the requested variables from the cookie using the following statement:
return urldecode($_COOKIE[$ibforums->vars['cookie_id '].$name]);
[Note] The value returned from this cookie is not processed at all. Although $mid is cast to an integer before being used in the query, $pid remains unchanged. Therefore, it is vulnerable to the type of injection attacks we discussed earlier.
So, by modifying the my_cookie() function in the following way, this vulnerability is exposed:

Copy code The code is 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 such correction, the key variables are returned after "passing" the global clean_value() function, while other variables are not checked.
Now, now that we have a general understanding of what SQL injection is, how it works, and how vulnerable this injection is, let’s explore how to effectively prevent it. Fortunately, PHP provides us with a wealth of resources, so we can confidently predict that an application carefully and thoroughly built using our recommended techniques will essentially eliminate any possibility of SQL from your scripts. Injection - This is achieved by "cleaning" your user's data before it can cause any damage.
4. Define each value in your query
We recommend that you make sure you define each value in your query. String values ​​are the first to be affected, as are things you would normally expect to use "single" (rather than "double") quotes. On the one hand, if you use double quotes to allow PHP variable substitution within the string, it makes typing queries easier; on the other hand, this (admittedly, only minimally) will also reduce the amount of PHP code in the future. analysis work.
　Next, 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 statements as:
$query = "SELECT * FROM wines WHERE variety = '$variety'";
Technically, 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 a query like the following :
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 (probably ) will 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 main source of SQL injection often comes from 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 in determining what input content you want to obtain - this can make it easier to check the validity of the user's entry. In previous articles, we have discussed such verification issues; so, here we will only briefly summarize the main points of our discussion at that time. If you are expecting a number, then you can use one of these techniques to ensure that what you are getting is actually a numeric 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 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 entry does not contain a semicolon character (unless punctuation can be legally included). You can easily achieve this with the help of strpos() function as shown below:
if( strpos( $variety, ';' ) ) exit ( "$variety is an invalid value for variety!" );
As we did in As mentioned earlier, as long as you carefully analyze your user input expectations, you should be able to easily detect many problems.
6. Filter out every suspicious character from your query
Although in previous articles, we have discussed how to filter out dangerous characters; but here, let us briefly emphasize and summarize this issue again :
· Do not use the magic_quotes_gpc directive or its "behind-the-scenes partner" - the addslashes() function. This function is restricted in application development and requires an extra step - use the stripslashes() function.
· In comparison, the mysql_real_escape_string() function is more commonly used, but it also has its own shortcomings.
The above introduces the code to prevent SQL injection in PHP, including the content of preventing SQL injection. I hope it will be helpful to friends who are interested in PHP tutorials.