- Understanding Relationship Tables 3:41
- Relational Data 5 questions
- Querying Multiple Tables with JOIN 4:02
- JOINing Tables 1 objective
- Understanding SQL Injections 5:21
- SQL Injection 1 question
- Preparing SQL Statements 6:25
- Using a Prepared Statement 3 objectives
- Adding a Secondary Query 4:30
- Fetching in a While Loop 7:38
- Fetching Many Relationships 1 objective
Preview
Video Player
00:00
00:00
Understanding SQL Injections
5:21When extra code you never intended to run is passed into your database query, it is called a SQL injection, because this extra code is **injected** into your query.
Right now we set up our single item array
function to accept an ID as an attribute.
0:00
This ID is going to come from
query string in our browser.
0:05
If someone visits
details.php?id=1 our code
0:11
passes the one to the single
item array function.
0:15
The function then uses this
one to query the database.
0:20
If someone visits details.php?id=2;
then it should use two in the query.
0:23
But what if someone types
this into the web address?
0:30
Think for
a minute about what that might do.
0:34
What if that whole string got
inserted into our simple select query
0:38
before it was executed?
0:43
The query would look like
this with a semi colon
0:45
that comes between these two queries.
0:48
One to retrieve the item information and
one to drop the entire media table.
0:50
If those two queries get executed.
0:56
It would remove the entire
media table from our database.
0:59
This is the kind of thing that
malicious hackers try to do to websites
1:03
all the time.
1:07
This kind of attack is called a sequel
injection because another query,
1:08
one you never intended to run,
would be injected into your code.
1:13
When dealing with values from outside your
code, you often hear these two rules.
1:18
Filter input, escape output.
1:23
We've looked at both of these before.
1:25
And you can see them being
used in our suggest form.
1:28
This is a perfect example of when
you would mean to filter input.
1:31
To make sure that you're not
getting bad or harmful data.
1:35
I would filter this in two places.
1:39
Both when we receive the user data and
also when we query our database.
1:42
Open details.php.
1:46
Our code takes a value for id from
the queries string using a get variable.
1:51
And puts it into a variable named id.
1:56
This value would be considered input,
2:00
since it comes from
somewhere outside our code.
2:02
Right now our id should
always be an integer.
2:04
For a sequel injection to work here,
2:09
the specified id would have to be
something other than an integer.
2:11
Let's use our filter_input function
like we do for the suggest form.
2:15
This time we'll use INPUT_GET
as our type and id as our name.
2:19
Finally, since our id should
always be an integer,
2:29
we're going to use
the FILTER_SANITIZE_NUMBER_ INT.
2:34
You might also hear this
process called sanitizing input
2:41
because you are removing any
harmful material from the input.
2:45
And making sure that only clean,
sanitized input comes through.
2:48
It's good practice to sanitize the input
immediately after you receive it.
2:53
In this case we get the value
from the query string and
2:57
sanitize it in the same line of code.
3:00
Let's move the call to our
single_item_array from our functions page
3:04
into our details page.
3:07
We'll remove this var_dump as well.
3:13
We want to replace our if statement.
3:18
We'll replace the static id
of one with our variable, id.
3:23
Then we'll assign the single
item array to our item variable.
3:28
We still want to see our item array.
3:33
So let's add a var_dump on the next line.
3:35
Since we're now calling a function
that will pull the data it needs,
3:41
let's remove the creation of
the catalog array from this page.
3:44
We still see our notices.
3:53
And now we see a different item.
3:54
That's because our full catalog array
function auto-assigns array keys.
3:56
Then when we try to pull
that key from the database,
4:02
it doesn't match up with the media ID.
4:04
Let's change our functions
to use our media ID.
4:07
Back in functions.php, we need to change
the select in our full catalog array.
4:10
We're going to add media_id.
4:18
We then need to change our
get_item_html function.
4:22
Instead of using the id from the array
key, we use our item media_id.
4:30
Now let's go back to our browser.
4:39
We'll hit our back button and
refresh the page.
4:41
Then will click on our
seven habits book again.
4:46
This time our ID is sixteen which matches
up with the media ID in the database.
4:49
We haven't fixed everything yet.
4:55
But our controller code now sanitizes the
input it receives from the web address and
4:57
passes that value to our function.
5:02
We've also modified our
function to use the media id.
5:05
The other place we want to sanitize or
filter input is in the function itself.
5:09
We will use another
method of the pdo class
5:14
to make sure that our query is not
subject to a sequel injection.
5:17