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.on=> Numeric variables.Method 1
=> The variables containing strings
.
II)Security .
=> Explana
tiMethod 2>>>>>>

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


--------njection:
 Injection into theI) Presentation of the problem.
*
___________________________

There are two types of SQL
 i variables that contain strings;* Injection into numeric variables.
rently for each of these types.
######################
These are two very different types and to avoid them, it will actThe vari
a
iff
debles containing strings:######################

t fetches the age of a member according to its
Imagine a PHP script thahis:
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++.nickname. This nickname has gone from one page to another via the URL
+
by $ _GET what: p). This script should look like 
(t..$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 querylike 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 forfear that someone is not nice to walk around. Well, so let us go to thection is sisecurity:).
I) Security .

I_______________
o secure this type of inj
Temple. You use the functionmysql_real_escape_string ().
######################
er to the following chaUh ... It does what it?######################

" characThis feature adds the "
\
tracters:
+++++++++++++++++++++++++++++++++++++++++++++++++

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

######################
And what's the point?
s you have noticed #####################
A
#in previous injection, the attacker uses the quote(to close the 'around $ nick): if she is prevented from doing that, thebad boy will only have to look elsewhere . This means that if oneis ...
++++++++++++++++++++++++++++++++++++++++++++++++++++...

+$pseudoapplies a mysql_real_escape_string () to the variable name like th = 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 functionynot have used? Well recently, a security hole was discovered on thisomewhat similar to mysql_real_escape_string () is addslashes (), whs ifit is used on a PHP 4.3.9 installation with magic_quotes_gpc enabled.
######################
Numeric variables:
rts as just now with a######################
his type of injection is less known than the previous one, making it

Tmore frequent, and it stan example. This time, itdisplays the age of a member according to its id, and by passing it by aform ($ _POST) to change:

OM membres WHERE id=$id");...
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++...+++
+
$id = $_POST['id'];
y("SELECT age F$requete = mysql_que
rR++++++++++++++++++++++++++

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 theion:
+++++++++++++++++++++++++++++++++++++++++++++++++++++variable $ id is not surrounded by quotes. Simple example of
+
xploita
et++++++++++

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:
ontains only numbers;* Check if the variable actually contains a * Change the contents of the variable so it cnumber before using it in a query.
##########
Method 1:
##########

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

$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 thisMéthode 2:###########
ere we use a function that returmethod on a code that is not yours without understand it.
H

############
ns TRUE when a variable contains only
cks whether is_numeric ( ) returnsTRUE well.

++++++++++++numbers and FALSE if it is not the case this function is is_numeric (),+++++++++++
+
e will use it in a condition that ch
we+++++++++++++++++++++++++++++++++++++++++++

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

##################################################################
What is the best, depending intval () and is_numeric ()?#########
ell I will say that they are both equally effective, t########################################################
W
#hey are equal.But I prefer inval () since with is_numeric () write more code, and ifof course you can run the same query by choosing andefault value for tthe variable does not contain only numbers, the request is canceled (inprinciple, but he variable used). Well that's it! You know all aboutsecuring your applications. If you apply these methods, there is
absolutely no risk of having a fault type SQL injection on its website
(or PHP).