Website design and development tips – security with PHP and MySQL

With PHP and MySQL hosting being such a common combination powering many of the world’s websites, it’s not surprising hackers often choose to target well-known vulnerabilities in the PHP MySQL internet technology stack to gain back-door access to data.

It is therefore important that web designers and developers know what these vulnerabilities are and write code that is secure, in order to keep customer data safe from opportunistic hackers. So, what is the biggest threat to PHP MySQL and how can you write code that prevents unauthorised access?

Direct SQL command injection

Direct SQL Command Injection is used by hackers to create or alter existing SQL commands to execute dangerous system level commands on the host.

If the code is poorly written, all a hacker has to do is alter the PHP string, created by the application and inject static malicious code to query the MySQL database for sensitive content.

SELECT fieldlist FROM table WHERE field = ‘$EMAIL’;

The above example shows a pretty standard PHP query string. In this case it is asking for an email address submitted in a form. So this query is expecting an email address in response. Now let’s alter the string to gain access to more than one field in the database.

SELECT fieldlist FROM table WHERE field = ‘anything’ OR ‘x’=’x’;

See how we have altered the query WHERE to return multiple items in the database? Because the clause ‘x’=’x’ is guaranteed to be true no matter what the first clause is, this query will return all fields in the database. In this case, that means every email address this database contains.

The above example is pretty simple, but it gives a good idea of the vulnerabilities of the PHP MySQL stack. Imagine if this was an e-commerce site, a hacker would be able to gain access to the email address of every single customer. And that is by attacking just one field, most e-commerce sites have hundreds of fields, including credit card numbers.

4 Tips to prevent direct SQL command injection

• Always connect to the database using customised users with very limited privileges.
• Always build your strings using prepared statements with bind variables. You can find a list of prepared statements here
• Always check the input matches the expected data type. The PHP library comes with a range of input validation functions, here is a list of Variable Functions and Character Type Functions to get you started.
• When requesting a numerical input, always verify the data with ctype_digit(), you can also silently change the corresponding PHP type using settype().