Avoiding SQL Injection

SQL injections are among the flaws the most widespread and dangerous in PHP.
This tutorial will explain clearly the concept of SQL Injection and how to avoid 

them once and for all.

--------------------------------------------------------------------

>>>>>>
Summary of tutorial:
I) Presentation of the problem.
=> The variables containing strings
II)Security .
=> Explanation
=> Numeric variables
.Method 1
.Method 2
>>>>>>

--------------------------------------------------------------------


I) Presentation of the problem.
___________________________

There are two types of SQL injection:

* Injection into the variables that contain strings;
* Injection into numeric variables.

These are two very different types and to avoid them, it will act
differently for each of these types.

######################
The variables containing strings:
######################

Imagine a PHP script that fetches the age of a member according to its
nickname. This nickname has gone from one page to another via the URL
(by $ _GET what: p). This script should look like this:

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++...
$pseudo = $_GET['pseudo'];
$requete = mysql_query("SELECT age FROM membres WHERE pseudo='$pseudo'");
...
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Well keep you well, this script is a big SQL injection vulnerability.
Suffice it to a bad boy putting in place the username in the URL a query
like this:

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

' UNION SELECT password FROM membres WHERE id=1
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

It is to arrive to show (just an example), for example the password for
the member with the id 1. I will not explain in detail the operation for
fear that someone is not nice to walk around. Well, so let us go to the
security:).

II) Security .
_______________

To secure this type of injection is simple. You use the function
mysql_real_escape_string ().

######################
Uh ... It does what it?
######################

This feature adds the "\" character to the following characters:

+++++++++++++++++++++++++++++++++++++++++++++++++

NULL, \ x00, \ n, \ r, \, ', "and \ X1A
++++++++++++++++++++++++++++++++++++++++++++++++++++

######################
And what's the point?
######################

As you have noticed in previous injection, the attacker uses the quote
(to close the 'around $ nick): if she is prevented from doing that, the
bad boy will only have to look elsewhere . This means that if one
applies a mysql_real_escape_string () to the variable name like this ...

+++++++++++++++++++++++++++++++++++++++++++++++++++++...
$pseudo = mysql_real_escape_string($_GET['pseudo']);
$requete = mysql_query("SELECT age FROM membres WHERE pseudo='$pseudo'");
...
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

The application is completely secure.
Explanation

######################
Injection hacker to recall:
######################

+++++++++++++++++++++++++++++++++++++++++++++++++++

' UNION SELECT password FROM membres WHERE id=1
+++++++++++++++++++++++++++++++++++++++++++++++++++++++

Well if we apply mysql_real_escape_string () to the variable $ name used
in the query is what will the injection:

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

\' UNION SELECT password FROM membres WHERE id=1
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

This means that we do not even come out of assessments around $ nick in
the request because the \ has been added. There is another function
somewhat similar to mysql_real_escape_string () is addslashes (), why
not have used? Well recently, a security hole was discovered on this if
it is used on a PHP 4.3.9 installation with magic_quotes_gpc enabled.

######################
Numeric variables:
######################

This type of injection is less known than the previous one, making it
more frequent, and it starts as just now with an example. This time, it
displays the age of a member according to its id, and by passing it by a
form ($ _POST) to change:

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++...
$id = $_POST['id'];
$requete = mysql_query("SELECT age FROM membres WHERE id=$id");
...
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

mysql_real_escape_string () would be nothing here, since if an attacker
wants to inject SQL code, it will not need to use quotes, because the
variable $ id is not surrounded by quotes. Simple example of
exploitation:

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

2 UNION SELECT password FROM membres WHERE id=1
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

This injection did exactly the same as the previous one, except that
here, to avoid it, there are two solutions:

* Change the contents of the variable so it contains only numbers;
* Check if the variable actually contains a number before using it in a query.

##########
Method 1:
##########

We'll use a function , intval () This function returns regardless of the
contents of a variable its numerical value. For example:

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

$variable = '1e10';  // $variable vaut '1e10'
$valeur_numerique = intval($variable); // $valeur_numerique vaut 1
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Now back to our sheep:

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

$id = intval($_POST['id']);
$requete = mysql_query("SELECT age FROM membres WHERE id=$id");
}
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

That is: you can stop there and is more than enough, but I recommend you
continue to find another method, or you have air beast if you find this
method on a code that is not yours without understand it.

############
Méthode 2:
###########

Here we use a function that returns TRUE when a variable contains only
numbers and FALSE if it is not the case this function is is_numeric (),
we will use it in a condition that checks whether is_numeric ( ) returns
TRUE well.

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

$id = $_POST['id'];
if (is_numeric($id))
{
$requete = mysql_query("SELECT age FROM membres WHERE id=$id");
}
else
{
echo "Trying to hack me ? Your ip is recorded Ksecurity-Team";
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

##################################################################
What is the best, depending intval () and is_numeric ()?
##################################################################

Well I will say that they are both equally effective, they are equal.
But I prefer inval () since with is_numeric () write more code, and if
the variable does not contain only numbers, the request is canceled (in
principle, but of course you can run the same query by choosing an
default value for the variable used). Well that's it! You know all about
securing your applications. If you apply these methods, there is
absolutely no risk of having a fault type SQL injection on its website
(or PHP).