Database and Password Security for Web Applications

In this article we will discuss security for databases accessed through the Internet. We will also examine the issue of password management, since handling that task properly will help us make our web site and its applications more secure. This is the seventh part of an eight-part series that shows you how to build security into an application for an Internet cafe.

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.

{mospagebreak title=The Password Management Script}

The password management script is responsible for retrieving and sending a password to a user. This happens when a registered user forgets his or her password and needs to be reminded of what the password was. The logic of the script is very simple; it presents the user with a form that takes the username and email address. It then checks to see if the user exists in the database. If the user does exist, it retrieves the password of the user and sends it to the user using the PHP mail() function.


<?


if(isset($_POST['key'])){


$errmsg=””;

$error=false;


//1. Check if form fields are filled in

if(!filledin($_POST)){

//print "Please enter your username and email.";

$errmsg=”Please make sure that all required form fields are filled in”;

$error=true;

}


//check that the username and email address is string

if( is_numeric($_POST['name']) && (is_numeric($_POST['email]))){

//print "Please enter a valid username and email address.";

$errmsg=" Please enter a valid username and email address.";

$error=true;

}



//Check if email address has correct format

if(!eregi("^[a-z0-9]+[a-z0-9_-]*(.[a-z0-9_-]+)*@[a-z0-9_-]+(.[a-z0-9_-]+)*.(

[a-z]+){2,}$", $_POST['email'])) {

$errmsg=" Please enter a valid email address.";

$error=true;

}



if(!$error){

$name=$_POST['name'];

$em=$_POST['mail'];


//2. Check if entered name exist


$query="Select pw from users where uname=’$name’" or die(mysql_error());

$result= mysql_query($query);


if(mysql_num_rows($result)>0){

for ($i=0; $i<mysql_num_rows($result); $i++) {

$row = mysql_fetch_assoc($result);


$pass=$row['pw'];

$to="$emrn";

$from="From: admin@mysite.comrn";

$msg="Password:$passrn";

$msg .="Username:$namern";

$msg .="Please change your password as soon as you logonrn";

$subject="From Admin re:Your Login Passwordrn";

}

}else{

print "Your username is either spelled incorrect or does not exist, please try again";

exit;

}



//4. Send password to user

if(mail($to,$subject,$msg,$from)){

print "Your password has been sent to <b>$em</b>" ;

}else{

print "could not send email";

}

}

}

?>



<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml"><!– InstanceBegin template="/primary/Templates/was.dwt.php" codeOutsideHTMLIsLocked="false" –>

<head>

<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />

<!– InstanceBeginEditable name="doctitle" –>

<title>WebSecure::Password</title>

<!– InstanceEndEditable –>

<!– InstanceBeginEditable name="head" –>

<!– InstanceEndEditable –>

<link href="Templates/was.css" rel="stylesheet" type="text/css" />

</head>


<body>

<table width="99%" border="1">

<tr>

<td bgcolor="#333333" class="header">Web Secure</td>

</tr>

 

 

<tr>

<td><!– InstanceBeginEditable name="main" –>

<form name="form1" method="post" action="../forgotten.php">

Please fill in the following:

<br>

<table width="445" border="0">

<tr>

<td width="187"><div align="left">Username</div></td>

<td width="242"><input name="name" type="text" size="40"></td>

</tr>

<tr>

<td><div align="left">Email <font color="#FF0000" size="2">(password will be sent to this email address)</font> </div> </td>

<td><input name="mail" type="text" size="40">

<input type="hidden" name="key" /></td>

</tr>

<tr>

<td> <input name="submit" type="submit"> </td>

<td></td>

</tr>

</table>


</form>

 

 

<!– InstanceEndEditable –></td>

</tr>

<tr>

<td class="copy">&copy;2008</td>

</tr>

</table>

</body>

<!– InstanceEnd –></html>

{mospagebreak title=The Code}

The script first checks to see if the form has been submitted:


if(isset($_POST['key'])){


If the form has been submitted, the form data is filtered. The process of filtering starts by checking to see if the submitted form data actually contains any values:


//1. Check if form fields are filled in

if(!filledin($_POST)){

//print "Please enter your username and email.";

$errmsg=”Please make sure that all required form fields are filled in”;

$error=true;

}


Next, the type of data is tested. We expect only string values for the name and email values. So we check the data type by using the is_numeric() function of PHP. This function checks to see if the value that it is fed is a number:


//check that the username and email address is string

if( is_numeric($_POST['name']) && (is_numeric($_POST['email]))){

//print "Please enter a valid username and email address.";

$errmsg=" Please enter a valid username and email address.";

$error=true;

}


We use regular expressions to test the format of the email address that the user entered into the form and set the appropriate error messages if the format is invalid:


//Check if email address has correct format

if(!eregi("^[a-z0-9]+[a-z0-9_-]*(.[a-z0-9_-]+)*@[a-z0-9_-]+(.[a-z0-9_-]+)*.(

[a-z]+){2,}$", $_POST['email'])) {

$errmsg=" Please enter a valid email address.";

$error=true;

}


if no errors were found, the form values are transferred to shorter variables:


if(!$error){

$name=$_POST['name'];

$em=$_POST['mail'];


Then we check to see if the username that the user entered exists in the database. This is very important, because we will not be able to retrieve the database without this piece of information. Also, it is a good way to make sure that no unauthorized person gets the password:


//2. Check if entered name exist


$query="Select pw from users where uname=’$name’" or die(mysql_error());

$result= mysql_query($query);


if(mysql_num_rows($result)>0){

for ($i=0; $i<mysql_num_rows($result); $i++) {

$row = mysql_fetch_assoc($result);

{mospagebreak title=Code continued}

Once the password is successfully retrieved, we build the email message that we want to send to the user. Because we are going to use PHP’s mail() function, we need to define to whom the message is going, the subject of the email message, from whom it is coming and the body of the mail message:


$pass=$row['pw'];

$to="$emrn";

$from="From: admin@mysite.comrn";

$msg="Password:$passrn";

$msg .="Username:$namern";

$msg .="Please change your password as soon as you logonrn";

$subject="From Admin re:Your Login Passwordrn";

}


We also deal with what happens when the SQL query fails to match the given username to a name in the database:


}else{

print "Your username is either spelled incorrect or does not exist, please try again";

exit;

}


If everything has gone well and no errors were encountered, we send the message containing the password to the given email address:

//4. Send password to user

if(mail($to,$subject,$msg,$from)){

print "Your password has been sent to <b>$em</b>" ;

}else{

print "could not send email";

}

}

}

?>


The user enters the information that is used by the script in an HTML form. The form itself is contained in a table and has the following code:


<form name="form1" method="post" action="../forgotten.php">

Please fill in the following:

<br>

<table width="445" border="0">

<tr>

<td width="187"><div align="left">Username</div></td>


The first element takes the username:


<td width="242"><input name="name" type="text" size="40"></td>

</tr>

<tr>

The second element takes the email address:

<td><div align="left">Email <font color="#FF0000" size="2">(password will be sent to this email address)</font> </div> </td>

<td><input name="mail" type="text" size="40">

And finally, the hidden element with the name value of “key” is also present:

<input type="hidden" name="key" /></td>

</tr>

<tr>

<td> <input name="submit" type="submit"> </td>

<td></td>

</tr>

</table>


In the next article we will look at some of the most common attacks on databases.

Google+ Comments

Google+ Comments