Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
Prepared statements are a feature of MySQL, and of many other databases. The topic is too advanced for an introductory tutorial, but I do think it's important for you to understand what they are. The general idea is that you give MySQL a template for a query that you want to run, with places indicated where you can fill in the blanks later. For example, we might have an insert statement, and in place of the three bits of data that we would be dropping into the columns, we would put question marks. Those are place holders for where the data is going to go later. Then when we want to run the query: we call up the template, fill in the blanks and then MySQL to run it. So why do it this way, why go that extra step? Well, because when any query runs, the database has to parse the query and develop a plan for running it. With prepared statements, the database will do this work once and then you can reuse it for future queries where only the variables change. The parsing and the plan of attack stay the same. This can be faster, especially if you're doing complex queries, repeating a query many times.
But more importantly, prepared statements separate the query from the dynamic data. This completely, 100% eliminates the possibility of SQL injection. It even eliminates the need to escape your values to prevent it. Because the blanks where the data goes are so clearly defined, and the data that's going in there, is going to be cast into the right type. So the database will handle it for you, it'll make sure that it puts the right kind of data in the right spot, and it won't accidentally inject SQL. Now, as I said, we aren't going to be using these in this tutorial, but they have come into pretty common usage in PHP, so I think you should at least recognize them. Let's take a quick peek at an example before we move on. So, this would be an example of a prepared statement. You would open up your database connection, you would design your query, but then you would have question marks in place of username and password, where the data would be dropped in.
Those are the fill in the blank spots. Then you would call mysqli_prepare to prepare the statement, and then from on we have a statement object we can work with. The magic of this happens with the bind_param function. so, mysqli_stmt_bind_param takes variable values and drops them into those spots and that's where the escaping takes place. After that we tell it to execute the statement. We get back the results, we have to do a little bit of extra work with those, but in the end we can work with that data the same way that we normally do. Now, there are a few quirks with prepared statements that make them a little bit harder to pick up, until you've had more experience with PHP.
So for now, you should stick with what we've learned and query the database the standard way and escape your values using mysql real escape string. At least now you know what lies ahead for you when you do start working with prepared statements. (BLANK_AUDIO)
Get unlimited access to all courses for just $25/month.Become a member