Understanding SQL Injections5:21 with Alena Holligan
When 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
You need to sign up for Treehouse in order to download course files.Sign up