Lecture Notes On Class 17: Working with Databases - Part 3

Lecture Notes On Class 17: 

Working with Databases - Part 3

Objective:

• Understand and use prepared statements to enhance security.
• Prevent SQL injection attacks.

Outcome:

Students will be able to use prepared statements to securely interact with the database and safeguard against SQL injection.

---

Introduction

In the previous classes, we discussed the basics of databases and how to interact with them using SQL. In this class, we will delve deeper into enhancing security when working with databases, focusing on prepared statements and the prevention of SQL injection attacks.

What Are Prepared Statements?

Prepared statements are a feature of many database management systems that allow you to execute the same SQL statement repeatedly with high efficiency and security. Instead of embedding user inputs directly into SQL queries, prepared statements separate the SQL logic from the data, which helps in preventing SQL injection attacks.

Benefits of Using Prepared Statements

1. Security Against SQL Injection: By using placeholders instead of directly including user input in the SQL query, prepared statements significantly reduce the risk of SQL injection attacks.
2. Improved Performance: When executing the same statement multiple times, the database can optimize the execution plan, leading to better performance.
3. Easier Maintenance: Prepared statements can make your code cleaner and more readable.

Understanding SQL Injection

SQL injection is a malicious technique where an attacker can execute arbitrary SQL code on your database by injecting it through user input fields. For example, consider the following SQL query:

sql

Copy code

SELECT * FROM users WHERE username = '$username' AND password = '$password';

If an attacker inputs admin' OR '1'='1 as the username, the query will become:

sql

Copy code

SELECT * FROM users WHERE username = 'admin' OR '1'='1' AND password = '$password';

This query will always return true, allowing unauthorized access to the system. Prepared statements help to prevent this by treating user input as data, not executable code.

How Prepared Statements Work

Using PHP with MySQLi

In this section, we will demonstrate how to use prepared statements in PHP with MySQLi.

Step 1: Establish a Database Connection

First, you need to connect to your MySQL database using MySQLi.

php

Copy code

$servername = "localhost";

$username = "username";

$password = "password";

$dbname = "database";

 

// Create connection

$conn = new mysqli($servername, $username, $password, $dbname);

 

// Check connection

if ($conn->connect_error) {

    die("Connection failed: " . $conn->connect_error);

}

Step 2: Prepare a SQL Statement

Next, prepare the SQL statement with placeholders. The placeholders (?) will be replaced with actual values later.

php

Copy code

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

Step 3: Bind Parameters

Now, bind the parameters to the prepared statement. The bind_param method takes two arguments: the type of the parameters (string, integer, etc.) and the actual values.

php

Copy code

$username = $_POST['username'];

$password = $_POST['password'];

 

$stmt->bind_param("ss", $username, $password); // "ss" means both are strings

Step 4: Execute the Statement

Execute the prepared statement.

php

Copy code

$stmt->execute();

Step 5: Fetch the Results

After executing the statement, you can fetch the results as needed.

php

Copy code

$result = $stmt->get_result();

 

if ($result->num_rows > 0) {

    // Output data of each row

    while ($row = $result->fetch_assoc()) {

        echo "id: " . $row["id"] . " - Name: " . $row["username"];

    }

} else {

    echo "0 results";

}

Step 6: Close the Statement and Connection

Finally, close the statement and the connection to the database.

php

Copy code

$stmt->close();

$conn->close();

Prepared Statements with PDO

You can also use PHP Data Objects (PDO) for prepared statements, which is a more flexible option that works with multiple database types.

Step 1: Create a Database Connection

php

Copy code

try {

    $pdo = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);

    // Set the PDO error mode to exception

    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

} catch (PDOException $e) {

    echo "Connection failed: " . $e->getMessage();

}

Step 2: Prepare the SQL Statement

php

Copy code

$stmt = $pdo->prepare("SELECT * FROM users WHERE username = :username AND password = :password");

Step 3: Bind Parameters

php

Copy code

$stmt->bindParam(':username', $username);

$stmt->bindParam(':password', $password);

Step 4: Execute and Fetch Results

php

Copy code

$stmt->execute();

$result = $stmt->fetchAll();

 

foreach ($result as $row) {

    echo "id: " . $row["id"] . " - Name: " . $row["username"];

}

Step 5: Close the Connection

With PDO, you don't explicitly close the connection; it will close automatically when the script ends.

Conclusion

In this class, we learned about prepared statements and their importance in preventing SQL injection attacks. By using prepared statements, you can ensure a secure interaction with the database, safeguarding user data and enhancing the overall integrity of your applications.

Assignment

1. Practice Exercise: Create a simple PHP application that uses prepared statements to perform basic CRUD operations (Create, Read, Update, Delete) on a users table.
2. Reflection: Write a short essay on how prepared statements differ from traditional SQL queries in terms of security and performance.

---