SQL Injection Attack – Examples and Preventions in PHP
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’].”‘”;
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
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.
Recommended Reading on SQL Injection Attack
16 Comments on “SQL Injection Attack – Examples and Preventions in PHP”
- Change dropdown list (options) values from database with ajax and php
- Useful functions to tighten the PHP security
- 7 Useful functions to tighten the security in PHP « HMV.co.in
- SQL Injection Attack Examples and Preventions in PHP | Green Tea Fat Burner
- Change Dropdown menu with Ajax « Ten Minute media