Prepared Statements - PHP Beginner's Tutorial

today we're going to talk about something called prepared statements inside PHP and prepared statements is something can use to help prevent SQL injection inside our websites now to give you guys an example of SQL injection let's say you have a website like this one and I go to it and because it has an input feel that actually connect to a database like a login system I can actually go into the input field and type it's girl code and actually run it inside the database because it's not protected against SQL injection that means i can actually extract use information or even destroy the database if I want to now the previous episode we talked about using a PHP function which looks like this to actually help protect against SQL injection but let's actually talk about prepared statement which is a bit better than doing it this way even though i recommend doing it both ways both the PHP function and prepared statements we're just going to remove the function just so we make sure it actually works using prepared statements which it will but just so you guys can see it works we're going to go and remove the PHP functions so the thing that's going on right now is that when i type in my username and my password and click submit it goes in and grab this function here called get lock in and inside this function we can get our username we get our password from the input fields after he hit submit and then we go ahead and put it inside an SQL statement that then gets run inside out database now this SQL statement is where the problem occurs because right now if you were to type is called code inside for example my password will actually insert the code directly inside is co statement now what we can do a prepared statement is we can actually go ahead and run the SQL command first inside the database without actually having to input from the users and then later on we can tell it oh by the way this is information the user typed in to the lock-in field now doing it this way essentially means that the data the user typed in and SQL statement is not seen as part of one which means that it's not going to execute the data for a user as part of this disclosure statement so the way we're going to do this is we do actually need to do quite a bit of changes to our you know actual connection to the database which is bad news for some of you people if you already made an entire website and now you're looking into how to do prepared statements because now we need to go into every single connection you have inside the website and do it this way we're about to do it I mean it's not a lot we are going to do but it can get a bit tedious if you've already made a website and you need to do this afterwards so the first thing we need to do is we do actually need to make changes to this select statement now the first thing we're going to do is we're going to go and change the name of this variable here which is called SQL right now and we're going to call it is TMT now you don't have to call it this is just it's called the statement and since this is a prepared statement it makes sense calling a statement now the next thing we're going to do is we're going to go ahead and go in front of our actual SQL code here i'm going to go ahead and write the variable connection that we have inside our dph file which is right now called dollar sign khan and then we're going to go to point to a method called prepare we're going to say parentheses and put it around our SQL statement that we have in here like so now the next thing we need to do is to do actually need to remove our user input from my SQL code and instead we're actually going to go and write question marks because right now we don't actually know what the input is going to be like so we're just going to tell is called command that this is unknown but we're going to tell it later and we do that by writing question marks so after we created our prepared statement we need to go underneath our code and we need to tell it it's kind of parameters should expect to have inserted inside are prepared statement and we do that by saying that we have a variable called stmt which you created up here that has prepared statement in it we're going to go to . for method called binds underscore param potential parameters parentheses semicolon and inside these parentheses we're going to go ahead and tell it how many piece of data to expect and what the names of the data is going to be so right now i'm going to say double quotes and inside the double quotes i'm going to tell how many data it should expect now we can write any kind of characters in here i'm just gonna go ahead and write s4 statement and i'm going to write two of them because it would expect to have two pieces of data after the double quotes going to say , space and then i'm going to write the name of the first variable it should expect to have inside this piece of code here and the first one to be user name i'm going to write comment and give it the second piece of data which is going to be telesign password now just to let you guys know if we did actually have another piece of data inside our select statement up here that's actually go and say we also had a or something is equal to ? they would actually need to add another s in here and then we need to add a third parameter inside with the name hold the sign something so that's basically how this you know this line of code here would actually work but because you only have password and username we're just going to stick with this so underneath out by a parameter function here or method we're going to go ahead and tell it which data it should actually insert inside this by parameter line of code here and the first data was the one we called dolla sign uid so we're going to go and say we have a variable called user name because that's what we told it that it was going to be named inside here which is going to be equal to the dollar sign you are d we got from the user in the previous page the second data is going to get his dollar sign password which is also inside here and it's going to be equal to the data we have inside here as well then after we have these two different variables we're going to go and actually execute these two lines of code and we do that by reference to our statement up here stmt i'm going to point to a method called execute so now will actually take the two variable names that I actually referred to inside this line of code where they were executing right now and it's actually going to take these data and put them inside i'll select statement which are part of our by parameters down here so now we've done this it will actually run this code and if i wanted to run another you so we could essentially just copy this piece of code appears underneath there and we could change the input to something else and would actually run this code up here twice inside the database which is kind of neat if you have to set up multiple users at the same time then you can run them separately multiple times but not going to do that for now so now we've done this we do actually have these disco'd up here executed and the next thing we need to do is actually get the data so what we're going to do that in order to not screw up anything we've done so far down here is we're going to go ahead and take our connection down here at these two results we get down here called dollars on result equal to my school on the scope you know query connect as well and we're going to go and delete this one because i'm going to write something else in step instead we're going to save dollars on results is equal to cosine is TMT because there was the actual statement up here and then we're going to . the method called gets results get on the school result like so so now we're actually getting the result in the same way as this line of code actually do which we had before so these does essentially the very same thing so you don't need to worry about you know dr. Koh changing they do the same thing now if you want to actually you know gets the number of rows like we do have to do it down here we do actually check if you have a certain you know number of rows we need to do is slightly different as well right now this would actually you know actually work because you actually have double somersault which is the same variable name is as we've been using in the bottom part down here but if you want to get the number of rows from a prepared statement we're going to have to write it slightly different it's actually going to go ahead and let's say create a variable called row number and we're going to go and set it equal to dollars on results which is reference to - what we have up here and then we're going to point to something called num rows which now gets the number of rows we have inside our database using well prepared statements which means that down here we need to actually go ahead and tell it that num rows on num row should not be greater than or should be greater than 0 which we have down here so that's essentially don't need to do and this actually going to save this going to a website refresh everything we don't get an error message which is good and let's try and locked in one two three lock in and now says you're locked in as admin even though we just basically did a bunch of changes to a coat down here so that means that everything is working like it should now let's actually go ahead and try to hack into the database and actually locked in without actually typing in the right password so I'm going to go into my login field I'm going to go into the password and i'm going to go inside single quotes space or spaced single quote one single quote equals single quote one which is basically when I can go into the database and lock in as you know that - even though it's not the right password if a lock-in it now says you are not locked in which basically means it worked the prepared statement is allowing is not allowing us to actually lock in without knowing the password which is what we aim to do like I said another good thing to do would actually be to use our PHP function we did in the previous episode down here would you actually give it to user name and password all we could do it up here doesn't matter and we could actually go in and it's going to copy paste from my cheat sheet on the side here so we can actually go in here and places in as well so now we actually have a function called my school I real escape string that actually goes in and protect this against SQL injection as well so now we basically have two different ways to protect ourselves so that's basically how we can do a pretty decent SQL injection protection on our website by using prepared statement so we actually run the SQL code before we actually know the actual data and by doing that we can separate the data to the SQL commands and I also using the PHP code you know the actual function up here we also tell it to not run this data as actual esco code so if you guys enjoyed and this is basically all I wanted to do this episode so i'll see you guys next time