Over the years, iterations of PHP and SQL have changed, in both function and overall usability. The single quote is the most important change, which is why it's cared about the most. View this tutorial about escaping strings for MySQL to learn more about how editing single data quotes can protect against MySQL injections.
- View Offline
We learned about SQL injection in the last movie, in this movie we're going to learn what to do about it. As we saw, the biggest problem comes from having a single quote in the data that you want to include in your SQL. The way to solve that is to escape the single quote, that is to tell MySQL this is not the single quote that ends the string, this is just part of the string. And the way we escape it, give it that special meaning to MySQL, is that so we put a back slash before single quotes. So for example, if we had today's widget trivia, we just simply put a back slash in front of the single quote and then it'll be fine. It'll be put into our string, MySQL will look at it, and when it gets to that backslash it will say oh, the next character has special behavior. This is not the ending single quote, this is just a single quote and then it will keep going from there until it gets to the real ending single quote. Now, it's not very practical for us to always put in backslashes by hand, which is what we would be doing here.
Instead, what we need is a function that will do that for us. We need some way that we can just tell it whatever the data is, whether it's data that came in from a form, whether it was pulled from a database, we want to run our function on it to escape all the values that might be in there. PHP offers us something like that, it's called add slashes. And addslashes will go through our string and add the backslash in front of those characters. Now there's a little bit of PHP history that we should go into here. People thought addslashes ($string) was a great idea. And that it was such a great idea that it ought to be a default in order to keep new developers who were just learning PHP from making this very common, very tragic mistake.
So they decided to build it in as a default with something called Magic Quotes. So PHP would automatically call addslashes, and all GET, POST, and COOKIE data. And that would at least make sure that we were protected from the outside SQL injection coming in automatically. It was added way back in PHP 2, it became the default as of PHP 3, but it was finally removed in PHP 5.4. Why was it removed? Well, because it caused a lot of problems. It was a real big headache for developers. And the biggest reason was that magic quotes could be turned on or off.
So some people liked to have it on or unknowingly had it on so that it was doing this for them. Other people turned it off. And then did they adding the slashes themselves. So then if you had a PHP application that was developed on one machine, it wasn't portable to another machine anymore. Because that other machine might be adding slashes itself, or it might not be adding the slashes when you were expecting them to be there. So it started causing lots and lots of problems. So finally, they removed it, and they removed it in favor of something better. Something that you aught to be using instead of add slashes, which is real escape string. The idea of real escape string first came about in PHP 4.3 with the MySQL database API.
However, when MySQLi came out, that's the version in PHP5, and that's the one that we're going to be using. So MySQLi, real escape string is going to escape the string in a way that's appropriate for MySQLi. That's what it does, it's database sensitive. So, it knows alright, for the MySQL database, here's the things that need to be escaped. So that's the one that we're going to be using, and using it is very easy. Let's see how. So, to begin with, let's open up database's insert and let's do our work in here. You see where we had our insert before.
Right now we're just taking the value that came in from menu name and just dropping it right in here for our values. So, if we try to create a new subject called Today's Widget Trivia, we'll get a problem. As soon as this gets dropped in down here, this single quote is going to cause us a problem. We could escape it by hand, but that's not useful; instead we want to use one of our functions. And so we're going to use that MySQLi real escape string. So, let's imagine these came in from POST variables. So, we're not going to mess with them, we're going to leave them exactly as they are. This is what came in from the POST. But once we get it, then we're going to escape it. So, what we'll do is menu_name equals the result of mysqli_real_escape_string. Now, the values that we provide to this, the two arguments. The first one is going to be the connection to the database, and then the second one is going to be the old value, menu_name.
Now I've got he same old name and new name, that's fine. It's no big deal. It's going to take what was the old name and put it in, and the output will just replace it. So it'll be got at that point. Now, if you were used to using the MySQL real escape string version, these arguments are flipped. It's the reverse. And that's true for a lot of the MySQLi functions, they reverse the order of the arguments. So, that's it, that's all there is to being able to do this. It's really that easy. It's just a matter of remembering to escape it, before you include it down here. So, let's save it and let's go try it. Let's go to Firefox, here's our pages.
Now reload, that's the pages I have. And now we'll do our insert. And I have my new value up there. Go back and reload the page. There it is, Today's Widget Trivia. You see, it submitted that with no problem whatsoever. And that's because we used real escape string. So, what about position and visible? Well, you don't really need to escape anything that's not a string. It's real escape string, that's what it's for. It's designed for strings. I'll make a note here, escape all strings. You don't need to escape the integers, they're not going to go inside single quotes here. In fact, it's a good idea, not to use single quotes around it for exactly that reason.
So it won't cause that problem. In addition, you can take the additional step of just making sure that this is an integer. That will just make sure that it's typecast as an integer, and then will make sure that, that's what gets used here. So you can do that additional step if you want to check, but you don't need to call real escape string on it. So what kinds of things should you be escaping then? Well, you should not trust anything, anything that comes in from a user. Whether it's from the URL string, whether it's a form value, a cookie value, even your database values which may have originated from somewhere else, from some user somewhere else.
We also need to escape because they may have innocent little single quotes in them like this, we may not be thinking about. So we always want to use anything that we're going to construct into our MySQL into a string, needs to have real escape string. The one thing you don't want to real escape is something that has already been escaped. You don't want to escape it twice. Just make sure you only escape it once right before you use it. Now, we've mostly been talking about the single quote, it's worth noting that it does actually change a few other characters as well. There's a special ASCII character that represents null, there's some line return characters.
Things like that that also get changed at the same time, it's not just the single quote. But the single quote is the most important one, and the one we care the most about. So it's that easy, there's no excuse for not protecting your database from SQL injection by using MySQLi real escape string.
- What is PHP?
- Installing and configuring PHP and MySQL
- Exploring data types
- Controlling code with logical expressions and loops
- Using PHP's built-in functions
- Writing custom functions
- Building dynamic webpages
- Working with forms and form data
- Using cookies and sessions to store data
- Connecting to MySQL with PHP
- Creating and editing database records
- Building a content management system
- Adding user authentication