Hello, in this video we are going to cover SQL injection and prepared statements. So SQL injection is when you can basically write some code, let's say if you have a form on a website, and the user can put something in there, the user could potentially try and hack your website by putting some code some code in that box, that when you try and submit or when they submit the form, and you try and let's say put that data into database, you could actually execute some more code. That code could be, you know, as harmless as something like this. Well, this is relatively harmless. So it was a connection. Prepare and also into the users table, and I'm gonna say Under the insert insert something is was gone blank now, if literally is gone blank of how to do an insert query up above brackets at the end, and then you put the fields that using type and then you follies and let me separate all this so we can easily visualize what is happening.

So insert into users type. And then the values we are going to say is going to be the first one. I'm going to put a save up for later. And then for the type output node, but I'm gonna make a little change on a put this around, which is the bold tags in HTML. See weapons. So imagine if for some reason the user literally wants to create this, are they using it, like they wanted that they wasn't trying to hack you.

But if they want to execute this, if I execute it execute, and I've got a bit of code set up here that'll actually retrieve the data immediately after, but you'll see what happens. As you can see, you know where I had before, as you can see as there but it's bold and not to the database. It's got the B tag there, but when it retrieves it, it is bold, and that's basically for hacking the website. And imagine if this you know, that little piece there is meant to be used on several different pages. Any cook probably won't be using them. It could be like a Comment, for example, from a user or a post, and you might need if they had a bit, you know, they didn't just have a, like a B tag, which, you know, is harmless.

Why if they had an actual script tag where they were running JavaScript and messing with scripting your website, and there's a bunch of other stuff you can do, like have an SQL statement in here, which could potentially delete all the contents of your database. You don't want that at all. So first of all, how do we just fix this aspect of it, you know, the HTML, it's really simple. So what we want to do is basically put this into a separate variable, so I'm gonna say call it you name. It after you name sure for us and make the word he goes something else for username right here equals quotation marks, and there we go. Then what we want to say is you name is equal to HTML entities.

And then itself. So it will get this right here, pour it in here. So you could literally put your mind to their boss wanting to separate line for you would be easier for you to understand. And he minds, he basically replaces this with, you know, as as I specific ASCII characters. And if I use that in here and I still need the quotation because it's a string. And now if I was to run that we, you know, we get the B tag, but what's happened on our database.

So, as you can see, basically put some sort of, you know, like, this replaced the special characters, there's an awful function called HTML special chars, HTML entity will cover a lot more cover literally every thought of like a different type of character. And when you actually print it out, it will just look as if you know, the way the user, you know, actually typed in. Okay, that's all well and good. For something this basic, if you want to, you know, prevent the injection of SQL that could harm the database, then you're going to need to have a more advanced prepared statements or what we need to do. And on a common design, we don't need this anymore. What we need to do is have an array of the data.

So I can, you know, I do need that is more than the insert query. And it could be on the Select query as well. But at the moment, I'm not using a data like a variable inside of here doesn't matter. What we want to do is create an array or a poly parameter to this family, all the parameters, and this is going to be an array like this, and what we're going to say is instead of this right Here, we are going to put a poll on username, colon type, this does not have to match what I've got here, so I'm gonna change this. So, you can actually see that in practice and then in here you call on you then you map a value to it, which is you name then you say cologne to you know value to her, which is well what specifically put it in the domain as a separate variable.

So now, what you do you grab hold of that for in the execute, and when he gets the code, the values are separated, it will find the value they bind it to you, and here and here, and then it will basically put them inside here. So separating them out. Not only is it more efficient on your query, Because they if you need to have multiple query, you know, you need to run the insert query multiple times, and it doesn't automatically always run them. The other benefits is a lot more secure in terms of SQL injection. And if I run that advocacy, it still works the exact same way. There's another way that you can do it, which isn't quite that obvious what's happening.

And that if you remove the binding, you know what, I'm going to leave this in there. I'm going to duplicate that aren't as common as excellent. Unless this other way, instead of specifying like some sort of binding name, all you do is you just put a question mark, and it has to be in that that order though. So that's the silver you have to make sure that this is a slightly more shorthand version against the works the same way. I prefer this method if I'm being honest with you, last week, I want to comment this one in on commentary, and put the communist one out. So you can still say, but this is not my preferred method, this is just immediately obvious, okay?

You You TT, you can even if you know, programming, you can probably start to make some sort of connection, but with this, you may not. And even though all of my variables were, you know, like this, that I mapped them, you don't necessarily have to map them because if you're just specifically pointing in code, you could just put you know, values like no, you could put some value there and obviously, make sure you have the field you know, the row the column mentioned, but that is it. So, this should get you going and secured from SQL injection and this will be an introduction into more basically advanced prepared statements. If you have any questions Feel free to pop me a message. And as usual, I look forward to seeing you in the next video.