Database Security

Because of the popularity of PHP and its ability to work with a wide variety of databases, security in retrieving and posting data has become a serious issue. Below is a list of some of the databases that PHP supports:

• DB2
  
• ODBC
  
• SQLite
  
• InterBase
  
• Oracle
  
• Sybase
  
• MySQL
  
• PostgreSQL
  
• DBM

As with any remote data store, databases carry their own security risk. First, putting data into, or retrieving data from, a remote database exposes your data to the Internet or whatever medium you are using. Second, you will need to use connection credentials to access that remote database, which can also be intercepted.

Therefore, all input must be filtered, and all output must be escaped. When dealing with a database, this means that all data coming from the database must be filtered, and all data going to the database must be escaped. For example, if you only filter data when you put it into the database and do not escape it when retrieving it, then you might get data that contain slashes and other undesirable characters in your text. For example, if filtered, the text below

“I’ve been working all night.”

will be stored in the database in the following format:

“I/’ve been working all night.”

This is because an apostrophe usually causes an error when used in an insert query. When retrieving this text from the database, you will use something like the stripslashes() function to remove the slashes from the text, so you’ll get this:

“I’ve been working all night.”

This is not a security risk; I'm just using this example to show the importance of escaping and filtering data. To properly escape data for insertion into MySQL databases, use the new mysql_real_escape_string(). This function is used to escape special characters in a string for use in a SQL statement. Below is an example of how to use this function:

<?php

// Connect

$link = mysql_connect('mysql_host', 'mysql_user', 'mysql_password')

OR die(mysql_error());

//clean using mysql cleaner

$cleanuname=mysql_real_escape_string($n);

$cleanupass=mysql_real_escape_string($p);

?>

It is common practice with most developers (including myself) not to filter data that comes from the database. Though the security risk that we take is small, it is still better to take the “better safe than sorry” attitude in these cases. What we are saying by implication is that we should trust the security of the remote database, and as I’ve bitterly discovered recently, we should not take risks like that with sensitive information from clients. It is better to use even redundant safeguards, because as happened to me, if malicious data is somehow injected into the database, that redundant filtering can catch it, and save the day.