Sanitize data for SQL

- [Instructor] In the last movie, we learned about SQL injection and we saw just how bad it can be. In this movie, we're going to learn what we can do about it. If you think about it, breaking the syntax of SQL is similar to how we saw that we saw that we could break the syntax of a URL or HTML for cross-site scripting. The solution here is similar to what it was for both of those, we need to convert characters which have special meaning to the SQL into data. We need to escape the string, that is transform it, so that any characters that have meaning and power are rendered harmless.

In simple terms, the solution is to add a backslash before all single quotes in the string. That's how we escape them for SQL. For example, if we had a subject whose menu name was David's Story, we'd put a backslash in front of the single quote. Now, SQL would no longer think that it was a special control character in SQL, indicating the end of the value. It would see it just simply as being data. It's easy to add a slash by hand when we have control over the string, but with dynamic data, we need a more dynamic solution. We need code that will handle the escaping for us.

In old versions of PHP there was a configuration called magic quotes that would try to escape these values for you while they were on their way to MySQL. Unfortunately, it created more problems than it solved. It was removed, and it's up to us to add it to our code. PHP gives us two functions to help. The first is addslashes. It takes a string as an argument, and then it returns a string with backslashes before characters that need to be escaped. That is a single quote, a double quote, a backslash, and a special character which represents null.

There's another function, called mysqli_real_escape_string it does the same thing as addslashes and even more. It's part of the mysqli API, and it's designed specially for MySQL. It escapes single quote, double quote, backslash and null, but it also escapes line return and other odd control characters that you might not think about. It even takes into account the characters set that's being used with the database. Notice that the first argument to mysqli_real_escape_string is the database connection handle, then the string comes next.

We have to remember to provide the database connection first, and it means that we can only use this function when we have a connection to the database. Whereas addslashes is available all the time, you can use it with this database or with other databases. If we're using MySQL, we're much better off using the mysqli_real_escape_string function. The only problem with the function is the name is super long and we're going to be using it over and over again. Imagine our insert statements where we need to add it to every single value we're putting into the database.

Because of that, I like to define a function that has a shorter name that I can use instead. Here I've got a function called db_escape, it's taking two values, the connection and the string, and passing them right on in to mysqli_real_escape_string and returning the result. It's like writing an alias with a shorter name. Here's how you use it. We have our same SQL query, but now you can see that instead of just using the id, I'm first escaping the id using that new db_escape function which calls mysqli_real_escape_string. Now notice what happens when we have an attempted SQL injection.

The result looks like this. I've highlighted the value to make it clearer. Since the single quote is now just data, the semicolon and the SQL statement that come after it are just data too. The end of the data string would be the next single quote. Let's try it in our project. First, let's see SQL injection in action. Here we are in our subjects area and I'm going to click on view to go to the commercial. I'm going to take a look at commercial, pick one that's further down the list, don't pick the first one. Here you can see I have id=5 up here and that's telling it which one to pick.

I'm going to remove that five and I'm going to put a single quote and a space and then OR and then after that I'm going to put 'a'='a no single quote at the end, the reason why is because that's going to be provided in the code. Now let's hit return and see what we get. Notice I still got a valid page, it returned the subject that's in the first position. Why is that? Because the SQL that is constructed said, find subjects where page id is equal to blank, or, where a equals a.

Just simple string a equals a. And a does equal a, it's always true, so it returned true for every single subject. It returned all the subjects in the database. My where clause became meaningless. When it returned all subjects from the database, it pulled back the first subject and that's what it returned instead. Now that we've seen it, let's try to prevent it. In our project code, you can see that in the private datebase.php file I've added a new function already called db_escape and that's just my shortcut alias for mysqli_real_escape_string.

Now I want to use db_escape in my project. I'm going to come over to query_functions, that's the place where we're doing our queries, and let's look for values that need to be escaped. Let's start with find_all_subjects. If you look here, I'm constructing some SQL, but I'm not using any dynamic data so there's nothing to escape here. This is all text that's hard coded in here that I have total control over. If we look at find_subject_by_id you'll see that I'm using a dynamic value here. I'm passing in the id, in fact, the same function that's being called by that page we were just looking at, it's getting the id from the URL and it's using it to construct the query.

This is exactly the kind of value that I need to escape. I'm going to use my db_escape function, remember that the first argument is the database connection, which I have assigned to db. Then I've got the value that I want to escape, that's string id. That's all there is to it. Now I've escaped that value and it should be harmless. Let's save it and let's go try it out. Come back over here, let's reload this same page. And look at that. You can see that we didn't get the subject back. If we were to take a look at this SQL, if we were actually to echo the SQL back, you would see that it has been escaped.

Let's go over here real quick and let's just try that. Before we actually do this, let's do echo SQL, it's a good way to test our SQL. Let's reload the page. And there it is, select all from subjects where id equals, and you can see that it's got those backslashes in there. It escaped all those, now this is what it's looking for. It's looking for a subject where this is the id, and there is none. Let's comment that line out, and then I'm going to copy this db_escape, all the way past the comma, because I'm going to reuse that.

Let's jump down here to, we don't have anything in validate_subject, insert_subject is the next time we're doing a query. Let's take a look at that, here's where we're constructing the SQL. Here are values that need escaping. I'm going to put one in here, db_escape, close the parentheses at the end, let's do the same thing here. Let's close both of those parentheses. Now those values are escaped. Incidentally, that's why I did break this up before and use the period to concatenate it together. I would have room to use this function. We've got that one. Let's come down to update subject and take a look at that one.

Once again, here's values going into the database, they're dynamic values so we need to make sure they're escaped, not just the values being submitted, but even for that where clause. For each and every one, I just add my function. Let's scroll on down a little more. We have delete_subject, again that also takes this id. Delete from subjects where id equals, we want to make sure we escape that value as well. Now a good way to find all of these is to track them down in your application, make sure you don't miss one, is to do a global search for mysqli_query. I can do a global search in atom with command + shift + f that's often the case, and then I can hit return and it comes up and it finds all the places where it has mysqli_query.

Notice that most of those are in my query_functions file, however there is one other one that's over here in my validations file. We want to make sure we don't miss that one. Let's jump over to validation_functions and it happens to be at the very bottom. Let's jump down to the bottom of the page. Has_unique_page_menu_name you can see I have mysqli_query right there. Click escape to take the search away. Let's just take current_id and let's do db_escape using the database connection and let's just copy that and let's do it to the menu_name as well.

Now both of those values are being escaped. It's not actually that not hard to prevent MySQL injection. The trick is understanding what it is, and the need for it, and then making sure that you escape every single value without fail. That's where it's easy to slip up, is to forget to escape a value in one place. Now back in query_functions, I did it for all of the subject queries that we did. I have not done it yet for all of the page queries. As an exercise for yourself, go ahead and add that now. Escape all of the dynamic values that we're using in these page queries.

Make sure that you've got all of them in your project. That every single dynamic value has been escaped properly to prevent SQL injection.