|

Escape Strings For MySQL To Avoid SQL Injection

Escape Strings For MySQL To Avoid SQL Injection

With all the talk about working with databases using MySQL and PHP in this tutorial series, one thing we didn’t cover yet is SQL Injection and how to protect your site from it. The syntax for MySQL is very specific, and if you don’t get it right, it is easy to break. As we build up queries using dynamic data from our variables in PHP, you need to be careful that any data contained in those variables do not break the syntax as well. One of the main things to look out for is the single quote in strings. In this episode we’ll talk a little bit about SQL Injection, and the method used to combat it.


What Is MySQL Injection

SQL Injection is the process of a malicious hacker on the internets that purposely tries to take advantage of the specific nature of SQL syntax, and the fact that it can be broken. If a hacker is able to carefully put together an URL string, form data, or cookie data, to nefariously inject their malicious SQL into yours, your database could become the victim of dropped tables, stolen data, entire databases being dropped, or worse. The main idea is that the hacker takes advantage of the ability of single quotes to denote starting and ending points of SQL code. If those single quotes are not properly escaped, then they are prone to this type of attack.


The History of Escaping Strings

The problem of escaping strings goes all the way back to the beginnings of PHP. Recall the main problem is that pesky single quote. There may be times when a string contains a literal single quote that is needed, but we need to make sure that MySQL understands that this particular single quote is not the end of string boundary, but an actual character that we want in the string.

Problem

If we were inserting this into our database, it might look something like this:

The problem is that the single quote included in the string may cause a problem for MySQL.

Solution

The solution to this problem is to simply escape the string like so.

What Does It Mean To Escape A String?

This is simply a means of telling MySQL that this is not the single quote that ends the string, rather it is part of the actual string itself and should be treated as such. So as you can see the way that we assign this special meaning to the character so that MySQL knows it is safe, is to prepend it with a backslash character. PHP has had a few ways to try and deal with this over the years, let’s look at a few now.


An Escaping Strings History Lesson

If you have a lot of data in your PHP application, you can see that having to escape any single quote that may exist in the strings of your application by hand would be a tiresome chore. Not only that, you’re bound to miss a few and introduce problems despite your best efforts. Wouldn’t it be ideal to simply have a function that does this for you? Of course it would, so PHP added this ability to the language many moons ago.

addslashes($string)

It all started with the addslashes function some time in the past. This function takes a string as it’s argument, and returns the string with any problem characters like a single quote automatically escaped for you. This was a good idea, so good in fact, that it was made a default baked into the language by way of something called Magic Quotes.

Magic Quotes

Magic Quotes is a configuration directive in PHP that would automatically call addslashes on all GET, POST, or COOKIE data by default. The thinking was that this would save developers the mistake of forgetting to do this on their own and open up their websites to a security vulnerability. This was added in PHP2 and became the default in PHP3. All good things come to an end however, and in PHP5.4 Magic Quotes were sent to the trash can. Why you say? Well, it caused a lot of confusion for developers, and made programs much less portable from host to host. This is because one never knew if the configuration was on or off for Magic Quotes. So in some cases, the code would work just fine, and in others if might fail altogether. There is a solution to these woes however.

mysqli_real_escape_string( $connection , $string )

In the wonderful world of PHP, we sometimes run across insanely long function names with underscores in between the words. This is one such instance. This is a great function however which you should make use of. It takes a string and then escapes it in such a way as to make it perfectly safe for MySQL statements. Therefore, you can build up your queries in PHP as much as you like, then before you run the query, just make sure to put the fully assembled sting through mysqli_real_escape_string and all will be well.

Observe

Note that when the query is first echoed out, it contains all of those problematic ‘ characters. In MySQL, strings must be enclosed by single quotes exclusively, so by putting this string inside of single quotes, the query is now broken and dangerous to the database. Also notice that once we run our query string through the mysqli_real_escape_string function, it comes out crisp, clean, and safely escaped for use with the database.


This Is Not Good
Isn’t it nice that we don’t have to escape ‘ characters all by ourselves?


This Is Good!
Isn’t it nice that we don’t have to escape ‘ characters all by ourselves?


Provide The Correct Arguments

The mysqli_real_escape_string function takes two arguments. The first argument is the database connection itself, and the second is the string you want to cleanse. It’s pretty obvious that we need to provide the string to clean, but the database connection is not as obvious. Just remember to provide it, or the function will in fact fail.

The mysqli_real_escape_string Conclusion

The takeaway from this quick lesson is, keep your data safe by properly using the mysqli_real_escape_string function. When you’re using a framework, you won’t need to worry about it, but when native PHP and MySQL is in use, you need to take care of this step manually.