How to Mitigate SQL Injection

Mitigating SQL injection involves ensuring that user inputs are properly handled and validated. Here are some effective methods with examples:

Use Prepared Statements (Parameterized Queries):
Prepared statements ensure that user inputs are treated as data rather than executable code.

Example in PHP with PDO:

php
Copy code
$stmt = $pdo->prepare(‘SELECT * FROM users WHERE username = :username AND password = :password’);
$stmt->execute([‘username’ => $username, ‘password’ => $password]);
Example in Python with SQLite:

python
Copy code
cursor.execute(‘SELECT * FROM users WHERE username = ? AND password = ?’, (username, password))
Use Stored Procedures:
Stored procedures can encapsulate SQL logic, though they should still be used with care to avoid SQL injection.

Example in MySQL:

sql
Copy code
DELIMITER //
CREATE PROCEDURE GetUser(IN userName VARCHAR(255), IN userPassword VARCHAR(255))
BEGIN
SELECT * FROM users WHERE username = userName AND password = userPassword;
END//
DELIMITER ;
Calling from PHP:

php
Copy code
$stmt = $pdo->prepare(‘CALL GetUser(:username, :password)’);
$stmt->execute([‘username’ => $username, ‘password’ => $password]);
Escape User Inputs:
Escaping user inputs is less preferred compared to prepared statements but is still a useful technique.

Example in PHP:

php
Copy code
$username = mysqli_real_escape_string($conn, $username);
$password = mysqli_real_escape_string($conn, $password);
$query = “SELECT * FROM users WHERE username = ‘$username’ AND password = ‘$password’”;
Use ORM Libraries:
Object-Relational Mapping (ORM) libraries handle SQL queries and help prevent SQL injection.

Example in Python with SQLAlchemy:

python
Copy code
user = session.query(User).filter_by(username=username, password=password).first()
Validate and Sanitize Input:
Ensure that inputs are validated for expected types and formats.

Example in PHP:

php
Copy code
if (filter_var($username, FILTER_SANITIZE_STRING) && filter_var($password, FILTER_SANITIZE_STRING)) {
// Proceed with query
}
Least Privilege Principle:
Ensure that the database user has the minimum permissions necessary to perform their tasks.

Example:
If your application only needs to read data, ensure the database user cannot perform write operations.

By combining these techniques, you can effectively mitigate SQL injection vulnerabilities in your applications.