Prepared Statements: Free PHP & MySQL Tutorial

Topics Covered in This PHP & MySQL Tutorial:

Advanced data selection and filtering techniques, plus enterprise-level security through SQL injection prevention using prepared statements

Exercise Overview

In this hands-on exercise, you'll master one of the most critical skills in database programming: securely selecting and filtering specific records from your database. We'll begin with fundamental row selection techniques, demonstrating how to query records based on specific criteria like email addresses or unique identifiers. More importantly, you'll learn to implement prepared statements—the industry standard for preventing SQL injection attacks that have compromised countless applications over the years.

SQL injection remains one of the most prevalent and dangerous web application vulnerabilities in 2026. By the end of this exercise, you'll understand not just how to write secure database queries, but why these security measures are absolutely essential for any production application.

Getting Started

1. Open search-results-simple.php from the SQL-prepared folder in the phpclass folder.

   Take a moment to examine the existing code structure. You'll notice it follows the same SELECT statement pattern from our previous exercise, retrieving all rows from the users table and displaying the complete dataset. This basic approach works for small datasets but lacks the precision and security needed for real-world applications.

2. Before executing this page, we need to establish our database connection using the script created in the previous exercise. If you haven't completed that exercise yet, please do so now—it's essential for this tutorial. At the top of the page within the PHP tags, add the following code:

   <?php 
   
      require_once('../inc/dbConnect.php');
   
      $SQL = "SELECT *
              FROM users 
              ";

3. Save your changes and navigate to the following URL in your browser:

   • Mac: localhost:8888/phpclass/SQL-prepared/search-results-simple.php
   • Windows: localhost/phpclass/SQL-prepared/search-results-simple.php

   You should see the complete user table displayed. This demonstrates our baseline functionality before we add filtering and security measures.

4. Return to your code editor to begin implementing targeted data selection.

5. Now we'll add precision to our query by filtering for specific email addresses. Modify the SQL statement to include a WHERE clause:

   $SQL = "SELECT *
           FROM users
           WHERE email = 'noble@nobledesktop.com'
           ";

   This WHERE clause transforms our query from a broad data dump into a precise search tool. The query now returns only records where the email field exactly matches noble@nobledesktop.com. Notice the single quotes around the email address—this tells MySQL we're searching for a string value. For numeric fields like ID numbers, quotes aren't necessary (example: WHERE id = 24).

6. Save the file and refresh your browser:

   • Mac: localhost:8888/phpclass/SQL-prepared/search-results-simple.php
   • Windows: localhost/phpclass/SQL-prepared/search-results-simple.php

   You should now see only a single row returned—the record matching our specific email criteria.

7. Switch back to your code editor to implement dynamic user input.

8. Static queries are useful for testing, but real applications need to handle dynamic user input. Let's modify our query to accept form data using PHP's $_POST superglobal:

   $SQL = "SELECT *
           FROM users
           WHERE email = '$_POST[email]'
           ";

   This change allows our query to search for whatever email address a user submits through a form. However—and this is crucial—this approach introduces a severe security vulnerability that we'll address shortly.

9. Save your changes.

10. Open form.php from the SQL-prepared folder. This pre-built form is configured to submit data to our search-results-simple.php page. Let's test our dynamic query functionality.

11. Navigate to the form in your browser:

    • Mac: localhost:8888/phpclass/SQL-prepared/form.php
    • Windows: localhost/phpclass/SQL-prepared/form.php

12. In the Email field, enter: noble@nobledesktop.com

    Precision is critical here—the email must be entered exactly as it appears in your database.

13. Click Submit to execute the query. You should see one matching record returned. This isn't a fuzzy search—it requires exact matches. Try entering your own email address that you added to the database in the previous exercise to verify the functionality.

    While this dynamic approach works, we've just opened a massive security hole in our application. Let's explore this vulnerability and learn how to close it properly.

Understanding SQL Injection Vulnerabilities

Our current implementation works perfectly for legitimate users, but it's dangerously vulnerable to SQL injection attacks—one of the most common and devastating security flaws in web applications. When you allow user input to be directly inserted into SQL statements, malicious users can manipulate your queries to access unauthorized data or even compromise your entire database.

Let's demonstrate this vulnerability firsthand so you understand the real-world implications.

1. Navigate back to the form in your browser:

   • Mac: localhost:8888/phpclass/SQL-prepared/form.php
   • Windows: localhost/phpclass/SQL-prepared/form.php

2. In the Email field, enter this malicious payload exactly:

   ' or '1' = '1

3. Click Submit and observe the results. You'll see every row in your database displayed! This happens because our vulnerable query now reads:

   SELECT *
   FROM users
   WHERE email = '' or '1' = '1'

   This modified query asks the database to return records where the email is blank OR where 1 equals 1. Since 1 always equals 1, this condition is always true, causing every record to be returned. In a real application, this could expose sensitive user data, financial records, or administrative accounts.

   Imagine if this were a login system—an attacker could potentially bypass authentication entirely without knowing any valid usernames or passwords. This is why SQL injection consistently ranks among the top web application security risks according to OWASP's annual threat assessment.

   So how do we defend against these attacks? There are several approaches, but the most robust and widely adopted solution is prepared statements. While older methods like real_escape_string() provide some protection by escaping dangerous characters, they can't defend against every possible attack vector that creative hackers might devise.

   Prepared statements work by completely separating the SQL logic from the user data. The database server first receives and compiles the query structure:

   SELECT * FROM users WHERE email = ?

   The ? serves as a placeholder for user data. Next, we specify the data type (string, integer, etc.) that this placeholder should accept. Finally, the actual user input is transmitted separately from the query structure. Because the SQL statement is already compiled when the user data arrives, there's no possibility for malicious input to alter the query's logic—it's simply processed as data.

   Beyond security, prepared statements offer performance benefits. When executing similar queries repeatedly with different parameters, the database server only needs to compile the SQL once, then reuse that compiled version with new data values.

Implementing Secure Prepared Statements

Now that you understand the security risks, let's implement the proper solution. We'll create a new page using prepared statements that maintains the same functionality while eliminating the SQL injection vulnerability.

First, let's configure our form to use the secure version of our search results page.

1. Return to your code editor.

2. In form.php, locate line 16 and update the form action attribute:

   <form name="form1" id="form1" method="post" action="search-results-prepared.php">

3. Save the file.

4. Open search-results-prepared.php from the SQL-prepared folder.

5. Begin by adding the database connection at the top of the page:

   <?php 
   
      require_once('../inc/dbConnect.php');
   
   ?>

6. Now we'll create our SQL statement using a parameter placeholder instead of direct variable insertion. Notice we're also following best practices by specifying only the columns we need rather than using SELECT *:

   require_once('../inc/dbConnect.php');
   
   $SQL = "SELECT id, firstName, lastName, email
          FROM users 
          WHERE email = ?
          ";

   This approach is more secure, performs better, and makes your code more maintainable. The ? placeholder will be safely replaced with user input during execution.

7. Initialize the prepared statement object:

   $SQL = "SELECT id, firstName, lastName, email
           FROM users 
           WHERE email = ?
           ";
   
   $stmt = $conn->stmt_init();

   This creates a statement object called $stmt using the database connection ($conn) established in our include file.

8. Prepare the statement by sending the SQL structure to the database server:

   $stmt = $conn->stmt_init();
   $stmt->prepare($SQL);

9. Bind the user input to our parameter placeholder, specifying the data type for additional security:

   $stmt = $conn->stmt_init();
   $stmt->prepare($SQL);
   $stmt->bind_param('s', $_POST['email']);

   The 's' indicates we're binding a string parameter. For multiple parameters, you'd list the types together: bind_param('ss', $firstVar, $secondVar) for two strings. The available parameter types are:

   • s: String (text data)
   • i: Integer (whole numbers)
   • d: Double (decimal numbers)
   • b: Binary (files, images, etc.)

10. Bind the result columns to PHP variables for cleaner code and easier maintenance:

    $stmt = $conn->stmt_init();
    $stmt->prepare($SQL);
    $stmt->bind_param('s', $_POST['email']);
    $stmt->bind_result($id, $firstName, $lastName, $email);

    These variable names must correspond to the column order in your SELECT statement.

11. Execute the prepared statement:

    $stmt = $conn->stmt_init();
    $stmt->prepare($SQL);
    $stmt->bind_param('s', $_POST['email']);
    $stmt->bind_result($id, $firstName, $lastName, $email);
    $stmt->execute();

    This sends the compiled query and bound parameters to the database for execution.

12. Add comprehensive error handling—essential for debugging during development and monitoring in production:

    $stmt = $conn->stmt_init();
    $stmt->prepare($SQL);
    $stmt->bind_param('s', $_POST['email']);
    $stmt->bind_result($id, $firstName, $lastName, $email);
    $stmt->execute();
    if ($stmt->error){
            echo 'There was an error: '. $stmt->error;
        }

    In production environments, you may want to log errors rather than display them to users for security reasons. During development, detailed error messages are invaluable for troubleshooting. You can also use $stmt->errno for specific error codes.

13. Store the result set to enable additional functionality like row counting:

    $stmt = $conn->stmt_init();
    $stmt->prepare($SQL);
    $stmt->bind_param('s', $_POST['email']);
    $stmt->bind_result($id, $firstName, $lastName, $email);
    $stmt->execute();
    if ($stmt->error){
            echo 'There was an error: '. $stmt->error;
        }
    $stmt->store_result();

    While store_result() is optional, it enables useful features like $stmt->num_rows for counting returned records.

14. Now let's display the results. Locate the empty <tr> tags around line 44 and add a while loop to iterate through the results:

    <?php while ( $stmt->fetch() ):?>
       <tr>
          <td></td>
          <td></td>
          <td></td>
          <td></td>
       </tr>
    <?php endwhile;?>

15. Populate the table cells with our bound variables:

    <?php while ( $stmt->fetch() ):?>
       <tr>
          <td><?php echo $id; ?></td>
          <td><?php echo $firstName; ?></td>
          <td><?php echo $lastName; ?></td>
          <td><?php echo $email; ?></td>
       </tr>
    <?php endwhile;?>

16. Test your secure implementation by navigating to:

    • Mac: localhost:8888/phpclass/SQL-prepared/form.php
    • Windows: localhost/phpclass/SQL-prepared/form.php

17. Enter a valid email address: noble@nobledesktop.com

18. Click Submit. The functionality should work identically to our previous version, returning the matching record.

    Now let's verify that our security measures are effective.

19. Use your browser's back button to return to the form.

20. Enter the same malicious payload as before:

    ' or '1' = '1

21. Click Submit. This time, no records are returned!

    The prepared statement treats the malicious input as literal text to search for, rather than executable SQL code. This demonstrates the fundamental security principle of prepared statements: complete separation between SQL logic and user data.

    While implementing prepared statements requires more code than simple string concatenation, the benefits—enhanced security, better performance, and more maintainable code—make this the standard approach for professional web development.

22. Return to your code editor.

23. Let's add a user-friendly feature to display the number of results found. In search-results-prepared.php, locate the area between the <body> and <table> tags and add:

    <body>
    <?php echo $stmt->num_rows ?>
    <table>
       <tr>
          <td><strong>ID</strong></td>
          <td><strong>First Name</strong></td>
          <td><strong>Last Name</strong></td>
          <td><strong>Email</strong></td>

24. Save all files and close your editor. You've successfully implemented secure database queries using prepared statements—a fundamental skill for any serious web developer.

    You now understand both the critical security vulnerabilities that plague many web applications and the professional-grade solutions used to prevent them. These prepared statement techniques form the foundation for secure database programming in virtually every modern web application.