A Simple, One-Page PHP Admin Login (with prepared SQL statements)

Question

First off, I want mention that this code works well. This is more of a request for suggestions...

We're attempting to program a one-page, recursive, token-based admin shell that is safe from intrusion, and replicable for several DB backend manipulation pages via tokening. We wanted to protect it from SQL injection (thus all SQL statements are prepared and sanitized) and from being able to relog by reposting form data when hitting the back button.

We could use some critiquing (please be gentle) and breaktesting if you're up to it! Everything is procedural to improve readability. We avoided "get_result()" since this is not supported on our host and is inconsistently installed among various hosts.

A couple of things we are considering:

• A regex test on user input to further mitigate SQL injection possibilities.
• Simplifying the code (getting rid of a lot of variable flushes)
• Hashing passwords (I know, this should be done ASAP - working on an input form to be able to enter hashed passwords now)
• ... Anything else should we consider??

Here's a general overview of what this code does:

• Connects to your DB (table needs are described in preamble)
• [1.1] If() a logout flag has been POSTed...
• --- [1.1.1] If() a SESSION token is set...
• ------ Prepare a SQL statement that pulls the token from the admin log
• ------ [1.1.1.1] If() the number of rows pulled is equal to zero...
• --------- Set the token expiration to now
• ------ [1.1.1.2] Else()...
• --------- Report a logout error ("Token does not exist")
• ------ Close the SQL statement
• --- [1.1.2] (No else() for this tier)
• --- Flush SESSION and POST variables
• [1.2] Elseif() a login flag has been POSTed with username, password and temp token...
• --- Prepare a SQL statement that pulls the password of the supplied username
• --- [1.2.1] If() the number of rows from the password pull is zero...
• ------ Report a login error ("User does not exist")
• ------ Flush SESSION variables
• --- [1.2.2] Elseif() the POSTed password matches the DB pull...
• ------ Insert a new token in the log with expiration
• --- [1.2.3] Else()...
• ------ Report a login error ("Incorrect Password")
• ------ Flush SESSION variables
• --- Flush local and POST variables
• [1.3] (No else() for this tier)
• [2.1] If() a SESSION username and password are set...
• --- Prepare SQL statement to pull the lease exp of the supplied token
• --- [2.1.1] If() the token exp time is greater than now...
• ------ [This is where all the things go that you need secure]
• ------ A "logout" button is included here
• --- [2.1.2] Elseif() the number of rows pulled is equal to zero...
• ------ Report a login error ("lease does not exist")
• ------ echo out the login sequence (POST calls itself)
• ------ Flush SESSION variables
• --- [2.1.3] Else()...
• ------ Report a login error ("lease has expired")
• ------ echo out the login sequence (POST calls itself)
• ------ Flush SESSION variables
• --- Flush POST variables
• [2.2] Else()
• --- [NOTE: this is where a first-time visitor lands]
• --- echo out the login sequence (POST calls itself)
• --- Flush SESSION and POST variables
• Close connection

Here is the actual code:

<?php

// Created by: Wyatt Miller, 150321

//Below is the SQL to create the necessary tables
/*
CREATE TABLE IF NOT EXISTS `admins` (
  `admin_id` int(64) NOT NULL AUTO_INCREMENT,
  `a_user` varchar(255) NOT NULL,
  `a_pass` varchar(255) NOT NULL,
  PRIMARY KEY (`admin_id`),
  UNIQUE KEY `adminusername` (`a_user`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `adminlog` (
  `adminlog_id` int(64) NOT NULL AUTO_INCREMENT,
  `a_user` varchar(255) NOT NULL,
  `a_login` int(64) NOT NULL,
  `a_logout` int(64) NOT NULL,
  `a_token` varchar(255) NOT NULL UNIQUE,
  PRIMARY KEY (`adminlog_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
*/

// Uncomment the following for debugging
/* 
error_reporting(E_ALL);
ini_set('display_errors', 1);
mysqli_report(MYSQLI_REPORT_STRICT);
*/

session_start();

/* Database connection information*/
$dbservername = 'localhost';   //Database server name
$dbusername = '[username]';    //Database username
$dbpassword = '[password]';    //Database password
$db = '[dbname]';              //Database name
$leasetime = 86400;            //The lease timer for logged in users (86400 secs = 24hours)

//Connect to the database (or throw an error)
$conn = new mysqli($dbservername, $dbusername, $dbpassword, $db) or die ('Unable to connect to database. Error: '. mysqli_error($conn));

//(1.1) If a POST logout flag is set...
if (isset($_POST['submit']) && $_POST['submit'] == 'logout') {

    //(1.1.1) If a SESSION token has been set [this is the only if() in this tier]
    if(isset($_SESSION['a_token'])) {

        $stmt = mysqli_prepare($conn, 'SELECT a_token FROM adminlog WHERE a_token=?');   //Preparing a SQL statement to check if the token has been registered in the DB
        mysqli_stmt_bind_param($stmt,'s',$_SESSION['a_token']);                          //Binds the SQL the the statement
        mysqli_stmt_execute($stmt);                                                      //Executes the statement
        mysqli_stmt_store_result($stmt);                                                 //This is needed to count the number if rows

        //(1.1.1.1) If the number of rows is greater than zero...
        if(mysqli_stmt_num_rows($stmt) > 0) {
            $currtime = time();                                                                //Assigns current time to variable (prepared statements need a variable to reference)
            $stmt = mysqli_prepare($conn, 'UPDATE adminlog SET a_logout=? WHERE a_token=?');   //Prepared statement updates the running log, setting the logout to the current time for the selected token
            mysqli_stmt_bind_param($stmt,'is',$currtime,$_SESSION['a_token']);                 //Binds the SQL the the statement
            mysqli_stmt_execute($stmt);                                                        //Executes the statement
        }
        //(1.1.1.2) Else...
        else {
            echo "Bad Monkey! Logout attempted, but token does not exist.<br/><br/>";   //Report that the token does not exist
        }
        mysqli_stmt_close($stmt);   //Kills the $stmt variable
    }
    //(1.1.2) [No else() for this tier]
    else {}

    unset($_SESSION['a_token']);   //Kills the SESSION token
    unset($_POST['temp_token']);   //Kills the POST temporary token from a prior form submission
    unset($_POST['a_user']);       //Kills the POST user name from a prior form submission
    unset($_POST['a_pass']);       //Kills the POST password from a prior form submission
    unset($_POST['submit']);       //Kills the POST submit flag from a prior form submission
}

//(1.2) Elseif POST login flag is set, along with username, password and a temporary token...
elseif(isset($_POST['submit']) && $_POST['submit'] == 'login' && isset($_POST['a_user']) && isset($_POST['a_pass']) && isset($_POST['temp_token'])) {

    $stmt = mysqli_prepare($conn, 'SELECT a_pass FROM admins WHERE a_user=?');   //Preparing a SQL statement to pull the password of the supplied username
    mysqli_stmt_bind_param($stmt,'s',$_POST['a_user']);                          //Binds the SQL the the statement
    mysqli_stmt_execute($stmt);                                                  //Executes the statement
    mysqli_stmt_store_result($stmt);                                             //This is needed to count the number if rows
    mysqli_stmt_bind_result($stmt,$a_pass);                                      //Binds the result of the SQL execution to a variable
    mysqli_stmt_fetch($stmt);                                                    //Executes the variable binding

    //(1.2.1) If the number of rows from the password pull is zero...
    if(mysqli_stmt_num_rows($stmt) == 0) {
        echo 'Bad monkey! User does not exist.<br/><br/>';   //Report the user doesn't exist
        unset($_SESSION['a_user']);                          //Kills the SESSION username
        unset($_SESSION['a_token']);                         //Kills the SESSION token
    }
    //(1.2.2) Elseif the POST password matches the pulled password...
    elseif($_POST['a_pass'] == $a_pass) {
        $currtime = time();                                                                                                          //Assigns current time to variable (prepared statements need a variable to reference)
        $currtimeexp = $currtime + $leasetime;                                                                                       //Assigns lease expiration time to variable (prepared statements need a variable to reference)
        $_SESSION['a_user'] = $_POST['a_user'];                                                                                      //Assigns username to SESSION (prepared statements need a variable to reference)
        $_SESSION['a_token'] = $_POST['temp_token'];                                                                                 //Assigns username to SESSION (prepared statements need a variable to reference)
        $stmt = mysqli_prepare($conn, 'INSERT INTO adminlog (a_user,a_login,a_logout,a_token) VALUES (?,?,?,?)');                    //Preparing a SQL statement to insert a new row in the token log
        mysqli_stmt_bind_param($stmt,'siis',$_SESSION['a_user'], $currtime, $currtimeexp, $_SESSION['a_token']);                     //Binds the SQL the the statement
        mysqli_stmt_execute($stmt);                                                                                                  //Executes the statement
    }
    //(1.2.3) Else...
    else {
        echo 'Bad monkey! Incorrect password.<br/><br/>';   //Report an incorrect password
        unset($_SESSION['a_user']);                         //Kills the SESSION username
        unset($_SESSION['a_token']);                        //Kills the SESSION token
    }

    mysqli_stmt_close($stmt);   //Kills the $stmt variable
    unset($a_pass);             //Kills the password variable pulled from the SQL statement
    unset($_POST['a_user']);    //Kills the POST user name from a prior form submission
    unset($_POST['a_pass']);    //Kills the POST password from a prior form submission
    unset($_POST['submit']);    //Kills the POST submit flag from a prior form submission
}
//(1.3) [No else() for this tier]
else {}

//(2.1) If a SESSION username and token are set...
if(isset($_SESSION['a_user']) && isset($_SESSION['a_token'])) {

    $stmt = mysqli_prepare($conn,'SELECT a_logout FROM adminlog WHERE a_token=?');   //Preparing a SQL statement to pull the lease expiration time of the supplied token
    mysqli_stmt_bind_param($stmt,'s',$_SESSION['a_token']);                          //Binds the SQL the the statement
    mysqli_stmt_execute($stmt);                                                      //Executes the statement
    mysqli_stmt_store_result($stmt);                                                 //This is needed to count the number if rows
    mysqli_stmt_bind_result($stmt,$a_logout);                                        //Binds the result of the SQL execution to a variable
    mysqli_stmt_fetch($stmt);                                                        //Executes the variable binding

    //(2.1.1) If the token expiration is greater than the current time
    if($a_logout > time()) {

        /*Begin all the admin stuff*/
        /*Begin all the admin stuff*/
        /*Begin all the admin stuff*/

        echo '<html><head>';
        echo '<title></title>';
        echo 'Welcome to the DB admin page, ' . $_SESSION['a_user'] . '. (All the thingz are coming...)<br/>';
        echo '</head><body>';
        echo '<form action=' . $_SERVER['REQUEST_URI'] . ' method = "post"><button type = "submit" id="submit" name="submit" value = "logout">Logout</button>';
        echo '</form></body></html>';       

        /* End all the admin stuff*/
        /* End all the admin stuff*/
        /* End all the admin stuff*/
    }
    //(2.1.2) Elseif the number of rows from the logout pull is zero
    elseif(mysqli_stmt_num_rows($stmt) == 0) {
        //Report the lease has expired and echo out the login form that contains a temporary token
        echo 'Bad monkey! Lease does not exist.<br/><br/>';
        echo '<html>';
        echo '<head>';
        echo '<title></title>';
        echo 'Admin Login<br/><br/>';
        echo '</head>';
        echo '<form action=' . $_SERVER['REQUEST_URI'] . ' method = "post">';   echo '<label for="a_user">Username:</label> <input type="text" autocomplete="off" id="a_user" name="a_user"><br /><br />';
        echo '<label for="a_pass">Password:</label> <input type="password" autocomplete="off" id="a_pass" name="a_pass"><br /><br />';
        echo '<input type="hidden" id="a_pass" name="temp_token" value="' . md5(time()) . '">';
        echo '<button type = "submit" id="submit" name="submit" value = "login">Login</button>';
        echo '</form>';
        echo '</html>';

        unset($_SESSION['a_user']);    //Kills the SESSION username
        unset($_SESSION['a_token']);   //Kills the SESSION token
    }
    //(2.1.3) Else...
    else {
        //Report the lease has expired and echo out the login form that contains a temporary token
        echo 'Bad monkey! Lease has expired.<br/><br/>';
        echo '<html>';
        echo '<head>';
        echo '<title></title>';
        echo 'Admin Login<br/><br/>';
        echo '</head>';
        echo '<form action=' . $_SERVER['REQUEST_URI'] . ' method = "post">';   echo '<label for="a_user">Username:</label> <input type="text" autocomplete="off" id="a_user" name="a_user"><br /><br />';
        echo '<label for="a_pass">Password:</label> <input type="password" autocomplete="off" id="a_pass" name="a_pass"><br /><br />';
        echo '<input type="hidden" id="a_pass" name="temp_token" value="' . md5(time()) . '">';
        echo '<button type = "submit" id="submit" name="submit" value = "login">Login</button>';
        echo '</form>';
        echo '</html>';

        unset($_SESSION['a_user']);    //Kills the SESSION username
        unset($_SESSION['a_token']);   //Kills the SESSION token
    }
    unset($_POST['a_user']);    //Kills the POST user name from a prior form submission
    unset($_POST['a_pass']);    //Kills the POST password from a prior form submission
    unset($_POST['submit']);    //Kills the POST submit flag from a prior form submission
}
//(2.2) Else...
else {
    //This is the likely landing spot when first visiting the page - it simply echos out the login prompt containing a temporary token
    echo '<html>';
    echo '<head>';
    echo '<title></title>';
    echo 'Admin Login<br/><br/>';
    echo '</head>';
    echo '<form action=' . $_SERVER['REQUEST_URI'] . ' method = "post">';   echo '<label for="a_user">Username:</label> <input type="text" autocomplete="off" id="a_user" name="a_user"><br /><br />';
    echo '<label for="a_pass">Password:</label> <input type="password" autocomplete="off" id="a_pass" name="a_pass"><br /><br />';
    echo '<input type="hidden" id="a_pass" name="temp_token" value="' . md5(time()) . '">';
    echo '<button type = "submit" id="submit" name="submit" value = "login">Login</button>';
    echo '</form>';
    echo '</html>';

    unset($_SESSION['a_user']);    //Kills the SESSION username
    unset($_SESSION['a_token']);   //Kills the SESSION token
    unset($_POST['a_user']);       //Kills the POST user name from a prior form submission
    unset($_POST['a_pass']);       //Kills the POST password from a prior form submission
    unset($_POST['submit']);       //Kills the POST submit flag from a prior form submission
}

/* Close all the DB connections */
mysqli_close($conn);

?>

Answer 1

Security

• I'm not all that comfortable with echoing unsanitized user input: echo '<form action=' . $_SERVER['REQUEST_URI'] . ' method = "post">';. I did not find any way to exploit it though. I asked a question on security.SE, maybe they can find a way. I would probably use SCRIPT_NAME instead.
• you should regenerate the session id via session_regenerate_id(true); if something in the session changes (such as on login) to prevent session fixation.
• it's not all that clear what a_token/temp_token do, but they seem to be some sort of session id inside the session, which isn't necessary for security (and if it were necessary, basing it on the time isn't a good idea).
• You probably should not be storing database credentials in a publicly available PHP file, but in a configuration file outside the webroot instead (some editors leave backup files (eg admin_login.php~) which could be read if you made small changes on the production server, you always have to remember to remove them when sharing code, etc).
• If you follow the previous point and now have a configuration file, I would create a DEBUG field in it and use that in your code instead of relying on developers to comment/uncomment lines for debugging (will they remember it each time in all of the places? Probably not).
• A regex test on user input to further mitigate SQL injection possibilities.: You could do that, or you could just use a web application firewall such as mod_security. If you do write your own code, I would put it all in one file that cleans all POST/GET variables which then is included everywhere instead of performing these on each POST/GET variable individually.
• use a timing safe compare function to compare passwords (although you really should hash your passwords. If you use bcrypt it will handle the comparison and even salts for you).

Readability

• Everything is procedural to improve readability: are you sure about that? Personally, I would prefer OOP and MVC. But either way, procedural style does allow the use of functions, which I would highly recommend, as it helps you to structure your code into logical blocks (login, logout, index, etc) which makes your code easier to read, and it helps to avoid code-duplication. Eg you have your login form three times! This makes it really difficult to read the code (are they really exactly the same? If not, what is the small difference?), and it makes it hard to maintain (If I want to add an extra field or change the layout, I have to do it in three places).
• your comments are way too much (I'm not even talking about the structured policy comments, but about Database username, Kills the [...], etc). I read the first couple of them, realized that they just say what the code already said, and then ignored all future comments. Most readers will behave the same way. But what if there is something important you have to say in the comments? People would ignore that as well.

Functionality

• unsetting POST will not prevent form resubmits, so I would just get rid of it. If you are sure that you need it, I would write a generic unsetAllPost function and use that to reduce code duplication.
• always write output as if this was already on a production server. Unless this is code for a very specific niche, calling people monkey is just not acceptable.

Answer 2

I agree with tim.

The data is stored without analysis the content:

$_SESSION['a_user'] = $_POST['a_user']; 

I recommend two treatments: One for database (mysqli_real_escape_string or mysqli_prepare as is what you are currently doing) and one for web display (htmlentities).

Then, when you show user-source data, remember the Cross-site scripting:

echo 'Welcome to the DB admin page, ' . $_SESSION['a_user'] . '. (All the thingz are coming...)<br/>';

Must be replaced with

printf( 
    'Welcome to DB admin page, %s. (All the thingz are coming...)br/>',
    htmlentities( $_SESSION['a_user'] ) );

Answer 3

A few things to consider:
Not validating user input
Content Security Policy
input-protection
report-uri
SecurityPolicyViolationEvent
See W3C User Interface Security Directives for Content Security Policy Cross-site scripting (XSS)
SQL injection
Cross-site request forgery
Clickjacking
Read: W3C Security Activity

Do not put database connect username and password in public_html documents.
Use an include or get_file_contents to access a file in a private secure directory. Could be exposed if php Content Type becomes text/plain.
Use header('Content-Type: text/html; charset=utf-8');
Unicode Security Considerations

Restrict admin login to specific IP addresses.

Put a limit on the number of login attempts.

If the time between login attempts is less than humanly possible ban the IP

Don't just minimize SQL Injection, examine submitted data for key SQL injection characters and SQL commands.
If SQL Injection code is being submitted, ban the IP.

Examples SQL Injection Characters and keywords

coalesce, 0x, like, regex, mid, select, delete, drop, insert, do, call,
 replace, update, infile, lock, set, from, into, show, table, kill, reset,
[\x21-\x2F], [\x3A-\x40], [\x5B-\x60], [\x7B-\x7F],x22, x28,x29,x2C, ")(,%^&

Use numeric integer input where possible and declare character set and language.

`if (isset($_POST['submit']) && $_POST['submit'] == 'logout')
`  assign a value to logout e.g. 2

Add hidden field and validate with intval(). Make zero the default value.

<input type=”hidden” name=”sub” value=”2” />

$sub = intval($_POST['sub']);
if($sub ==2)

I have a problem with your HTML and coding

No Doctype.
autocomplete="off" missing in <form>
Be aware:

The ability for websites to disable the password manager using autocomplete = "off" is being removed in Firefox 30

As of Internet Explorer 11, the autocomplete property is no longer supported for input type=password fields.

Missing tags: <br> inputs do not have closing slash<br> duplicate ida_pass<br> <title>empty<br> echo output before<br> use ofecho` for each line rather than Heredoc Syntax.
Heredoc Example (with other suggestions)

<?php  
header('Content-Type: text/html; charset=utf-8');
header('Connection: Close');
header('Cache-Control: no-cache, no-store, must-revalidate');
$temp_token = md5(time());
$action = $_SERVER['REQUEST_URI'];
echo <<<EOT
<!DOCTYPE html>
<html lang="en"><head><title>Use a Title</title>
<style type="text/css">
</style></head><body>
<p>Admin Login</p>
<form action="$action " method="post" autocomplete="off" ><div>
<label for="a_user">Username:</label>
<input type="text" autocomplete="off" id="a_user" name="a_user" /><br /><br />
<label for="a_pass">Password:</label>
<input type="password" autocomplete="off" id="a_pass" name="a_pass"/><br /><br />
<input type="hidden" id="temp_token" name="temp_token" value="$tt" />
<button type = "submit" id="submit" name="submit" value = "login">Login</button>
</div></form>
</body></html>
EOT;
?>

I used an HTML doctype for HTML5 because of the autocomplete. When checking a page with W3C HTML Markup Validator I also check it against XHTML Basic 1.1. that is why I added the div <form...><div> and added <p>