SQL Injection and Mitigation with Example

SQL injection is a type of security vulnerability where an attacker is able to insert or "inject" malicious SQL code into a query, which can then be executed by the database. This might allow the attacker to view data they are not authorized to view, modify or delete data, and perform other malicious actions.

Example of SQL Injection:

Consider a simple login form where users enter a username and password. The backend code that checks the login credentials might look like this:

sql = "SELECT * FROM users WHERE username='" + input_username + "' AND password='" + input_password + "';"

If an attacker enters the following as the input_username:

' OR '1'='1'; --

The SQL query becomes:

SELECT * FROM users WHERE username='' OR '1'='1'; -- ' AND password='whatever';

The -- is an SQL comment, so everything after it is ignored. Thus, the query will return a row even if the username and password do not match, potentially allowing unauthorized access.

Mitigation Techniques:

Use Prepared Statements (Parameterized Queries): Instead of constructing SQL statements using string concatenation, use prepared statements. They ensure that user input is always treated as data and not executable code.
Example with prepared statement (in Java with JDBC):
```
String query = "SELECT * FROM users WHERE username=? AND password=?;";
PreparedStatement stmt = connection.prepareStatement(query);
stmt.setString(1, input_username);
stmt.setString(2, input_password);
ResultSet rs = stmt.executeQuery();
```
Use Stored Procedures: Instead of writing SQL in your application code, use stored procedures in the database and call them from your application.
Escape User Input: If you must include user input in SQL queries (which is not recommended), make sure to properly escape it to make it safe for inclusion in SQL.
Least Privilege: The database account used by the application should have the least privileges necessary. For instance, if your application only needs to fetch data, do not give it delete or update privileges.
Web Application Firewall (WAF): A WAF can help detect and block SQL injection attempts.
Regularly Update and Patch: Ensure that your database software and application frameworks are regularly updated and patched.
Error Handling: Do not reveal detailed database error messages to users. Generic error messages should be displayed, and detailed errors should be logged for internal review.

By following best practices and employing the techniques above, the risk of SQL injection can be greatly minimized.

SQL Injection prevention techniques with examples:

One effective technique to prevent SQL Injection is to use parameterized queries or prepared statements. Here's an example in Java using JDBC:

String username = request.getParameter("username");
String password = request.getParameter("password");

String query = "SELECT * FROM users WHERE username = ? AND password = ?";
try (PreparedStatement preparedStatement = connection.prepareStatement(query)) {
    preparedStatement.setString(1, username);
    preparedStatement.setString(2, password);

    ResultSet resultSet = preparedStatement.executeQuery();
    // Process the result set
} catch (SQLException e) {
    // Handle exceptions
}

Parameterized queries ensure that user inputs are treated as data and not executable SQL code.

Preventing SQL Injection in PHP with examples:

In PHP, you can prevent SQL Injection by using prepared statements with PDO (PHP Data Objects) or MySQLi. Example using MySQLi:

$username = $_POST['username'];
$password = $_POST['password'];

$conn = new mysqli("localhost", "username", "password", "database");

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

$stmt->execute();

$result = $stmt->get_result();
// Process the result set

$stmt->close();
$conn->close();

Parameterized queries and SQL Injection prevention:
Parameterized queries or prepared statements are a powerful defense against SQL Injection. They ensure that user inputs are treated as data and not executable SQL code. Examples were provided earlier for Java and PHP.
Web application firewall for SQL Injection protection:
A Web Application Firewall (WAF) can add an additional layer of defense against SQL Injection attacks. It filters and monitors HTTP traffic, blocking malicious requests before they reach the web application. WAFs often have signature-based detection and can be configured to block known SQL Injection patterns.