How to sanitize user input in PHP to prevent SQL injection attacks?

@filiberto 

To sanitize user input in PHP and prevent SQL injection attacks, you can use prepared statements and parameterized queries. Here's an example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

// Establish a database connection
$db = new PDO("mysql:host=localhost;dbname=mydatabase", "username", "password");

// Prepare a SQL statement with a parameterized query
$stmt = $db->prepare("SELECT * FROM users WHERE username = :username");

// Sanitize and bind the user input to the prepared statement
$username = filter_input(INPUT_POST, 'username', FILTER_SANITIZE_STRING);
$stmt->bindParam(':username', $username);

// Execute the prepared statement
$stmt->execute();

// Fetch the results
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);

In the example above, we use the PDO (PHP Data Objects) library to establish a database connection and prepare a SQL statement with a parameterized query. Then, we sanitize the user input using the filter_input function with the FILTER_SANITIZE_STRING filter to remove any potential SQL injection attacks. Finally, we bind the sanitized user input to the prepared statement using the bindParam function and execute the statement.

By using prepared statements and parameterized queries, we can prevent SQL injection attacks because the user input is treated as a parameter rather than a part of the SQL statement. This means that any potential SQL injection attacks will not affect the structure of the SQL statement and will not be executed by the database.

@filiberto 

SQL injection attacks occur when an attacker attempts to execute malicious SQL statements in your application, typically by manipulating user input. To prevent SQL injection attacks in PHP, you can use the following techniques to sanitize user input:

1. Prepared Statements: Prepared statements can be used to parameterize your SQL queries. This means that any user input is treated as a parameter and not as part of the SQL statement itself. This prevents SQL injection attacks by ensuring that any user input is never directly included in the SQL query.

Here is an example of a prepared statement in PHP:

1
2

$stmt = $pdo->prepare('SELECT * FROM users WHERE username = ?');
$stmt->execute([$username]);

1. Input Validation: Validate user input to ensure that it meets your expected format and structure. This can include checking the length, format, and data type of the input. For example, if you expect an email address, you can use PHP's filter_var() function to validate the email address format.

1
2
3
4
5

if (filter_var($email, FILTER_VALIDATE_EMAIL)) {
    // Email is valid
} else {
    // Email is not valid
}

1. Sanitization: Sanitize user input by removing any unwanted characters, such as quotes or semicolons. This can be done using the mysqli_real_escape_string() function in PHP, which escapes special characters in a string.

1

$username = mysqli_real_escape_string($conn, $_POST['username']);

1. Use a Database Abstraction Layer: A database abstraction layer is a software layer that provides a uniform interface for interacting with various database management systems. This can help to prevent SQL injection attacks by abstracting away the specific details of the database system being used.

By using these techniques, you can help to prevent SQL injection attacks in your PHP applications.