Top 6 rules to build Solid MySQL AND PHP Applications

PHP & MYSQL are the those preferred technologies for building web applications, since they provide a way to build web applications rapidly. However, sometimes it leads to code that is hard to maintain , doesn’t scale well and perform poorly. And here there are several golden rules that helps you to build rock-solid applications.

1) Do not Trust your Users’ Input

A first golden rules that you should bear in mind is to never write code that trusts customer input. Here is an example to illustrate. You created a password reset form, and you ask user for his email to reset the password. Youl would construct a query might look like this:

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

If your users always enter perfectly formed email address, and this code works perfectly. However, in real world, a lot of crafty hacker would try to spoof the security of your application and might input email format as follow:

'' OR 1=1

This nefarious code is legal SQL, thus selecting everything from that field and displaying it, leading him to slowly gain info. And most importantly, 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.

2) Use PHP Framework

One way to ensure that you are code PHP/MYSQL according to best practices is to develop your application using one of the fine frameworks available. Actually, there are a lot of framework available, like: Zend Framework, CakePHP, CodeIgniterSymfony, Yii Framework. All of those here are available in communities for a period of times, I would highly recommended to adopt it in your web application development. And there are reasons behind my suggestion.

First, easy to learn with great communities support and well documentation.Those PHP frameworks, with  great number of contributors and participationer in communities would make you easier to learn the framework, since many of those would create blog posts or articles to share their experience on using the framework. Moreover, a well structured documentation would make you easier to learn and motivate more people to learn this framework as well.

Secondly, a well organized road-map and frequent update on framework version. Those PHP frameworks have a well planned road-map on developing their framework by providing a well organized schedule to relase next generation version of its product and version upgrade, this ensure any know bugs or securities holes are periodically monitored and patached by group of professionals. Moreover, this also ensure new features are going to be added which may improve your web application performance and your coding efficiency.

Apart from that, 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!

3) Document Your Codes

To enhance the 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.

4) Test Early, Test Often

Another best practice is to perform test early and 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.

5) Always Encrypt passwords

Many programmer would not border about the security of web application. The common case, they never encrypt user passwords, never save your user passwords in plain text in 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).

6) 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!


Keep in mind 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. And 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.

Related Posts

8 thoughts on “Top 6 rules to build Solid MySQL AND PHP Applications

  1. Yi

    Actually essential submit admin good a single i bookmarked your internet page see you in future webpage put up.

  2. php md5 encryption

    I used to be recommended this web site by way of my cousin. I’m now not sure whether or not this submit is written by him as no one else understand such special approximately my trouble. You’re amazing! Thank you!

  3. Custom Joomla Website design

    Attractive part of content. I just stumbled upon your weblog and in accession capital to claim that I get actually loved account your blog posts. Any way I’ll be subscribing on your augment or even I success you get entry to persistently rapidly.

  4. selber machen Naturkosmetik

    You realize thus significantly in relation to this matter, produced me personally believe it from a lot of numerous angles. Its like women and men aren’t interested except it is something to accomplish with Lady gaga! Your own stuffs excellent. Always care for it up!

  5. data recovery

    Hi all, I came across your internet site by way of Search engines when buying identical subject, your website came way up, it’s like excellent. We have added to be able to this favourites types|added to favorites.

  6. Sdfi

    Hey there, You’ve done an incredible job. I’ll certainly digg it and in my opinion suggest to my friends. I am confident they’ll be benefited from this web site.

  7. Equipement de protection individuelle

    I have already been browsing on-line in excess of Three hours currently, but I under no circumstances found just about any useful post like your own property. It really is beautiful selling price sufficient to me. In person, in the event that many online marketers and blog writers made excellent content material while you would, online will likely be a great deal more helpful than in the past.


Leave a Reply

Your email address will not be published. Required fields are marked *