Create a Portfolio Client Area Using PHP and MySQL: Part 2

Continuing from Part 1, this tutorial will go over some security issues for our sign up form. When dealing with databases, it is important to make sure the client’s information is secure. The tutorial will also go over some ways to make sure the user enters in correct data.In the last tutorial we left off with a solid database structure, a signup form that successfully input data into our database, and an overall plan for the project. Read up on all the other parts below:

As promised, in today’s tutorial we’re going to go over security when working with PHP and databases, and apply some security issues specifically to our sign up form.

This tutorial will go over SQL injections, basic HTML form security, md5 password encryption, and more.

Form Security

This will be a bit of a mini HTML tutorial and very basic, but it must be covered because many people may be working with secure forms for the first time. The first step to securing our sign up form is to hide the password on the HTML form itself:

Hidden Password Field

Doing this will help secure the password from being stolen off-screen. It is a bit of an obvious step, but an essential one nonetheless.

To hide the password in the HTML form, simply change the “type” attribute for the password field to “password” instead of “text”.

<form action="signup.php" method="post">
	Username:
	<input name="username" type="text" />

	Password:
	<input name="password" type="password" />

	Email:
	<input name="email" type="text" />

	PayPal Address:
	<input name="paypal" type="text" />

	<input type="submit" />
</form>

Password Encryption

The second form of security is to encrypt the password. Looking at our current clients in the database, we can see the password field is completely left open for administrators, or other viewers of the database to see. This is, of course, a bad practice to keep it in plain sight like this.

Md5 Encryption

To solve our problem, we’re going to encrypt the password with the md5() function in PHP. This will allow us to encrypt a password that not even an administrator can decrypt. Easily enough, we can just place our password variable in as the parameter for the md5() function: md5($password).

<?php 

// Require the file that connect to the database.
// It is good practice to put the database connection
// information in a separate file. require('db.php'); 

// Since we used the post method in our form, we can
// securely call our data using the $_POST predefined
// variable, with parameters specified by the name
// attribute in our form.
$username = $_POST["username"];
$password = $_POST["password"];
 	$pw = md5($password);
$email = $_POST["email"];
$paypal = $_POST["paypal"]; 

// Finally, we use a MYSQL_QUERY to insert our information
// into the database. INSERT INTO defines what fields we want
// to insert information, and VALUES defines the values that
// we are entering.
$result= MYSQL_QUERY(
 		 "INSERT INTO clients (client_ID, username, password, email, paypal)".
 		 "VALUES ('', '$username', '$pw', '$email', '$paypal')"
 		 ); 		 

// Notice in VALUES, we leave the first field blank: ' '
// This is because our database automatically assigns this
// value to each client. 

echo "Thank you for signing up.";
?>

So what happens here is, first, the password is taken from the POST method via our form, and put into the variable $password. Then, we create a new variable, $pw, and put the encrypted $password string into it.

As you’ll notice below, in our query to insert the information in the database, we’ve inserted the encrypted version of the password: $pw.

Let’s test our or form and see if it worked:

Md5

Looks like it did! Our password is now encrypted and cannot be read in plain text.

SQL Injection

SQL injection is a type of malicious code that can be used to manipulate an unsecured database by entering some well thought-out data into a form.

For example, below is a basic SQL statement that selects data from “users” where the “name” is equal to whatever was input into the form field named “formfield.”

SELECT * FROM users WHERE name = '$_POST(formfield)';

If someone were to enter in a normal name, say “Kayla” for example, the resulting PHP would execute the following.

SELECT * FROM users WHERE name = 'Kayla';

It would select the name equal to “Kayla” in the table “users”. However, malicious code entered into the form could be this:

a';DROP TABLE users; SELECT * FROM data WHERE name LIKE '%

This seems like a bunch of nonsense until we put it into our SQL code:

SELECT * FROM users WHERE name = 'a';DROP TABLE users; SELECT * FROM DATA WHERE name LIKE '%';

This creates an entirely different purpose for the code, which finds a name “a”, drops the “users” table, and selects all the data (%). Anyone can see how this can be very destructive.

Fortunately, the fix is very easy. We just need to add mysql_real_escape_string() around each of our variables associated with an input field in the form. For an example, the updated sign up form is below.

<?php

// Require the file that connect to the database.
// It is good practice to put the database connection
// information in a separate file.
require('db.php');

// Since we used the post method in our form, we can
// securely call our data using the $_POST predefined
// variable, with parameters specified by the name
// attribute in our form.
$username = mysql_real_escape_string($_POST["username"]);
$password = mysql_real_escape_string($_POST["password"]);
	$pw = md5($password);
$email = mysql_real_escape_string($_POST["email"]);
$paypal = mysql_real_escape_string($_POST["paypal"]);

// Finally, we use a MYSQL_QUERY to insert our information
// into the database. INSERT INTO defines what fields we want
// to insert information, and VALUES defines the values that
// we are entering.
$result= MYSQL_QUERY(
		 "INSERT INTO clients (client_ID, username, password, email, paypal)".
		 "VALUES ('', '$username', '$pw', '$email', '$paypal')"
		 );

// Notice in VALUES, we leave the first field blank: ''
// This is because our database automatically assigns this
// value to each client.

echo "Thank you for signing up.";

?>

Check for Invalid Characters

The next step is to check for any invalid characters that may be inserted into any of the fields. Because a username & password should be allowed different characters than an email, we’ll create two separate pieces of code to handle them. The first we need to create is to handle invalid characters for the username and password.

To specify, let’s make sure the client can only enter in letters a-z, A-Z, or numbers 1-9.

We can use regular expressions (/^[a-zA-Z0-9]+$/ in the code below) to identify what characters we’d like to allow, and use the preg_match() function to compare it to whatever our string is ($str). Our string in this case would be either the username or password.

!preg_match('/^[a-zA-Z0-9]+$/', $str);

The exclamation point in front of the function (!) means NOT, so the statement above is going to return true if the $str and regular expressions do not match, and false if everything is ok. So, to create some workable code to deal with this, we can use a basic if/else statement.

if(!preg_match('/^[a-zA-Z0-9]+$/', $username)) // If our username is invalid
{
     echo "The username can only contain letters or numbers."; // Tell the user
}

if(!preg_match('/^[a-zA-Z0-9]+$/', $password)) // If our password is invalid
{
     echo "The password can only contain letters or numbers."; // Tell the user
}

Similar to checking for invalid characters, we’ll need to do the same for an email. The regular expressions have to be different, however, to make sure the email can use the @ symbol, and to make sure everything is in the right spot.

// If our email or PayPal addresses are invalid
if(!preg_match("^[_a-z0-9-]+(.[_a-z0-9-]+)*@[a-z0-9-]+(.[a-z0-9-]+)*(.[a-z]{2,3})$", $email))
{
     echo "The email or PayPal address you entered is invalid."; // Tell the user
}

To put this all into action, we need to combine the three if statements. In basic pseudo code, here is what we need to do:

If the username is invalid, tell the user.
Or else if the password is invalid, tell the user.
Or else if the email or PayPal address is invalid, tell the user.
Else, if everything is ok, insert the data into the database and tell the user they’ve successfully signed up.

// If the username is invalid, tell the user.
// Or else if the password is invalid, tell the user.
// Or else if the email or PayPal address is invalid, tell the user.
// Else, if everything is ok, insert the data into the database and tell
   // the user they’ve successfully signed up.

if(!preg_match('/^[a-zA-Z0-9]+$/', $username)) // If our username is invalid
{
     return "The username can only contain letters or numbers."; // Tell the user
}

else if(!preg_match('/^[a-zA-Z0-9]+$/', $password)) // If our password is invalid
{
     return "The password can only contain letters or numbers."; // Tell the user
}
// If our email or PayPal addresses are invalid
else if(!eregi("^[_a-z0-9-]+(.[_a-z0-9-]+)*@[a-z0-9-]+(.[a-z0-9-]+)*(.[a-z]{2,3})$", $email))
{
     return "The email or PayPal address you entered is invalid."; // Tell the user
}
else{

// Inserts the data into the database
$result= MYSQL_QUERY(
		 "INSERT INTO clients (client_ID, username, password, email, paypal)".
		 "VALUES ('', '$username', '$pw', '$email', '$paypal')"
		 );
echo "Thank you for signing up.";

}

Check to See if the Username has Been Taken

The last thing we’ll do is check to see if the username has been taken. In the code below, we use a simple MySQL query to get an array of clients. We then save that in the variable $result.

Finally, with a while statement, we go through each instance of the array, $result, naming each instance $row. Inside the statement we can check to see if our current username and the username we’re at in the database ($row) match.

If they do, we create a variable and call it true. Otherwise, we create the same variable and make it false.

//Check to see if the username is already taken
$query = "SELECT * FROM clients";
$result = mysql_query($query) or die(mysql_error()); // Get an array with the clients
while($row = mysql_fetch_array($result)){ // For each instance, check the username
	if($row['username'] == $username){
		$usernameTaken = true;
	}else{$usernameTaken = false;}
}

Now we can add a few simple lines to our if/else if statement to check for duplicate usernames too.

if($usernameTaken)
{
	echo "That username has been taken.";
}

Simply read, the above code says: If the username has been taken ($usernameTaken), echo out that it has been already taken.

Our Final Signup.php

Below is our final signup.php page for your convenience.

<?php

require('db.php');

// Create the variables, while encrypting the password and
// preventing SQL injection
$username = mysql_real_escape_string($_POST["username"]);
$password = mysql_real_escape_string($_POST["password"]);
	$pw = md5($password);
$email = mysql_real_escape_string($_POST["email"]);
$paypal = mysql_real_escape_string($_POST["paypal"]);

//Check to see if the username is already taken
$query = "SELECT * FROM clients";
$result = mysql_query($query) or die(mysql_error()); // Get an array with the clients
while($row = mysql_fetch_array($result)){ // For each instance, check the username
	if($row['username'] == $username){
		$usernameTaken = true;
	}else{$usernameTaken = false;}
}

// If the username is invalid, tell the user.
// Or else if the password is invalid, tell the user.
// Or else if the email or PayPal address is invalid, tell the user.
// Else, if everything is ok, insert the data into the database and tell
   // the user they’ve successfully signed up.

if($usernameTaken)
{
	echo "That username has been taken.";
}

else if(!preg_match('/^[a-zA-Z0-9]+$/', $username)) // If our username is invalid
{
     echo "The username can only contain letters or numbers."; // Tell the user
}

else if(!preg_match('/^[a-zA-Z0-9]+$/', $password)) // If our password is invalid
{
     echo "The password can only contain letters or numbers."; // Tell the user
}
// If our email or PayPal addresses are invalid
else if(!eregi("^[_a-z0-9-]+(.[_a-z0-9-]+)*@[a-z0-9-]+(.[a-z0-9-]+)*(.[a-z]{2,3})$", $email))
{
     echo "The email or PayPal address you entered is invalid."; // Tell the user
}
else{

// Inserts the data into the database
$result= MYSQL_QUERY(
		 "INSERT INTO clients (client_ID, username, password, email, paypal)".
		 "VALUES ('', '$username', '$pw', '$email', '$paypal')"
		 );
echo "Thank you for signing up.";

}

?>

Wrapping Up

Security is probably one of the most boring issues in web development, but of course, one of the most important. For any other experienced programmers out there, let me know if I missed something with the security, and I can discuss it in the next tutorial.

Otherwise, next time we’ll be going over creating the log in form, PHP sessions, and creating a profiile page for the client.

Written By Kayla

Kayla Knight is a 20 year old college student, part-time web developer, freelancer, and blogger. Webitect is where she spends too much of her freetime, sharing interesting finds and valuable resources. Be sure to check out her portfolio.

13 Comments

  1. Nick

    July 8th, 2009 at 06:07 pm

    Great stuff! I’m fairly new to php, so although I already knew the material it was really helpful to see it compiled so concisely and thoughtfully. By the way, I saw you used preg_match() for validating the password and username, but eregi() for validating the paypal address, and I wondered what the difference between the two functions is. Thanks again, and keep up the good work.

  2. Kayla

    July 8th, 2009 at 06:57 pm

    @Nick

    That was actually a mistake on my part, and I’ve changed the one with eregi() to preg_match(). Essentially, they are the same, which is why I easily grabbed both when collecting snippits for this tutorial, and had my tutorial still work. They are both functions to compare regular expressions to a string.

    There are a few minor differences, the most important in our case being that preg_match() is claimed to be a bit faster. I found a great thread explaining a bit more in detail if you’re interested:
    http://www.phpbuilder.com/board/showthread.php?t=10351963

    Thanks for pointing that out Nick!

  3. John

    July 15th, 2009 at 03:49 pm

    Very nice tutorial so far. These client scripting tuts are becoming fairly common, but this one is quite good and explained well. The only thing I’d like to see is a zip of files so far. That’s always nice on tut series that are broke up. Either way, keep the PHP tuts coming. =D

  4. Michal Kopanski

    July 19th, 2009 at 11:29 pm

    Very nice tutorial! I’m looking forward to part 3.

    Regards,

    Michal Kopanski

  5. charles

    July 20th, 2009 at 12:08 pm

    Your clients db and table is not so secure. Suppose I download the page db.php and read it in my development environment (for example macromedia dreamweaver mx), then I will be able to see the code:

    ”
    // Require the file that connect to the database.
    // It is good practice to put the database connection
    // information in a separate file. require(‘db.php’);

    ”
    From this code I have your login information to perhaps one of your most critical db (clients database and your table called clients). I can then do all the mischief with it. Yes I wont be able to read the MD5 encrypted pass words for me to access your application, but neither will you after I have been very mischievous with the clients table.

    Now, how can you make this really secure? This is also my problem.

  6. Bruno Correia

    July 21st, 2009 at 04:09 am

    Kayla,

    A quick look at this tutorial, and I could add the following suggestions ( personal preferences ).

    1. Much easier to user Filters than write preg_match like so ( ‘email’ => FILTER_VALIDATE_EMAIL ). More info on filters can be found at php.net/filters

    2. There is no need in this instance to write all the field names: “INSERT INTO clients (client_ID, username, password, email, paypal)” Instead, you can simply write “INSERT INTO clients VALUES …

    3. A date created ( DATETIME ) field is usually recommend.

    4. md5 works well, however sha1 is more secure.

  7. Bruno Correia

    July 21st, 2009 at 04:13 am

    Correction – On my comment number 1 above, the correct URL is php.net/filter and NOT php.net/filters

  8. Stuart Pringle

    July 22nd, 2009 at 11:36 pm

    One thing I noted was that you didn’t use htmlentities at all… So people can still mess around with html edits of things to the site by making a username like “randomname” and turn everything after their name is displayed into bold font face, or… Yeah, other things as well.

    I’m sure you know this, but the way to ensure that doesn’t happen is to use “htmlentities($variable);”… You can even use it on your “mysql_real_escape_string();” tag, making “htmlentities(mysql_real_escape_string($variable));”

    Just a thought.

  9. Stuart Pringle

    July 22nd, 2009 at 11:38 pm

    Err, okay, my last comment proved my point somewhat: I had said “<randomname” as the username in which someone could mess around with things.

  10. Stuart Pringle

    July 22nd, 2009 at 11:40 pm

    Correction: “<b>randomusername”

    Apologies for the… Triple post.

  11. Derek

    July 26th, 2009 at 11:26 pm

    Not to bash on you Kayla lol, but you have an unnecessary variable in your code….

    $password = mysql_real_escape_string($_POST["password"]);
    $pw = md5($password);

    you can do this ;)

    $password = mysql_real_escape_string(md5($_POST["password"]));

    save some space + typing…. may I ask why you use double quotes within your $_POST[] ???? Nothing serious, I personally think it looks neater with single quotes eg: $_POST['password']

  12. Vector

    September 6th, 2009 at 10:36 pm

    great tut.
    i have had a few isues and had to re-write a few areas but outher than tht its been good. all i hoped was tht there was a littel bit more like downloads, cart ect.. also please could you notify me when or if your making a tutorial on the admin panel side thankyou