Preparing SQL Statements

6:25 with Alena Holligan

Whenever a piece of code receives input from somewhere else, whether that's directly from a site visitor or from code dealing with another concern, you want to filter or sanitize that input. We’ll use the built in PDO functionality to filter input as well, it is called a prepared statement.

Teacher's Notes
Video Transcript
Downloads

Links

Documentation: PDO::prepare
Documentation: PDOStatement Class
Documentation: bindParam

Example Code

try {
    $results = $db->prepare(
          "SELECT title, category, img, format, year, 
          publisher, isbn, genre
          FROM Media
          JOIN Genres ON Media.genre_id=Genres.genre_id
          LEFT OUTER JOIN Books 
          ON Media.media_id = Books.media_id
          WHERE Media.media_id = ?"
    );
    $results->bindParam(1,$id,PDO::PARAM_INT);
    $results->execute();
} catch (Exception $e) {
    echo "bad query";
    echo $e;
}

$item = $results->fetch(PDO::FETCH_ASSOC);

0:00
Our filter input code sanitizes the id it receives from the web address,
0:04
which it then passes to the single item array function.
0:09
We then want our function to receive that as an argument and
0:13
use that ID in a query on the database.
0:16
It's a good idea to sanitize any input being used with the database.
0:21
It might seem redundant to sanitize the input in both places.
0:25
But you have to remember that functions might get called from multiple places.
0:30
Whenever a piece of code receives input from somewhere else,
0:34
whether that's directly from a site visitor or
0:36
from code dealing with another concern, you want to filter or sanitize that input.
0:43
In our function, we use a new method of the PDO class.
0:46
To make sure that our query is not subject to a SQL inject, the prepare method.
0:53
Go back into functions.php in your workspace and
0:56
scroll back to the single item array function that we've been working on.
1:00
Here's our query.
1:02
Right now, we put the ID directly into the WHERE clause.
1:05
Essentially, we want to do the same thing.
1:08
But in a way that's not vulnerable to a SQL injection.
1:12
That's what PDO's prepare method is for.
1:15
Instead of running the query at this point, let's call the prepare method.
1:19
We change query to prepare.
1:22
You pass the prepare method a query just like we did for a query method.
1:27
But this time it doesn't actually run it yet.
1:30
Now, in place of the ID we're going to put a placeholder.
1:34
You can do this in a couple of ways and I encourage you to check out the prepare
1:38
methods documentation that I’ve linked to in the teacher's notes.
1:42
We're going to do this with a question mark.
1:44
This is considered an unnamed placeholder.
1:47
The results variable is still a PDO statement object, like it was before, but
1:52
the statement hasn't been run yet.
1:55
There is no result set to fetch.
1:57
Before we run it, we want our ID to be used where the placeholder is.
2:02
We do that by calling a method on the results object.
2:06
The method is called bindParam.
2:10
This is short for bind parameter and it can be used to connect a variable or
2:15
parameter to one of the placeholders in the SQL statement.
2:19
This method receives two arguments and an optional data type.
2:23
The first argument is a reference to the placeholder.
2:27
With unnamed placeholders, you specify the number of the placeholder like this.
2:33
In our case, that's one because we want to bind the first parameter.
2:37
We only have one question mark.
2:39
So there's actually only one we can bind.
2:42
The second argument is a reference to the variable that you want to bind
2:45
to that placeholder.
2:47
In our case, id.
2:49
This line of code will replace the first question mark in our query
2:53
with a value from the id variable in a way that protects it from a SQL injection.
2:57
Just like when we filtered input,
2:59
we can also specify the data type of this parameter, as well.
3:04
Because we want this to be the number of our item media id,
3:12
we'll use PDO::PARAM_INT.
3:16
I've added a link in the Teacher's Notes to the manual for
3:19
prepared statements with PDO.
3:22
After we have our one parameter bound,
3:24
we can run the query by calling the execute method.
3:29
Our result object now has a result set inside of it,
3:32
just like it did when we ran the query method itself.
3:36
So we can use the fetch method to retrieve it.
3:39
Just to recap.
3:40
This line here creates a PDO statement object with the SQL query we want to run.
3:46
It contains a question mark instead of the id.
3:49
This line, here, binds our id variable to the first question mark.
3:53
This line, here,
3:55
executes the query which loads the results set into our results object.
4:01
This line of code then calls the fetch method
4:04
to retrieve the item information for the one product that matches the ID.
4:09
It loads it into an item variable and
4:11
then we can return that back to the function call.
4:15
One thing to note about this fetch method, if there is no item that matches the ID,
4:19
then our result object will contain no records.
4:23
If we call the fed method in that case, It will return a boolean-false.
4:28
This is exactly what we want.
4:29
The item variable will contain an array of item-details if an item is found.
4:35
Or it will contain a boolean-false.
4:38
Let's refresh the page for item 16.
4:41
As you can see, our function will return an array of item details corresponding to
4:46
the item number 16, the book 7 Habits of Highly Effective People.
4:51
If we change that ID to 27, [BLANK AUDIO],
4:56
we'll get a different set of item details for the movie Frozen.
5:01
Let's see what happens when we change this to an invalid ID.
5:06
1001.
5:09
Our code ran the query with the ID of 1001 and
5:12
got no results back because no item in our database has that ID.
5:17
Then, when the fetch method was called on the result set,
5:21
instead of returning in array of item details, every turn a boolean-false.
5:26
It showing a mostly empty page instead of redirecting.
5:30
Let's go back to the details page and modify or redirect conditional.
5:35
This conditional is checking if the item value is not set.
5:39
And a false value is still set.
5:41
So instead of using not is set,
5:43
we're going to use another favorite of mine, empty.
5:47
if (empty($item)) then we'll redirect.
5:53
This checks if our item variable is not set or
5:57
if it is set to an empty string or if it is set to a false value.
6:03
Now, the code will also redirect to the full catalog page
6:06
if our item value is false.
6:09
From here, we can click on Beethoven and see the details of that item.
6:15
We have the details pages working again, at least for
6:17
the data in the media, genres, and books tables.
6:20
The last thing we'll need to tackle is the people associated with those items.

You need to sign up for Treehouse in order to download course files.

Integrating PHP with Databases

Preparing SQL Statements

Links

Example Code

Sign in