How to implement data validation and sanitization with PDO in PHP?

When working with databases, it is crucial to validate and sanitize user input to prevent SQL injection attacks. PDO (PHP Data Object) is a PHP extension that provides a consistent interface for accessing databases. Here's how you can implement data validation and sanitization with PDO in PHP:

Establish a database connection: Use PDO to connect to your database by specifying the database driver, host, database name, username, and password.

$db = new PDO('mysql:host=localhost;dbname=database_name', 'username', 'password');

Prepare a database query: Use PDO's prepare method to create a prepared statement. Prepared statements help prevent SQL injection attacks by separating the query and the data being inserted.

$query = $db->prepare('SELECT * FROM users WHERE username = :username');

Validate user input: Before executing the query, validate the user input to ensure it meets certain criteria. For example, you can check if a string is not empty or meets a specific length requirement.

if (strlen($username) < 6) {
    // User input does not meet the validation criteria
    // Handle the validation error
}

Sanitize user input: Sanitization involves removing any unwanted characters or escaping special characters to prevent SQL injection attacks. You can use PDO's quote method to sanitize data.

$cleanedUsername = $db->quote($username);

Bind parameters: Bind the sanitized user input as parameters to the prepared statement using PDO's bindParam method. This step is crucial as it avoids SQL injection attacks.

$query->bindParam(':username', $cleanedUsername);

Execute the query: Finally, execute the prepared statement to retrieve the desired data.

$query->execute();

Fetch results: Use PDO's fetch method to retrieve the results of the executed query.

$result = $query->fetch(PDO::FETCH_ASSOC);

By incorporating data validation and sanitization techniques, you can enhance the security of your PHP application while interacting with a database using PDO.