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
So the key here is to never ever (ever) trust user input. Lets do some validation on our user input and secure our queries.
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
So the key here is to never, ever, ever
trust user input.
0:00
One of the great idioms of web security is
filter input, escape output, and
0:04
we'll do just that by using some very
helpful PDO methods.
0:10
Let's take a look at how we can use the
pair and binding to filter out input.
0:13
Now we should know not to trust user
input.
0:19
So we're gonna need to do a lot here.
0:22
But filter filter, filter, filter.
0:25
Let's get rid of all this cuz it's ugly.
0:27
We'll do back to our ID 555, which is
Mallrats United.
0:30
Okay so first step here is to make sure
that this is an integer.
0:35
Now we're not gonna really do any crazy
validation on this.
0:38
We're just going to force this to be an
integer cuz we're expecting an ID.
0:41
So I'll actually use the built in function
int val.
0:46
And then we'll do let's see, val.
0:50
And then open and close our parens here.
0:53
Save that, and now whatever we type in
here, if we do 555, you know, whatever,
0:56
and then hit Enter, we'll actually get
just the integers,
1:02
the first section of it, so that's 55 I
believe.
1:06
Let's get rid of that.
1:08
[SOUND] And yes, that's correct, 55.
1:09
If I was to say ADDD and then another
five, again, just taking the first bit.
1:11
So, gets rid of all of the string
characters there.
1:17
We'll get rid of all of those.
1:19
And then, just go back to our 555.
1:22
Okay, Mallrats united.
1:23
Now, that we've done that little bit of
forcing it
1:25
to an integer by using the int val
function.
1:27
The next step is we wanna actually use
what's built into the PDO object or
1:30
to the PDO class, to tidy up SQL injection
by preparing our statements.
1:35
So let's go over here and we'll do a
search for the PDO class again.
1:41
[BLANK_AUDIO]
1:44
All right, first one, class PDO.
1:49
You're gonna scroll down here, and we have
two or
1:51
three things that we need to look into.
1:55
The first one is prepare.
1:57
Let's look at the docs for PDO prepare.
2:00
Now this method prepares a statement for
execution and returns a statement object.
2:03
So, looking a little bit further here.
2:08
We can see that the argument that it
receives,
2:11
that is required is the statement.
2:14
If you read the first paragraph it says
that it's gonna
2:17
be prepared to executed by the execute
method.
2:20
Okay, so that's our next method that we're
gonna call.
2:25
So this is going to help us with the
SQL injection by
2:27
making sure that the only the statement we
want to run, gets run, so
2:31
anything tagged onto the end of it will
not work.
2:35
So, we'll do a prepare statement first by
putting our statement inside of it.
2:38
So let's do that by going over to our
code.
2:44
And we are going to replace, what's down
here on the db query with db prepare.
2:47
[SOUND] Okay now the only caveat here is
that now we have a film ID
2:55
that we want to pass through.
3:00
So we're not gonna be able to do that
here,
3:02
what we're going to use is a place holder
and that place holder is a question mark.
3:04
So we need to get the film ID from line
six down here to
3:10
this place holder that's on line 11.
3:15
Before we run our execute command.
3:17
We're gonna do that by using another
method and
3:20
then finally on line 13 we'll actually run
our execute.
3:23
I'm gonna go ahead and type the execute
statement in now.
3:26
If we go back up to the manual, we click
on execute,
3:29
you'll see that it only needs input
parameters if we want them.
3:32
But for right now, we're not gonna use
them.
3:36
So we'll go back here.
3:38
All right, we'll do that by calling the
results object.
3:41
Results, object operator to execute.
3:44
Okay, now going back to the docs for
execute.
3:50
If we click on it, you'll see that it says
execute the prepared statement.
3:55
If the prepared statement included
parameter markers you
4:00
must either call bindParam or pass an
array.
4:04
Well, so we're not going to pass an array,
4:08
we're going to actually use bindParam
or bindValue.
4:10
We can use either one.
4:13
So let's click on bindParam and take a
look at that.
4:14
It binds a parameter to the specified
variable name.
4:18
And it's got two arguments here that are
required, the first is the parameter,
4:22
which is a mixed type and the second is a
variable.
4:27
We already know what a variable is gonna
be,
4:30
which is gonna be our film ID that we
retrieved from the URL.
4:33
But what is the mixed parameter here?
4:37
So if we look at parameter it says, that
the parameter identifier for
4:40
a prepared statement using named place
holder, it'll be the name or
4:45
if it's using question mark place holders,
it'll be the one indexed position.
4:50
Okay so like arrays are zero based we want
a one based index.
4:55
Because we're using the question mark
place holder.
5:01
So we'll actually use the bindParam
method.
5:04
We'll do dollar sign results and
5:08
then object operator to bind and then
capital P and param.
5:12
Right then we'll open and close our
parens.
5:18
Now we know that the first argument is
supposed to be a one indexed number
5:20
that represents the placement of the
question mark in our prepared statement.
5:26
We only have one question mark and it's
one indexed, so the number one.
5:31
Then a comma to separate the arguments and
5:36
then our variable that we want to bind to
that question mark.
5:38
So in our case it's $film_ID, which we set
on line six.
5:43
So just as a review we are getting the
results set and
5:52
putting it into a prepared statement.
5:55
We're using a placeholder of question
mark.
5:58
Then we're binding that question mark, or
that parameter to our film ID,
6:00
which comes from our url on line six.
6:06
Then finally we run the execute command on
line 13 or the execute method.
6:09
Which will actually create our results
object that has everything in it.
6:16
Now, when we run execute does execute
return something to us.
6:20
So let's go up here, and take a look back
one to our execute.
6:26
It's going to return to us a boolean which
is one is a true on success or
6:31
a false on failure, okay.
6:37
We can test for that for sure, but for
6:39
now we just know that it's returning our
result set to the actual results object.
6:41
So we should still be able to use a code
that exists on line 19,
6:47
as well as our code that exists on the
bottom here on line 39.
6:50
So make sure our file is saved and then
click our preview icon.
6:55
Okay so now we have our films by title
just like before.
7:00
This is the index.php file.
7:02
I'm gonna click on number nine here
Alabama devil.
7:04
And then it'll say films by ID equal to
nine Alabama devil.
7:08
But we should not be able to paste in our
SQL injection code here.
7:13
We should just be able to enter in numbers
and get the ID that we expect.
7:17
So, we'll go back one Alabama devil ID
nine.
7:22
Gonna take the actual code that was the
SQL injection from before,
7:26
then I'm gonna take that and paste it back
in to see what result we get now.
7:32
Paste this in, hit Enter, and then now
it's returning nothing to us.
7:36
Well that's fine because it's not giving a
valid ID, but
7:40
it's also not dumping back which is
exactly right.
7:44
So if I was to enter in something that
doesn't exist say 1,000 or
7:47
yeah 10,000 one will work, hit Enter it's
still returned nothing.
7:51
So, let's clean that up a little bit and
7:55
that should get us to where we want to be
for right now.
7:58
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