Prepared Statements: Free PHP & MySQL Tutorial

Topics covered in this PHP & MySQL tutorial:

Selecting & filtering results, Preventing SQL injection attacks with prepared statements

Exercise Overview

In this exercise we are going to show how to select a certain row of data in a database. We’ll start by selecting all the records in the database that are equal to a certain email. The same concept could be used to select a particular ID. In addition we’ll show how to prevent SQL Injection attacks by using prepared statements.

Getting Started

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

   Take a moment to look over the code. It is the same type of SELECT statement we made in the last exercise. It selects all the rows in the users table and then outputs the information.

2. Before we can run the page, we have to include the connection script we made earlier in order to connect to the database (if you did not do the previous exercise do it now). At the top of the page inside the PHP tags, add the following bold code:

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

3. Save the page and then in a browser go to:

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

   You’ll see that the entire table is output.

4. Switch back to your code editor.

5. What if we wanted to only select rows that have a certain email? Change the SQL statement so it reads:

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

   This selects everything from the users table where the email is exactly equal to noble@nobledesktop.com.

   Notice that the email address is inside of quotes. This tells SQL that it is searching for a string. If we were selecting the id we wouldn’t need to wrap it in quotes (for example: WHERE id = 24).

6. Save the page and then in a browser go to:

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

   You’ll see that only one row is output.

7. Switch back to your code editor.

8. What if we wanted to let a user enter this email from a form? We would use the $_POST array and select on that. Change the SQL as shown in bold:

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

   This will now select on whatever the user enters into a form.

9. Save the page.

10. Open form.php from the sql-prepared folder. This is a form we already made that links to search-results-simple.php. Let’s preview it in a browser.

11. In a browser go to:

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

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

    You must enter it exactly.

13. Click Submit and you’ll see that it returns one row. If you don’t type it exactly, it won’t find anything (this isn’t a search; we’ll do that later) because we are looking for a row that has an email exactly equal to what we enter.

    Go ahead and try it with your own email that you entered into the database in the last exercise, too.

SQL Injection

We have everything working fine, but there is a problem. Because we are allowing user input directly into the SQL statement, we have opened ourselves up to a huge security risk. Let’s check it out.

1. In a browser go to:

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

2. In the Email field enter the following exactly:

   ' or '1' = '1
   

3. Click Submit. All the rows in the table will be displayed! What happened? We modified the SQL statement to read:

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

   This says select everything from the users table where the email is blank or where 1 equals 1. Because 1 is always equal to 1, it will return every row in the database! What if this was a login form? With a little bit of knowledge a hacker wouldn’t need to guess a password, they would just have to enter code similar to this and potentially gain access to your system!

   So how do we prevent this? There are a few different methods. One of the easiest is to wrap each variable in the function real_escape_string(). This will escape any quotes and prevent most types of SQL injection attacks. While this method works fine, it is still potentially vulnerable to some attacks (can you really escape every possible combination of characters a hacker might use?).

   A better method is to use prepared statements. A prepared statement separates the SQL query from the variables so that the server processes them separately. First the general query is sent to the database server, for example:

   SELECT * FROM users WHERE email = ?
   

   The ? is a placeholder. We then tell the database server that it should expect a string for this placeholder. Then the variable is sent separately. Because the server is sent the SQL statement beforehand it is impossible for a hacker to modify the SQL statement in any way. Any variable that is passed will just be processed as a string.

   Prepared statements have the added benefit of being potentially faster as well. If you are reusing the same query over and over again with only the variables changing, the database server doesn’t need to compile the SQL over and over, it just changes the variables it is searching for.

Prepared Statements

First let’s set the form action to point to the prepared statement page we are going to make.

1. Switch back to your code editor.

2. In form.php, around line 16, change the action as shown in bold:

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

3. Save the page.

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

5. At the top of the page add the include to connect to the database:

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

6. Next add the SQL statement, but with a ? placeholder for the variable. Instead of selecting all, we should select only the columns we want. This is a bit more work, but is faster, more secure, and is generally best practice. Add the bold code:

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

7. Now we have to initialize the statement:

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

   This initializes a statement object called $stmt. The $conn object is the connection that was set up in the include.

8. Then we need to prepare the statement:

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

9. The next step is to bind the parameters (variables) to the statement. We have to specify the type (string, integer, binary, or double) and the name of the variable. Add the bold code:

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

   The s stands for string. If we had two string parameters to bind it would look like: bind_param('ss',$firstVar,$secondVar)

   The different types that bind_param() accepts are:

   • s: String (any text)
   • i: Integer (any whole number)
   • d: Double (floating point number)
   • b: Binary (Binary data like an image, PDF, or other file)

10. Besides parameters, it’s also best to bind the results. This way we can give our output variables nice names like $id, $email, etc. Add the bold code:

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

    These variables must be in the same order as the SELECT statement.

11. Next we have to execute the statement. Add the bold code:

    $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 SQL to the database server to run.

12. It is best to add some error-checking code at this point to make sure things have run correctly. In a production server you may not want to display errors to the end user for security reasons, but for developing an application, having accurate error-reporting is invaluable. Add the bold code:

    $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;
        }
    

    If there is an error, display it. You can also use $stmt->errno to display the exact error code. Both are useful although a plain English error is a little easier to understand than “error 2013.”

13. Last we should store the result. Using store_result() is optional. The benefit of using it is that we can then access the number of rows like: $stmt->num_rows. Add the bold code:

    $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();
    

14. OK, that’s a prepared statement! To loop through and display the results use $stmt->fetch() to fetch the results. Around line 44, find the empty <tr> tags and wrap the following bold code:

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

15. Then, to display the results just use the variable we bound earlier. Add the following bold code:

    <?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. Save the page and then in a browser go to:

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

17. In the Email field enter: noble@nobledesktop.com

18. Click Submit and it should return one row.

    Now let’s double-check to see if this will repel our simple SQL injection attack.

19. Hit the back button.

20. In the Email field enter the following exactly:

    ' or '1' = '1
    

21. Click Submit. No rows will be returned!

    It may seem like a bit of extra work to use prepared statements, but the added security and speed are well worth it.

22. Switch back to your code editor.

23. Lastly, we need to add the number of rows. Make sure you are in search-results-prepared.php.

24. Between the <body> and the <table> tags, add the following bold code:

    <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>
    

25. Save the file and close any open files. We’re done for now.