Assignments On Class 17:
Working with Databases - Part 3
By understanding and implementing prepared statements, you will be better equipped to handle user inputs safely and effectively in your applications.
Here are some assignments related to prepared statements and SQL injection prevention, complete with solutions and detailed explanations.
Assignment 1: Using Prepared Statements for User Authentication
Problem Statement:
Create a PHP script that allows users to log in using prepared statements. Use a MySQL database with a table named users that has the following fields:
- id (int, primary key, auto-increment)
- username (varchar)
- password (varchar)
- Establish a database connection.
- Create a prepared statement to select a user by username and password.
- Bind the parameters and execute the statement.
- Fetch the result and display a message indicating whether the login was successful or not.
Solution:
php
Copy code
<?php
// Database credentials
$servername = "localhost";
$username = "your_username";
$password = "your_password";
$dbname = "your_database";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// Get user input
$input_username = $_POST['username'];
$input_password = $_POST['password'];
// Prepare a SQL statement
$stmt = $conn->prepare("SELECT * FROM users WHERE username = ? AND password = ?");
$stmt->bind_param("ss", $input_username, $input_password);
// Execute the statement
$stmt->execute();
// Get the result
$result = $stmt->get_result();
if ($result->num_rows > 0) {
echo "Login successful!";
} else {
echo "Invalid username or password.";
}
// Close the statement and connection
$stmt->close();
$conn->close();
?>
Explanation:
- Database Connection: The script connects to the MySQL database using the MySQLi extension.
- Prepared Statement: It prepares a SQL statement with placeholders (?) for user input to prevent SQL injection.
- Binding Parameters: The bind_param method binds the input values to the placeholders, ensuring they are treated as data.
- Execution: The statement is executed, and the result is fetched.
- Result Handling: If a user record is found, it indicates successful login; otherwise, it shows an error message.
Assignment 2: Inserting User Data with Prepared Statements
Problem Statement:
Create a PHP script to register a new user by inserting their data into the users table using prepared statements. The user should provide a username and password.
- Establish a database connection.
- Create a prepared statement to insert the username and password into the users table.
- Bind the parameters and execute the statement.
- Display a message indicating whether the registration was successful.
Solution:
php
Copy code
<?php
// Database credentials
$servername = "localhost";
$username = "your_username";
$password = "your_password";
$dbname = "your_database";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// Get user input
$new_username = $_POST['username'];
$new_password = $_POST['password'];
// Prepare a SQL statement
$stmt = $conn->prepare("INSERT INTO users (username, password) VALUES (?, ?)");
$stmt->bind_param("ss", $new_username, $new_password);
// Execute the statement
if ($stmt->execute()) {
echo "Registration successful!";
} else {
echo "Error: " . $stmt->error;
}
// Close the statement and connection
$stmt->close();
$conn->close();
?>
Explanation:
- Database Connection: Similar to the previous assignment, a connection is established to the MySQL database.
- Prepared Statement for Insertion: The SQL statement is prepared to insert a new user into the database.
- Binding Parameters: The username and password are bound to the prepared statement.
- Execution and Feedback: After executing the statement, it checks if the insertion was successful and provides feedback to the user.
Assignment 3: Updating User Password Securely
Problem Statement:
Write a PHP script to update a user’s password in the users table using prepared statements. The user will provide their username and new password.
- Establish a database connection.
- Create a prepared statement to update the password for the specified username.
- Bind the parameters and execute the statement.
- Display a message indicating whether the update was successful.
Solution:
php
Copy code
<?php
// Database credentials
$servername = "localhost";
$username = "your_username";
$password = "your_password";
$dbname = "your_database";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// Get user input
$update_username = $_POST['username'];
$new_password = $_POST['new_password'];
// Prepare a SQL statement
$stmt = $conn->prepare("UPDATE users SET password = ? WHERE username = ?");
$stmt->bind_param("ss", $new_password, $update_username);
// Execute the statement
if ($stmt->execute()) {
echo "Password updated successfully!";
} else {
echo "Error updating password: " . $stmt->error;
}
// Close the statement and connection
$stmt->close();
$conn->close();
?>
Explanation:
- Database Connection: The script establishes a connection to the MySQL database.
- Prepared Statement for Update: A prepared statement is created to update the password for the specified username.
- Parameter Binding: The new password and username are bound to the statement.
- Execution and Feedback: The statement is executed, and feedback is provided to the user regarding the success of the operation.
Assignment 4: Deleting a User Record
Problem Statement:
Create a PHP script to delete a user from the users table using prepared statements. The user will provide their username.
- Establish a database connection.
- Create a prepared statement to delete the specified user by username.
- Bind the parameters and execute the statement.
- Display a message indicating whether the deletion was successful.
Solution:
php
Copy code
<?php
// Database credentials
$servername = "localhost";
$username = "your_username";
$password = "your_password";
$dbname = "your_database";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// Get user input
$delete_username = $_POST['username'];
// Prepare a SQL statement
$stmt = $conn->prepare("DELETE FROM users WHERE username = ?");
$stmt->bind_param("s", $delete_username);
// Execute the statement
if ($stmt->execute()) {
echo "User deleted successfully!";
} else {
echo "Error deleting user: " . $stmt->error;
}
// Close the statement and connection
$stmt->close();
$conn->close();
?>
Explanation:
- Database Connection: The script establishes a connection to the MySQL database.
- Prepared Statement for Deletion: A prepared statement is created to delete a user record based on the provided username.
- Parameter Binding: The username is bound to the statement.
- Execution and Feedback: The statement is executed, and the user is informed whether the deletion was successful.
Conclusion
These assignments help reinforce the concepts of using prepared statements for secure database interactions. Each script demonstrates how to prevent SQL injection by treating user inputs safely and separately from the SQL code. Students should practice these examples and modify them to enhance their understanding of prepared statements and secure coding practices.
4o mini
You said:
give some more
ChatGPT said:
ChatGPT
Here are additional assignments related to prepared statements and SQL injection prevention, along with their solutions and explanations.
Assignment 5: Fetching User Data Securely
Problem Statement:
Write a PHP script that retrieves and displays user details (username and registration date) from the users table based on a given username using prepared statements.
- Establish a database connection.
- Create a prepared statement to select the username and registration date.
- Bind the username parameter, execute the statement, and fetch the result.
- Display the user details or an appropriate message if the user is not found.
Solution:
php
Copy code
<?php
// Database credentials
$servername = "localhost";
$username = "your_username";
$password = "your_password";
$dbname = "your_database";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// Get user input
$fetch_username = $_POST['username'];
// Prepare a SQL statement
$stmt = $conn->prepare("SELECT username, created_at FROM users WHERE username = ?");
$stmt->bind_param("s", $fetch_username);
// Execute the statement
$stmt->execute();
// Get the result
$result = $stmt->get_result();
// Fetch data
if ($row = $result->fetch_assoc()) {
echo "Username: " . $row['username'] . "<br>";
echo "Registration Date: " . $row['created_at'];
} else {
echo "No user found with the username: " . $fetch_username;
}
// Close the statement and connection
$stmt->close();
$conn->close();
?>
Explanation:
- Database Connection: Connects to the MySQL database.
- Prepared Statement: Prepares a statement to fetch user details by username.
- Parameter Binding and Execution: The username is bound to the statement and executed.
- Result Fetching: If a record is found, the username and registration date are displayed; otherwise, a message indicating that no user was found is shown.
Assignment 6: Fetching All Users Securely
Problem Statement:
Create a PHP script that retrieves all users from the users table and displays their usernames and registration dates using prepared statements.
- Establish a database connection.
- Create a prepared statement to select all users.
- Execute the statement and fetch the results.
- Display the usernames and registration dates in a list format.
Solution:
php
Copy code
<?php
// Database credentials
$servername = "localhost";
$username = "your_username";
$password = "your_password";
$dbname = "your_database";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// Prepare a SQL statement
$stmt = $conn->prepare("SELECT username, created_at FROM users");
// Execute the statement
$stmt->execute();
// Get the result
$result = $stmt->get_result();
// Display the users
echo "<h2>Users List:</h2><ul>";
while ($row = $result->fetch_assoc()) {
echo "<li>Username: " . $row['username'] . ", Registration Date: " . $row['created_at'] . "</li>";
}
echo "</ul>";
// Close the statement and connection
$stmt->close();
$conn->close();
?>
Explanation:
- Database Connection: Connects to the MySQL database.
- Prepared Statement: A statement is prepared to select all user details.
- Execution and Result Fetching: The statement is executed, and results are fetched in a loop to display all users in an unordered list.
Assignment 7: Updating Multiple User Records
Problem Statement:
Create a PHP script that allows an admin to update the password of multiple users at once. The admin will provide an array of usernames and the new password.
- Establish a database connection.
- Loop through the array of usernames and create a prepared statement to update the password for each user.
- Bind the parameters and execute the statement.
- Display a message indicating the number of records updated.
Solution:
php
Copy code
<?php
// Database credentials
$servername = "localhost";
$username = "your_username";
$password = "your_password";
$dbname = "your_database";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// Array of usernames
$usernames = ["user1", "user2", "user3"];
$new_password = "new_secure_password";
// Prepare a SQL statement
$stmt = $conn->prepare("UPDATE users SET password = ? WHERE username = ?");
$stmt->bind_param("ss", $new_password, $username);
// Execute the statement for each user
$updated_count = 0;
foreach ($usernames as $username) {
if ($stmt->execute()) {
$updated_count++;
}
}
echo "$updated_count user(s) updated successfully.";
// Close the statement and connection
$stmt->close();
$conn->close();
?>
Explanation:
- Database Connection: Connects to the MySQL database.
- Looping through Usernames: Each username is processed in a loop.
- Prepared Statement: A prepared statement is used to update the password for each user.
- Count of Updates: A counter keeps track of how many users were successfully updated.
Assignment 8: Securely Handling Passwords with Hashing
Problem Statement:
Modify the registration script to securely store passwords using PHP's password hashing functions. The user will provide a username and password, and the script will hash the password before storing it in the database.
- Establish a database connection.
- Hash the password using password_hash.
- Create a prepared statement to insert the username and hashed password.
- Bind the parameters and execute the statement.
- Display a message indicating whether the registration was successful.
Solution:
php
Copy code
<?php
// Database credentials
$servername = "localhost";
$username = "your_username";
$password = "your_password";
$dbname = "your_database";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// Get user input
$new_username = $_POST['username'];
$plain_password = $_POST['password'];
// Hash the password
$hashed_password = password_hash($plain_password, PASSWORD_DEFAULT);
// Prepare a SQL statement
$stmt = $conn->prepare("INSERT INTO users (username, password) VALUES (?, ?)");
$stmt->bind_param("ss", $new_username, $hashed_password);
// Execute the statement
if ($stmt->execute()) {
echo "Registration successful!";
} else {
echo "Error: " . $stmt->error;
}
// Close the statement and connection
$stmt->close();
$conn->close();
?>
Explanation:
- Password Hashing: Uses PHP's password_hash function to hash the user-provided password before storing it.
- Database Insertion: The hashed password is inserted into the database using a prepared statement.
- Security: This method enhances security by ensuring that raw passwords are not stored directly in the database.
Assignment 9: Securing Data Retrieval with Authorization
Problem Statement:
Write a PHP script that retrieves user data only if the user is logged in. Use sessions to manage user authentication. If the user is logged in, fetch their details from the users table using prepared statements.
- Start the session and check if the user is logged in.
- Establish a database connection.
- Create a prepared statement to select the user details.
- Bind the session username and fetch the results.
- Display the user details or an appropriate message if not logged in.
Solution:
php
Copy code
<?php
session_start();
// Check if the user is logged in
if (!isset($_SESSION['username'])) {
echo "You need to log in first.";
exit();
}
// Database credentials
$servername = "localhost";
$username = "your_username";
$password = "your_password";
$dbname = "your_database";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// Prepare a SQL statement
$stmt = $conn->prepare("SELECT username, created_at FROM users WHERE username = ?");
$stmt->bind_param("s", $_SESSION['username']);
// Execute the statement
$stmt->execute();
// Get the result
$result = $stmt->get_result();
// Fetch data
if ($row = $result->fetch_assoc()) {
echo "Username: " . $row['username'] . "<br>";
echo "Registration Date: " . $row['created_at'];
} else {
echo "No user found.";
}
// Close the statement and connection
$stmt->close();
$conn->close();
?>
Explanation:
- Session Management: The script starts a session and checks if the user is logged in by checking for a session variable.
- Prepared Statement for Data Retrieval: A prepared statement retrieves user details based on the session username.
- Data Display: If the user is found, their details are displayed; otherwise, an error message is shown.
Assignment 10: Preventing SQL Injection in User Input
Problem Statement:
Create a PHP script that sanitizes user input before performing a search operation on the users table. The user will input a username, and the script will return matching records.
- Establish a database connection.
- Retrieve user input and sanitize it.
- Create a prepared statement to search for users based on the sanitized input.
- Bind the input and execute the statement.
- Display matching users or an appropriate message.
Solution:
php
Copy code
<?php
// Database credentials
$servername = "localhost";
$username = "your_username";
$password = "your_password";
$dbname = "your_database";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// Get user input and sanitize
$search_username = htmlspecialchars(trim($_POST['username']));
// Prepare a SQL statement
$stmt = $conn->prepare("SELECT username FROM users WHERE username LIKE ?");
$search_param = '%' . $search_username . '%';
$stmt->bind_param("s", $search_param);
// Execute the statement
$stmt->execute();
// Get the result
$result = $stmt->get_result();
// Fetch data
if ($result->num_rows > 0) {
echo "<h2>Matching Users:</h2><ul>";
while ($row = $result->fetch_assoc()) {
echo "<li>" . htmlspecialchars($row['username']) . "</li>";
}
echo "</ul>";
} else {
echo "No matching users found.";
}
// Close the statement and connection
$stmt->close();
$conn->close();
?>
Explanation:
- Input Sanitization: User input is sanitized using htmlspecialchars and trim to prevent XSS and remove unnecessary spaces.
- Prepared Statement for Safe Search: A prepared statement is created to search for users safely.
- Result Display: Matching usernames are displayed in an unordered list.