**Course Title:** Modern PHP Development: Best Practices and Advanced Techniques **Section Title:** Working with Databases (MySQL/MariaDB) **Topic:** Prepared statements and parameterized queries to prevent SQL injection. **What are SQL Injection Attacks?** SQL injection attacks occur when malicious input from a user is executed by a web application, causing unexpected and potentially destructive database operations. These attacks can lead to unauthorized data access, modification, or deletion, and even allow attackers to take control of the entire database. **Why are Prepared Statements Important?** Prepared statements, also known as parameterized queries, are a crucial security measure to prevent SQL injection attacks. By separating SQL code from user input, prepared statements ensure that the SQL code is executed as intended, without allowing malicious input to alter the query. **How to Use Prepared Statements in PHP** To use prepared statements in PHP, you'll need to use the PDO (PHP Data Objects) extension. Here's an example of how to use a prepared statement to select users from a database: ```php $dsn = 'mysql:host=localhost;dbname=mydatabase'; $username = 'myusername'; $password = 'mypassword'; try { $pdo = new PDO($dsn, $username, $password); $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); } catch (PDOException $e) { echo 'Connection failed: ' . $e->getMessage(); } $stmt = $pdo->prepare('SELECT * FROM users WHERE username = :username'); $stmt->bindParam(':username', $username); $stmt->execute(); $users = $stmt->fetchAll(); foreach ($users as $user) { echo $user['username'] . "\n"; } ``` In this example, the `prepare()` method is used to create a prepared statement, and the `bindParam()` method is used to bind a parameter value to the `:username` placeholder. **Key Concepts** * **Parameterized queries**: Separate SQL code from user input to prevent SQL injection attacks. * **Prepared statements**: Use the `prepare()` method to create a prepared statement, and bind parameter values using the `bindParam()` method. * **Bind parameters**: Use named or positional parameters to bind values to placeholders in the SQL query. **Best Practices for Using Prepared Statements** * Always use prepared statements when executing SQL queries that include user input. * Use parameterized queries to separate SQL code from user input. * Bind parameter values using the `bindParam()` method to prevent SQL injection attacks. **Example Use Cases** * **Authenticating users**: Use a prepared statement to select a user from the database based on their username and password. * **Inserting data**: Use a prepared statement to insert data into a database table, binding parameter values to prevent SQL injection attacks. * **Updating data**: Use a prepared statement to update data in a database table, binding parameter values to prevent SQL injection attacks. **Common Mistakes to Avoid** * **Concatenating user input into SQL queries**: Avoid concatenating user input into SQL queries, as this can lead to SQL injection attacks. * **Not using prepared statements**: Always use prepared statements when executing SQL queries that include user input. **References** * [PDO Prepared Statements](https://www.php.net/manual/en/pdo.prepared-statements.php) * [SQL Injection Prevention](https://www.owasp.org/index.php/SQL_Injection_Prevention_Cheat_Sheet) **Exercise** Create a PHP script that uses a prepared statement to select a user from a database based on their username and password. Use the `bindParam()` method to bind parameter values to the SQL query. **Leave a comment or ask for help** If you have any questions or need help with the exercise, please leave a comment below. This course material is designed to provide a comprehensive understanding of prepared statements and parameterized queries in PHP. By following the examples and exercises provided, you should now be able to use prepared statements to prevent SQL injection attacks in your own PHP applications. In the next topic, we will cover **Working with relational data and database design in PHP**.

$dsn = 'mysql:host=localhost;dbname=mydatabase';
$username = 'myusername';
$password = 'mypassword';

try {
    $pdo = new PDO($dsn, $username, $password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
    echo 'Connection failed: ' . $e->getMessage();
}

$stmt = $pdo->prepare('SELECT * FROM users WHERE username = :username');
$stmt->bindParam(':username', $username);
$stmt->execute();

$users = $stmt->fetchAll();

foreach ($users as $user) {
    echo $user['username'] . "\n";
}