SQL Injection

Understanding the vulnerability

Stand for Structured Query Language injection. The idea is to access SQL database content by abusing an entry that parses user input to use it in SQL.

Simple test like ' OR 1=1 #-- a are often used on most input fields to test out easiest SQLi but its rarely sufficient. Burp Suite active scanner automatically has a list of payloads made to trigger this vulnerability. SQLMap Essentials can be a good module to look after to get comfortable with that amazing tool.

OWASP Definition

---

A SQL injection attack consists of insertion or “injection” of a SQL query via the input data from the client to the application.

A successful SQL injection exploit can read sensitive data from the database, modify database data (Insert/Update/Delete), execute administration operations on the database (such as shutdown the DBMS), recover the content of a given file present on the DBMS file system and in some cases issue commands to the operating system.

SQL injection attacks are a type of injection attack, in which SQL commands are injected into data-plane input in order to affect the execution of predefined SQL commands.”

SQL injection attack occurs when:

1. An unintended data enters a program from an untrusted source.
2. The data is used to dynamically construct a SQL query

The main consequences are:

• Confidentiality: Since SQL databases generally hold sensitive data, loss of confidentiality is a frequent problem with SQL Injection vulnerabilities.
• Authentication: If poor SQL commands are used to check user names and passwords, it may be possible to connect to a system as another user with no previous knowledge of the password.
• Authorization: If authorization information is held in a SQL database, it may be possible to change this information through the successful exploitation of a SQL Injection vulnerability.
• Integrity: Just as it may be possible to read sensitive information, it is also possible to make changes or even delete this information with a SQL Injection attack. Source : OWASP.

Cheatsheet

Depending on the constraints and the behavior we encounter when trying SQL injection, you might want to use different ones :

Union Based SQLi

---

Union Based SQL injection works by using the union operator in SQL to include additional content to the original request.

Union based SQLI must satisfy these constraints :

• same number of columns as the query on the left union
• data types must be compatible between the types of left union and right

Query using UNION operator combine the different SELECT queries result in one set, thus needing the different types to be compatible with each other.

Methods to find the correct number of columns

1. You can iterate using : ORDER BY 1— ORDER BY 2— ORDER BY 3— … Intruder feature in Burpsuite is quite handy for this. Once you get an different response than usual from the server, you know that the previous iteration was the correct one.
2. Using parameters such as : UNION SELECT NULL— UNION SELECT NULL,NULL— UNION SELECT NULL,NULL,NULL—

Blind SQLi

---

When trying to exploit SQLis, you might not get any error or unintended behavior reflected by the server to the client. However, manipulating the query might reveal exploitation vector.

Modifying a result query

If you happen to have a specific set of results (let’s say 30 articles) from a proper query, you can see if the input is vulnerable by trying to get different returned values when selecting everything or nothing depending of the context. If you’re able to see unintended results by modying the query, you might be onto something.

Playing with time to reveal injection vector

Using the sleep() command in the payload and comparing intended query response time with the malicious one can help discover SQLis.

Trying conditional responses

One way to exploit blind SQLis is to determine content based on the validity of your query. For example, if you want to find an account’s password knowing the table names you can test for individual chars using SUBSTRING and trying different values.

Payload example :

xyz' AND SUBSTRING((SELECT Password FROM Users WHERE Username = 'Administrator'), 1, 1) > 'm
xyz' AND SUBSTRING((SELECT Password FROM Users WHERE Username = 'Administrator'), 1, 1) > 't
xyz' AND SUBSTRING((SELECT Password FROM Users WHERE Username = 'Administrator'), 1, 1) = 's

Use of intruder is extremely appreciated in these scenario. You will want (need) to automate to find results on large research.

Error Based SQLi

---

Trigger error to reveal data

If the server is too verbose when returning erros, you might be able to retrieve usefull data, queries content or results.

Blind SQL behaviour

An error that show up information can help through exploiting a blind SQL injection

Trigger error to get a different output then expected

Consider you can trigger an error in a SQLi, you might see a different output from the server than a valid query. By observing this, you can replicate a similar methodology than for blind SQL.

Examples :

' and (select case when (1=2) then 1/0 else 'a' end)='a

' AND (SELECT CASE WHEN (1=1) THEN 1/0 ELSE 'a' END)='a

Here you can try this query and observe if you get different server responses wether you triggered the error on the second payload or not.

Example :

' AND (SELECT CASE WHEN (Username = 'Administrator' AND SUBSTRING(Password, 1, 1) > 'm') THEN 1/0 ELSE 'a' END FROM Users)='a

Second example shows how you can trigger the error if you satisfy the first case condition, thus revealing information on the database (need for scripting or automation is mandatory for big tests).

Using CAST to trigger an error

If the content result is a string and you cast it to an unexpected type, you might get an error that reveal sensitive data. This can be used to exploit Blind SQLi that would not normally return useful data, howver SQL error might reveal some content from the query.

Use case

1. You have an injection point.
2. You’re able to get a verbose error message.
3. You can leverage it to return variable names and content such as tables, columns, data

MySQL based examples

Keep in mind the syntax needs to be adapted for other DB such as Oracle where limits are rownum, types are different etc…

Examples : Using cast to get info on the table names

' and 1=cast((select table_name from information_schema.tables limit 1) as int)--

Using cast to get info on the users names

'and 1=cast((select users from users limit 1 offset 1) as int)--

Repeat the for passwords (especially on privileged users)

'and 1=cast((select password from users limit 1)as int)--

Embed queries

If quotes are sanitized in any way, embed the previous request you made to retrieve a table in parameters using WHERE

Example :

cast((select column_name  FROM information_schema.columns where table_name=(SELECT table_name FROM information_schema.tables LIMIT 1 OFFSET 0) limit 1) as int)--```

Time Based SQLi

---

Use delay to reveal data based of delay. This can be used when Blind SQLi is not weak to conditional errors or revealing content using errors.

;SELECT CASE WHEN (username='administrator') THEN pg_sleep(3) ELSE NULL END FROM USERS--

;SELECT CASE WHEN (username='administrator' AND SUBSTRING(password,1,1)<'m') THEN pg_sleep(3) ELSE NULL END FROM USERS--

tocomplete

Note for testing purpose

---

Find parameters used in sensitive input and try to exploit them from different vector

Explanation : You might find classical parameters such as username and password in login fields. Even though these are properly sanitized, some other vulnerable vectors on a webapp might be exploitable. You might be able to leverage these vectors to reach the parameters you couldn’t get through their own input fields.

Catenation to get multiple values from one column

BEWARE : Depending on the Database syntax can change. If you have only one column available but you need to retrieve multiple values together, you can use the catenation feature. On a default Oracle DB, it looks like : SELECT username||' '||password FROM USERS-- Note that the separator isn’t needed, double pipe allow catenation but it is great for lisibility.

Checking for DB type and version

First simple check is gathering intel upon triggering erros. If the server’s response is too verbose, you can get from a simple DB error with it’s type and version or info from stacktrace. Second option is directly trying queries for version. In order to get the right one (if the server is accepting these queries depending on access), you’ll need to try multiple payloads such as :

Database type	Query
Microsoft, MySQL	SELECT @@version
Oracle	SELECT * FROM v$version
PostgreSQL	SELECT version()

Using information schema

Similarly to GraphQL you can query information from the DB’s schema. If the response allows such queries, you can list tables, and next proceed to list columns from tables … These data will then help you read targeted information such as users name, password (or hashed password)…

MariaDB enumeration with schema :

/*Enumerate databases */
SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA; 
 
/*Enumerate tables */
SELECT TABLE_NAME,TABLE_SCHEMA FROM INFORMATION_SCHEMA.TABLES WHERE table_schema='target';
 
/*Enumeratecolumns*/
SELECT COLUMN_NAME,TABLE_NAME,TABLE_SCHEMA FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name='target';

Portswigger SQLi cheatsheet

Remediation

The first protection to SQL injections is Parameterized Queries, known as Prepared Statements. Understanding the power of this tool can be looked-up in the following folded callout (Source from Stackoverflow)

Using parameterized Queries to avoid SQLi (application for PHP)

The correct way to avoid SQL injection attacks, no matter which database you use, is to separate the data from SQL, so that data stays data and will never be interpreted as commands by the SQL parser. It is possible to create an SQL statement with correctly formatted data parts, but if you don’t fully understand the details, you should always use prepared statements and parameterized queries. These are SQL statements that are sent to and parsed by the database server separately from any parameters. This way it is impossible for an attacker to inject malicious SQL.

You basically have two options to achieve this:

1. Using PDO (for any supported database driver):

   $stmt = $pdo->prepare('SELECT * FROM users WHERE name = :name');
   $stmt->execute([ 'name' => $name ]);
   
foreach ($stmt as $row) {
       // Do something with $row
}

• Using MySQLi (for MySQL):
  Since PHP 8.2+ we can make use of execute_query() which prepares, binds parameters, and executes SQL statement in one method:

$result = $db->execute_query('SELECT * FROM users WHERE name = ?', [$name]);
while ($row = $result->fetch_assoc()) {
   // Do something with $row
}

Up to PHP8.1:

$stmt = $db->prepare('SELECT * FROM employees WHERE name = ?');
$stmt->bind_param('s', $name); // 's' specifies variable type 'string'
$stmt->execute();
$result = $stmt->get_result();
while ($row = $result->fetch_assoc()) {
  // Do something with $row
}

If you’re connecting to a database other than MySQL, there is a driver-specific second option that you can refer to (for example, pg_prepare() and pg_execute() for PostgreSQL). PDO is the universal option.

Explanation

The SQL statement you pass to prepare is parsed and compiled by the database server. By specifying parameters (either a ? or a named parameter like :name in the example above) you tell the database engine where you want to filter on. Then when you call execute, the prepared statement is combined with the parameter values you specify.

The important thing here is that the parameter values are combined with the compiled statement, not an SQL string. SQL injection works by tricking the script into including malicious strings when it creates SQL to send to the database. So by sending the actual SQL separately from the parameters, you limit the risk of ending up with something you didn’t intend.

Any parameters you send when using a prepared statement will just be treated as strings (although the database engine may do some optimization so parameters may end up as numbers too, of course). In the example above, if the $name variable contains 'Sarah'; DELETE FROM employees the result would simply be a search for the string "'Sarah'; DELETE FROM employees", and you will not end up with an empty table.

Another benefit of using prepared statements is that if you execute the same statement many times in the same session it will only be parsed and compiled once, giving you some speed gains.

Oh, and since you asked about how to do it for an insert, here’s an example (using PDO):

$stmt = $db->prepare('INSERT INTO table (column) VALUES (:column)');
$stmt->execute(['column' => $value]);

Can prepared statements be used for dynamic queries?

While you can still use prepared statements for the query parameters, the structure of the dynamic query itself cannot be parametrized and certain query features cannot be parametrized.

For these specific scenarios, the best thing to do is use a whitelist filter that restricts the possible values.

// Value whitelist
// $dir can only be 'DESC', otherwise it will be 'ASC'
if (empty($dir) || $dir !== 'DESC') {
   $dir = 'ASC';
}

Second option would be Stored Procedures though I would recommend against it notably due to this citation from OWASP :

When Stored Procedures Can Increase Risk

Occasionally, stored procedures can increase risk when a system is attacked. For example, on MS SQL Server, you have three main default roles: db_datareader, db_datawriter and db_owner. Before stored procedures came into use, DBAs would give db_datareader or db_datawriter rights to the webservice’s user, depending on the requirements.

However, stored procedures require execute rights, a role not available by default. In some setups where user management has been centralized, but is limited to those 3 roles, web apps would have to run as db_owner so stored procedures could work. Naturally, that means that if a server is breached, the attacker has full rights to the database, where previously, they might only have had read-access.

Note that performance wise, Parameterized Queries and Stored Procedures shouldn’t see any difference if properly setup, thus it’s not a valid criteria to advocate for the latter option.

Allow-list Input Validation is an excellent alternative as long as your model is suited for it. If the list of expected parameters is well defined, you can setup the allow list to refuse any other input.

Practicing

Portswigger SQL injections lab are numerous and varied. Rootme also has different SQLi challenges with manual exploitation in mind, though they can be used to practice SQLMap tooling.

Two whole modules are dedicated to SQLi in CPTS and CWES courses :

• SQL Injection Fundamentals
• SQLMap Essentials