PDO Security

[MUSIC] 0:00 [SOUND] Security is very important when we start working with databases. 0:04 Most all databases contain information that could be 0:08 considered sensitive or private. 0:11 We would not wanna open up ourselves to a tax from the outside or 0:12 accidental dumps of this private information. 0:16 Let's see how these attacks could affect us. 0:18 >> SQL Injection is a common method that is used to attack data driven websites and 0:21 web applications by appending malicious SQL statements for the attackers purposes. 0:26 Taking our customers hard earned data, and dumping it to the screen, simply because 0:32 we did not take the time to filter all of our input and escape all of our output. 0:37 One form of this attack would be to simply end the previous statement in the url, and 0:42 append our malicious query to suit our purposes. 0:47 The code we've written, it works. 0:51 It does what we want it to do. 0:53 It takes the ID and it produces an actual film for us. 0:55 So I can type in 555. 1:00 And it is Mallrats United. 1:02 Must've been a good film. 1:05 I don't remember seeing it. 1:06 Either way, this is terribly insecure, unvalidated code. 1:07 You never want this in production as it sits right now. 1:13 You are definitely primes to one, having some nasty errors like, you know, 1:17 people typing in whatever and it's just you know dumping our exceptions, but 1:22 we don't want that we want to make sure our ID is a number for sure. 1:27 And the other thing we want to make sure is that no one can do SQL Injection. 1:31 Which is in essence stopping this statement here, 1:35 because we're just taking our id, whatever is, whatever this variable is, 1:38 with no validation, no verification, and just creating a variable. 1:45 Then we're taking that variable, and 1:49 just dumping it right into our database as a query. 1:51 So, they could technically just attach whatever code they 1:54 wanted to the end of it and it'll get executed on our database. 1:58 And that's, that's a big no, no. 2:02 Just to show you, as an example, to just drive this home, 2:04 instead of just the ID which is, you know, 555, 2:08 I can actually get rid of this and paste in a little bit of code. 2:11 Hit Enter, and I've basically told the database that I rather it 2:16 return the SQL LIKE version to me instead of the actual title of the film. 2:21 And as you can see here, there's our SQL LIKE version, 3.8.2. 2:27 That's not good, they can do anything they want. 2:31 Well, almost anything but it's still bad news, let's not do this. 2:34 Let's fix this by first validating that what we're getting through is an, 2:38 an actual integer, or making it an integer. 2:43 And we'll also take some built in methods to the pdo class, pdo object. 2:46 And use that to filter our results and to make sure it's secure. 2:52 We're going to prepare our statements before we execute them. 2:56 We'll do that next. 3:00