So far we’ve created some basic PHP pages and added some simple authentication. Today we’re going to going to build on that by adding database support. This will allow us to add proper authentication to our application and start saving tasks. I should also note that I am currently writing PHP in-line and not using functions (or object orientated PHP) I will tidy this up in the next tutorial and spend more time explaining it and what it’s benefits are.
Last week
Last week we installed XAMPP, so you should already have MySQL installed and ready to go. If not, please check back on the previous tutorial here. If you decided to use IIS last week, you can download MySQL using the Microsoft Web Platform Installer, you can either install phpMyAdmin or download the MySQL Workbench tool
If you have any trouble installing or configuring any of the above, please leave a comment below and I will try and help you get sorted.
Creating our schema
Assuming you’ve got MySQL and phpMyAdmin installed, we can start creating our schema. So far we know that we will need at least two tables. Our user table for logging in and our tasks table. We also know that a user owns a specific task, so that tells us we’ll also need to use a Foreign Key to define the relationship.
First we will need a database to create our tables. A basic script to get us going would look something like this. It creates the database ‘todo’ and creates a ‘users’ and a ‘tasks’ table. It also defines the foreign key in the ‘tasks’ table for the ‘user_id’ column.
CREATE DATABASE todo;
USE todo;
CREATE TABLE IF NOT EXISTS `users` (
`user_id` bigint(20) unsigned NOT NULL auto_increment,
`user_login` varchar(100) NOT NULL,
`user_password` varchar(64) NOT NULL,
`user_firstname` varchar(50) NOT NULL,
`user_surname` varchar(50) NOT NULL,
`user_email` varchar(100) NOT NULL,
`user_registered` datetime NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY (`user_id`),
KEY `idx_user_login_key` (`user_login`)
) DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
CREATE TABLE IF NOT EXISTS `tasks` (
`task_id` bigint(20) unsigned NOT NULL auto_increment,
`user_id` bigint(20) NOT NULL REFERENCES `users`(`user_id`),
`task_name` varchar(60) NOT NULL,
`task_priority` tinyint(2) NOT NULL default '2',
`task_color` varchar(7) NOT NULL default '#ffffff',
`task_description` varchar(150) NULL,
`task_attendees` varchar(4000) NULL,
`task_date` datetime NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY (`task_id`),
KEY `idx_task_name_key` (`task_name`)
) DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
To create our first user we need to insert a row into the ‘users’ table. The sql below will define a user with the login ‘developerdrive’. If you have a look at the SQL you can also see it is calling the PASSWORD function. This is a built in function for MySQL that uses the the SHA1 function to hash a string. It’s recommended that you also salt a password before hashing it. Salting is the appending (or prepending) of a piece of randomly generated data to the original password (like ‘password’ + ‘dhskhfkjhsdkfhskad’), this increases the randomization of the password and making it more difficult to break. If you are salting the password, you will also need to store the salt separately to the password.
Feel free to update the details below to your own, don’t forget your password though!
INSERT INTO `users` ( `user_login`, `user_password`, `user_firstname`,
`user_surname`, `user_email`, `user_registered` )
SELECT 'developerdrive', PASSWORD('to-do-password'), 'developer',
'drive', 'developer@email.com', NOW();
The entire script (schema.sql) including any SQL examples is included in the tutorial download at the end
Authentication
Now that we have our users table and our own login, we need to update our login.php to connect to the database and check the provided login against the ‘users’ table. To do that we are going to use the MySQLi library. To create a database connection using MySQLi, you need to provide the library the MySQL database details. The default XAMPP mysql credentials are a username of “root” and a blank password. Since we’re using the local database, you specify “localhost” as the server and “todo” as the name of the database.
$connection = new mysqli("localhost", "root", "", "todo");
if (mysqli_connect_errno()) {
die(sprintf("Connect failed: %s\n", mysqli_connect_error()));
}
We want to be able to use this connection all over our application, so we’re going to create a new file called database.php and put the following PHP in it.
<?php
global $connection;
if ( isset( $connection ) )
return;
$connection = new mysqli("localhost", "root", "", "todo");
if (mysqli_connect_errno()) {
die(sprintf("Connect failed: %s\n", mysqli_connect_error()));
}
?>
We can now update out login.php file to require ‘database.php’ and we can also add our first SQL query. We’re going to use parameterized SQL and pass in our $username and $password variables from before. Parameterized SQL helps prevent attackers overriding your SQL using what is called a SQL Injection attack. A SQL injection attack can be quiet serious and can result in the complete loss of your data.
The SQL statement queries the ‘users’ table for a ‘user_id’ that has a ‘user_login’ and ‘user_password’ that matches the provided values. We wrap the password parameter in the PASSWORD function to make sure we only compare the hash values. If the query returns a user_id we know that the correct username and password have been provided.
if ($_SERVER['REQUEST_METHOD'] == 'POST') {
require_once('database.php');
if(!empty($_POST["username"]) && !empty($_POST["password"])) {
$username = $_POST["username"];
$password = $_POST["password"];
$query = $connection->prepare("SELECT `user_id` FROM `users` WHERE `user_login` = ? and `user_password` = PASSWORD(?)");
$query->bind_param("ss", $username, $password);
$query->execute();
$query->bind_result($userid);
$query->fetch();
$query->close();
if(!empty($userid)) {
session_start();
$_SESSION["authenticated"] = 'true';
header('Location: index.php');
}
else {
header('Location: login.php');
}
} else {
header('Location: login.php');
}
}
The function that actually sets the parameters in the query is the bind_param function. The first parameter (“ss” above) defines the number of parameters and their data types:
- i for integer numbers e.g. 9999
- d for double or decimal numbers e.g. 10.99
- s for string e.g. “testing”
- b for binary or blob values
The bind_result function binds a variable to the result column in the same ordinal position. In the above example I am selecting only the ‘user_id’ column, if I was selecting col1, col2, col3 I could use bind_result($val1, $val2, $val3) to get their values.
While this has improved security, we still suffer from the fact that the authenticated flag is stored in session. It would be a much better idea to store the session in the database along with the expiry date (in one hour) and the IP address the user logged in from. To do this, all we have to do is create a new ‘sessions’ table and insert a row that can be checked by the authenticate.php file
CREATE TABLE IF NOT EXISTS `sessions` (
`session_id` bigint(20) unsigned NOT NULL auto_increment,
`user_id` bigint(20) NOT NULL REFERENCES `users`(`user_id`),
`session_key` varchar(60) NOT NULL,
`session_address` varchar(100) NOT NULL,
`session_useragent` varchar(200) NOT NULL,
`session_expires` datetime NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY (`session_id`),
KEY `idx_session_key` (`session_key`)
) DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
Here’s the full login.php content
<?php
$username = null;
$password = null;
if ($_SERVER['REQUEST_METHOD'] == 'POST') {
require_once('database.php');
if(!empty($_POST["username"]) && !empty($_POST["password"])) {
$username = $_POST["username"];
$password = $_POST["password"];
$query = $connection->prepare("SELECT `user_id` FROM `users` WHERE `user_login` = ? and `user_password` = PASSWORD(?)");
$query->bind_param("ss", $username, $password);
$query->execute();
$query->bind_result($userid);
$query->fetch();
$query->close();
if(!empty($userid)) {
session_start();
$session_key = session_id();
$query = $connection->prepare("INSERT INTO `sessions` ( `user_id`, `session_key`, `session_address`, `session_useragent`, `session_expires`) VALUES ( ?, ?, ?, ?, DATE_ADD(NOW(),INTERVAL 1 HOUR) );");
$query->bind_param("isss", $userid, $session_key, $_SERVER['REMOTE_ADDR'], $_SERVER['HTTP_USER_AGENT'] );
$query->execute();
$query->close();
header('Location: index.php');
}
else {
header('Location: login.php');
}
} else {
header('Location: login.php');
}
} else {
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta http-equiv="content-type" content="text/html; charset=utf-8">
<title>Creating a simple to-do application - Part 1</title>
<meta name="viewport" content="width=device-width, initial-scale=1">
</head>
<body>
<div id="page">
<!-- [banner] -->
<header id="banner">
<hgroup>
<h1>Login</h1>
</hgroup>
</header>
<!-- [content] -->
<section id="content">
<form id="login" method="post">
<label for="username">Username:</label>
<input id="username" name="username" type="text" required>
<label for="password">Password:</label>
<input id="password" name="password" type="password" required>
<br />
<input type="submit" value="Login">
</form>
</section>
<!-- [/content] -->
<footer id="footer">
<details>
<summary>Copyright 2013</summary>
<p>Jonathan Schnittger. All Rights Reserved.</p>
</details>
</footer>
</div>
<!-- [/page] -->
</body>
</html>
<?php } ?>
Now we can update authenticate.php to check for the correct session. We do this by querying for the ‘session_key’, ‘session_address’ and the ‘session_agent’. This way any changes to the session will be detected and the user will be asked to re-authenticate. We also confirm that the session has not expired.
<?php
session_start();
$session_key = session_id();
require_once('database.php');
$query = $connection->prepare("SELECT `session_id`, `user_id` FROM `sessions` WHERE `session_key` = ? AND `session_address` = ? AND `session_useragent` = ? AND `session_expires` > NOW();");
$query->bind_param("sss", $session_key, $_SERVER['REMOTE_ADDR'], $_SERVER['HTTP_USER_AGENT']);
$query->execute();
$query->bind_result($session_id, $user_id);
$query->fetch();
$query->close();
if(empty($session_id)) {
header('Location: login.php');
}
?>
It’s probably a good idea to update the session expiry date every time the user does something, this way we’re not forcing them to login every hour. If you add the following php to the end of authenticate.php the session expiry will be extended for another hour.
$query = $connection->prepare("UPDATE `sessions` SET `session_expires` = DATE_ADD(NOW(),INTERVAL 1 HOUR) WHERE `session_id` = ?;");
$query->bind_param("i", $session_id );
$query->execute();
$query->close();
It’s also important to note that certain browsers share the session_id across tabs. This means that because we’re storing the session in the database, if the user opens up a new tab they will automatically be logged in. If this is not the behavior you want, you can use the session_regenerate_id function to generate a new session_id
Listing and saving tasks
Now that we have authentication out of the way, we can save our first task and view a list of existing tasks. First up we’ll update index.php to be able to list any existing tutorials. We need to ensure that only the tasks for the logged in user are shown. To do this we’ll use the $user_id variable we set in authentication.php. The following code is from between the table tbody tags in index.php
<?php
global $user_id;
$query = $connection->prepare("SELECT `task_id`, `task_name`, `task_priority`, `task_color`, `task_description`, `task_attendees`, `task_date` FROM `tasks` WHERE `user_id` = ?");
$query->bind_param("i", $user_id);
$query->execute();
$query->bind_result($id, $name, $priority, $color, $description, $attendees, $date);
while ($query->fetch()) {
echo '<tr id="task-' . $id . '"><th scope="row" style="background-color:' . $color . '"><input type="checkbox" /></th><td>' . $date . '</td><td>' . $priority . '</td><td>' . $name . '</td><td>' . $description . '</td><td>' . $attendees . '</td></tr>';
}
$query->close();
?>
Next up is actually saving our first task. To do this we need to update submit.php. Here we’re going to add an insert statement once we’ve validated the data submitted.
<?php
require_once('authenticate.php');
$name = null;
$date = date('c');
$desc = '';
$email = '';
$priority = 2;
$color = '#ffffff';
$result = array();
$result['error'] = array();
if (!empty($_POST["new-task-name"]))
$name = $_POST["new-task-name"];
else
array_push($result['error'], 'Please specify a name for your task');
if (!empty($_POST["new-task-date"]))
$date = new DateTime($_POST["new-task-date"]);
else
array_push($result['error'], 'Please specify a date for your task');
if (!empty($_POST["new-task-desc"]))
$desc = $_POST["new-task-desc"];
if (!empty($_POST["new-task-email"]))
$email = explode(',', $_POST["new-task-email"]);
if (!empty($_POST["new-task-priority"]))
$priority = intval($_POST["new-task-priority"]);
else
array_push($result['error'], 'Please specify a valid priority for your task');
if (!empty($_POST["new-task-color"]))
$color = $_POST["new-task-color"];
if(isset($result['error']) && count($result['error']) > 0){
$result['success'] = false;
} else {
if(!empty($email)){
$email = implode(',', $email);
}
$date = $date->format('c');
$query = $connection->prepare("INSERT INTO `tasks` ( `user_id`, `task_name`, `task_priority`, `task_color`, `task_description`, `task_attendees`, `task_date` ) VALUES ( ?, ?, ?, ?, ?, ?, ? );");
$query->bind_param("isissss", $user_id, $name, $priority, $color, $desc, $email, $date );
$query->execute();
$result['id'] = $query->insert_id;
$query->close();
$result['success'] = true;
$result['name'] = $name;
$result['date'] = $date;
$result['desc'] = $desc;
$result['email'] = $email;
$result['priority'] = $priority;
$result['color'] = $color;
}
echo json_encode($result);
?>
Here you can see that I’m returning the $query->insert_id in the $result array. This is the ‘task_id’ created by when we inserted the new task
And there you have it, we’ve got some of the basic functionality done. We can now log in and create or view existing tasks. Next up I’ll be refactoring the code to use functions and adding better error handling. We’ll also be looking at adding a cron job to tidy up expired sessions and perhaps we can add in some statistics to see what features or pages our users are looking at