Database Storage

Intro

By default, Securimage uses PHP sessions to correlate CAPTCHA codes to visitors on your website. The drawback to using sessions is that they require cookies to be set on the client to match a user to a session, and also store session data on the filesystem by default. This causes problems if some clients have cookies disabled, or if you have multiple frontend webservers behind a load balancer.

To remedy these problems, Securimage can store its CAPTCHA codes in a Sqlite3, MySQL, or PostgreSQL database. If you have multiple webservers, you will need to use either MySQL or PostgreSQL as Sqlite databases will also be local to frontend webservers.


Example #1: Using a SQLite Database

First, open securimage.php since the changes made here will be reflected not only when viewing the CAPTCHA, but when validating a user’s input as well.

Next, find public $use_database in securimage.php and make the following changes:

public $use_database = true;

//... The next setting $database_driver is already set to use SQLite.

public $database_driver = self::SI_DRIVER_SQLITE3;

Important: The file securimage/database/securimage.sq3 must be writeable by the user running the webserver. This means the file permissions on Linux must be 0666 and the file may also need to be owned by the user running PHP. Securimage will not be able to use SQLite if PHP cannot read or write the file!


Example #2: Using MySQL or PostgreSQL

To configure Securimage to store codes in a MySQL or PostgreSQL database, we will make the changes to securimage.php so the settings don’t need to be entered in securimage_show.php and in the file where you validate the captcha input. See TODO: Setting Options for more information.

Open securimage.php and find the line that contains public $use_database. This setting and the next several settings are used to set the database options. First set $use_database to true.

public $use_database = true;

Depending on whether you will be using MySQL or PostgreSQL, change the $database_driver option to one of self::SI_DRIVER_MYSQL for MySQL, or self::SI_DRIVER_PGSQL for PostgreSQL.

public $database_driver = self::SI_DRIVER_MYSQL;
// or to use PostgreSQL
public $database_driver = self::SI_DRIVER_PGSQL;

To configure the host which Securimage connects to for your database, set the $database_host option (default ‘localhost’).

public $database_host   = 'localhost';

Next, the database username and password need to be configured using the $database_user and $database_pass options. The default values are empty so most likely you need to change these values.

public $database_user   = 'dbuser';
public $database_pass   = 'password';

Finally, specify the name of the database the Securimage table will belong to by changing the $database_name option. The default value is empty so you must specify a database name.

public $database_name   = 'mydb';

Now Securimage is configured to store CAPTCHA codes in MySQL or PostgreSQL.


Turning off session storage

If Securimage is configured to use a SQLite, MySQL, or PostgreSQL, then session storage can be completely turned off to remove the requirement of using PHP sessions and storing a cookie on the client computer. To turn off session support, change the $no_session option to true. With this value set to true, no session is used and no cookies will be sent to the browser.

public $no_session = true;  // change to 'true' to disable sessions

2 comments “Database Storage”

I use the SQLite database for all my sites, my question is that the size of database keeps increasing and there are hundreds of entries now, there should be an option to delete older entries after some time, like 3 hours or user customizable value.

It does periodically delete old, expired entries. But SQLite files will not shrink when data is deleted. It might later be able to re-use some of that space instead of growing, but to reclaim the unused space, you need to use the VACUUM command.

I wouldn’t recommend SQLite for a production site if other options (session, MySQL) are available since SQLite is way slower than the alternatives.


Leave a Reply


CAPTCHA Image
Play CAPTCHA Audio
Reload Image