Security is important for all developers, but more so when combining two of the most popular Database and Web Programming technologies.
With more and more programmers creating dynamic, database driven websites, the proliferation of PHP and MYSQL is reaching critical mass proportions. With that however, comes the added “bonus” of many different security holes being opened on hosted boxes everywhere. With the current atmosphere of fear and mistrust when it comes to the security of the Internet right now, as programmers, we should be doing everything that we can to build safe, secure applications.
How can we do that? Well, the first step is education. Knowing the risks you are creating with your programming, and mitigating those risks with good programming practices. The most important part of educating yourself is understanding the different vulnerabilities that you can create with your programs. By far the most dangerous is SQL injection.
What is SQL injection? Simply put, it is the ability to inject and run arbitrary SQL code without having access to the database by traditional means. In lay terms, a user can access information or gain unauthorized access to the information contained in your database. This can be particularity troubling if you keep information such as customer contacts, credit cards, or other personal or critical information in your database.
What does SQL injection look like? Imagine the following SQL query:
select * from customer_info where name=’$_POST[username]’ and pass=’$_POST[password]’
If someone wanted to try and gain access to the area you are securing with this query, they could simply enter as their password: ‘ or ‘1’=’1′, resulting in the following query being executed:
select * from customer_info where name=’someusername’ and pass=” or ‘1’=’1′
If you evaluate that, then it will select everything from that table, since the OR ‘1’=’1′ will always be true, resulting in ALL the records being selected.
Protecting against this should take the form of several steps, but the first and foremost is that ANY and ALL input that is going to be used in an SQL query should first be filtered for content. This can be done for each variable individually, but that can get tedious. Instead, when I am processing a form of information that will end up in a database of some sort, I have a function that filters data based on what I want to do with it.
Because I only trust data that I have filtered exclusively for the uses I have in the script, the first thing that I do for all my scripts is filter out any slashes that may be present. I do this specifically because of a couple of php.ini settings that may be turned on: magic_quotes_gpc, and magic_quotes_runtime. These functions automatically escape quotes, which is generally a good thing, but can make a programmer lazy. This is especially true if you are developing an application for widespread use on different systems that may or may not have this setting active.
I prefer to control my own data filtering, so the first thing I do is remove any slashes that may have automatically been added:
function cleanSlashes($array=Array()) {
foreach($array as $x=>$y) { $array[$x]=stripslashes($y); }
return $array;
}
This would strip slashes from an array of information – a single variable could be stripped just as $var=stripslashes($var);
Once the slashes are stripped and I have the bare data, I can now decide how the data is used, and where. If I were going to insert the data into a database, I would filter it with:
function makeDBSafe($array=Array()) {
foreach($array as $x=>$y) { $array[$x]=mysql_real_escape_string($y); }
return $array;
}
If I wanted to use the data back in a text box (for error checking or the like):
function makeInputBoxSafe($array=Array()) {
foreach($array as $x=>$y) { $array[$x]=htmlentities($y,ENT_QUOTES); }
return $array;
}
Or if I were saving it to a file on the server:
function makeShellSafe($array=Array()) {
foreach($array as $x=>$y) { $array[$x]=escapeshellcmd($y); }
return $array;
}
Ideally however, I would have one function that would perform all of these, as I need them:
function filterData($value="",$filter_type="basic",$data_type="array") {
if($data_type=="array") {
foreach($value as $x=>$y) {
switch($filter_type) {
case 'basic': //filter out slashes from a post/get/cookie
$retvar[$x]=stripslashes($y);
break;
case 'dbencode': //encode db data using mysql_escape_string
$retvar[$x]=mysql_real_escape_string($y);
break;
case 'shellencode': //encode shell argument string data
$retvar[$x]=escapeshellarg($y);
break;
case 'htmlencode': //encode for form display with htmlentities
$retvar[$x]=htmlentities($y,ENT_QUOTES);
break;
}
}
} elseif($data_type=="string") {
switch($filter_type) {
case 'basic': //filter out slashes from a post/get/cookie
$retvar=stripslashes($value);
break;
case 'dbencode': //encode db data using mysql_escape_string
$retvar=mysql_real_escape_string($value);
break;
case 'shellencode': //encode shell argument string data
$retvar=escapeshellarg($value);
break;
case 'htmlencode': //encode for form display with htmlentities
$retvar=htmlentities($value,ENT_QUOTES);
break;
}
}
return $retvar;
}
This function will allow me to pass through an array or a string, and select the type of filtering that I want to perform on the data based on an argument sent to the function.
The data filtering solution for SQL Injection is only one method among many that should be observed when creating web applications. You should also be paying attention to access rights within your database its self, server security, and data management security. However, if you start by filtering your data, then you have a solid foundation from which to build on.
Just as data filtering is only one solution for SQL Injection, SQL Injection is only one type of potential security flaw that is exploitable in PHP code, there are many others such as Cross-Site Scripting, access control flaws, cookie/session protection, and more. As a developer, you should be familiar with each of the potential risks of these expoits, and educated on how to avoid them in your programming.
With more and more Web 2.0 application being built, and technologies such as AJAX being leveraged to build next generation online programs, we have a responsibility as developers to pay more attention to these sorts of potential problems, and be aware that we must stop programming as if our users are all benign and will nicely use the application as developed. There are bad guys out there, and we should always build with them in mind.
Mike Morton has been developing web applications with PHP and MYSQL for more than 6 years, and is the author of Real World PHP Programming: The Basics which can be found at http://www.vtccanada.com
