Error-based SQL Injection:
This is the most common type of vulnerability in SQL injection. It is quite easy to exploit.
It relies on feeding the server unexpected commands or invalid inputs, which will force the server to send some error.
From this error, the attacker can determine the structure, version, operating system or even return full query results.
In the below example, This webpage returns if the name entered exists in the table or not.
The SQL query used by the application is the following:
"SELECT * FROM customer WHERE first_name='". $_GET['name'] ."'";
The server accepts input from the user and returns if that name exists or not. This means that the attacker can input invalid inputs to modify the backend query.
For example, we can add a single quote at the end of the input like mary’. After accepting this input the backend query will look like the following.
"SELECT * FROM customer WHERE first_name=’ mary’ ’ “; (note the extra quote here)
This means that the query is invalid so the server will return the following error.
The attacker uses this type of injection because of the fact that injected malicious input will cause the server to output the results into an error message. This message can reveal many details about the underlying DB. For example, the following injected payload
MARY' AND (SELECT 0 FROM (SELECT count(*), CONCAT((SELECT @@version), 0x23, FLOOR(RAND(0)*2)) AS x FROM information_schema.columns GROUP BY x) y) - - '
Will result in the following error.
Duplicate entry '5.7.29-0ubuntu0.16.04.1#1' for key ''
This error is generated because of the fact that GROUP BY requires unique group keys, which we have intentionally not passed unique to return the value of SELECT @@version in the error message.
UNION-based SQL Injection:
The union operator appends the results from both queries vertically. This allows the user to run two or more queries if they have the same structure. I have chosen the SELECT query in this example. For this exploit to work, the following conditions must be satisfied.
Each SELECT statement within the UNION has the same number of columns.
The columns must also have similar data types.
The columns in each SELECT statement are in the same order.
SELECT first_name, last_name from customer UNION SELECT username, password from login;
Let’s try to implement a UNION SQL injection.
We have a simple web page that takes a name and if the name is found return the name and its email address.
We will use the following input in the search box.
Which gives the following output.
It seems that the structure of our query is different than the original query. Let’s try to increase the number of columns
mary’ UNION SELECT 1,2;--
Which gives the following output.
This query was successful which means that we have the number of columns correct now. Expanding on this idea the attacker can get any data from the database as long as he is using 2 columns. For example the following query:
mary’ UNION SELECT 1,@@version;--
gives the following output.
We can get all of the information on the databases present in this server. Using the following query.
mary’ UNION SELECT table_name,column_name FROM information_schema.columns;--
The above query will show all of the database tables and columns but I have shown the ones which we will be using.
Now we know that this table contains these columns. We can exploit it by the following query.
Blind SQL Injection:
mary’ UNION SELECT customer_id, email FROM customer;--
This type of SQL injection does not show any kind of error. That’s why it is called a Blind SQL injection. In this type of injection, the attacker relies on the response type from the server or the time it takes to get a response from the server. That is why there are 2 types in Blind SQL Injection.
Boolean-Based Blind SQL injection
Time-Based Blind SQL injection
In the Boolean-Based injection, the server responds with a True or False response. The attacker will modify the query according to the response of the server and will reveal information about the database.
For example, as we saw in Error-Based SQL injection the server will respond with “exists or not exists” response.
From the Error-Based injection, we know that name MARY exists in the database so we will modify our query to MARY’ AND 1=1;--, which will generate the following response.
Alternatively, feeding MARY’ AND 1=2;-- will return not exist as MARY exists in the database but 1=2 is false.
We can use the same method to find out the version of the database using the following query.
MARY' and substring(@@version,1,1)=1;--
If the first character of the version of the database is 1 then this will generate a positive response.for the second character we can use the following query.
MARY' and substring(@@version,2,1)=1;--
We can also know the name of the database. First, we need to know the length of the name of the database. We can do it from the following query.
MARY’ and length(database())=1;--
We can increment the trailing value until we get a positive response from the server.
To find out the characters in the database name we can try the following query.
MARY' and substring(database(),1,1)='a';--
We can replace the last character with all the alphabets until we get a positive response.
We can change the value after the database() to check for that particular character, i.e.
MARY' and substring(database(),2,1)='a';--
In the Time-Based Blind SQL injection, the attacker relies on the time it takes to get a response from the server. For example, consider the following query.
The above input will force a 10-second delay in the response. We can expand on this concept by doing the following query.
If the response comes in two seconds, it means that the version starts with “10.” The “like” string operator we used in the query is designed to make a character-by-character comparison.