Handling SQL Injection Attack in PHP

What is SQL injection?

It is a basically a trick to inject SQL command or query as an 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 an 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, an attacker 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 query for user login in PHP,

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

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 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.

$sql="SELECT * FROM tbl_user WHERE username='x' OR 'x'='x' AND password='x' OR 'x'='x' ";

Now 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. An 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 it’s turn of the 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://www.unsecuresite.com/products.php?product_id=10’; DROP TABLE product;#

Now query becomes like this:

$sql="SELECT * FROM product WHERE product_id='10'; DROP TABLE product; #'";

You might be wondering what the meaning of hash “#” is, it tells MYSQL server to ignore the rest of the query. In this query, it simply ignore the last single quote (‘) of the query.

Preventing Sql Injection Attack in PHP

• 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.
• Always validate for the proper input like weather the value is valid email or not, is numeric or not, valid date or not etc.
• Finally, Always use mysql_real_escape_string() function before sending the variable to the SQL query,

For example,

$username=mysql_real_escape_string($_POST['username']);

$password=mysql_real_escape_string($_POST['password']);

$product_id=mysql_real_escape_string($_GET['product_id']);