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