OWASP Top 10
A key part of all CTF's and bug bounties has to do with SQL injection.
SQL injection is such a widely utilized entry point for web attacks that it has been listed under the OWASP Top 10 vulnerabilities.
Basic's of SQL Injection
SQL stands for structured query language and is utilized for manipulating, reading, and writing data in databases. Largely used by front end web applications such as a website allowing users to search products, or login. SQL injection attacks occur when a bad actor inputs malicious code, into a web form or input field, usually involving what are known as escape characters such as ' " -- . These escape characters allow an attacker to append the SQL queries with malicious statements. It is up to the developer to prevent these attacks by utilizing string sanitization, or input validation which I will cover later in this blog with some visual examples.
Overview of SQL Databases
The simplest way to think of a database is by picturing an excel spread sheet with rows and columns. These are referred to as tables and a database can have several tables to pick from.
A database stores information allowing easy access for applications to retrieve data or even conduct deep data analyses across multiple databases. Typically all databases perform the same basic task, that is to enable users to create, edit and access stored information.
Sample table name = salesman
The example table above contains four columns that define each salesman's name, city and commission. This is how virtually all databases will look like when querying and requesting tables.
Some of the more popular databases include;
Oracle 12c - This database management tool was created in the 70's and typically sets the bar for other DBMS (Database management systems).
MySQL - Is a very popular database for web applications as it is open source and free. Frequent updates, features and security patches provide for a reliable DBMS tool.
Microsoft SQL Server - Microsoft DBMS is commonly used with Microsoft Azure cloud computing and provides fast and stable infrastructure that can also be utilized with Linux. This is the commonly used option when combining Microsoft products for SQL functionality.
SQL Keywords
These words are the foundation blocks of all SQL statements, you can think of keywords as standard functions found within any programming language. These key words will make up the bulk of your statements with the most emphasis on;
SELECT - Selects from a database
FROM - Specifies which table in the database to target
WHERE - Filters a result set to include records that meet specific conditions
SQL Operators
Within the SQL language, just like other languages there are operators which perform operations such as comparisons, arithmetic or logical operations. The operators define conditions in SQL statements and can be combined to form multiple conditions in a statement or query.
SQL Statements and Syntax
SQL statements are passed as code or instructions, interpreted and executed by that database management system.
Below is an example and breakdown of an SQL statement or query.
SELECT * FROM salesman
The query "Select * FROM salesman" is requesting a selection of wild card (*) which suggests everything, in this case from the "salesman" table.
SELECT * FROM salesman WHERE city = 'Paris'
In this instance we already reviewed the first part of the statement, in which we define the table we wish to select from and we further add the WHERE keyword which allows us to specify a condition. In this case the condition is equal to the string Paris within the city column.
The output is shown below.
The entry point of attacks
The entry point of SQL injection attacks is almost always traced back to user input that has not been validated or sanitized for escape characters or additional SQL statements. Below is an example of a web form and the back end code.
This is of course a standard web form requesting a product name, lets look at the SQL query behind the scenes.
<?php
$product = $_GET['Search term'];
$result = mysql_query("SELECT * FROM store WHERE
product='$Search term'");
?>The above SQL query written in PHP is an example of a programmatic query, but also an example of vulnerable code, due to the lack of input sanitization/validation.
An attacker could input the following string into the product form,
' OR 1=1;
This would create the following back end query, which would evaluate each row has 1=1 (True) and return all data in each row.
SELECT * FROM shop WHERE product='' OR 1=1
This is an example of SQL escaping, whereby we enter a single quote which is syntactically incorrect and causes the SQL application to continue reading the appended statement of OR 1=1.
The query now checks all product rows in the shop table, and returns each product that has the property value of True, which is conditionally defined by OR 1=1.
SQL Secure Coding
Lets have some fun and take a look at SQL in PHP, to identify vulnerable code and then securely re-write that code.
In the above code we see that user input is being passed directly to parameters, and those parameters are then passed and executed without checking the input for escape characters such as;
' ; --Below is an example of how we can accomplish the same task while securing our code from escape characters.
SQL secure coding practices dictate that you prepare your statements so as to separate them from parameters prior to execution. This intermediary step avoids directly executing user input.
In this instance, the parameter "product title" supplied from the user, is sanitzed for escape characters using the LIKE operator. This operator is a comparison operator that compares the user input string against a predefined function called query, and is then combined with the user parameter.
The query function, who's responsibility it is to check the string for escape characters, looks something like this;
$query = test_input($_POST["user input"]);
if (!preg_match("/^[a-zA-Z ]*$/",$%)) {
$Error = "Alphanumerical characters and white spaces only";
}The preg_match() function searches a string for patterns, returning true/false if the pattern does or does not exist. In this instance, the ! operator (does not equal) proceeds the pre_match function, and then specifies within regular expression, the specific character range of a-zA-Z.
So if the user string does not equal a-z or A-Z then the query function would return false, and the user would be prompted to re-search.
SQL Injection Enumeration
Part of being able to engage in SQL injection vulnerabilities in a CTF or bug bounty is knowing how to find them. Although there are volumes of knowledge and methodologies I am going to cover a very simple technique knows as error based testing. Having already established that using single quotation marks in a user input form can cause a poorly developed SQL server to error, we will further use this to enumerate for entry points from which we can launch manual or automated attacks.
The example below is taken from the Hack the Box Giddy machine, in which SQL injection is required in order to obtain data from the back end database.
In the below example we browse to the server and it is an online shop with a search function.
We simply enter a single quotation mark and await the response, keeping in mind that if the SQL query was coded securely, it will respond with invalid input detected. If the SQL query is not configured correctly, the response will be a server error page.
So we see that the server did not have instructions for how to handle an escape character, which means our input was passed directly for execution and we can now start testing for syntax.
Although in some cases, error based SQL enumeration will provide a response that suggests what the syntax is suppose to be. In this instance however, we are going to use an automated tool "SQL Map" to help with this testing.
If we manually browse to a product we will see parts of the syntax leaked in the URL. This tells us that the Product Sub Categories are grouped by a numerical ID value. So let's drop a single quote and see what happens.
We received an error after the single quotation mark, that tells us the application does not know how to handle escape characters.
We will use a common penetration testing tool "SQL Map". This tool will test SQL injection points with thousands of queries, while identifying back end infrastructure in a methodical order, making it my personal favorite for SQL enumeration.
The command is simple and only involves calling the function, and specifying one flag.
u - specifies URL
sqlmap -u http://10.10.10.104/mvc/Product.aspx?ProductSubCategoryId=26
The enumeration process was able to determine that the database management system is Microsoft sql server, so we will now take our SQL enumeration one step further.
The command below, will specify our database management system as Microsoft SQL Server, and will use a dictionary of defined attacks tailored for the mssql server, in an attempt to enumerate present databses.
sqlmap -u http://10.10.10.104/mvc/Product.aspx?ProductSubCategoryId=26 --dbms mssql --dbsu - specifies URL
--dbms - specifies a database management system
--dbs - calls the database enumeration function
We see that our enumeration was successful, and we are provided with 5 databases.
Now, we are going to take this another step, and attempt to specify one of those databases, and enumerate the tables within.
sqlmap -u http://10.10.10.104/mvc/Product.aspx?ProductSubCategoryId=26 --dbms mssql -D Injection --tablesu - specifies URL
--dbms - specifies a database management system
D - specifies our database
-- tables - calls the enumerate tables function
We see the tables above, found in the injection database.
Our final step will be to dump the information from the table above.
sqlmap -u http://10.10.10.104/mvc/Product.aspx?ProductSubCategoryId=26 --dbms mssql -D Injection -T CreditCard --dumpu - specifies URL
--dbms - specifies a database management system
D - specifies our database
T- specifies the table
--dump - calls the dump function
As you can see, we have successfully dumped a table, and all information within the table.
That is all
Thank you for reading my blog on SQL injection, and I would like to impart the following resources to assist with further SQL learning and practice.
A live SQL database that allows for an interactive and engaging experience from the browser.
A secure coding platform, designed to test, train and teach you about secure SQL coding with simulated environments.
Commentaires