Avoiding SQL Injection in PHP

Development3 min read

Recently, I’m reviewing some web project that made using PHP. It nice website, responsive and can support both PC and mobile display. But, it seems that they didn’t keep security in mind when they develop that website.

By security, I didn’t mean that unauthorized people can access certain login-only page or something. But it seems that they still trust user input naively.

Of course, they still have some sort of validation, but it only work on username or password. I still can do some SQL injection as long as it wasn’t username and password. I can input thing like this:

And when I asked if they know that their website has this kind of vulnerability, they said they don’t know. Security is overrated huh.

But I’m amazed that they didn’t have any security issue to this day. I guess they are quite lucky.

So, how we avoid this kind of problem? I know some ways to fix this kind of issue, and they are pretty much easy to do.

Avoid Using SQL Special Char

This makes sense since the cause of this kind of issue is because user input contain SQL special char and it makes your SQL syntax bugged. You can use any validation to only allow some character to be passed like alphabet or number only. Maybe with some kind of regex validation like this:

The code above will only allow the username to be an alphabet, number, underscore or hyphen and must in 5 to 16 character length.

I think that this is what I do when I started doing web programming 7 years ago. I naturally thought that to avoid this, I just need the user to not input those problematic characters.

But even if I tell you can avoid SQL injection using this kind of method, I won’t recommend it for you to rely on it. Sometimes, you can’t really avoid inserting this special character to your database.

It’s better to use the next solution.

Using Mysql Escape String

One of the fixes you can do is using mysql_escape_string or mysql_real_escape_string, or similar function on MySqli. As far as I know, this is the most common and most known way to avoiding SQL Injection. This will let you avoiding SQL special character like apostrophe (‘) and quotation (“) mark and you can use it like this:

Now you can avoid any kind of SQL special character in your SQL syntax. The downfall using this code is that you need connection to your database server first. This can be inconvenient if you are using this method before you have connection to your database server. I have this code snippet to mimic how

I have this code snippet to mimic how mysql_real_escape_string work and avoiding special character and you can use it on your project if you want:

You can make this as global function or static method or whatever method you prefer.

There is some important thing you should know, in the previous version of PHP I don’t know which version. There is a bug in mysql_real_escape_string escape routine and it become unreliable to escaping SQL special characters.

So I recommend you using the next method for avoiding SQL Injection.

Use SQL Binding

This one is my most recommend method to avoiding any issue like SQL injection. By using SQL Binding like mysqli_stmt_bind_param, rather than replacing, escaping and securing the input string, it let you bind your input to be passed when using mysqli_prepare. And it not really that complicated too, look at the code bellow:

You just need to prepare your SQL syntax statement using mysqli_prepare and then bind them to your input variable using mysqli_stmt_bind_param.

If you use any kind of class to warp database function, you may find this method to be unimplemented because mysqli_stmt_bind_param accept dynamic number arguments but you can’t really create a method that accepts dynamic number arguments.

Fortunately, there is some kind of workaround using ReflectionClass`. which you can do it like this example code:

With the code above, you can pass many arguments with an array. You just need to define your arguments order as if they are an array, and pass it using invokeArgs.

I think that all you need to know about how to secure your website from SQL injection. One tips I want to give is that don’t trust any kind of input that inputed by the user. You don’t want to learn this the hard way.

If you have any question or feedback, feel free to write them in the comment bellow. Don’t worry, it’s free. Thank you for reading.