40: What are Prepared Statements and how to use them | PHP tutorial | Learn PHP programming

in the last episode we learned how to protect our database when a user goes to your website and inserts data directly from your website into the database and the reason we need to do this is because it's actually possible for people to go to a website and when you allow for them to actually write something themselves that then gets them to the database is that we allow for people to actually write code which will then get read by the database and we actually allow for people to mess up the database so the user could potentially go into a database and delete or change or see things that they shouldn't see inside the database so we need to make sure we protect the database inside our website from what's called SQL injection which is when people do actually go into your website and write something inside an input like inside a form and send code inside your database now in the previous episode we talked about how to protect the database by using MySQL I which is what we did right here by using the my SQL Lite function inside PHP now there's a much better way we can do this which is not using masks you live by using something called prepared statements now the idea behind prepared statements is that when we have some kind of SQL statement like the one we have down here we can actually go in and say well we don't know what the data tags are going to be yet which means we're going to go ahead and add placeholders instead of the actual variables down here we then send the SQL code to the database to actually parse it which means we actually going to run the code inside the database and then afterwards later on we send the actual data to the database and replace the placeholders inside our SQL statements and because when we do actually send the data later on we use a different protocol inside the database the database won't actually read the placeholders as code inside the database just going to see it as characters now today prepared statements is actually seen as the preferred method we need to actually connect to a database and either need to see stuff or insert stuff into the database so once you do exist are making website the new tags to be uploaded online then it's recommended that you do actually use prepared statements okay now what we're going to go and do in this episode is first of all I'm going to go and show you guys how to use prepared statements when it comes to selecting data inside the database and then afterwards I'm going to show you guys how to do it when we want to insert data inside the database so a couple episodes ago we talked about how to select data directly from the website which I believe was the first so what we did actually do anything inside the actual website that had anything to do with the database so if you guys want the lesson files from that previous episode I recommend you guys go back a couple episodes to where we do actually learn how to select data directly from the website and down the lesson file so you guys can actually follow this lesson if you guys want to so this is the found that we had a couple episodes ago where we just simply insert the index file when then select data and spin it out inside the website and what we did here is we just selected everything from inside the users table and spin out the user name of every single user so what I'm going to go and do is I'm going to go ahead and say we want to select a specific user inside the database what we're going to do first here is we're going to go and say we have a select statement that makes everything from users where user underscore UID is equal to a specific name so right now inside my database if we were to take a look at it you guys can see the right now I have just one user inside the database called admin now I want to select admin inside my website so what we could do here is inside this leg statement say we want to select a user ID called single quotes admin so we'll just save this and then refresh the browser you guys should see that we get admin ok so what I want to do is I want to say that well we want to include a placeholder instead of the actual name of use we want to select from the database so I'm going to go and say question mark and then afterwards I'm just going to go and delete everything we have in here except for the while loop down inside our check down here I'm just going to delete everything except for a while loop and then we're going to go and replace all this code with prepared statements instead of using MySQL I which is what we used before okay so underneath the SQL statement and we can actually go and write a comment to test we know exactly what's going on I'm going to go ahead and say that we just created a template which will then use later on when we do actually have the actual data that it needs to insert instead of the placeholder so I'm going to say we just created a template and then after we create the template we need to actually create the prepared statement so what we're going to do is we're going to say we have a comment that says create a prepared statements which I'm going to go ahead and say is the variable called as TMT which stands for statements I'm going to set it equal to a PHP function called my sqli underscore as TMT underscore init which stands for initialize so basically what we're starting up here is a prepared statement now right now inside this prepared statement here we need to tell it which connection want to use to actually do this inside the database so what we do actually have is inside our DB H file we have a connection to the database called variable con so I'm going to go to say we want to use this connection inside our statement down here so I'm going to go and paste it inside as a parameter inside the function so after creating the prepared statement I'm going to go underneath here and now we need to actually prepare the prepared statement using the SQL statement we have up here the template so I'm going to go and say we want to prepare the prepared statements and we do that using another PHP function called MySQL eye on the score is TMT underscore prepare now before we actually continue we can actually go ahead and create a check here the checks if this prepares tape and can actually work using the SQL statement we have up here using the template that we created beforehand so what I can do here is I can actually go and say we have an if statement and inside the condition of the if statement I'm going to insert my function down here called MySQL i underscore statement underscore prepare insert inside of here and then inside the parameters of this function I can actually go out and insert the statement they've created previously so I'm going to insert as the first parameter and then the second parameter is going to be the actual SQL template that we created up here so I'm going to paste it in as parameter number two so now we just did here is we went ahead and said okay if this function here does actually succeed is to go ahead and run the code inside the if statement now when it comes to PHP code we want to always check for failure before we take the success so what I'm going to go and do is I'm going to go and insert a exclamation mark right before the function which now means that instead of checking if it succeeds we now check if it failed so if this does actually fail we should write some kind of message inside the if statement so could actually echo some kind of string that says SQL statements fails just so we know what actually happened if we were to actually fail using this SQL statement okay so afterwards you want to say well we have an if statement then else if this axis succeeded then we want to run the code inside the else statements so now we did so voice we did like to create the template we did actually create a prepared statement using the connection to the database and then afterward we prepared the prepared statement using the template so what we want to do now is you want to actually bind the parameters want to replace with the placeholders inside the template and we do that by going inside the else statement you create a comment called bind parameters to the placeholders for holders since you only have one in here and underneath that we're going to go and create a PHP function called my sqli underscore s TMT underscore binds underscore parent which stands for parameters parentheses semicolon and inside the parentheses we need to have a couple of different parameters now the first one is going to be the active statement that we prepared up here called s TM T so we're going to go and say we have variable as TM T comma space then the second parameter is going to be an indicator for the placeholder we insert inside the template up here so right now we have a question mark instead of the actual data so what I'm going to do is inside us the second time so I'm going to say double quotes and then I'm going to go and write s which means the right now we only have one placeholder inside the statement if it were to have a second placeholders such as if it were to also check if user underscore first which is the first name was equal to question mark and again we will also add something like and in between here then right now would actually take for two different factors inside our database would take for a user name and the first name so right now we do have two different place lose in that case we would actually add a second as inside the double quotes now in this episode we're not going to do that so I'm just going to go and delete what I just wrote to you but just assure guys who would accidie tat now the third parameter inside this function is going to be the actual data they want to insert instead of the placeholder up here now we haven't actually created that data yet because in some cases like inside a typical website we could actually allow for people to write something then search for it inside the database but because right now we just want to select some kind of user using some kind of name I'm going to create a variable at the very top here which I'm going to call data just to give you some kind of name I'm going to set it equal to double quotes which is a string called admin so right now data is equal to Edmund which is the name you want to search for inside the database or a user name they want to search for so I'm going to set this s down here which right now represents the place hole inside the SQL statement I'm going to say I want to replace it with this variable data created up here by saying that the third parameter is going to be variable data now if we were to have two different placeholders and we did I except the second as the way we do this is we would actually create a fourth parameter which would then be equal to whatever variable we created for the second parameter or the second place holder inside the statement here okay but right now because we just have one I'm just going to include one inside the parameters down here so now we did this we can actually execute the actual statement inside our database so essentially what we're actually doing here is we're saying that we have this SQL statement we then run it inside the database and after it's been passed inside the database we then include the actual data that needs to be used instead of the placeholders so underneath that we're going to go and say is that we want to actually run this SQL statement but using the parameters we gave it down here and the way we're going to do that is first of all that's actually going to create a comment that says run parameters inside database then we're going to go and say we have a PHP function called my SQL i underscore s TM t underscore execute parentheses and then inside the parentheses we just need to include the statement that we just prepared like so which means that right now we actually ran this line of code which had all the data inside we need to replace inside the placeholders inside the database now after doing this we still need to get the actual data it's on an Ethier what we're going to go and say is we want to take the results from the executors statement and insert them inside a variable so right now I want to say we have a variable called result which is equal to a PHP function called my sqli underscore s TM t underscore get underscore the source parentheses semicolon then inside the parentheses we need to actually get the data from inside the statement which is prepared and then after we do this we can actually take the while loop that we have down here and just simply put it inside our else statement like so so right now we just did the exact same thing as we did in the previous episode we just went in and select the data but instead we did it using prepared statements which is much safer to use inside a website so just to go and check what we did in here for what you refresh the browser you guys can see you still get admin inside a browser now what x go back inside the code and so you want to search for not admin but John because you don't have that inside database you guys will notice that we don't get any kind of results so what we want to do now is now we learn how to actually select data from inside the database now we want to actually insert data inside the database so in order to do this I'm going to go and use the lesson material from the previous episode where we learned how to insert data inside the database which is right after depth so we learned how to select data so if you guys are interested you can just go and go back to that episode and download the lesson files if you want to follow this lesson here now when it comes to inserting data inside a database using prepared statements it's very much the same as we did previously when we wanted to use prepare statements in order to select data from inside the database so down inside our insert statement down here inside SQL variable I'm going to go and go inside my values in place the first last email you ID and password and I'm going to replace these with a question mark which means the right now we actually created placeholders which we're then going to give the SQL statement later on when we like to create a prepared statement so I'm going to go and delete my Maskull I quarry down here because you don't actually need it and then instead I'm going to go and create a prepared statement just like we did previously so I'm going to say we have a variable as CMT which is equal to MySQL I underscore as TNT on the score in its parentheses and I'm going to do this using the connection to the database which again is variable con which I created inside a separate document I'm going to insert it inside my parameter and then underneath here I'm going to go ahead and prepare this prepared statement using this SQL code up here so we're going to go ahead and do a check just like we did previously where we take for failure first so we're going to take some basic mark and then we're going to use a function called my sqli underscore as TM t underscore prepare parentheses and inside the parentheses it's actually right there correctly like so then inside parentheses we need to include first of all the statement up here and then the second parameter is going to be the SQL statement now if we do actually get an error message because it can actually run this SQL statement at the database then it's going to go and run a piece of code that says echo a string called SQL error or something you can just go and write whatever you want in here it's just for us to know if there was some kind of error so after the if statement going to say we have 10 our statements and inside the else statement want to actually bind the parameters to the placeholders inside the interest statement just like we did in the previous part so what we need to do here is we need to create the function called my sqli on the score as TMT underscore bind underscore param because you need to bind the parameters to the actual statements so inside the function parameters we're going to say want to use this Eggman's they were created up here and then the second parameter is going to be the indicator of how many parameters we want to bind to the template we created up here which in this case it's going to be one two three four five because they're five different placeholders inside the SQL statement so I'm going to say SS s SS which is five SS then afterwards we're going to say comma space and then we to insert the actual variables that we want to replace with the placeholders inside the SQL statement so the first one is going to be variable first I'm going to say variable first comma then variable last comma and then we just paste in the rest of them which is email comma you ID comma and a password like so so now that we told the statement what the parameters should be inside the placeholders let's likes going to run the code inside the database so we can actually use the function called my sqli underscore test emt underscore execute parentheses colon and then inside the parentheses we're going to say we have variable statements we're going to save it and then try to run it inside our browser so I'm going to go and refresh and type in something inside the inputs I'm going to say John Doe email is going to be John at gmail.com then I'm going to go and give him a username called John 23 and his password is going to be test 1 2 3 I'm going to say sign up and then I'm going to go inside my database refresh it and now you guys can see we get John Doe inside the database so this is how we can actually use prepared statements inside a website when we want to either select or insert data inside a database and it's the exact same method if you were to instead of inserting data if you want to change data or use any of the other SQL statements we have inside SQL you know tags interact with the database now one thing I want to mention before we end up this episode is that it's very important that you guys understand that we used functions in PHP meant for procedural programming in PSP so if you were to use object-oriented programming you don't use the exact same functions as we did inside this episode and again if you guys confused about what procedural programming and object-oriented programming is don't worry too much about it if you want to learn object-oriented programming and do have a course on it if you guys want to check it out but the important thing is that you guys just need to know that we've been using procedural programming in this course here so if you guys enjoyed and I'll see you guys next time

Channel: Dani Krossing

Views: 177,660

Keywords: php prepared statements, prepared statements, prepared statements mysqli, sql injection, how to prevent sql injection, prevent sql injection using php, php, php tutorial, Learn to protect your database against SQL injection, mysqli placeholders, sql using placeholders, how to use prepared statements in php, how to interact with your database using prepared statements, how to use prepared statements together with mysqli, what are prepared statements and how to use them

Id: I4JYwRIjX6c

Length: 18min 11sec (1091 seconds)

Published: Thu Jul 20 2017