PHP & MySQL Best Practices for Rock-Solid Applications

March 11th, 2011 Leave a comment 2 comments
Like the article?
PHP & MySQL Best Practices

PHP and MySQL are often the preferred technologies for building web applications because they allow you to build robust web applications very rapidly. This rapid development, however, sometimes leads to code that is hard to maintain, doesn’t scale well or performs poorly. Fortunately, there are a few things that you can do early in your application development cycle that will keep your application from developing problems. Follow these PHP/MySQL best practices to build rock-solid applications. Also, keep in mind in general that a bit of extra time invested upfront in keeping your code easily maintainable will save you a LOT of time down the road, when your application becomes sufficiently complex! Be mindful of application security and do your best to avoid any potential gaping holes that would leave it exposed to outside threats.

Don’t Trust Customer Input

One important best practice for building rock-solid PHP/MySQL applications is to never write code that trusts customer input. Let’s say you’ve got a password reset form, and you ask the user for his email to reset the password. Your query after that might look like this:

SELECT * FROM users WHERE email = '$email';

In a perfect world, this would work because people would enter in only perfectly formed email addresses. What if, however, our password resetter is a crafty hacker? He might put in something like this as his email:

'evilhack@example.com' OR 1=1

His nefarious code is legal SQL, thus selecting everything from that field and displaying it, leading him to slowly gain info and Unfortunately, this is one of the most common attacks against a PHP/MySQL framework, and notoriously hard to defend against; you have to strip slashes, guard against wily quote use, and many other tips and tricks to stop hackers from getting into your system through SQL injection. There are tools available to check your site for these errors and you should use them. However, using PHP/MySQL best practices like always sanitizing data from customer input will prevent many of these kinds of errors.

Use PHP Framework

One way to ensure that you’re using PHP/MySQL best practices is to develop your application using one of the many fine frameworks available for PHP (see my earlier post, Why Use a PHP Framework?). These frameworks have already been tested and incorporate a number of PHP/MySQL best practices into their design. Using a framework saves you from having to reinvent the wheel in deciding how to sanitize and validate customer input, how to abstract the database layer and many other common tasks. In the example above, if we used a prepared PDO statement like so:

$sql = $db->prepare('SELECT thing FROM table where email = :email');
$stmt->execute( array(':email' => $_REQUEST['email']) );

the PDO framework takes care of the work for you, preparing the statement and getting rid of the ability for an attacker to ruin your day with quotes and slashes. While this isn’t the only reason to use a framework, it is the most obvious one following our example; frameworks are a must to ensure speed, stability, and security for your budding PHP/MySQL site!

Document Your Code

The PHP/MySQL best practice that will benefit the future maintainability of your code is to document it. Use comments throughout your code to describe the various functions and code. When you revisit that code months later, you won’t remember your own thought process: while you thought if (renamelater) { placeholder = 1; } was fine at the time, chances are you won’t remember what on earth those were referring to three months from the day you wrote it. Comments in your code will jog your memory and help you remember what various variables represent and how a function calculates a value. If you only adopt one PHP/MySQL best practice, this is the one you should use.

Test Early, Test Often!

Another PHP/MySQL best practice is what I refer to as “test early and test often”. Good testing will prevent problems down the road. Break your code into modules and test each module the same way each time. This is known as regression testing. By defining test cases that test each possible scenario and reusing those tests each time you change code you can be assured that you did not accidentally introduce an error in one module by changes made in another. There are a number of useful testing suites for PHP. Find one that you like and make it a habit to test your code frequently. This is a critical PHP/MySQL best practice and one that is frequently overlooked or done poorly.

Encrypt Your Passwords

Many websites, especially those designed by beginners, will not bother to encrypt user passwords, instead storing them in plain text in the database. For any MySQL/PHP database, it is imperative that you always remember to store your passwords in an encrypted form, whether it’s MD5, SHA, or some other encryption (preferably salted – some hashes can be broken with large enough rainbow tables. It’s best if you use SHA2).

EXPLAIN Your Statements

This is perhaps more MySQL than PHP related, but it’s important to note since so many people run unoptimized queries: Run your queries with the EXPLAIN command! For example, consider these two queries:

SELECT * FROM table WHERE year(yearvar) >= 2000;
SELECT * FROM table WHERE yearvar >= '2000-01-01';

On the surface, these two queries might look exactly the same. An astute programmer will notice, however, that the first query does not allow the use of an index. Most programmers (including myself) would miss this on a cursory examination, but running it with an EXPLAIN command produces these two (far different) results:

*********************** 1. row ***************************
table: table
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 459751
Extra: Using where
*********************** 1. row ***************************
table: table
type: range
possible_keys: yearvar
key: yearvar
key_len: 4
ref: NULL
rows: 59341

That’s a big difference in rows, and the EXPLAIN command explicitly spells out for you that one query can use the index while the other can’t; use this for all your queries and optimize them for the best site you can have!

Conclusion

PHP and MySQL are great development platforms that are capable of building large and complex web applications rapidly. If you follow PHP/MySQL best practices like commenting your code and testing frequently, you can make sure that your application will be rock-solid.

Help us spread the word!
  • Twitter
  • Facebook
  • LinkedIn
  • Pinterest
  • Delicious
  • DZone
  • Reddit
  • Sphinn
  • StumbleUpon
  • Google Plus
  • RSS
  • Email
  • Print
If you liked this article, consider enrolling in one of these related courses:
Don't miss another post! Receive updates via email!

2 comments

  1. fred says:

    Seriously? I would think if anyone is even looking for php best security practices in google then they probably know this stuff since it should be the first thing anyone learns about in a basic login page or simple form. I was hoping to find more advanced concepts.

    Thanks for the article though. I’m sure some beginners need it.

  2. Michael says:

    I also have heard that parameterized SQL is not a fool-proof method of injection defense.

Comment