First of all, your test case provided is not a unit test, it's called integration test, because it depends on the MySQL server available in the environment.
We'll be doing integration testing, then. Not delving in intricacies of proper DB testing with PHPUnit to keep things simple enough, here's the example test case class, written with usability in mind:
tests.php
<?php
require_once(__DIR__.'/code.php');
class BruteForceTests extends PHPUnit_Framework_TestCase
{
/** @test */
public function NoLoginAttemptsNoBruteforce()
{
// Given empty dataset any random time will do
$any_random_time = date('H:i');
$this->assertFalse(
$this->isUserTriedToBruteForce($any_random_time)
);
}
/** @test */
public function DoNotDetectBruteforceIfLessThanFiveLoginAttemptsInLastTwoHours()
{
$this->userLogged('5:34');
$this->userLogged('4:05');
$this->assertFalse(
$this->isUserTriedToBruteForce('6:00')
);
}
/** @test */
public function DetectBruteforceIfMoreThanFiveLoginAttemptsInLastTwoHours()
{
$this->userLogged('4:36');
$this->userLogged('4:23');
$this->userLogged('4:00');
$this->userLogged('3:40');
$this->userLogged('3:15');
$this->userLogged('3:01'); // ping! 6th login, just in time
$this->assertTrue(
$this->isUserTriedToBruteForce('5:00')
);
}
//==================================================================== SETUP
/** @var PDO */
private $connection;
/** @var PDOStatement */
private $inserter;
const DBNAME = 'test';
const DBUSER = 'tester';
const DBPASS = 'secret';
const DBHOST = 'localhost';
public function setUp()
{
$this->connection = new PDO(
sprintf('mysql:host=%s;dbname=%s', self::DBHOST, self::DBNAME),
self::DBUSER,
self::DBPASS
);
$this->assertInstanceOf('PDO', $this->connection);
// Cleaning after possible previous launch
$this->connection->exec('delete from login_attempts');
// Caching the insert statement for perfomance
$this->inserter = $this->connection->prepare(
'insert into login_attempts (`user_id`, `time`) values(:user_id, :timestamp)'
);
$this->assertInstanceOf('PDOStatement', $this->inserter);
}
//================================================================= FIXTURES
// User ID of user we care about
const USER_UNDER_TEST = 1;
// User ID of user who is just the noise in the DB, and should be skipped by tests
const SOME_OTHER_USER = 2;
/**
* Use this method to record login attempts of the user we care about
*
* @param string $datetime Any date & time definition which `strtotime()` understands.
*/
private function userLogged($datetime)
{
$this->logUserLogin(self::USER_UNDER_TEST, $datetime);
}
/**
* Use this method to record login attempts of the user we do not care about,
* to provide fuzziness to our tests
*
* @param string $datetime Any date & time definition which `strtotime()` understands.
*/
private function anotherUserLogged($datetime)
{
$this->logUserLogin(self::SOME_OTHER_USER, $datetime);
}
/**
* @param int $userid
* @param string $datetime Human-readable representation of login time (and possibly date)
*/
private function logUserLogin($userid, $datetime)
{
$mysql_timestamp = date('Y-m-d H:i:s', strtotime($datetime));
$this->inserter->execute(
array(
':user_id' => $userid,
':timestamp' => $mysql_timestamp
)
);
$this->inserter->closeCursor();
}
//=================================================================== HELPERS
/**
* Helper to quickly imitate calling of our function under test
* with the ID of user we care about, clean connection of correct type and provided testing datetime.
* You can call this helper with the human-readable datetime value, although function under test
* expects the integer timestamp as an origin date.
*
* @param string $datetime Any human-readable datetime value
* @return bool The value of called function under test.
*/
private function isUserTriedToBruteForce($datetime)
{
$connection = $this->tryGetMysqliConnection();
$timestamp = strtotime($datetime);
return wasTryingToBruteForce(self::USER_UNDER_TEST, $connection, $timestamp);
}
private function tryGetMysqliConnection()
{
$connection = new mysqli(self::DBHOST, self::DBUSER, self::DBPASS, self::DBNAME);
$this->assertSame(0, $connection->connect_errno);
$this->assertEquals("", $connection->connect_error);
return $connection;
}
}
This test suite is self-contained and has three test cases: for when there's no records of login attempts, for when there's six records of login attempts within two hours of the time of check and when there's only two login attempt records in the same timeframe.
This is the insufficient test suite, for example, you need to test that check for bruteforce really works only for the user we interested about and ignores login attempts of other users. Another example is that your function should really select the records inside the two hour interval ending in time of check, and not all records stored after the time of check minus two hours (as it does now). You can write all remaining tests yourself.
This test suite connects to the DB with PDO, which is absolutely superior to mysqli interface, but for needs of the function under test it creates the appropriate connection object.
A very important note should be taken: your function as it is is untestable because of static dependency on the uncontrollable library function here:
// Get timestamp of current time
$now = time();
The time of check should be extracted to function argument for function to be testable by automatic means, like so:
function wasTryingToBruteForce($user_id, $connection, $now)
{
if (!$now)
$now = time();
//... rest of code ...
}
As you can see, I have renamed your function to more clear name.
Other than that, I suppose you should really be very careful when working with datetime values in between MySQL and PHP, and also never ever construct SQL queries by concatenating strings, using parameter binding instead. So, the slightly cleaned up version of your initial code is as follows (note that the test suite requires it in the very first line):
code.php
<?php
/**
* Checks whether user was trying to bruteforce the login.
* Bruteforce is defined as 6 or more login attempts in last 2 hours from $now.
* Default for $now is current time.
*
* @param int $user_id ID of user in the DB
* @param mysqli $connection Result of calling `new mysqli`
* @param timestamp $now Base timestamp to count two hours from
* @return bool Whether the $user_id tried to bruteforce login or not.
*/
function wasTryingToBruteForce($user_id, $connection, $now)
{
if (!$now)
$now = time();
$two_hours_ago = $now - (2 * 60 * 60);
$since = date('Y-m-d H:i:s', $two_hours_ago); // Checking records of login attempts for last 2 hours
$stmt = $connection->prepare("SELECT time FROM login_attempts WHERE user_id = ? AND time > ?");
if ($stmt) {
$stmt->bind_param('is', $user_id, $since);
// Execute the prepared query.
$stmt->execute();
$stmt->store_result();
// If there has been more than 5 failed logins
if ($stmt->num_rows > 5) {
return true;
} else {
return false;
}
}
}
For my personal tastes, this method of checking is quite inefficient, you probably really want to make the following query:
select count(time)
from login_attempts
where
user_id=:user_id
and time between :two_hours_ago and :now
As this is the integration test, it expects the working accessible MySQL instance with the database in it and the following table defined:
mysql> describe login_attempts;
+---------+------------------+------+-----+-------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+------------------+------+-----+-------------------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| user_id | int(10) unsigned | YES | | NULL | |
| time | timestamp | NO | | CURRENT_TIMESTAMP | |
+---------+------------------+------+-----+-------------------+----------------+
3 rows in set (0.00 sec)
It's just my personal guess given the workings of function under test, but I suppose you really do have the table like that.
Before running the tests, you have to configure the DB* constants in the "SETUP" section within the tests.php file.
