Intro to SQL Injections

SQL in Web Applications

Web applications connect to databases (e.g., MySQL) to store and retrieve data. In PHP:

$conn = new mysqli("localhost", "root", "password", "users");
$query = "select * from logins";
$result = $conn->query($query);

Results can be iterated and displayed:

while($row = $result->fetch_assoc() ){
    echo $row["name"]."<br>";
}

User input is commonly incorporated into queries, e.g., for search:

$searchInput =  $_POST['findUser'];
$query = "select * from logins where username like '%$searchInput'";
$result = $conn->query($query);

When user input is placed directly into a query without sanitization, SQL injection becomes possible.

What is an Injection?

Sanitization - removal of special characters from user input to prevent injection attempts.

Injection occurs when an application misinterprets user input as code rather than a string, changing code flow and executing it. This happens by escaping input bounds with special characters like ', then writing code (JavaScript, SQL, etc.) to be executed.

SQL Injection Basics

An unsanitized input like 1'; DROP TABLE users; turns the query:

select * from logins where username like '%$searchInput'

Into:

select * from logins where username like '%1'; DROP TABLE users;'

The injected DROP TABLE query executes alongside the original.

Note: Stacking queries with ; is not possible in MySQL but works in MSSQL and PostgreSQL.

Syntax Errors

The trailing ' after the injected query causes a syntax error. To achieve successful injection, the modified query must remain syntactically valid. Techniques to handle this:

Comments - comment out the trailing characters
Matching quotes - pass additional single quotes to balance the syntax

Types of SQL Injections

In-Band

Output of the injected query is displayed directly on the front end.

Type	Description
Union Based	Specify which column to read from; output is directed to be printed there
Error Based	Intentionally cause SQL errors that return query output in error messages

No direct output is visible; output is inferred through logic.

Type	Description
Boolean Based	Use conditional statements to control whether the page returns any output
Time Based	Use conditional statements with `Sleep()` to delay the response if the condition is true

Out-of-Band

No direct access to output at all. Output is directed to a remote location (e.g., DNS record) and retrieved from there.

notebook