# SQLi 101

{% hint style="info" %}
**SQL injection (SQLi)** is a vulnerability where an attacker can manipulate a web application's database queries by injecting malicious SQL code through user inputs.
{% endhint %}

{% hint style="danger" %}
This can lead to unauthorized data access, data corruption, deletion of data, and potentially complete control over the database server.
{% endhint %}

{% hint style="success" %}
Use [prepared statements](#prepared-statements) and [parameterized queries](#parameterized-queries), validate and sanitize user inputs, and employ ORM (Object-Relational Mapping) frameworks to abstract database operations.
{% endhint %}

## Educated Guesses

If we know the web server, we might infer what database is in use.

<table><thead><tr><th width="166">Web Server</th><th width="145">Database</th><th>Db-specific SQLi</th></tr></thead><tbody><tr><td>IIS </td><td>MSSQL</td><td>For <strong>MSSQL-specific</strong> SQLi info see <a href="../../../../services/tcp/dbms/sql/1433-mssql#sqli">here</a>. </td></tr><tr><td>Apache/Nginx</td><td>MySQL </td><td>For <strong>MySQL-specific</strong> SQLi info see <a href="../../../../services/tcp/dbms/sql/3306-mysql-mariadb#sqli">here</a>.</td></tr></tbody></table>

## SQLi Types

> _Alex Olsen's Blind SQLi_ [_payload examples_](https://www.db-fiddle.com/f/nLpyQDMd49iRygnY9H7CB8/5).

<div align="center"><figure><img src="https://2056709691-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FFqURPjUnKKhebI962QD8%2Fuploads%2FHqLHDRzID40Pyv9kS1Fq%2Fsqli_types.png?alt=media&#x26;token=29191a43-e973-4863-b53e-9a37f8b10ae2" alt=""><figcaption><p>Figure 1: SQL Injection types (<em>imaged adapted from</em> <a href="https://www.qafox.com/sql-injection-types/"><em>here</em></a>).</p></figcaption></figure></div>

## Detection

We can enumerate SQLi flaws via sending **manual payloads, s**earching for dynamic SQL statements during a **code review** and/or **fuzzing** the request. The most common method for enumerating a SQLi flaw is to identify input fields that might be used in a database and **submit characters that will break SQL queries**. This might lead to **verbose error messages** or **subtle differences in responses**.

### String Delimiters

Suppose we control an input field that asks for the parameter `name`. If our input is `x7331` the query may look like this:

```sql
// normal query when the input is x7331
SELECT * FROM users WHERE name = 'x7331'
```

If our input is `x7331' OR '1'='1` the query becomes:

```sql
// malicious query when the input is x7331' OR '1'='1
SELECT * FROM users WHERE name = 'x7331' OR '1'='1'
```

| Payload | URL Encoded |
| ------- | ----------- |
| `'`     | `%27`       |
| `"`     | `%22`       |
| `#`     | `%23`       |
| `;`     | `%3B`       |
| `)`     | `%29`       |

We can also fuzz for pontially banned characters.

{% code overflow="wrap" %}
```bash
ffuf -k -u https://watch.streamio.htb/search.php -d 'q=FUZZ' -w /usr/share/seclists/Fuzzing/special-chars.txt -c -ic -ac -H 'Content-Type: application/x-www-form-urlencoded'
```
{% endcode %}

### Closing Out Strings and Functions

There are various common functions used for string comparison, such as `LOWER()` and `UPPER()` to ensure consistency.

```sql
SELECT * FROM users WHERE LOWER(name) = LOWER('<userInput>');
```

In this case, the function should be closed out.

```sql
// injected payload
x7331') OR '1'='1
```

```sql
// resulting query
SELECT * FROM users WHERE LOWER(name) = LOWER('x7331') OR '1'='1')
```

We end up with a hanging `)` at the end, thus, we will need to comment out whatever comes after our payload.

```sql
// injected payload
x7331') OR '1'='1'#
```

```sql
// resulting query
SELECT * FROM users WHERE LOWER(name) = LOWER('x7331') OR '1'='1'#)
```

### Boundary Testing

{% hint style="info" %}
[**Boundary Value Analysis**](https://www.geeksforgeeks.org/software-testing-boundary-value-analysis/)[ ](https://www.geeksforgeeks.org/boundary-value-analysis-triangle-problem/)is common software testing technique based on testing the boundary values of valid and invalid partitions. The behavior at the edge of the equivalence partition is more likely to be incorrect than the behavior within the partition, so **boundaries are an area where testing is likely to yield defects**.
{% endhint %}

Boundary testing within a SQLi context refers finding a parameter that takes a limited data set and submitting values that fall outside that set. One form of SQLi boundary testing is [using the `ORDER BY` clause to enumerate the number of columns displayed in a SQL statement](../in-band#number-of-columns).

### Fuzzing

Automated tools, such as [`ffuf`](broken-reference) or Burp's Intruder, can be used to fuzz the suspected parameters with SQLi payloads and then assess the application's responses. The results of automated tools must always be validated manually.

## Prevention

### Parameterized Queries

1. **Separation of SQL and Data:** When you use a parameterized query, **the SQL statement and the data are sent to the database as separate entities**. The SQL statement is pre-compiled by the database, and the placeholders (`?`, `%s`, `:value`, etc.) are used to specify where the parameters will be substituted.
2. **Safe Binding:** The database engine safely binds the user-provided input values to the placeholders. This means that **the user input cannot alter the SQL command structure**. Instead, it is strictly **treated as data**.

Here’s how an SQLi might happen with a normal query.

```python
# Normal query with user input directly concatenated
user_id = "'; DROP TABLE users; --"
query = f"SELECT * FROM users WHERE id = '{user_id}'"
```

```sql
// resulting query
SELECT * FROM users WHERE id = ''; DROP TABLE users; --'
```

Here's how a parameterized query handles this.

```python
# Parameterized query with user input
user_id = "'; DROP TABLE users; --"
query = "SELECT * FROM users WHERE id = %s"
cursor.execute(query, (user_id,))
```

```sql
// resulting query
SELECT * FROM users WHERE id = '''; DROP TABLE users; --'
```

### Prepared Statements

**Parameterized queries** and **prepared statements offer the same level of protection against SQLi.** They both achieve the same goal of **separating SQL logic from user input**. However, prepared statements might be more explicitly linked to performance optimization through the process of compiling and reusing the SQL command.

```python
# Prepared statement with user input
user_id = input("Enter the user ID: ")  # User enters: 1; DROP TABLE users;
query = "SELECT * FROM users WHERE id = %s"
cursor.execute(query, (user_id,))
```

```sql
// resulting query
SELECT * FROM users WHERE id = '1; DROP TABLE users;'
```
