Introductory PHP/MySQL Security Tips

It is quite easy to build a web application using PHP and MySQL.  However, you will find that many novice programmers are not aware of various security issues that may be present.  This tutorial provides some simple tips, including source code, to help you see and understand common mistakes.

The basic thing that you need to keep in mind when programming a web application is that you should never trust the user.

What this means is that you don't know what a user will do.  They may request filenames you did not link to.  They may put more text into a field than you would expect them to -- even though you may have limited the field input size.  They may type in special characters that will cause problems when attempting to work with entered values.  It doesn't make any difference whether these actions are done by mistake or with malicious intent, you still have to protect against them.

Unexpected File Execution

Often, this is protected against by moving your PHP include directory outside of your web server path.  This ensures that users will not be able to ask for an be served a commonly used filename such as "config.inc" or "config.php".  Also, be sure to realize that using a non-interpreted name such as "config.inc" as a source file name would cause your web server to display the file as plain text.  Doing this is a huge no-no.

If you don't have the ability to move your files outside of your web server file
structure, then ensure that all your include files protect against direct inclusion. First, ensure they are also PHP files, so that they will be executed.  Then, within the top few lines of each file you can check to ensure that they weren't directly invoked:

if ( strstr($GLOBALS['PHP_SELF'],"/filename.php") )
   die("ERROR: Direct invocation detected!n");
</pre></blockquote>
You will of course have to ensure that each include file checks for it's own name so that this strategy will work properly.  Also, notice that the error message also says
that the access attempt has been detected.  A savvy hacker will realize that you may also have decided to email yourself a message upon such an event and avoid causing repeated issues.  You could do so like this.
<blockquote><pre>
if ( strstr($GLOBALS['PHP_SELF'],"/filename.php") )
{
   mail('you@your.site','Site Error','WARNING: Invocation of: '.$GLOBALS['PHP_SELF']);
   die("ERROR: Direct invocation detected!n");
}

Basic MySQL Protection

Anyone programming a web application should be aware of the issues surrounding single quote characters.  It is presumably due to this issue that PHP has a feature known as magic quoting.  This is the practice of adding backslash characters into strings that contain single quotes, double quotes, backslashes and nulls.  If magic quotes are on  you will notice input fields seem to accumulate '' characters.

There are two ways to deal with this magic quoting issue.  One is to make sure you use stripslashes() whenever you display or email something that may have originated from an HTML input form.  The other method is to disable magic quoting but be sure to use addslashes() whenever performing a MySQL query.  This matter is made somewhat more complex because each web hosting company may set this feature to on or off by default.

Personally, I like to strip slashes from input fields by default and then always
manually add them back if I am executing a SQL statement.  An example of this, which works for simple input fields, but not input arrays, could be implemented as
demonstrated below.

if ( get_magic_quotes_gpc() )
{
   reset($_REQUEST);
   while ( list($key,$val) = each($_REQUEST) )
   {
      if ( !is_array($val) )
         $_REQUEST[$key] = stripslashes($val);
   }
}

So, if you do have input values that do not already have slashes added to them, it is vital that you do add them before executing a SQL statement based on user input.  This is because a user could enter in a single quote, either maliciously or not, and terminate a string literal according to SQL parsing rules.  Consider if a user typed the following into an input field that did not have slashes applied.
<blockquote><pre>
sample'; delete from table x; drop table x;
</pre></blockquote>
The single quote would "expose" the semicolon, leading MySQL to execute the input buffer, probably return an error and then execute any following statements.  This is of course a huge problem.  You must always, in all circumstances, know whether or not your strings have had slashes applied before executing SQL statements.  Here is a example showing how to build a statement from unslashed input strings.

$sql  = "SELECT *n";
$sql .= "FROM   tablen";
$sql .= "WHERE  key = '" . addslashes($keyval) . "'n";
if ( !$res=mysql_query($sql) )
   die("ERROR: Unable to query TABLE for details!n");

while ( $row=mysql_fetch_assoc($res) )
{
   ;
}
mysql_free_result($res);

While I am sure there are varying strategies available for managing this issue, with pros and cons for each, the above is a simple look at how I prefer to deal with it. 
This allows my web server to not incur the added performance cost of adding slashes to all input data by default, because I have the discipline to write every SQL statement within all applications I create as demonstrated above.  If I am
delivering an application I am sure to include the magic_quotes_gpc() check so that I am not adding spurious additional slashes during database operations.

For More Information

Obviously, there are many more issues to consider when developing a professional Internet application.  A good place to start is the documentation provided for both the PHP and MySQL packages.  However, this topic is quite involved and even major corporations with professional developers and administrative personnel make glaring mistakes from time to time.