Zend_Auth_Adapter_DbTable provides the ability to authenticate against credentials stored in a database table. Because Zend_Auth_Adapter_DbTable requires an instance of Zend_Db_Adapter_Abstract to be passed to its constructor, each instance is bound to a particular database connection. Other configuration options may be set through the constructor and through instance methods, one for each option.

The available configuration options include:

// Create an in-memory SQLite database connection
$dbAdapter = new Zend_Db_Adapter_Pdo_Sqlite(array('dbname' =>
                                                  ':memory:'));

// Build a simple table creation query
$sqlCreate = 'CREATE TABLE [users] ('
           . '[id] INTEGER  NOT NULL PRIMARY KEY, '
           . '[username] VARCHAR(50) UNIQUE NOT NULL, '
           . '[password] VARCHAR(32) NULL, '
           . '[real_name] VARCHAR(150) NULL)';

// Create the authentication credentials table
$dbAdapter->query($sqlCreate);

// Build a query to insert a row for which authentication may succeed
$sqlInsert = "INSERT INTO users (username, password, real_name) "
           . "VALUES ('my_username', 'my_password', 'My Real Name')";

// Insert the data
$dbAdapter->query($sqlInsert);

// Configure the instance with constructor parameters...
$authAdapter = new Zend_Auth_Adapter_DbTable(
    $dbAdapter,
    'users',
    'username',
    'password'
);

// ...or configure the instance with setter methods
$authAdapter = new Zend_Auth_Adapter_DbTable($dbAdapter);

$authAdapter
    ->setTableName('users')
    ->setIdentityColumn('username')
    ->setCredentialColumn('password')
;

// Set the input credential values (e.g., from a login form)
$authAdapter
    ->setIdentity('my_username')
    ->setCredential('my_password')
;

// Perform the authentication query, saving the result

// Print the identity
echo $result->getIdentity() . "\n\n";

// Print the result row
print_r($authAdapter->getResultRowObject());

/* Output:
my_username

Array
(
    [id] => 1
    [username] => my_username
    [password] => my_password
    [real_name] => My Real Name
)

By default, Zend_Auth_Adapter_DbTable returns the identity supplied back to the auth object upon successful authentication. Another use case scenario, where developers want to store to the persistent storage mechanism of Zend_Auth an identity object containing other useful information, is solved by using the getResultRowObject() method to return a stdClass object. The following code snippet illustrates its use:

// authenticate with Zend_Auth_Adapter_DbTable
$result = $this->_auth->authenticate($adapter);

if ($result->isValid()) {
    // store the identity as an object where only the username and
    // real_name have been returned
    $storage = $this->_auth->getStorage();
    $storage->write($adapter->getResultRowObject(array(
        'username',
        'real_name',
    )));

    // store the identity as an object where the password column has
    // been omitted
    $storage->write($adapter->getResultRowObject(
        null,
        'password'
    ));

    /* ... */

} else {

    /* ... */

}

While the primary purpose of Zend_Auth (and consequently Zend_Auth_Adapter_DbTable) is primarily authentication and not authorization, there are a few instances and problems that toe the line between which domain they fit within. Depending on how you've decided to explain your problem, it sometimes makes sense to solve what could look like an authorization problem within the authentication adapter.

With that disclaimer out of the way, Zend_Auth_Adapter_DbTable has some built in mechanisms that can be leveraged for additional checks at authentication time to solve some common user problems.

// The status field value of an account is not equal to "compromised"
$adapter = new Zend_Auth_Adapter_DbTable(
    $db,
    'users',
    'username',
    'password',
    'MD5(?) AND status != "compromised"'
);

// The active field value of an account is equal to "TRUE"
$adapter = new Zend_Auth_Adapter_DbTable(
    $db,
    'users',
    'username',
    'password',
    'MD5(?) AND active = "TRUE"'

Another scenario can be the implementation of a salting mechanism. Salting is a term referring to a technique which can highly improve your application's security. It's based on the idea that concatenating a random string to every password makes it impossible to accomplish a successful brute force attack on the database using pre-computed hash values from a dictionary.

Therefore, we need to modify our table to store our salt string:

$sqlAlter = "ALTER TABLE [users] "
          . "ADD COLUMN [password_salt] "
          . "AFTER [password]";

Here's a simple way to generate a salt string for every user at registration:

for ($i = 0; $i < 50; $i++) {
    $dynamicSalt .= chr(rand(33, 126));

And now let's build the adapter:

$adapter = new Zend_Auth_Adapter_DbTable(
    $db,
    'users',
    'username',
    'password',
    "MD5(CONCAT('"
    . Zend_Registry::get('staticSalt')
    . "', ?, password_salt))"
);

Another alternative is to use the getDbSelect() method of the Zend_Auth_Adapter_DbTable after the adapter has been constructed. This method will return the Zend_Db_Select object instance it will use to complete the authenticate() routine. It is important to note that this method will always return the same object regardless if authenticate() has been called or not. This object will not have any of the identity or credential information in it as those values are placed into the select object at authenticate() time.

An example of a situation where one might want to use the getDbSelect() method would check the status of a user, in other words to see if that user's account is enabled.

// Continuing with the example from above
$adapter = new Zend_Auth_Adapter_DbTable(
    $db,
    'users',
    'username',
    'password',
    'MD5(?)'
);

// get select object (by reference)
$select = $adapter->getDbSelect();
$select->where('active = "TRUE"');

// authenticate, this ensures that users.active = TRUE
$adapter->authenticate();