Thursday, 17 January 2013

Secure PHP Programming : Prevent SQL Injection

What is sql injection ?

Consider the following fragment of code :-

$query = "SELECT * FROM table_name where column_name='$some_value'";


the same query in a different manner :-

$query = "SELECT * FROM table_name where column_name='";
$query.=$some_value;     //some_value contains a value given by the user .

Consider the second case . Now let us see all the possibilities -

Normally a user would give a regular value which matches the value of a certain field , however say some hacker wants to destroy all your data , he gives something like :-

table_name';DELETE * FROM table_name;

Now consider the entire string :-

SELECT * FROM table_name where column_name='table_name';DELETE * FROM table_name;'

SQL interprets this string in the following way -> since in sql ; signifies the end of a query-statement , so we get two query-statements , one of select and the other one of delete . I hope it is not necessary for me to say what the latter statement can do to your data !! Thus sql can be injected in this way to your site and you will keep on wondering as to where did all the data go !! Deletion , granting of privileges to anonymous users , just think of what can happen , if you don't properly take care of this thing !!

Problem , SQL injection ?

How to prevent sql injection ?

1) Use quotes the way i used them in the first statement , i.e., instead of appending , build the string at one go.

2) Check the type of user's Submitted values.

3) Escape dangerous characters . Use something like mysql_real_escape_string()