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 thoughts on “SQL Injection Attack – Examples and Preventions in PHP

  1. Fat Hobbit

    An easy method of preventing sql injection with php and mysql (or any database) is using prepared statements

    This is also true for oracle or other databases.
    I like using this technique, since i lets the database do what it’s good at, while i can focus on the task at hand.

  2. Jim O

    It’s worth noting that the mysql_query function in PHP doesn’t support/allow multiple statements, so your DROP TABLE example wouldn’t work anyway.

    As a previous commenter said, prepared statements are a good way to avoid SQL injection, so much so that prepared statements are the only database feature that PDO will emulate if the database doesn’t support them natively. PDO, ADOdb, and CpdeIgniter’s database libraries all support prepared statement style syntax, and I find it a very convenient way to deal with SQL Injection.


  3. will

    In response to Hobbit:
    Prepared statements are ideal, however, only MySQL 5 and newer support them. Most LAMP environments are pre-packaged with MySQL 3.23 or 4.1, leaving much to be desired for site developers.

    mysql_real_escape_string is handy, but requires you to connect to the database to clean your input.
    as a result, you waste a database connection until it is actually needed.

    Jay Pipes of MySQL (www.jaypipes.com) has a handy SQLConnection class which utilizes lazy loading to aid this interaction.

    in a perfect environment, mysql_real_escape_string would be ideal because it takes into account the connection’s character set. in medium to large scale environments, it can be a curse because it lengthens the amount of time a connection needs to stay open with MySQL until the query is built and run. Many people choose to use php’s addslashes() in leui of this function.

  4. Mark Butler

    We should also mention that some of the most destructive exploits can be avoided by designing applications so that the user does not have access to DDL statements like DROP, ALTER, or CREATE.

  5. Hi,
    Good article.

    I am not sure if point #1 [ restricting the length of the fields of the html form ] is of much use, since a user can always create a query without using the form we create. I feel that server side checks are the only sure way to go, and assuming that html form based security will save us might lull some developers into a false sense of security.

  6. @Animesh – Ya you’re a attacker can post the data from other domain as well but you can also restrict the cross domain request forgery

  7. Gk

    However, I can use some of the programs to create a packet w/o any limit of data be posted. And trust me, it’s very easy. So check on the server side is only method to defense a SQL injection attack.

  8. Can we do sql Injection if the developer use session before we login..??

  9. This article was helpful, but I would also suggest create views on tables, and disallowing any script files to access tables directly.

    Sometimes when error generates, the debugging information is sent out, if you use views, an attacker cannot easily identify the tables.

    for example – create view LoginInfo as select id, useremail, password from MasterTable.

    As for Cross domain checking, I always follow token methods, whenever a script is sent, its sent with a token number, and whenever i receive any data from the script, i lookout for token numbers.

    I generally use POST to share information from a page to another, and hardly use querystrings or GET method.

    Hope that helps.

    Rajiv Lodha
    Kolkata Web Hosting

Leave a Reply

Your email address will not be published. Required fields are marked *


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>