Extension of Niels database class to support a way of handling too many failed login attempts.

action.php

<?php
session_start();
require_once('config.php');
require_once('db.class.php');

// New instance of the db class
$db = new db(DB_NAME, DB_HOST, DB_USER, DB_PASSWORD);

// If nothing send by _POST, redirect to index
if ( ! count($_POST) > 0)
{
    header( 'Location: ' . BASEPATH . 'index.php' );
	// Have to exit after a header() redirect
	exit;
}

// The submit_signin button was pressed
if (isset($_POST['submit_signin']))
{
	// You can set a custom error message, %s is replaced with seconds left.
	// (This is the default if nothing is set).
	$db->throttle_message = 'Wait %s seconds before trying again!';

	// Custom options:
	// Interval is how many minutes back it should count failed attempts from.
	// Throttles is an array with 'failed attempts to trigger' => 'Seconds locked'.
	//   If the value is 'recaptcha', you can check for it as i've done below.
	// (This is the default if nothing is set).
	$db->throttle_options = array(
		'interval'  => 15,
		'throttles' => array(
			10 => 1,
			20 => 2,
			30 => 3,
			40 => 'recaptcha'
		)
	);

	// Check if it's throttled (too many failed attempts).
	if ( $db->throttled(DB_TBL_LOGINFAIL) ) {
		// Use this to insert a failed attempt into the database.
		// Remember to do this all places your loginscript fails.
		$userip = $_SERVER['REMOTE_ADDR'];
		$sql = "INSERT INTO ".DB_TBL_LOGINFAIL." 
				SET username = '$username', 
				ip_address = INET_ATON('$userip'), 
				attempted = CURRENT_TIMESTAMP;";
		$db->query($sql);

		// If the throttle error is 'recaptcha' show a recaptcha to ensure it's a human trying to login.
		if ($db->throttle_error == 'recaptcha') {
			// My way of sending a message with a redirect.
			// Sets a session 'message', and unsets it after it has been shown on the page.
			$_SESSION['message'] = (object) [
				'type' => "error",
				'text' => 'Show captcha',
			];
		}
		else {
			// If the error wasn't 'recaptcha', show the error message with time left until they can try again.
			$_SESSION['message'] = (object) [
				'type' => "error",
				'text' => $db->throttle_error,
			];
		}

		// Redirect back to login page.
		header( 'Location: ' . BASEPATH . 'index.php' );
		exit;
	}
}

?>

config.php

<?php

DEFINE ('BASEPATH',         'http://localhost/school_security/2/');

DEFINE ('DB_USER',          'root');
DEFINE ('DB_PASSWORD',      '');
DEFINE ('DB_HOST',          'localhost');
DEFINE ('DB_NAME',          'secur1');

DEFINE ('DB_TBL_USERS',     's2_users');
DEFINE ('DB_TBL_POSTS',     's2_posts');
DEFINE ('DB_TBL_LOGINFAIL', 's2_login_fail');

?>

db.class.php

<?php
class db extends mysqli {
	private $connection;
	private $result;

	public $throttle_options;
	public $throttle_error = null;
	public $throttle_message = 'Wait %s seconds before trying again!';


	public function __construct($db, 
                                $host='localhost', 
                                $user='root', 
                                $pwd='')
	{
		parent::__construct($host, $user, $pwd, $db);
		if ($this->connect_error) {
			die('Connect Error (' . $this->connect_errno . ') ' . $this->connect_error);
		}

		// set defaults
		date_default_timezone_set("Europe/Copenhagen");
		
		$this->throttle_options = array(
			'interval'  => 15,
			'throttles' => array(
				10 => 1,
				20 => 2,
				30 => 3,
				40 => 'recaptcha'
			)
		);
	}

	public function close() {
		$this->close();
	}

	function query($declaration) {
		try {
			if (! $this->result = parent::query($declaration)) {
				$s = sprintf("Query error: %s: %s<br/>%s"
							 , $this->errno, $this->error, $declaration);
				throw new Exception($s);
			}
		}
		catch (Exception $e) {
			die($e->getMessage());
		}
		return $this->result;
	}
	
	function fetch_array() {
		return $rowArray = $this->result->fetch_array();
	}
	
	function fetch_object() {
		return $rowObject = $this->result->fetch_object();
	}

	/**
	 * Test if login is throttled
	 * @param  string $table
	 * @param  array $options (Must contain 'interval:int' and 'throttles:array')
	 * @return mixed
	 */
	function throttled($table, $options = null) {
		// if no options provided as param use the options from instance
		if ($options == null) {
			$options = $this->throttle_options;
		}
		// set variables for easier access
		$interval = $options['interval'];
		$throttles = $options['throttles'];

		// retrieve the latest failed login attempts
		$sql = "SELECT MAX(attempted) AS attempted FROM $table";
		$result = $this->query($sql);
		if ($this->affected_rows > 0) {
			$row = $result->fetch_assoc();
			// store time for last attempt
			$latest_attempt = (int) date('U', strtotime($row['attempted']));

			// get the number of failed attempts
			$sql = "SELECT COUNT(1) AS failed FROM $table WHERE attempted > DATE_SUB(NOW(), INTERVAL $interval minute)";
			$result = $this->query($sql);
			if ($this->affected_rows > 0) {
				$row = $result->fetch_assoc();
				// store number of failed attemps
				$failed_attempts = (int) $row['failed'];
				// check if any of the throttles is relevant
				krsort($throttles);
				foreach ($throttles as $attempts => $delay) {
					if ($failed_attempts > $attempts) {
						// delay is a number
						if (is_numeric($delay)) {
							$time_since_last_try = time() - $latest_attempt;
							$time_left = abs($time_since_last_try-$delay);
							// is this time less than the delay from last attempt
							if ($time_since_last_try <= $delay) {
								// set error
								$this->throttle_error = sprintf($this->throttle_message, $time_left);
								return true;
							}
							// allow this attempt
							break;
						}
						// delay is 'recaptcha'
						if ($delay == 'recaptcha') {
							// set error 
							$this->throttle_error = 'recaptcha';
							return true;
						}
						break;
					}
				}
			}
		}
		// no throttles, so return false
		return false;
	}

}
?>

dummydata.php

<?php
session_start();
require_once('config.php');
require_once('db.class.php');

$db = new db(DB_NAME, DB_HOST, DB_USER, DB_PASSWORD);



///////////////////////////////////////////////////////////////
// Delete users table
$sql = "DROP TABLE IF EXISTS ".DB_TBL_USERS;
$db->query($sql);

///////////////////////////////////////////////////////////////
// Create users table
$sql = "CREATE TABLE IF NOT EXISTS ".DB_TBL_USERS." (
    	`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
		`username` varchar(255) NOT NULL,
		`email` varchar(255) NOT NULL,
		`password` varchar(128) NOT NULL COMMENT 'hash(''sha512'', ''password'')',
		`salt` varchar(255) NOT NULL,
		`lastlogin` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
		PRIMARY KEY (`id`)
) engine=InnoDB charset=UTF8";
$db->query($sql);



///////////////////////////////////////////////////////////////
// Delete posts table
$sql = "DROP TABLE IF EXISTS ".DB_TBL_POSTS;
$db->query($sql);

///////////////////////////////////////////////////////////////
// Create posts table
$sql = "CREATE TABLE IF NOT EXISTS ".DB_TBL_POSTS." (
		`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
		`title` varchar(255) NOT NULL,
		`slug` varchar(255) NOT NULL,
		`body` text NOT NULL,
		`publishdate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
		`author_id` int(10) unsigned NOT NULL,
		PRIMARY KEY (`id`)
) engine=InnoDB charset=UTF8";
$db->query($sql);



///////////////////////////////////////////////////////////////
// Delete loginfail table
$sql = "DROP TABLE IF EXISTS ".DB_TBL_LOGINFAIL;
$db->query($sql);

///////////////////////////////////////////////////////////////
// Create loginfail table
$sql = "CREATE TABLE ".DB_TBL_LOGINFAIL." (
	`id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
	`username` VARCHAR(16) NOT NULL,
	`ip_address` INT(11) UNSIGNED NOT NULL,
	`attempted` DATETIME NOT NULL,
	INDEX `attempted_idx` (`attempted`)
) engine=InnoDB charset=UTF8";
$db->query($sql);



///////////////////////////////////////////////////////////////
// Insert users data
$users = [
	[
		'id' => 1,
		'username' => "Martin",
		'email' => "[email protected]",
		'password' => "password",
		'salt' => md5(mt_rand()),
	],
	[
		'id' => 2,
		'username' => "User",
		'email' => "[email protected]",
		'password' => "password",
		'salt' => md5(mt_rand()),
	],
];

foreach ($users as $user) {
	$salted_password = sprintf("%s%s", $user['password'], $user['salt']);
	$hashed_password = hash("sha512", $salted_password);

	$sql = "INSERT INTO ".DB_TBL_USERS."
				(
					id,
					username,
					email,
					password,
					salt
				)
			VALUES
				(
					'" . $user['id'] . "',
					'" . $user['username'] . "',
					'" . $user['email'] . "',
					'" . $hashed_password . "',
					'" . $user['salt'] . "'
				)
	";
	$db->query($sql);
}



///////////////////////////////////////////////////////////////
// Insert posts data
$date = new DateTime(null, new DateTimeZone('Europe/Copenhagen'));

$posts = [
	[
		'id' => 1,
		'title' => "First blog post",
		'slug' => "first-blog-post",
		'body' => "Lorem ipsum dolor sit amet, consectetur adipiscing elit. Ut volutpat turpis at erat ullamcorper interdum. Mauris facilisis sollicitudin ante nec sagittis. Curabitur auctor diam eget massa commodo non congue lacus tristique. Integer viverra interdum tempus. Proin lectus neque, lacinia quis accumsan id, venenatis suscipit sem. Suspendisse in magna libero. Morbi varius congue risus a semper. Donec ut metus sit amet nisi dictum scelerisque malesuada ac mi. Ut lobortis nibh sed mauris malesuada tincidunt. Nulla eu arcu mauris. Sed nec dui non lorem semper dapibus. Duis a dui dui, sit amet aliquet nunc. Curabitur blandit consectetur quam id tincidunt. Quisque ut turpis ut nibh volutpat varius.",
		'publishdate' => $date->format('Y-m-d H:i:s'),
		'author_id' => 1,
	],
	[
		'id' => 2,
		'title' => "Second blog post",
		'slug' => "second-blog-post",
		'body' => "Mauris facilisis sollicitudin ante nec sagittis. Curabitur auctor diam eget massa commodo non congue lacus tristique. Integer viverra interdum tempus. Proin lectus neque, lacinia quis accumsan id, venenatis suscipit sem. Suspendisse in magna libero. Morbi varius congue risus a semper. Donec ut metus sit amet nisi dictum scelerisque malesuada ac mi. Ut lobortis nibh sed mauris malesuada tincidunt. Nulla eu arcu mauris. Sed nec dui non lorem semper dapibus. Duis a dui dui, sit amet aliquet nunc. Curabitur blandit consectetur quam id tincidunt. Quisque ut turpis ut nibh volutpat varius.",
		'publishdate' => $date->format('Y-m-d H:i:s'),
		'author_id' => 1,
	],
	[
		'id' => 3,
		'title' => "Third blog post",
		'slug' => "third-blog-post",
		'body' => "Integer viverra interdum tempus. Proin lectus neque, lacinia quis accumsan id, venenatis suscipit sem. Suspendisse in magna libero. Morbi varius congue risus a semper. Donec ut metus sit amet nisi dictum scelerisque malesuada ac mi. Ut lobortis nibh sed mauris malesuada tincidunt. Nulla eu arcu mauris. Sed nec dui non lorem semper dapibus. Duis a dui dui, sit amet aliquet nunc. Curabitur blandit consectetur quam id tincidunt. Quisque ut turpis ut nibh volutpat varius.",
		'publishdate' => $date->format('Y-m-d H:i:s'),
		'author_id' => 2,
	],
];

foreach ($posts as $post) {
	$sql = "INSERT INTO ".DB_TBL_POSTS."
				(
					id,
					title,
					slug,
					body,
					publishdate,
					author_id
				)
			VALUES
				(
					'" . $post['id'] . "',
					'" . $post['title'] . "',
					'" . $post['slug'] . "',
					'" . $post['body'] . "',
					'" . $post['publishdate'] . "',
					'" . $post['author_id'] . "'
				)
	";

	$db->query($sql);
}



///////////////////////////////////////////////////////////////
// Redirect to index
$_SESSION['message'] = (object) [
	'type' => "success",
	'text' => "Dummy data created!"
];
header( 'Location: ' . BASEPATH . 'index.php' );
exit;

?>