It is the intention of this article to discuss and generate further debate surrounding best practices
in developing database driven, session orientated dynamic websites and to present some adopted styles
and conventions. The ideas and approaches that are developed here could be realised with almost any
combination of server side scripting language and relational database.
Sessions and relational databases both offer the opportunity of storing information server side.
Sessions can help to maintain state while you navigate an internet website and relational databases
offer a medium for the permanent storage of information in a logical and consistent manner which
allows for structured queries to be carried out against that stored information.
During the design process the dilemmas that are often encountered are deciding how information should
be stored in a database and what information should be registered to the session.
What I would like to demonstrate now is a framework of cooperation between relational databases and
sessions which is both aesthetically appealing as well as productive this will be done by examining
features of a secure website.
First of all I would like to discuss in simple terms what it means for a table to be normalised.
A table is considered normalised if information is organised logically without inconsistencies and
there is minimal redundancy an example will help to demonstrate.
Consider the following simple normalised table called security, this table stores sensitive
information and is queried against to gain authorisation to a secure website.
username password access_level screen_name security_id
rstallman r5y8jv5f8 U richard 1
billgates 8yc2d42f5 X bill 2
linustorvalds h89x35f56 P linus 3
Notice that this security table encapsulates all that is security related there is no redundancy,
that is no unnecessary repetition and if you had to update a username or a password you have only to
make a single change somewhere. This is normalisation.Also note that all records here in the security
table have a unique identifier called security_id this in a important field called a "primary key".
To gain access to this secure internet website a user has to supply a username and password in a form
this then results in a php block being executed when the script actions itself :
<?php
if(isset($_POST['submit']) ) { // true if form has been submitted
session_start();
include("inhouse_functions.inc");
connect_database("secure_online"); // obtain access to database
$SQL=" SELECT security_id FROM security
WHERE username='$username' AND password= '$password' ";
$security = @mysql_query($SQL);
if ( mysql_num_rows($security) ) {
$row = mysql_fetch_array($security);
$security_id = $row['security_id'];
$sess_security_id = $security_id ;
session_register('sess_security_id');
header("location: entry_page.php") ;
}
// authorisation not successful show form again....
}
?>
<html>
..
<form action = "<? echo $PHP_SELF;?>" method="post">
... rest of the form to collect username,password and submit button
<type="hidden" name="submit" value=1>
</form>
</html>
All other pages on this secure internet website then can check to see that authentication has
occured by checking if the primary key $sess_security_id has been registered to the session :
<?php
if( !session_is_registered('sess_security_id') ) {
// the security_id key is registered with the session
// on authorisation
header("location: index.html "); // send to authentication page ..
}
?>
A nice feature here is that nothing sensitive is registered to the session and the value stored
has the potential of reconstructing any security information the subsequent scripts might demand such
as checking access level privileges (U,X or P) ....
<?php
$SQL=" SELECT access_level FROM security
WHERE security_id='$sess_security_id' ";
$security = mysql_query($SQL);
$row = mysql_fetch_array($security);
$access_level = $row['access_level'];
if($access_level == 'U') {
// content sutiable for access level U
} ..
....
?>
Lets suppose now we have another table called memos which stores memos written by users of this secure
website where access level privilges dictate who can update a memo.When this table is normalised it will
look something like this:
relSecurityID details date time memo_id
2 what is gnu/linux ? 020823 1426 1
6 heard of open source? 020823 1705 2
4 dependencies!!! 020824 0852 3
In this we don't need to store the access level privileges or the name of the person who writes/updates the
memo all we need to store is the security_id, stored under relSecurityID (foreign key) in this table,
because this allows us to potentially reconstruct any security information we may need.
For example suppose an authorised user is searching/scrolling through a screen of memo extracts on his
browser from which he can select to see the full memo by clicking on a hyperlink, it will be necessary to
trap the memo_id and pass it to the next page this will then allow to reconstruct the complete memo and
potentially any associated security related information through the foreign key relSecurityID:
<?php
$SQL=" SELECT details,date,time FROM memo ";
$memo = mysql_query($SQL);
while($row = mysql_fetch_array($memo) ) { // produce all memos extracts....
$details = $row['details'];
$date = $row['date']; .... // etc
?>
// display a particular memo extract..
// hyperlink...
<a href="update_memo.php?memo_id=<? echo $memo_id; ?>"> memo </a>
<? } ?>
On reaching the update memo page I would then consider registering the memo_id to the session
($sess_memo_id) as then you can potentially reconstruct that last memo read/updated using previous
techniques.
Once on this page you may need to determine whether the memo information displayed in a text box
is read only by checking access level privileges of the person who wrote the memo against those of the
person currently viewing the memo.
We have seen how to determine the access level privileges of the current authorised user, to determine
those of the user who wrote the message we constuct a query like the following:
<?php
$SQL = " SELECT access_level FROM security,memo
WHERE security.security_id = memo.relSecurityID
AND memo_id = '$sess_memo_id' " ;
?>
This query will join the information for the particular memo with its associated information in the
security table to determine access level privileges of the person who wrote the message ,other
related information could be extracted if required by extending the select statement.
Take time to understand the above SQL query, if you are not familiar to joins,
as extracting information from normalised tables in this way is an important skill to master,
________________________ ____________________________
| X security_id | | relSecurityID memo_id |
|________________________| |____________________________|
security record memo record
it's kind of like a game of dominoes where a particular security record can connect with many memo records
providing security_id and relSecurityID match !
To summarise then registering keys to the session seems a good practice as they serve to give a sense of
orientation to the user currently navigating your website.
All transient information which does not maintain state I prefer to pass from page to page until
it's no longer required, ie inserted into a table.
However this is a broad generalisation of how I go about doing what I do, sometimes I will register non key
information to the session such as 'screen_name' if every page shows the current logged in user and I will
sometimes introduce repetition/redundancy into a database table if I am sure that it will never have to be
updated manualy, ie some generic names in a table such as 'su' , 'administrator', etc...
Finally this has been an open exposition of how I go about doing what I do as a php/mysql developer,it
has not been my intention to indoctrinate anyone in best practices rather to open the debate on what people
consider to be good practices and what there rationale is.
All developers clearly needs to have a coherent system of beliefs, a platform from which they can begin
and make decisions and all belief systems stand or fall on whether on not they can produce concrete results,
at the moment this simple outlook of mine is proving helpful.
paulsbooker