Skip to content

Student Listing - Adding Parameterized Query Function

We need to make sure all our inputs we use as queries into our database are sanitized. Parameterized queries were discussed in detail in the Creating Secure Web Applications chapter. Here is a function that we can call that will parameterize all of our queries (this function is discussed in more detail in the Adding User Logins chapter).

NOTE: You will want to go back and update all the queries we have created so far in the Student Listing application to use parameterize queries, especially when the input originates from a query parameter or field entry from a form.

For example, in studentdetails.php after including queryutils.php you would need to rewrite this query from:

1
2
3
4
5
$query = "SELECT * FROM studentListing WHERE id = $id";

$result = mysqli_query($dbc, $query)
       or trigger_error('Error querying database studentListing',
       E_USER_ERROR);
to this using the parameterizedQuery() function:

1
2
3
4
$query = "SELECT * FROM studentListing WHERE id = ?";

$result = parameterizedQuery($dbc, $query, 'i', $id)
        or trigger_error(mysqli_error($dbc), E_USER_ERROR);
Likewise you would need to rewrite two queries in editstudent.php with the second one that updates the entry in the studentListing table of the Student database the most interesting because of the number and different data types of parameters. So you would rewrite this query from:

1
2
3
4
5
6
7
8
$query = "UPDATE studentListing SET first_name = '$student_first_name', "
        . " last_name = '$student_last_name', email = '$student_email', "
        . " image_file = '$student_image_file' WHERE id = $id_to_update";

mysqli_query($dbc, $query)
    or trigger_error(
        'Error querying database studentListing: Failed to update student listing',
                            E_USER_ERROR);
to this using the parameterizedQuery() function:

1
2
3
4
5
6
$query = "UPDATE studentListing SET first_name = ?, last_name = ?, "
        . "email = ?, image_file = ? WHERE id = ?";

$results = parameterizedQuery($dbc, $query, 'ssssi', $student_first_name,
        $student_last_name, $student_email, $student_image_file, $id_to_update)
        or trigger_error(mysqli_error($dbc), E_USER_ERROR);

Create a new script called queryutils.php and add the following function code:

queryutils.php - Student Listing: Complete Code Listing 

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
<?php
/**
 * Purpose:        Parameterizes a database query
 *
 * Description:    Parameterizes an SQL query given a database connection, 
 *                 a query string, a data types string, and a variable number 
 *                 of parameters to be used in the query. If the query is 
 *                 successful, the database results object will be returned 
 *                 (or TRUE if no results set and the query was successful), 
 *                 otherwise FALSE is returned  and the connection has to be
 *                 queried for the last error.
 *
 * @param  $dbc              database connection
 * @param  $sql_query        SQL statement
 * @param  $data_types       string containing one character representing the data type for each parameter
 * @param  $query_parameters variable list of parameters representing each query parameter
 * @return string            Database results set, false if there is a database error, or true if successful.
 */
function parameterizedQuery($dbc, $sql_query, $data_types, ...$query_parameters)
{
    $ret_val = false;  // Assume failure

    if ($stmt = mysqli_prepare($dbc, $sql_query))
    {
        if (mysqli_stmt_bind_param($stmt, $data_types, ...$query_parameters)
                && mysqli_stmt_execute($stmt))
        {
            $ret_val = mysqli_stmt_get_result($stmt);

            if (!mysqli_errno($dbc) && !$ret_val)
            {
                $ret_val = true;
            }
        }       
    }
    return $ret_val;
}