What is SQL Injection?

Explain SQL Injection along with example?


This is the famous interview question for the beginners, So Let's see what it is all about.

SQL Injection is code injection technique where SQL is injected by user (as part of user input) into the back end query. Injected SQL data alters the purpose of original query and upon execution can gives harmful result.

A SQL injection attack is very dangerous and attacker can,

  1. Read sensitive data from the database.
  2. Update database data (Insert/Update/Delete).
  3. Slowdown the complete Database system etc.

At server side, generally, query is not complete without user data.
Let's understand by taking one example, say you want to get user details, for that query will be,



"select * from user where username = ' " + username + " ' ";
Is the above query complete and ready to execute? NO. there is dependency on user data.

username
here is the placeholder, which is going to be replaced by actual user name of the person stored in our database.
We ask user to enter his/her username, which is going to fill our placeholder username.


Client


Lets take a input from user,
 
Enter your user name: ___________________

Do we have control on what user is going to fill in username? NO. say user entered,

 
1. user can fill name of animal and press submit (
username = dog) or,
2. user can fill name of some hospital and press submit
(username = apollo) or,
3. user can fill some junk data say $$##^^aahh* and press submit
(username = $$##^^aahh*) or,
4. user can fill query like say
username = jayesh'; delete from user where id='1 and press submit
   
(username = jayesh'; delete from user where id='1).


Server


Behavior of user input on server data

1. username = dog
    Query will turn into,
    select * from user where username='dog';

2. username = apollo
    Query will turn into,
    select * from user where username='apollo';

3. username = $$##^^aahh*
    Query will turn into,
    select * from user where username='$$##^^aahh*';

4. username = jayesh'; delete from user where id='1
    Query will turn into,
    select * from user where username='jayesh'; delete from user where id='1';
 


How our application will behave in above cases? 
  1. For Case 1, 2 and 3 it may or may not have harmful behavior but
 

  2. For Case 4, it is sure that we are in trouble,
      Appending user data to our query template, turned query template into 2 queries and our SQL
      server engine will execute 2 queries separately.


           1. Select Query.

               select * from user where username='jayesh';


           2. Delete Query (This query will delete user record, which was never intended.)
               delete from user where id='1';
 

This indirect way of injecting SQL code inside the query is called SQL Injection.


SUMMARY


At server side, queries generally by themselves are not complete and require user data to make it complete, meaningful and executable.
"select * from user where username = ' " + username + " ' ";
Above query is not complete as it has dependency on username variable.
Now if username variable is filled by third party, then there are chances that user data contains SQL,

Take an example. Application is asking user to enter user name,
Enter user name:________________________

Enter user name:___jayesh'; delete from user where id='1__

At Server Side,

username = "jayesh'; delete from user where id='1"
Final Query = "select * from user where username = ' jayesh'; delete from user where id='1 ' ";

If you observe final query, upon execution it will delete the record from user table which was never the purpose of original query and this is called SQL Injection attack. 

Because of user data (which can be anything and uncontrolled) involvement in formation of query, SQL Injection attack can happen.

How prepared statement prevents SQL Injection?


Enjoy !!!! 

If you find any issue in post or face any error while implementing, Please comment.

4 comments

Post a Comment