Home > Programming > SQL Injection Attack explained!

SQL Injection Attack explained!

We need a medic here!

From OWASP, A SQL injection attack consists of insertion or “injection” of a SQL query via the input data from the client to the application. A successful SQL injection exploit can read sensitive data from the database, modify database data (Insert/Update/Delete), execute administration operations on the database (such as shutdown the DBMS), recover the content of a given file present on the DBMS file system and in some cases issue commands to the operating system. Some days ego, Pirate Bay was hacked by an SQL Injection Attack.

So let me explain how an SQL Injection Attack occurs by using an example. We have a website which allows users to post their high scores for a game.

A form for a user to enter Name and Score

In the MySQL database highScores and have a table which have the following fields: Name, High_Scores and isApproved. Records which have isApproved set to 1 is considered True and 0 treated as False.

|      Name       | High_Scores | isApproved |
| crash_override  |    10234    |      0     |
|   giga          |    11200    |      1     |
|   digitox       |    11500    |      0     |
|   sepulfan4eva  |    10600    |      1     |
|   sniper11      |    13500    |      1     |
|   1_dimun       |    12802    |      1     |
6 rows in set

You may (actually must) assume that we should have included other fields like ID set as primary key, date, etc. We will cut it short.

Then we have a back-end (PHP script) page for administrators that displays all the scores score in the database highScores. Scores which has isApproved value of 0 is displayed as Remove / Approve. Only unapproved scores have the Approve link next to them otherwise only delete is displayed in the administrative page.

Here is an example of how the page might look:

So far so good. Our website appears to be safe. A user entering fake scores (like something very big) stands no chance being approved. A better website would also allow the user to make a screenshot or any other kind of proof and upload it to the form while sending the high scores. But for the sake of brevity, i am not going to include these things here.

When the user submit his high scores the following query is executed:

INSERT INTO highScores
VALUES ('$name', '$score', 0)

OK. Enough talking now. Time to get serious.

Once upon a time, a user named roshans89 (yeah thats me) submitted his score as 1000000. Even if the moderator or admin knows that he didn’t approve that score, the score is there with the isApproved column set to 1. We know the script sets the isApproved column to 0 for new high scores.

Here is a screen of the administrator page:

What the hell just happened here?! The highlighted score was never approved by the moderator yet its isApproved column is set to 1, resulting in it being displayed.

What actually happened it that roshans89 entered the following data while entering the scores:

Name: roshans89
Score: 1000000', 1) --

Thus the SQL query was executed as follows:

INSERT INTO highScores
VALUES ( ‘roshans89‘, ‘1000000’, 1) -- ,0)

What happened is that a double-hyphen -- is used in SQL to comment out the remainder of a line of SQL code. You must follow the doublehyphen with a space for it to work (-- ), but everything after the space is ignored.

INSERT INTO highScores
VALUES ( ‘roshans89‘, ‘1000000’, 1) — ,0)

,0) : this SQL line is ignored.

Here is how the data got appended in the database:

|      Name       | High_Scores | isApproved |
| crash_override  |    10234    |      0     |
|   giga          |    11200    |      1     |
|   digitox       |    11500    |      0     |
|   sepulfan4eva  |    10600    |      1     |
|   sniper11      |    13500    |      1     |
|   1_dimun       |    12802    |      1     |
|   roshans89     |   1000000   |      1     |  <---- This row has 1 as isApproved!
7 rows in set

This clever hack is an illustration of a SQL Injection and its aim is to change the SQL query when a web page is submitted.

Later i may edit this post and post ways to countermeasure this malicious attack.

Have a nice day!

Adapted from Head first – PHP and MySQL

Categories: Programming Tags: ,
  1. Afzal
    July 11, 2010 at 8:53 pm

    Nice net sa.. selman avant resi ggne sa ban field names etc… 😛

    • July 11, 2010 at 9:05 pm

      Wep exactly, bizin kner ki man fields enan. Dan man forms parfois enan man hidden fields mais la plupart du ten man input dan man forms la saem ki pou sent dan SQL query. I guess enan imP trial and error with it.. But mo pou research sa imP plus mo pou fer tw kner.. 🙂

  2. Vicki Smith
    March 18, 2011 at 2:11 am

    Thank You! You have really helped me with my essay by explaining the concept like this 🙂

    • March 25, 2011 at 5:50 pm

      Your welcome 🙂
      Glad it helped!

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: