Prevent your Php MySQL scripts from SQL injections

If your site uses data from user and operates its database functionalities over it then you are not safe. SQL injections are the most comman type of attack which a site faces. I would like to mention some common types of attacks and how to save your script from these attacks.

Escape your input strings

Consider a query

"select * from users where username = '{$userid}' "

input : $userid = " 1'; delete from users"

so the query would become 

"select * from users where username = '1'; delete from users"

A query is injected to delete all the data in users table. But luckily mySql doesn't support query stacking hence more than one queries can't be executed. But this attack can occur in other databases like pgsql and ms sql which supports query stacking.

But still the input is not save . consider another input

$userid = " 1' OR '1' = '1' "

query becomes :

"select * from users where username = '1' OR '1' = '1' "

since 1 = 1 always the attack is successfull. So you must escape quotations from user input. Standard way of escaping will be:

function escape($data)

{

if ( get_magic_quotes_gpc() )

$data = stripslashes($data);

return mysql_real_escape_string($data);

}

Magic quotes should be checked for .. as if they are 'on' then your data will be escaped twice .. You might ask if you can use addslashes in place of mysql_real_escape_string as both escapes special characters .. but cases have been found in which addslashes failed against sql injections .. I won't go into much details of it .

mysql_real_escape_string escapes characters:  \x00, \n, \r, \, ', " and \x1a .

When quotations are not used

What if your query doesn't contain a quotation. Example

select * from users where id = $userid

and $userid = 1 OR 1 = 1

which will become

"select * from users where id = 1 OR 1 = 1"

But we have escaped quote .. and since simple int doesn't require to be quoted your query again became vulnerable.. what we can do in this case is to cast an input ..

$userid = (int)1 OR 1 = 1 //$userid = 1

or in case the input is a float we can cast it as a float to ensure that the data coming is of required type.

% and _ are not escaped still

Consider this query:

select * from users where date LIKE '{$date}%'

$date = "%" // we expected something like 2008-12

an attacker can use % and _ too ... they must also be escaped.. php provides a custom escaping function addcslashes.. we can further escape our data like this

$data = escape($data); // above escape function

addcslashes($data,"%_");

And all % and _ will also be escaped which culd attack on LIKE based queries.

Prevent your schema details from user

if you do query something like 

mysql_query ($query) or die(mysql_error());

the function mysql_error() can reveal certain details about your tables and field names. Try some other approach. Maybe you could store errors in a private log file which only you can read.

These were only some of the tips. Users of your web site are your evil enemies. Gmail, wikipedia and other big names all were attacked like this. Also keep your script save from these attacks by using above tips and more if you can. Good luck !