My journey started as a penetration tester specializing in web applications, and when I transitioned to bug bounties, my skills carried over 100%. Legitimately, 80% of the attacks you perform are going to be against a web application. After all, in today’s world, the vast majority of a company’s public-facing assets are web applications. For this reason alone, you MUST learn web application hacking if you want to be successful, and there is no better place to start than the OWASP Top 10. If all you learn is how to exploit these basic web vulnerabilities, you will be able to find bugs constantly.
SQL Injection (SQLi): A Classic Vulnerability
SQL Injection (SQLi) is a classic vulnerability that doesn’t seem to be going anywhere. This vulnerability can be exploited to dump the contents of an application’s database. Databases typically hold sensitive information such as usernames and passwords, so gaining access to this is basically game over. The most popular database is MySQL, but you will encounter others such as MSSQL, PostgreSQL, Oracle, and more.
The main cause of SQL injection is string concatenation in the application’s code. For example, if the application is concatenating user-supplied input directly into the SQL query string, you know you have an SQL injection vulnerability. The reason why this is so dangerous is because an attacker can append additional SQL queries to the current query. This would allow an attacker to query anything they want from the database without restrictions.
Finding the Vulnerability
Typically, when looking for this vulnerability, you can throw a bunch of double and single quotes everywhere until you see the famous SQL error message.
As you can see in the first image, appending a single quote to the cat variable value throws an SQL error. Look at the two error messages and notice how they are different. Note that %27 is the same as a single quote; it’s just URL encoded (a method of converting data into a format that can be transmitted over the internet).
In the following sections, the methods to exploit this vulnerability will be shown, and we won’t be using SqlMap. You need to know how to perform this exploitation by hand.
Note: SqlMap is an open-source penetration testing tool that automates the process of detecting and exploiting SQL injection flaws. You can learn more about it here: https://github.com/sqlmapproject/sqlmap.
Exploiting MySQL Databases
The two most common types of SQL injection in MySQL are union based and error based.
Union Based SQL Injection
Union based SQL injection uses the UNION SQL operator to combine the results of two or more SELECT statements into a single result.
1. Determining the Number of Columns
Once you know that an endpoint is vulnerable to SQL injection, the next step is to exploit it. First, you need to figure out how many columns the original query is using. This can be accomplished with the ORDER BY operator. Basically, you are asking the server, “Do you have one column?” If it does, the page will load. Then you ask, “Do you have two columns?” If it loads, it does; if it throws an error, you know it doesn’t.
If you were to try ORDER BY 4 and it fails, while ORDER BY 3 loaded without any errors, it means there are 3 columns in the original query.
2. Finding Displayable Columns
Now that you know how many columns the SQL query is using, you need to figure out which columns are being displayed to the page. This is crucial because you need a way to display the information you are extracting.
To accomplish this, you can use the UNION ALL SELECT statement. Note that for the second SELECT statement to show, you need to make the first query return nothing. This can be accomplished by putting an invalid ID (like a negative number) in the original query’s parameter.
' UNION ALL SELECT 1, 2, 3 --
Notice the numbers on the page (like “2” and “3”). These numbers refer to the columns which are being displayed on the front end. These are the columns you will use to display the results from your queries.
3. Extracting Data (Database Version, Tables, Columns)
One of the first pieces of information to extract is typically the database version. This can be accomplished with the following MySQL command:
@@versionversion()
' UNION ALL SELECT 1, 2, @@version --
You can see that the database is running with MySQL version 5.1.73. It’s a good idea to note this down as it might come in handy later.
To extract sensitive data, you first need to know what database tables you want to target. You can get a list of tables with the following command:
SELECT * FROM information_schema.tables
Note that information_schema.tables is a default table within MySQL that holds a list of table names. This table has two columns of interest: table_name and table_schema. The table_schema column holds the name of the database the table belongs to. To only get tables from the current database, filter the results with the WHERE operator.
' UNION ALL SELECT 1, 2, group_concat(table_name) FROM information_schema.tables WHERE table_schema = database() --
As you can see above, a list of all the tables belonging to this database is retrieved. You might have noticed the function database(); this function outputs the current database name and is used to filter the results via the table_schema column. You also might have noticed the group_concat function; this function will concatenate all the table names into a single string so they can all be displayed at once.
Once you pick which table you want to target (e.g., "users"), you need to get a list of columns belonging to that table. A list of columns belonging to a table can be retrieved via the information_schema.columns table as shown in the below query:
' UNION ALL SELECT 1, 2, group_concat(column_name) FROM information_schema.columns WHERE table_name = "users" --
The most interesting column names are typically uname and pass. The final step is to dump the contents of these two columns as shown below:
' UNION ALL SELECT 1, 2, group_concat(uname, ":", pass) FROM users --
The data shows a user called test with the password test. These credentials can then be used to log in to the application as that user.
Error Based SQL Injection (MySQL)
With union based SQL injection, the output is displayed by the application. Error based SQL injection is a little different as the output is displayed in an error message. This is useful when there is no output other than an SQL error.
Exfiltrating Data with ExtractValue() and XPath
If the MySQL server version is 5.1 or later, you can use the extractvalue() function to exfiltrate data from the database. The ExtractValue() function generates an SQL error when it is unable to parse the XML data passed to it, and the resulting error message can be manipulated to contain database information.
First, you need to understand how the ExtractValue() function works. It is typically used to parse out a value from an XML document: ExtractValue(xml_string, xpath_expression).
If the second argument (the XPath expression) starts with a semicolon (;), it will cause a MySQL error message to appear along with the string that caused the error. Attackers can abuse this to extract data via error messages.
Exploiting via Error Message:
To extract the database version:
' AND extractvalue("blahh", concat(";", @@version)) --
The next step is to get a list of table names. Similar to union based SQL injection, you will be utilizing the information_schema.tables table to achieve this.
' AND extractvalue("blahh", (SELECT concat(";", table_name) FROM information_schema.tables WHERE table_schema = database() LIMIT 0, 1)) --
Notice the LIMIT 0, 1 command at the end of the query. This is used to get the first row in the table. With error based SQL injection, you have to query one table name at a time. To get the second table, you would use LIMIT 1, 1, and so on.
The next step is to query the column names belonging to the target table ("users"):
' AND extractvalue("blahh", (SELECT concat(";", column_name) FROM information_schema.columns WHERE table_name = "users" LIMIT 0, 1)) --
The final step is to extract the data from the columns:
' AND extractvalue("blahh", (SELECT concat(";", uname, ":", pass) FROM users LIMIT 0, 1)) --
As you can see, the username and password of the first user, test:test, were extracted via an error message. To get the next user, just change LIMIT 0, 1 to LIMIT 1, 1.
Exploiting PostgreSQL Databases
If you know how to perform SQL injection on a MySQL server, then exploiting PostgreSQL will be very similar. Just like MySQL, you typically throw single and double quotes everywhere until you see the famous error message appear:
The name psycopg2 is a Python library for PostgreSQL, so if you see this name, you know you’re working with a PostgreSQL database server.
Union Based SQL Injection (PostgreSQL)
1. Determining the Number of Columns
Just like MySQL, the first step is to determine how many columns the SQL query is using, which is accomplished by using the ORDER BY operator.
If an error is displayed once you try ORDER BY 3, it tells you that there are only 2 columns being retrieved by the query.
2. Type Matching and Display
You can use the UNION ALL SELECT operator to perform the second query. Note how the second SELECT column is wrapped in single quotes, this is because the column types must match the original query. If the first column is an integer and the second is a string, your union must reflect that.
' UNION ALL SELECT 1, 'text' --
You can also use the word null if you don’t know the data type:
UNION ALL SELECT null, null
If you weren’t able to detect the database type from the error message, you could always use the version() function to print the database type and version:
' UNION ALL SELECT 1, version() --
3. Extracting Data with OFFSET
After you have the number of columns, you need to find all the tables in the database by querying the information_schema.tables table.
' UNION ALL SELECT 1, table_name FROM information_schema.tables WHERE table_schema != 'pg_catalog' AND table_schema != 'information_schema' OFFSET 0 --
For the most part, this is the same as MySQL, but there are a few differences. For starters, PostgreSQL doesn’t have a group_concat function. Instead, you can return one table_name at a time with the OFFSET operator. OFFSET 0 gets the first table name, OFFSET 1 gets the second, and so on. You also filter out the default databases pg_catalog and information_schema as they tend to clutter the results.
Once you have your target table (e.g., "users"), the next step is to extract the columns associated with it:
' UNION ALL SELECT 1, column_name FROM information_schema.columns WHERE table_name = 'users' OFFSET 0 --
Once the column names (e.g., username and password) are known, you can extract the data:
' UNION ALL SELECT 1, concat(username, ':', password) FROM users OFFSET 0 --
Finally, the username and password of the first user are shown. An attacker could then use these credentials to log in to the application.
Exploiting Oracle Databases
MySQL and PostgreSQL are very similar to each other, so if you know one, the other will come easy. However, Oracle is different from those two and will require some additional knowledge to successfully exploit it. As always, when testing for this vulnerability, you usually just throw a bunch of single and double quotes around until you get an error message as shown below:
The error message starts with ORA, and that’s a good sign that you are dealing with an Oracle database.
Sometimes, you can’t tell the database type from the error message. If that’s the case, you need to return the database version from an SQL query:
SELECT banner FROM v$version
Note that similar to PostgreSQL, when you are selecting a column, it must match the type of the first SELECT statement. You can also use the word null as well if you don’t know the type. Another thing to note is that when using the SELECT operator, you must specify a table; the default table of dual is often used.
Union Based SQL Injection (Oracle)
1. Determining the Number of Columns
Just like MySQL and PostgreSQL, the first step is to figure out how many columns the SELECT statement is using with the ORDER BY operator.
If an error is displayed once you reach column number 3, there must only be 2 columns used in the select statement.
2. Extracting Tables and Columns
The next step is to retrieve a list of tables belonging to the database. If you’re used to using MySQL or PostgreSQL, you would normally use the information_schema.tables table to get a list of tables, but Oracle uses the all_tables table for this.
' UNION ALL SELECT LISTAGG(table_name, ',') WITHIN GROUP (ORDER BY table_name), null FROM all_tables WHERE tablespace_name = 'USERS' --
You probably want to filter on the tablespace_name column value USERS; otherwise, you will get hundreds of default tables that are of no use. Also notice the listagg() function; this is the same as MySQL’s group_concat() function and is used to concatenate several rows into a single string. When using the listagg() function, you must also use the WITHIN GROUP() operator to specify the order of the results.
Once you get your target table (e.g., "EMPLOYEES"), you need to get a list of the column names belonging to that table. With Oracle, you use the all_tab_columns table to do this:
' UNION ALL SELECT LISTAGG(column_name, ',') WITHIN GROUP (ORDER BY column_name), null FROM all_tab_columns WHERE table_name = 'EMPLOYEES' --
Finally, once you know the table’s column names, you can extract the information you want using a standard SQL query as shown below:
' UNION ALL SELECT email, phone_number FROM employees --
As you might have noticed, Oracle SQL injection is a little different compared to MySQL and PostgreSQL, but it is still very similar. The only difference is the syntax of a couple of things, but the process remains the same: Figure out the number of columns, get the target table name, get the table’s columns, then finally extract the sensitive information.
Summary
SQL injection is one of the oldest tricks in the book yet it still makes the OWASP Top 10 list every year. It’s relatively easy to search for and exploit, plus it has a high impact on the server since you are able to steal everything in the database, including usernames and passwords. If you’re searching for this vulnerability, you are bound to come across a vulnerable endpoint; just throw single and double quotes everywhere and look for the common error messages. Unlike many other security enthusiasts, you should know how to exploit the vast majority of databases, not just MySQL, so when you do find this bug, it shouldn’t be too hard to exploit.
