Problem scenario
You are deploying a LAPP (Linux, Apache, PostgreSQL, and PHP) stack. You want the authentication to be done via a Postgres database behind-the-scenes. You can control the credentials from the back end this way. You want the user to be able to click around in the application once logged in. You want only one challenge for user login credentials.
How do you create a PHP login page (i.e., an authentication system) so the user can navigate through a series of multiple pages if the user enters a correct username and password on the first page?
Solution
Use session variables to allow a single login with unlimited web-page traversal thereafter.
n.b. This solution requires the user know a database name and table name that is within that database. These two names (one of a table and one of the table's database) are requested on the same web page that requests the username and password. Please note that this solution is more of an example and less of a step-by-step solution.
This solution has not been peer-reviewed for best practice security measures (e.g., it has not been validated to be impervious from SQL injection attacks). This solution below is ideal for testing on a development server or for learning purposes behind a firewall. Do not use this "solution" for a production environment with sensitive data.
#1 If you already have a database created with a table and username and password to log into the database and view the table, skip to step #5.
#2 Install Apache, PostgreSQL and PHP on a Linux server. See this link for details on how to do this.
#3 Once it is set up (you have deployed the LAPP stack), create a username and password in the PostgreSQL instance. Here is a SQL command to create the user: CREATE ROLE contuser WITH LOGIN PASSWORD 'integ3727';
#4 The role (the user itself) must be allowed to log into the database and run a SELECT statement on a known table in the database. Create a database that is owned by the user above (to ensure you have the access to it). One way would be to connect to the PostgreSQL instance as the user and run this SQL command with no quotes:
"CREATE DATABASE continual;"
Create a table inside this database. To create a table inside of a specific Postgres database, see this link.
#5 Create three files like the following in the /var/www/html/ directory.
The first file is /var/www/html/initial.php. It should have the following content:
<html>
<body>
<form action="step1.php" method="post">
UserName: <input type="text" name="username"><br>
Password: <input type="password" name="password"><br>
DatabaseWithTable: <input type="text" name="database"><br>
TableToView: <input type="text" name="table"<br>
<input type="submit">
</form>
</body>
</html>
The second file needed for this example is /var/www/html/step1.php and should have the following content:
<?php
session_start();
$part1 = $_POST["username"];//'jenny';
$part2 = $_POST["password"];//'P@$$W0rd!'
$part3 = $_POST["database"]; //cooldb
$part4 = $_POST["table"];//'neattable';
$comp = 'user='.$part1.' password='.$part2 ;
$host = "host=127.0.0.1";
$port = "port=5432";
$dbname = "dbname=" . $part3;
$credentials = $comp;
$_SESSION['hostvar'] = $host;
$_SESSION['portvar'] = $port;
$_SESSION['dbnamevar'] = $dbname;
$_SESSION['credentialsvar'] = $credentials;
$db = pg_connect( "$host $port $dbname $credentials" );
$query = 'select * from ' . $part4 . ';';
$cc = pg_query($query);
returner($cc);
function returner($cc)
{
/* This code below was copied from http://razorsql.com/articles/postgresql_column_names_values.html It was modified somewhat.*/
$i = 0;
echo '<html><body><table><tr>';
if ($cc) {
}
else {
echo '<td>' . "Failure. Were the credentials entered correctly? Was the database name entered correctly? Does that user entered have ownership of the table and access (ownership or some access rights) to the database name? Something went wrong. " . "Is the name of the database after dbname= here correct? " . $dbname .
'</td>';
}
while ($i < pg_num_fields($cc))
{
$fieldName = pg_field_name($cc, $i);
echo '<td>' . $fieldName . '</td>';
$i = $i + 1;
}
echo '</tr>';
$i = 0;
while ($row = pg_fetch_row($cc))
{
echo '<tr>';
$count = count($row);
$y = 0;
while ($y < $count)
{
$c_row = current($row);
echo '<td>' . $c_row . '</td>';
next($row);
$y = $y + 1;
}
echo '</tr>';
$i = $i + 1;
}
pg_free_result($cc);
echo '</table></body></html>';
}
?>
<html>
<body>
<form action="recursive.php" method="post">
TableToView: <input type="text" name="table"<br>
<input type="submit">
</form>
</body>
</html>
The third file needed for this example is /var/www/html/recursive.php and should have the following content:
<?php
session_start();
$part4 = $_POST["table"];//'cooltable'
$host = $_SESSION['hostvar'];
$port = $_SESSION['portvar'];
$dbname = $_SESSION['dbnamevar'];
$credentials = $_SESSION['credentialsvar'];
$comp = $credentials;
$db = pg_connect( "$host $port $dbname $credentials" );
$query = 'select * from ' . $part4 . ';';
$cc = pg_query($query);
returner($cc);
function returner($cc)
{
/* This code below was copied from http://razorsql.com/articles/postgresql_column_names_values.html It was modified somewhat.*/
$i = 0;
echo '<html><body><table><tr>';
if ($cc) {
}
else {
echo '<td>' . "Failure. Were the credentials entered correctly? Was the database name entered correctly? Does that user entered have ownership of the table and
access (ownership or some access rights) to the database name? Something went wrong. " . "Is the name of the database after dbname= here correct? " . $dbname .
'</td>';
}
while ($i < pg_num_fields($cc))
{
$fieldName = pg_field_name($cc, $i);
echo '<td>' . $fieldName . '</td>';
$i = $i + 1;
}
echo '</tr>';
$i = 0;
while ($row = pg_fetch_row($cc))
{
echo '<tr>';
$count = count($row);
$y = 0;
while ($y < $count)
{
$c_row = current($row);
echo '<td>' . $c_row . '</td>';
next($row);
$y = $y + 1;
}
echo '</tr>';
$i = $i + 1;
}
pg_free_result($cc);
echo '</table></body></html>';
}
?>
<html>
<body>
<form action="recursive.php" method="post">
TableToView: <input type="text" name="table"<br>
<input type="submit">
</form>
</body>
</html>
#6 Go to x.x.x.x/initial.php in a web browser. Enter info into the prompts and click submit. You'll be able to enter a table name and view its content repeatedly as many times as you like. The viewing will be in the web browser. The content will be the equivalent of a SQL command such as "select * from tableName;".
For further info you may want to read this link. For using PHP session variables more securely, you may want to view this GitHub link or this separate webpage.