Prevent SQL Injection Attacks in PHP
Recently I posted about connecting to a MySQL database and provided a simple function that comes in handy - keep your username and password in one place. This advice is all relative to PHP (Hypertext Preprocessor) programming. But how secure is your database from user input? One of the biggest threats to your security will be right at that front door where the rubber meets the road - username and password login.
The following YouTube video (3 minutes) shows a straight forward MySQL injection. It should give you the idea. A read of Wikipedia’s SQL injection resource and Steve Friedl’s tips on SQL injection attacks (particularly in Mitigations towards the bottom of the page) should take you a little further. It’s amazing how many web developers remain unaware of the problem - and I freely admit it only became something on my radar about 4 years ago when a client’s guestbook was hacked this way by script kiddies in the United States. Lesson two was to always keep open source software up-to-date.
The short answer for protecting yourself from this type of user manipulation of your database can involve a few reasonably simple steps. As a first measure, you might want to use regular expressions in your programming to ensure what you receive from the user is valid input. I do admit regular expressions might be a bit hardcore for some, but there are plenty of freely available snippets out there now you know what to look for.
You also need to use functions to addslashes and stripslashes when you’re putting user generated data in and out of your database. You addslashes before you put user generated data into the database, and stripslashes when it comes back out. Although you might be better just using the newer mysql_real_escape_string function, which I’m led to believe is a little more efficient. I should point out that adding and stripping slashes alone won’t protect you, nor will simply using regular expressions. You should use both strategies together.
I’d say those two measures are the hard and fast must-haves if you’re going to keep your database safe from this type of attack. But you can go a little further given a hardcore attitude and a wanton need to stop up even more holes in your defences.
Your queries might be run with only the necessary privileges from an account that does not have permissions to add new users, delete tables or do other significant modifications. This makes a lot of sense. Also, you might consider keeping your username and password in Apache rather than within your PHP code (although I don’t have a link to that one off-hand). Another thing to consider is error suppression in PHP - the last thing you want is for a hacker to generate an error that prints out on his screen explaining exactly what tripped up your defenses. Basically, never trust any input. The PHP Manual also has a good page in there on SQL injection.
Once you’re aware of this vulnerability you tend to just code a little differently and it stops being a real issue. This mostly happens when programmers are either unaware or apathetic about basic secure programming practices. I hope this has been of some help.
