All right, next up, we should discuss SQL injection, as well as, in general, improper SQL query formatting and the dangers associated with it. Okay, so in the US legal system, we have this ideal that a person is innocent until proven guilty, right? Well, as it turns out, on the web and in the programming world, actually, the opposite is true. We should always assume that the user is guilty, not innocent, and I'll show you what I mean. Let's open up TablePlus, and I will visit my SQL tab, and yeah, a quick refresher. Let's grab everything from the POST table, or we can grab the POST with an ID of 2, where ID equals 2, and as it turns out, we can also use the OR keyword.

Let's grab everything from the POST table, or we can grab the POST with an ID of 2, where ID equals 2, and as it turns out, we can also use the OR keyword. So maybe I could say, give me the POST where the ID equals 1 or 2, and there we go. Or if we want to be a little more verbose, we could say where the ID equals 1 or the ID equals 2, and we'll get the same thing. But what if we instead applied that to a different table? So you can see behind the scenes, I've quickly whipped up a users table, where the username is Varkar of 100 characters, and the admin is a Boolean, or a tiny integer of 2 characters, where 0 is false and 1 is true. Okay, so if we take a look at the data, I have two administrators and one guest.

where 0 is false and 1 is true. Okay, so if we take a look at the data, I have two administrators and one guest. So if I switch back to my SQL query tab, let's select * from users, all right, now let's find John, where the ID is 2, or let's find John and any administrators, like this, where ID equals 2 or admin equals 1. Okay, so now, yes, we have John Doe, but we've also retrieved every administrator from the system. And then finally, if we wanted to, we could delete the entire users table by saying drop table and then the name of the users table. So real quick, before I execute this, let's go here into structure, into info, and here

table and then the name of the users table. So real quick, before I execute this, let's go here into structure, into info, and here is the query that I can run to regenerate the table. So I will copy that, just so I can quickly recreate it. Okay, so I will hit command return, and now if I refresh with command R, sure enough, we've deleted that users table. It's entirely gone. Okay, so I will paste in that create query, and now it's back. All right, cool. So now, let's take everything we've learned just now and figure out how it might be used.

All right, cool. So now, let's take everything we've learned just now and figure out how it might be used against us. All right, so back to phpStorm, and at the moment, we're just selecting everything from the post table, and then we dump the results. So back to Firefox, give it a refresh, and sure enough, I get an array of two posts. But now, let's switch it to the post with the ID of one, and sure enough, we get a single record. And in this case, of course, if we're only getting one record, we can stick with fetch. I don't need a list or a collection, I just need a single result.

And in this case, of course, if we're only getting one record, we can stick with fetch. I don't need a list or a collection, I just need a single result. Okay, but now, as you can imagine in real life, we won't be hard coding the ID. Instead, for example, the user will click on a post with an ID of one, or another post with an ID of two. And maybe we could pass that through using the query string, like this, ID equals one. Okay, and now, of course, we're not yet doing anything with that parameter, so that's the next step. And as it turns out, we can access that query string by using the $_GET superglobal. So why don't we pass that to dd?

And as it turns out, we can access that query string by using the $get superglobal. So why don't we pass that to dd? And real quick, if this is confusing, well, remember, in the router episode, we reviewed superglobals right here. So the first one was $_SERVER, to grab information about the request in the server. This time, we're using $get to access information about the $_GET request. Okay, so if I come back to Firefox, and I give it a refresh, sure enough, I now have an array of all query string parameters. Okay, so now, if I want to grab the ID, I can access it like this. Cool.

Okay, so now, if I want to grab the ID, I can access it like this. Cool. So now you're probably thinking, great, why don't we save this, or cache it, to a variable, and then we will inline it here. All right, switch back, refresh, and yeah, there's the post with an ID of 1. And here's the post with an ID of 2. So yeah, it works, dot, dot, dot. So your instinct might be to pat yourself on the back and say, good job, me. But actually, no, we've introduced a major vulnerability here. All right, so here's the problem.

But actually, no, we've introduced a major vulnerability here. All right, so here's the problem. We are taking whatever the user types into that query string for the ID parameter, and we are inlining it as part of the SQL query. So let's do this. Let's take everything here, and extract it into a variable called query. And then I will save that, like so. That way, I can very quickly die() and dump() the query before we execute it. All right, I think this will help. So if I come back and refresh, this is the query that we will ultimately execute against.

threat in your application. So let's review another example. What if I said, give me the Post with an ID of 1, and then we'll add a semicolon here, and I will say, DROP TABLE users. All right, we're getting a little sneaky sneaky here, aren't we? We are now triggering two queries, one to fetch the Post with an ID of 1, and then another that we definitely didn't intend to DROP TABLE the users table entirely. So let's give it a shot. I will remove that dd, and now we will trigger and execute that query. So back to Firefox, give it a refresh, and yes, we have the results, but now if I come

Okay, but luckily, there is a fairly simple solution these days. Okay, so I have a rule for you here that I'd love for you to follow. And in general, you may have noticed this, I'm not the biggest fan of programming rules, as if they are, you know, commandments from above that shall not be broken. But in this case, it kind of is a commandment. You need to follow this one. Okay, so when accepting user input through a query string or through a form, never, ever, ever, ever, ever inline it as part of a SQL query. That's what allows for SQL injection, especially if you haven't formatted your query properly, which usually people don't.

That's what allows for SQL injection, especially if you haven't formatted your query properly, which usually people don't. Okay, so here's the solution. I'm no longer going to inline it directly. Instead, and I'll show you two ways to format this, I will replace it with a question mark. SELECT * from post where the id is something, something that later we will associate or bind to the query. But here's the key thing to understand. The query and the bound parameters almost travel in two different boats. Maybe that's a way to think of it.

The query and the bound parameters almost travel in two different boats. Maybe that's a way to think of it. You will send through the query to MySQL. And then in a separate boat, you will send through the parameters. And when you take this approach, you remove any possibility of improper formatting or SQL injection. Okay, so now, how do I bind to the parameters? Well, actually, let's do this. I'm going to open up a split, visit our Database class that we worked on. And yeah, right here, when we call the execute method, this is where you can bind to the parameters. And it will take the form of an array.

And yeah, right here, when we call the execute method, this is where you can bind to the parameters. And it will take the form of an array. But in this case, well, hmm, I need to pass them through here. So it sounds like this array should be dynamic. Why don't we call it params? And then I will accept it as part of the method signature. And we'll default that to an empty array. Okay, so now, when I call the query method as the second parameter, I can bind my ID like this. All right, cross your fingers, switch back.

like this. All right, cross your fingers, switch back. And let's start with the happy path. Give it a run. And yeah, it works. Let's switch to the id of 2. And that works as well. Now let's try something malicious. So if I switch back, ooh, I don't have my users table anymore. Real quick, let's go to the SQL tab.

So if I switch back, ooh, I don't have my users table anymore. Real quick, let's go to the SQL tab. And here's my SQL query to recreate it. All right, run it. Give it a refresh, and now it's back. And yeah, let's try to drop that table again. Give me the post where the ID is 2, semicolon, and then DROP TABLE users; Okay, so with any luck, if I switch back and refresh, aha, the users table is still intact. And it's intact specifically because we leveraged prepared statements with bound parameters. So just to be crystal clear, if I brought this back to what we had before, well, now

And it's intact specifically because we leveraged prepared statements with bound parameters. So just to be crystal clear, if I brought this back to what we had before, well, now we are once again inlining the variable. So we run it, and bada bing, bada boom, your users table is now gone. And this is the whole point of the video. This is the main thing I want you to avoid. Never, ever, ever inline user data into a query string. So I will bring this back. And yeah, this is a significantly safer approach. Okay, so finally, to wrap up, I did note that there were two ways to declare this.