Spider and web

Utilisation of your database

Your database that your web server (hence PHP scripts) use is one of your most important asserts. To protect this asset you need to use a combination of stored procedures and permissions. This will enable you to:

  • Prevent unauthorised access
  • Control access and when access is gained control privileges
  • Ensure that any bugs in your software are not catastrophic
  • Decouple the database from the PHP scripts to ensure:
    • Performance is maintained
    • Enable easier refactoring

Permissions

The best policy to adopt is to ensure that the web server has one account to access the database and this account can only run stored procedures. For the other accounts to provide administrative access you should ensure that the web server cannot access them by using the appropriate IP masks. To do this successfully you require that the web server and the database are on two different IP addresses. You can use virtualisation to achieve this if you want to run both on the same physical hardware.

Stored procedures

The advantage of only calling stored procedures from your PHP scripts is that you:

  • Can be sure that database integrity is maintained since you can check all the code for the procedures more easily
  • You can check performance because you can be sure that the statements in the procedures utilise the relevant indexes
  • As the PHP scripts can only perform a set of known operations it is impossible to slip in an ad hoc statements that either breaks the integrity of the database or adversely affects performance

Another good reason for using stored procedures is that it makes your system future proof. If you have to alter or add tables the only code that needs to be altered is the code that generates the stored procedures. This makes identification easier.