Authentication System Design Pattern

The following are PHP scripts that can serve as design pattern for an application authentication system.

This is the "login.php" file that serves as the HTML login form.

<?php
echo "
<center>
<form method='post' action='authenticate.php'>
<fieldset>
<legend> Login </legend>
<table border='0'>
<tr>
<td rowspan='3'><img src='../img/emblem-keys.png'></td>
<td>Username</td><td><input type='text' name='username'></td>
</tr>
<tr>
<td>Password</td><td><input type='password' name='wordpass'></td>
</tr>
<tr>
<td>   </td><td><input type='submit' value='Login'><input type='reset' value='Reset'</td>
</tr>
</table>
</fieldset>
</form>
";
?>

This is the "authenticate.php" file that is executed after the HTML login form.

<?php
session_start();
include_once('db_postgresql.inc.php');

$sql1 = "SELECT COUNT(*) AS numfound FROM webappuser WHERE username='{$_POST['username']}' AND wordpass='{$_POST['wordpass']}' AND access_level='1' AND record_state='1'";
$result1 = $con->query(''.$sql1.'');

// Login Logic
$my_array1 = $result1->fetch(PDO::FETCH_ASSOC);
if ( $my_array1 ['numfound'] < 1 ) // Login Failed
	{
	$delay = 0;
	$url = "unauthorized.php";
	echo "<meta http-equiv='REFRESH' content='$delay;url=$url'>";
	exit;
	}

// Get the login information from the user	
$sql2 = "SELECT username, wordpass, access_leve, record_state FROM webappuser WHERE username='{$_POST['username']}' AND wordpass='{$_POST['wordpass']}' AND access_level='1' AND record_state='1'";
$result2 = $con->query(''.$sql2.'');
$my_array2 = $result2->fetch(PDO::FETCH_ASSOC);

// Register authentication information in the session
$username = $my_array2['username'];
$wordpass = $my_array2['wordpass'];
$access_level = $my_array2['access_level'];
$_SESSION["u_id"] = $u_id;
$_SESSION["username"] = $username;
$_SESSION["wordpass"] = $wordpass; 
$_SESSION["access_level"] = $access_level;

// Go to the welcome page
$delay = 0;
$url = "welcome.php";
echo "<meta http-equiv='REFRESH' content='$delay;url=$url'>";
?>

This is the "unauthorized.php" file that is called by the authentication system if username and password does not match.

<?php
session_destroy();
echo "
<center>
<form method='post' action='authenticate.php'>
<fieldset>
<legend> Login </legend>
<table border='0'>
<tr>
<td rowspan='3'><img src='../img/emblem-keys.png'></td>
<td>Username</td><td><input type='text' name='username'></td>
</tr>
<tr>
<td>Password</td><td><input type='password' name='wordpass'></td>
</tr>
<tr>
<td>   </td><td><input type='submit' value='Login'><input type='reset' value='Reset'</td>
</tr>
</table>
</fieldset>
</form>
<br>
<font color='red'><blink><b>Unauthorized ...</b></blink></font>
</center>
";
?>

This is the "logout.php" file that is called when the user clicks on a logout button.

<?php
session_destroy();
echo "
<center>
<form method='post' action='authenticate.php'>
<fieldset>
<legend> Login </legend>
<table border='0'>
<tr>
<td rowspan='3'><img src='../img/emblem-keys.png'></td>
<td>Username</td><td><input type='text' name='username'></td>
</tr>
<tr>
<td>Password</td><td><input type='password' name='wordpass'></td>
</tr>
<tr>
<td>   </td><td><input type='submit' value='Login'><input type='reset' value='Reset'</td>
</tr>
</table>
</fieldset>
</form>
<br>
<font color='red'><blink><b>You have been logged out...</b></blink></font>
</center>
";
?>

This is the "db_postgresql.inc.php" database connection file that is included in PHP page whenever it needs to connect to the database. The assumption here is the application is connected to a PostgreSQL database system. You can change this to whatever database brands you prefer. Furthermore, the database named "mydb" will have a table named "webappuser" with a minimum of four fields, namely: (i) username, (ii) wordpass, (iii) access_level, (iv) record_state. In like manner, you can modify this to suit your needs.

<?php
$host = "localhost";
$user = "postgres";
$pw = "wordpass123";
$db = "mydb";
# Using pg PDO
# Connection Check
try {
    $con = new PDO('pgsql:host='.$host.';dbname='.$db.'', $user, $pw );
    $con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    #echo "Database Status:  <font color='green'><blink><b>Connected...</b></blink></font><hr />";
} catch(PDOException $e) {
    echo 'ERROR: ' . $e->getMessage();
}
?>

This is the "secure.php" file that can be added at the top of any of the PHP file that you want protected from unauthorized users.

<?php
ini_set( "display_errors", 0);
session_start();
include_once( 'db_postgresql.inc.php' );
$username = $_SESSION["username"];
$wordpass = $_SESSION["wordpass"];

$sql1 = "SELECT COUNT(*) AS numfound FROM webappuser WHERE username='$username' AND wordpass='$wordpass' AND access_level='1' AND record_state='1'";
$result1 = $con->query(''.$sql1.'');
$my_array1 = $result1->fetch(PDO::FETCH_ASSOC);

// Login Logic
if ( $my_array1 ['numfound'] < 1 ) // Login Failed
	{
	$delay = 0;
	$url = "unauthorized.php";
	echo "<meta http-equiv='REFRESH' content='$delay;url=$url'>";	
	exit;
	}
?>

SQL Query Form Design Pattern Using "pg" driver

The following is a PHP Script that can serve as a design pattern for HTML form handling with "pg" connection driver for the PostgreSQL database.

<html>
<head>
<title>Postgresql pg Query Interface</title>
<!--
Clement L. Rasul
copyright (c) 2013
-->
</head>
<body>

<?php
$sql = trim($_POST['sql']);
$host = "localhost";
$user = "postgres";
$pw = "wordpass123";
$db = "testdb";

# Query Form
echo "<h1>PostgreSQL pg Query Interface</h1>";
echo "<form action='postgresql_pg_query.php' method='post'><textarea cols='80' rows='5' name='sql'>";
echo "$sql";
echo "</textarea><br /><input type='submit'></form>";

# Connection Check
$con = pg_connect("host=$host port=5432 dbname=$db user=$user password=$pw") or 
   die ("Database Status: <font color='red'><blink><b>Connection Error!</b></blink></font>");
echo "Database Status:  <font color='green'><blink><b>Connected...</b></blink></font><hr />";

# SQL Query Script
$result = pg_query($con, $sql);
$num_rows = pg_num_rows($result);
$num_cols = pg_num_fields($result);
echo "Rows: ".$num_rows."<br />";
echo "Columns: ".$num_cols."<hr />";
echo "<table border='1'>";

# Table Heading
$f=0;
while ( $col = pg_field_name($result, $f) ) {
	$fn = $col;
	echo "<th>".$fn."</th>";
	$f=$f+1;
    	}

# Table Content
while ( $row = pg_fetch_array($result) ) {
	echo "<tr>";
	$c = 0;
	while ( $c < $num_cols ) {
		echo "<td>".$row[$c]."   </td.>";
		$c = $c+1;
		}
	echo "</tr>";
    	}
echo "</table>";

?>
</body>
</html>

SQL Query Form Design Pattern Using "PostgreSQL PDO" driver

The following is a PHP Script that can serve as a design pattern for HTML form handling with "PostgreSQL PDO" connection driver for the PostgreSQL database.

<html>
<head>
<title>PostgreSQL PDO Query Interface</title>
<!--
Clement L. Rasul
copyright (c) 2013
-->
</head>
<body>

<?php
$sql = trim($_POST['sql']);
$host = "localhost";
$user = "postgres";
$pw = "wordpass123";
$db = "eis2013v2";

# Query Form
echo "<h1>PostgreSQL PDO Query Interface</h1>";
echo "<form action='postgresql_pdo_query.php' method='post'><textarea cols='80' rows='5' name='sql'>";
echo "$sql";
echo "</textarea><br /><input type='submit'></form>";

# Connection Check
try {
    $con = new PDO('pgsql:host='.$host.';dbname='.$db.'', $user, $pw );
    $con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
	echo "Database Status:  <font color='green'><blink><b>Connected...</b></blink></font><hr />";
} catch(PDOException $e) {
    echo 'ERROR: ' . $e->getMessage();
}

# SQL Query Script
$result = $con->query(''.$sql.'');
$num_rows = $result->rowCount();
$num_cols = $result->columnCount();
echo "Rows: ".$num_rows."<br />";
echo "Columns: ".$num_cols."<hr />";
echo "<table border='1'>";

# Table Heading
for ($i = 0; $i < $result->columnCount(); $i++) {
    $col = $result->getColumnMeta($i);
    $columns[] = $col['name'];
}

$f=0;
while ( $f < $num_cols ) {
	$fn = $columns[$f];
	echo "<th>".$fn."</th>";
	$f=$f+1;
    	}

# Table Content
# PDO::FETCH_ASSOC
# PDO::FETCH_BOTH
while ( $row = $result->fetch(PDO::FETCH_NUM) ) {
	echo "<tr>";
	$c = 0;
	while ( $c < $num_cols ) {
		echo "<td>".$row[$c]."   </td.>";
		$c = $c+1;
		}
	echo "</tr>";
    	}
echo "</table>";

?>

</body>
</html>

SQL Query Form Design Pattern Using "MySQL PDO" driver

The following is a PHP Script that can serve as a design pattern for HTML form handling with "MySQL PDO" connection driver for the MySQL database.

<html>
<head>
<title>MySQL PDO Query Interface</title>
<!--
Clement L. Rasul
copyright (c) 2013
-->
</head>
<body>

<?php
$sql = trim($_POST['sql']);
$host = "localhost";
$user = "root";
$pw = "wordpass123";
$db = "testdb";

# Query Form
echo "<h1>MySQL PDO Query Interface</h1>";
echo "<form action='mysql_pdo_query.php' method='post'><textarea cols='80' rows='5' name='sql'>";
echo "$sql";
echo "</textarea><br /><input type='submit'></form>";

# Connection Check
try {
    $con = new PDO('mysql:host='.$host.';dbname='.$db.'', $user, $pw );
    $con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
	echo "Database Status:  <font color='green'><blink><b>Connected...</b></blink></font><hr />";
} catch(PDOException $e) {
    echo 'ERROR: ' . $e->getMessage();
}

# SQL Query Script
$result = $con->query(''.$sql.'');
$num_rows = $result->rowCount();
$num_cols = $result->columnCount();
echo "Rows: ".$num_rows."<br />";
echo "Columns: ".$num_cols."<hr />";
echo "<table border='1'>";

# Table Heading
for ($i = 0; $i < $result->columnCount(); $i++) {
    $col = $result->getColumnMeta($i);
    $columns[] = $col['name'];
}

$f=0;
while ( $f < $num_cols ) {
	$fn = $columns[$f];
	echo "<th>".$fn."</th>";
	$f=$f+1;
    	}

# Table Content
while ( $row = $result->fetch(PDO::FETCH_NUM) ) {
	echo "<tr>";
	$c = 0;
	while ( $c < $num_cols ) {
		echo "<td>".$row[$c]."   </td.>";
		$c = $c+1;
		}
	echo "</tr>";
    	}
echo "</table>";

?>

</body>
</html>

SQL Query Form Design Pattern Using mysqli

The following is a PHP Script that can serve as a design pattern for HTML form handling with "mysqli" connection driver for the MySQL database.

<html>
<head>
<title>MySQL mysqli Query Interface</title>
<!--
Clement L. Rasul
copyright (c) 2013
-->
</head>
<body>

<?php
$sql = trim($_POST['sql']);
$host = "localhost";
$user = "root";
$pw = "wordpass123";
$db = "testdb";

# Query Form
echo "<h1>MySQL mysqli Query Interface</h1>";
echo "<form action='mysql_mysqli_query.php' method='post'><textarea cols='80' rows='5' name='sql'>";
echo "$sql";
echo "</textarea><br /><input type='submit'></form>";

# Connection Check
$testcon = mysqli_connect("$host", "$user", "$pw", "$db") or 
   die ("Database Status: <font color='red'><blink><b>Connection Error!</b></blink></font>");
echo "Database Status:  <font color='green'><blink><b>Connected...</b></blink></font><hr />";

# SQL Query Script
$con = new mysqli("$host", "$user", "$pw", "$db");
$result = $con->query("$sql");
$num_rows = $result->num_rows;
$num_cols = $result->field_count;
echo "Rows: ".$num_rows."<br />";
echo "Columns: ".$num_cols."<hr />";

# Table Heading
echo "<table border='1'>";
while ( $col = $result->fetch_field() ) {
	$fn = $col->name;
	echo "<th>".$fn."</th>";
    	}

# Table Content
while ( $row = $result->fetch_array() ) {
	echo "<tr>";
	$c = 0;
	while ( $c < $num_cols ) {
		echo "<td>".$row[$c]."   </td.>";
		$c = $c+1;
		}
	echo "</tr>";
    	}
echo "</table>";
?>

</body>
</html>

SQL Query Form Design Pattern Using mysql

The following is a PHP Script that can serve as a design pattern for HTML form handling with "mysql" connection driver for the MySQL database.

<html>
<head>
<title>MySQL mysql Query Interface</title>
<!--
Clement L. Rasul
copyright (c) 2013
-->
</head>
<body>

<?php
$sql = trim($_POST['sql']);
$host = "localhost";
$user = "root";
$pw = "wordpass123";
$db = "testdb";

# Query Form
echo "<h1>MySQL mysql Query Interface</h1>";
echo "<form action='mysql_mysql_query.php' method='post'><textarea cols='80' rows='5' name='sql'>";
echo "$sql";
echo "</textarea><br /><input type='submit'></form>";

# Connection Check
$con = mysql_connect( $host, $user, $pw );
mysql_select_db( $db , $con) or 
  die ("Database Status: <font color='red'><blink><b>Connection Error!</b></blink></font>");
echo "Database Status:  <font color='green'><blink><b>Connected...</b></blink></font><hr />";

# SQL Query Script
$result = mysql_query( $sql , $con) or die(mysql_error());
$num_rows = mysql_num_rows($result);
$num_cols = mysql_num_fields($result);
echo "Rows: ".$num_rows."<br />";
echo "Columns: ".$num_cols."<hr />";
echo "<table border='1'>";

# Table Heading
$f=0;
while ( $col = mysql_field_name($result, $f) ) {
	$fn = $col;
	echo "<th>".$fn."</th>";
	$f=$f+1;
    	}

# Table Content
while ( $row = mysql_fetch_array($result) ) {
	echo "<tr>";
	$c = 0;
	while ( $c < $num_cols ) {
		echo "<td>".$row[$c]."   </td.>";
		$c = $c+1;
		}
	echo "</tr>";
    	}
echo "</table>";

?>
</body>
</html>