SQL Adapter

The SQL adapter authenticates users against a database table or view containing usernames and password hashes. Passwords must be hashed using PHP’s password_hash() function. The adapter uses PHP’s password_verify() to verify credentials..

// user table in database //
+----------+-------------+------------+---------------------+
| username | fullname    | email      | bcryptpass          |
|----------+-------------+------------+---------------------|
| juser    | Joe User    | juser@...  | $2y$.............   |
+----------+-------------+------------+---------------------+

The $columns argument specifies which database columns to retrieve. Array values represent the actual database column names. You can optionally use the array key to specify an alias for the returned column.

<?php
$columns = [
    'username',
    'bcryptpass'=>'password',
    'email',
    'fullname'=>'full_name'
];

The adapter retrieves the selected columns from the database. The username and password values are used for authentication and are not included in the userdata returned by getUserdata(). Other selected columns are made available as userdata, using their aliases where specified.

<?php

$userdata = $auth->getUserdata();
echo $userdata['email'];
echo $userdata['full_name'];

The SQL adapter accepts a PDO connection, a table or view name, an array of columns to retrieve, and an optional array of WHERE conditions.

<?php

use Vespula\Auth\Session\Session;
use Vespula\Auth\Auth;
use Vespula\Auth\Adapter\Sql;
use PDO;

$session = new Session();
$session->start();
$dsn = 'mysql:dbname=mydatabase;host=localhost';
$pdo = new PDO($dsn, 'dbuser', '********');

// Specify the database columns to retrieve.
// The resulting column set must include 'username' and 'password'.

$columns = [
    'username',
    'bcryptpass'=>'password', // alias
    'fullname'=>'full_name' // alias
    'email'
];

// The table or view to select from.
$from = 'users';

// Optional WHERE conditions.
// Conditions are combined using AND.
// A null value generates an IS NULL condition.

$where = [
    'active'=>1,
    'city'=>'edmonton',
    'other_column'=>null
];

// WHERE 'active=1 AND city='edmonton' AND other_column IS NULL';

$adapter = new Sql($pdo, $from, $columns, $where);
$auth = new Auth($adapter, $session);