Database Storage

Requirements

In order to use one of the provided database storage drivers, ensure these requirements are met.

• Have the PDO extension installed.
• Have the required PDO driver for your database type installed and enabled.
  • For MySQL: Driver
  • For PostgreSQL: Driver
  • For SQLite: Driver
  • For SQL Server: Driver or ODBC driver

Table Schemas

The standard database schemas for each driver, which consist of tables for storing challenge data and timeouts, can be found at the links below. You are free to add extra columns to these tables if needed. However, it's important to leave all the columns in the default schemas as they are when using the provided database drivers.

The tables may also be renamed. When doing so, make sure to update the table names in the configuration accordingly (validation.attempts.storage.options.table and session.options.table).

Schemas:

• MySQL schema

• PostgreSQL schema

• SQLite schema

• Postgres schema

Configuration

In order to use a database to store captcha data, you must specify the connection details to your database in the storage.connection configuration option. Ensure that you also specify the driver matching your database in the storage.driver option.

MySQL / PostgreSQL / SQL Server

Configuration for MySQL, PostgreSQL, and SQL Server follows the same approach. The connection details must be specified in the connection array. The default drivers only accept the host, port, database, username and password details to build a connection string. If your connection is more complex, you also have the option to specify the connection string manually using the url option. When using a custom connection string, the other connection details will be disregarded.

'storage' => [
    // ...
    'connection' => [
        // 'url' => '<connection string>', 
        'host' => '127.0.0.1',
        'port' => 3306,
        'database' => 'db',
        'username' => 'root',
        'password' => '',
    ],
    // ...
],

MySQL

The MySQL driver uses the default connection options mentioned above. Alternatively, you may use a connection string:

'connection' => [
    'url' => 'mysql:host=127.0.0.1;port=3306;dbname=db',
],

PostgreSQL

The PostgreSQL driver uses the default connection options mentioned above. Alternatively, you may use a connection string:

'connection' => [
    'url' => 'pgsql:host=127.0.0.1;port=3306;dbname=db',
],

SQL Server / OBDC

The SQL Server driver uses the default connection options mentioned above. Alternatively, you may use a connection string:

'connection' => [
    'url' => 'sqlsrv:Server=127.0.0.1,1433;Database=db',
],

If your application uses the odbc driver instead of the sqlsrv driver, you can configure this by either setting storage.connection.odbc to the driver name or specifying the alternative connection string:

'connection' => [
    // ...
    'odbc' => '{SQL Server}',
],

// or

'connection' => [
    'url' => 'odbc:Driver={SQL Server};Server=127.0.0.1,1433;Database=db',
],

SQLite

If you wish to use SQLite, you must simply specify the path to your database file. This path must be an absolute path. Alternatively, you may also store data in memory with SQLite. This is not advised, but can be configured using :memory: instead.

'connection' => [
    'database' => 'path\to\your\database.sqlite',
],

Existing PDO connection

Instead of having to define connection details and establish a new database connection with PDO, you also have the option to provide an already open PDO connection. Just make sure that the object you provide in the option is, in fact, a valid PDO object.

'storage' => [
    // ...
    'connection' => $database // Must be a PDO object.
]

Custom driver

If you wish to create your own PDO storage driver, ensure that your custom class extend the PDOConnector class and implements all required functions. The constructor of the base class will receive all details specified in the storage.connection option.

Important

When using a PDO storage driver, ensure that compatible drivers are configured for both the session (session.driver) and the timeout/attempts processor (validation.attempts.storage.driver). Compatible drivers are either one of the provided drivers (mysql, sqlsrv, pgsql, sqlite), or drivers which can work with a storage object of type PDOStorageInterface.

To enable your custom storage driver, update the storage.driver configuration option to use your class.

'storage' => [
    'driver' => \Path\To\YourDatabaseConnector::class,
    // ...
],

The code snippet below shows an example driver implementing the PDOConnector base class.

use IconCaptcha\Storage\Database\Connectors\PDOConnector;

class YourDatabaseConnector extends PDOConnector
{
    protected function createDsnString(array $config): string
    {
        // $host = $config['host']; // The $config parameter contains the connection details specified in the configuration.

        $dsn = "<connection string>"; // Specify your connection string.

        // ...

        return $dsn;
    }
}