SQL Injection Attack – Examples and Preventions in PHP

[riderz]

What is SQL injection?

It is a basically a trick to inject SQL command or query as a input mainly in the form of the POST or GET method in the web pages. Most of the websites takes parameter from the form and make SQL query to the database. For a example, in a product detail page of php, it basically takes a parameter product_id from a GET method and get the detail from database using SQL query. With SQL injection attack, a intruder can send a crafted SQL query from the URL of the product detail page and that could possibly do lots of damage to the database. And even in worse scenario, it could even drop the database table as well.

Examples of SQL Injection Attack in PHP:

Let’s look at the usual query for user login in PHP,

$sql=”SELECT * FROM tbl_user WHERE username= ‘”.$_POST['username'].”‘ AND password= ‘”.$_POST['password'].”‘”;
$result=mysql_query($sql);

Well, lots of people thinks that only the valid user can log in inside the system but that’s not true.Well anybody can log in to that website with a simple trick.

Let’s suppose that a intruder called SAM injected x’ OR ‘x’='x in the username field and x’ OR ‘x’='x in the password field. Then the final query will become like this

SELECT * FROM tbl_user WHERE username=’x’ OR ‘x’='x’ AND password=’x’ OR ‘x’='x’;

Well you can see that query is always true and returns the row from the database. As the result , the malicious guy could log in to the system.

Now even let’s look at the worst scenario of the SQL injection attack example. A intruder can even drop a table if the database user has drop privilege into that database.

Let’s suppose a query in a product detail page

$sql=”SELECT * FROM product WHERE product_id= ‘”.$_GET['product_id'].”‘”;

Now its turn of intruder to inject SQL command in the URL of the page, the code might be like this 10′; DROP TABLE product; # and the URL looks like this

http://xyz.com/product.php?id=10′; DROP TABLE product; #

Now query becomes like this

SELECT * FROM product WHERE product_id=’10′; DROP TABLE product; #’;

You might be wondering what is the meaning of hash “#”, it tell MYSQL server to ignore the rest of the query.In this query, it simply ignore the last single quote (‘) of the query.
Prevention from Sql Injection Attack in PHP

To avoid the sql injection attack, please follow the following simple mechanisms in PHP

1) Always restrict the length of the fields of form such as don’t allow more than 20 characters in the fields like username and password with the “maxlength” property available in the html form.

2) Always validate for the proper input like weather the value is valid email or not, is numeric or not , valid date or not etc.

3) Finally, Always use mysql_real_escape_string() function before sending the variable to the SQL query, it ad. For example

//note you must be connected to the database for using this function
$username=mysql_real_escape_string($_POST['username']);
$password=mysql_real_escape_string($_POST['password']);

if a intruder inject ‘ OR 1 in the user name and password field then the value of the $username and $password will become \’ OR 1 which is not going to harm us anymore.

[khan]

Thats Cool........
But I got A Problem Using mysql_real_escape_string()....That is it wont work in some numeric value and some of intejar.
I Don't Know Why...............!

[morse]

Thats Cool........
But I got A Problem Using mysql_real_escape_string()....That is it wont work in some numeric value and some of intejar.
I Don't Know Why...............!

my_sql_real_escape_string requires connection to database, if database is not connected - it wont work, then you will need to use an escape function instead.
All of php functions can be reconstructed in various ways, however efficiency and speed become a factor

[khan]

my_sql_real_escape_string requires connection to database, if database is not connected - it wont work, then you will need to use an escape function instead.
All of php functions can be reconstructed in various ways, however efficiency and speed become a factor

Yeah..........I know that pretty well. But again what escape function i should use if it wont workd!!
Lacking Of Information Of mine.

[Malka1]

Thanks for this useful theard!

[morse]

Yeah..........I know that pretty well. But again what escape function i should use if it wont workd!!
Lacking Of Information Of mine.

function escape($str)
  {
   $search=array("\\","\0","\n","\r","\x1a","'",'"');
   $replace=array("\\\\","\\0","\\n","\\r","\Z","\'",'\"');
   return str_replace($search,$replace,$str);
  }

[khan]

function escape($str)
  {
   $search=array("\\","\0","\n","\r","\x1a","'",'"');
   $replace=array("\\\\","\\0","\\n","\\r","\Z","\'",'\"');
   return str_replace($search,$replace,$str);
  }

How could i call that!!

[rukiya]

$search=array("\\","\0","\n","\r","\x1a","'",'"'); <--- last array value needs double quoted thats ,""");

How could i call that!!

usage

escape ("it's a string.");

[leadiztah]

you can prevent the sql injection just simple create an0nym0us table name tnt

[subzero]

Place this into your core.php

//////////////////////// Anti Sql hit...
function check_injection() 
  { 
    $badchars = array("DROP","TRUNCATE", "SELECT", "UPDATE", "DELETE" , "UNION", "WHERE", "FROM","INSERT","ORDER BY"); 
   
    foreach($_REQUEST  as $value) 
    { 
      if(in_array(strtoupper($value), $badchars)) 
      { 
      $logfile= 'logs/log.txt'; //chmod 777 
$IP = $_SERVER['REMOTE_ADDR']; 
$logdetails= date("F j, Y, g:i a") . ': ' . '<a href=http://dnsstuff.com/tools/city.ch?ip='.$_SERVER['REMOTE_ADDR'].' target=_blank>'.$_SERVER['REMOTE_ADDR'].'</a>'; 
$fp = fopen($logfile, "r+"); 
fwrite($fp, $logdetails, strlen($logdetails)); 
fclose($fp); 

       header('Location:antihack.fbi'); 

      } 
      else 
      { 
        $check = preg_split("//", $value, -1, PREG_SPLIT_OFFSET_CAPTURE); 
foreach($check as $char)
{
if(in_array(strtoupper($char), $badchars))
{
      $logfile= 'logs/log.txt'; 
$IP = $_SERVER['REMOTE_ADDR']; 
$logdetails= date("F j, Y, g:i a") . ': ' . '<a href=http://dnsstuff.com/tools/city.ch?ip='.$_SERVER['REMOTE_ADDR'].' target=_blank>'.$_SERVER['REMOTE_ADDR'].'</a>'; 
$fp = fopen($logfile, "r+"); 
fwrite($fp, $logdetails, strlen($logdetails)); 
fclose($fp); 

            header('Location:http://go-to-hell.com'); 
}}}} 
}

Place this under include("core.php");

check_injection();

Still get a error ??

This means your not doing it right.

Other words go back and learn php coding......

[morse]

$search=array("\\","\0","\n","\r","\x1a","'",'"'); <--- last array value needs double quoted thats ,""");

Hello rukiya sir
Thanks for correcting it, but it kinda works fine for me somehow, I haven't succeeded in injecting anything tho in the script where am using it. but then again the database is not compromised as there is no db connection at all.

[rukiya]

Hello rukiya sir
Thanks for correcting it, but it kinda works fine for me somehow, I haven't succeeded in injecting anything tho in the script where am using it. but then again the database is not compromised as there is no db connection at all.

That function was taken from PHP: mysql_real_escape_string - Manual

mysql_real_escape_string should be used in a mysql_query else use addslashes, strip_tags

[metulj]

That function was taken from PHP: mysql_real_escape_string - Manual
mysql_real_escape_string should be used in a mysql_query else use addslashes, strip_tags

how about usage like this?

$something = mysql_real_escape_string(strip_tags(htmlspecialchars($_POST['something']))); 

would this be any good ?

[something else]

how about usage like this?

$something = mysql_real_escape_string(strip_tags(htmlspecialchars($_POST['something']))); 

would this be any good ?

unless its on a mysql_query it causes errors but only causes silent warning so nearly everyone on here doesnt seem to worry about it.. however this could slow your script down

eg: try it on a script with no connection to database and see what happens lol