creating a login script using PHP and MySQL

This article will go trough the process of creating a fairly simple login script using PHP. The script is database driven so there are some things we need to take into consideration, for example the possibility of doing SQL-injections. I also assume you at least have access to a webserver with PHP and MySQL.

We start by creating the file that holds the form, the login page. Nothing fancy here, just a regular form which points to the php file that’s going to handle the login process. Here is how I did it.

<form action=”login.php” method=”POST”>
Username:<br>
<input type=”text” name=”username”><br>
Password:<br>
<input type=”password” name=”password”><br>
<input type=”submit” name=”submit” value=”Login”>
</form>

Before going on to the php code, we need a place to store the users. In this case i’ll create a table named ‘users’. I mostly use PhpMyAdmin when i’m administrating my databases, but it’s easier to explain the table structure using SQL.

CREATE TABLE `users` (

`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`username` TEXT NOT NULL ,
`password` TEXT NOT NULL

)

That should be fairly straightforward, it simply creates a table with two text fields. That’s all we need, now i’ll go trough the php code. Create a file named login.php. We need to open a connection to the database server and locate the database first of all.

Open connection to the database

mysql_connect(“localhost”, “username”, “password”) or die(“Cannot open connection: ” . mysql_error());
mysql_select_db(“database”) or die(“Database not found”);

After we’ve connected to the database, we need to check if the posted username and password is equal to the stored username/password in the ‘users’ table

Get user data and compare it with the stored users

$username = $_POST[‘username’];
$password = $_POST[‘password’];
$query = mysql_query(“SELECT * FROM users WHERE username=’$username’ AND password=’$password'”);

The problem with the above script, is that it’s completely vulnerable to SQL-injections. The user can input anything he/she wants in the form and post it. Imagine if the user then types ‘ OR ” = ‘ as the password which is one of the most common SQL injections in this kind of script. Then the SQL-query would look like this.

$query = mysql_query(“SELECT * FROM users WHERE username=’$username’ AND password=” OR ” = ” “);

And the user would be able to log in without typing a password.

Protect against SQL injections
The good news is that SQL injections like these are pretty easy to protect against. There are many ways to protect against them actually, but the most recommended way is to use the database’s own escape function. MySQL for example has the function named mysql_real_escape_string. A good way to describe this function is that it makes the data safe to use in the SQL query. It adds backslashes to characters that may pose a security risk, for example single quotes and regular quotes.

If we apply that to the code before, we will get the following.

$username = mysql_real_escape_string($_POST[‘username’]);
$password = mysql_real_escape_string($_POST[‘password’]);
$query = mysql_query(“SELECT * FROM users WHERE username=’$username’ AND password=’$password'”);

Next, we will check if the SQL query returns any results. If it does, the username and password is correct and we will get logged in with the specified username. If not, we will just be redirected to the login page again. Here is how I did it.

if (mysql_num_rows($query) == 0) {
header(“location: index.php”);
exit;
}

$_SESSION[‘user’] = $username;

header(“location: index.php”);

This is about how simple it can get, we store the username into a session variable. This session variable is what we use to check if the client is logged in everywhere on the website. Don’t forget to put session_start() at the top of the login file, else it won’t work.

The code is safe against SQL injections right now but we can still improve the login script by adding encryption to the password and a salt. This is quite important, it’s not very smart to store the passwords in clear text. I’ve used sha1 and a very simple salt function. This assumes that all the passwords are stored with the same salt function so the comparison will be correct. Here is the complete code.

login.php

<?php
session_start();

function salt($pw) {
$salt = “This comment should suffice as salt.”;
return sha1($salt.$pw);
}

if (isset($_POST[‘submit’])) {
mysql_connect(“localhost”, “root”, “”) or die(“Cannot open connection: ” . mysql_error());
mysql_select_db(“experiment”) or die(“Database not found”);

$username = mysql_real_escape_string($_POST[‘username’]);
$password = mysql_real_escape_string($_POST[‘password’]);
$password = salt($password);

$query = mysql_query(“SELECT * FROM users WHERE username=’$username’ AND password=’$password'”);

if (mysql_num_rows($query) == 0) {
header(“location: index.php”);
exit;
}

$_SESSION[‘user’] = $username;

header(“location: index.php”);
}
?>

index.php

<?php
session_start();

if (isset($_GET[‘logout’])) {
session_unset();
session_destroy();
header(“location: index.php”);
exit;
}
?>
<html>
<head>
<meta http-equiv=”content-type” content=”text/html; charset=iso-8859-1″>
<title>Login Script</title>
<style type=”text/css”>
body { font-size: 12px; }
input { font-size: 12px; }
</style>
</head>

<body>

<?php
if (isset($_SESSION[‘user’])) {
echo “You are logged in!”;
echo “<br><a href=’?logout’>Log out</a>”;
} else {
?>

<h2>Member Login</h2>

<form action=”login.php” method=”POST”>
Username:<br>
<input type=”text” name=”username”><br>
Password:<br>
<input type=”password” name=”password”><br>
<input type=”submit” name=”submit” value=”Login”>
</form>

<?php
}
?>

</body>
</html>