A backdoor provides access to an application bypassing the normal authentication process. There are many ways to do this. Some are more secure than others.

Why do you need a backdoor?

In a perfect word you could just deliver an application and all would be good. However in the real world there are unforeseen issues which need to be solved. This means that you as a developer will need access to the application. To reproduce the problem, you usually want to run the application logged in as the user that spotted the issue.

Another use of the backdoor is in a situation where you want to allow a user, that has already been authenticated, to bypassing further authentication. For example if you have a (web hosting) control panel where the user is already logged in, you can allow him to directly access the dashboard of the application without have to enter his password again. This requires a backdoor, since you don’t know his (unencrypted) password.

A very simple solution

The most simple solution is to use a backdoor password. This password will work for every user. A variation on this, is to have a superuser account, that is allowed to switch to any user on the system.

This solution is fine if you’re the only developer working on these applications. However in a professional environment this solution won’t do. With this method is easy to give somebody super privileges, but hard to take them away. This requires changing the backdoor password. Which is a tedious job if you’re managing any serious number of applications.

The secure way

It is easier if there is a project management system where you and other developers can log into. From within that system, the developer can directly login the customer application as any user. Within that application you can configure on which team each developer is. That limits to which applications the developer has access. More important, simply blocking the user account on the project management system will lock the developer out completely.

Private and public keys

The best known method for logging into a system, is the use of private/public (DSA) keys with SSH. The SSH client signs the request with the private key. The SSH server has the public key in the authorized_key file. It verifies the credentials using the public keys and grands access on success.

We can use the same method with PHP using the OpenSLL extension. We’ll let the client (project management system) sign the username and system name (URL) using openssl_sign. This signature is verified on the server (customer application) using openssl_verify. To unsure the login URL can’t be reused later, we’ll throw in a 5 second timeout.

Generating the keys

The keys can be generated on the (*nix) command line, using the ‘openssl’ binary. I’m using RSA keys, but DSA should also work if preferred.

# Generate private key
openssl genrsa -out master.key 1024
# Generate public key
openssl rsa -in master.key -pubout -out master.pub

The public key should be copied to the ‘pubkeys’ directory of the server application. Make sure the private key is absolutely private. Anybody who has a copy of that, can use the backdoor.

Download

Don’t use the downloaded code without replacing the private and public key!
Download the code @ github
A (not to impressive) demo

If I overlooked security issues with this implementation, please leave a comment below.

In the article ‘Versioning MySQL data‘, I showed the basics of implementing a revisioning system using trigger. As Jens Schauder already pointed out, often the data of a record is spread across multiple tables, like an invoice with multiple invoice lines. Having each invoice line versioned individually isn’t really useful. Instead we want a new revision of the whole invoice on each change.

The perfect solution
Ideally a change of one or more parts of the invoice would be changed, a new revision would be created. There are several issues in actually creating this those. Detecting the change of multiple parts of the invoice at once, generating a single revision, would mean we need to know if the actions are done within the same transaction. Unfortunately there is a connection_id(), but no transaction_id() function in MySQL. Also, the query would fail when a query inserts or updates a record in the child table, using the parent table. We need to come up with something else.

In the implementation we currently have in production, we version the rows in the parent as well in the child tables. For each version of the parent row, we register which versions of the child rows ware set. This however has really complicated the trigger code and tends to need a lot of checking an querying slowing the write process down. Since nobody ever looks at the versions of the child rows, the application forces a new version of the parent row. The benefits of versioning both are therefor minimal.

Only versioning the parent
For this new (simplified) implementation, we will only have one revision number across all tables of the record. Changing data from the parent table, will trigger a new version. This will not only copy the parent row to the revisioning table, but also the rows of the children.

Writing to the child will not trigger a new version, instead it will update the data in the revisioning table. This means that when changing the record, you need to write to the parent table, before writing to the child tables. To force a new version without changing values use

UPDATE mytable SET _revision=NULL where id=$id

The parent and child tables are defined as

CREATE TABLE `mytable` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL DEFAULT '',
  `description` text,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB

CREATE TABLE `mychild` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `mytable_id` int(10) unsigned NOT NULL DEFAULT '0',
  `title` varchar(255) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `mytable_id` (`mytable_id`),
  CONSTRAINT `mychild_ibfk_1` FOREIGN KEY (`mytable_id`) REFERENCES `mytable` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB

Note that we are using InnoDB tables here. MyISAM doesn’t have foreign key constraints, therefor it’s not possible to define a parent-child relationship.

Insert, update and delete
In the parent trigger, two different things happen concerning the child rows. When a new version is created, the data of `mychild` is copied to the revisioning table. On a revision switch, data will be copied from the revisioning table into `mychild`. The “`_revision_action` IS NULL” condition, means that `_revision_mytable` is only updated when a new revision is created.

CREATE TRIGGER `mytable-afterupdate` AFTER update ON `mytable`
  FOR EACH ROW BEGIN
    DECLARE `newrev` BOOLEAN;

    UPDATE `_revision_mytable` SET `id` = NEW.`id`, `name` = NEW.`name`, `description` = NEW.`description`, `_revision_action`='update' WHERE `_revision`=NEW.`_revision` AND `_revision_action` IS NULL;
    SET newrev = (ROW_COUNT() > 0);
    INSERT INTO `_revhistory_mytable` VALUES (NEW.`id`, NEW.`_revision`, @auth_uid, NOW());

    IF newrev THEN
       INSERT INTO `_revision_mychild` SELECT *, NEW.`_revision` FROM `mychild` WHERE `mytable_id` = NEW.`id`;
    ELSE
       DELETE `t`.* FROM `mychild` AS `t` LEFT JOIN `_revision_mychild` AS `r` ON 0=1 WHERE `t`.`mytable_id` = NEW.`id`;
       INSERT INTO `mychild` SELECT `id`, `mytable_id`, `title` FROM `_revision_mychild` WHERE `_revision` = NEW.`_revision`;
    END IF;
  END

CREATE TRIGGER `mychild-afterinsert` AFTER INSERT ON `mychild`
  FOR EACH ROW BEGIN
    DECLARE CONTINUE HANDLER FOR 1442 BEGIN END;
    INSERT IGNORE INTO `_revision_mychild` (`id`, `mytable_id`, `title`, `_revision`) SELECT NEW.`id`, NEW.`mytable_id`, NEW.`title`, `_revision` FROM `mytable` AS `p` WHERE `p`.`id`=NEW.`mytable_id`;
  END

CREATE TRIGGER `mychild-afterupdate` AFTER UPDATE ON `mychild`
  FOR EACH ROW BEGIN
    REPLACE INTO `_revision_mychild` (`id`, `mytable_id`, `title`, `_revision`) SELECT NEW.`id`, NEW.`mytable_id`, NEW.`title`, `_revision` FROM `mytable` AS `p` WHERE `p`.`id`=NEW.`mytable_id`;
  END

CREATE TRIGGER `mychild-afterdelete` AFTER DELETE ON `mychild`
  FOR EACH ROW BEGIN
    DECLARE CONTINUE HANDLER FOR 1442 BEGIN END;
    DELETE `r`.* FROM `_revision_mychild` AS `r` INNER JOIN `mytable` AS `p` ON `r`.`_revision` = `p`.`_revision` WHERE `r`.`id` = OLD.`id`;
  END

Changing data in table `mychild` simply updates the data in the revisioning table. The revision number is grabbed from the field in the parent table.

Switching the revision can only be done through the parent table. This will also automatically change the data in the child tables. We simply delete all rows of the record and replace them with data from the revisioning table. This would however trigger the deletion of the data in `_revision_child` on which the insert has nothing to do. To prevent this, we can abuse that fact that a trigger can’t update data of a table using in the insert/update/delete query. This causes error 1442. With a continue handler we can ignore this silently.

The InnoDB constraints will handle the cascading delete. Deleting child data won’t activate the deletion trigger, which is all the better in this case.

Without a primary key
A primary key is not required for the child table, since versioning is done purely based on the id of `mytable`.

CREATE TABLE `mypart` (
  `mytable_id` int(10) unsigned NOT NULL,
  `reference` varchar(255) NOT NULL,
  KEY `mytable_id` (`mytable_id`),
  CONSTRAINT `mypart_ibfk_1` FOREIGN KEY (`mytable_id`) REFERENCES `mytable` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB

This does cause an issue for the update and delete triggers of the child table. It can’t use the primary to id to locate the current version of the modified/removed row. This can be solved by a trick I got from PhpMyAdmin. We can simply locate the record by comparing the old values of all fields. There is no constraint for the table enforcing the uniqueness of a row, so we could be targeting multiple identical rows. Since they are identical, it doesn’t matter which one we target, as long as we limit to 1 row.

CREATE TRIGGER `mypart-afterupdate` AFTER UPDATE ON `mypart`
  FOR EACH ROW BEGIN
    DELETE FROM `_revision_mypart` WHERE `_revision` IN (SELECT `_revision` FROM `mytable` WHERE `id` = OLD.`mytable_id`) AND `mytable_id` = OLD.`mytable_id` AND `reference` = OLD.`reference` LIMIT 1;
    INSERT INTO `_revision_mypart` (`mytable_id`, `reference`, `_revision`) SELECT NEW.`mytable_id`, NEW.`reference`, `_revision` FROM `mytable` AS `p` WHERE `p`.`id`=NEW.`mytable_id`;
  END

CREATE TRIGGER `mypart-afterdelete` AFTER DELETE ON `mypart`
  FOR EACH ROW BEGIN
    DECLARE CONTINUE HANDLER FOR 1442 BEGIN END;
    DELETE FROM `_revision_mypart` WHERE `_revision` IN (SELECT `_revision` FROM `mytable` WHERE `id` = OLD.`mytable_id`) AND `mytable_id` = OLD.`mytable_id` AND `reference` = OLD.`reference` LIMIT 1;
  END

Unique keys
The revisioning table has multiple versions of a record. Unique indexes from the original table should be converted to non-unique indexes in the revisioning table. This information can be fetched using INFORMATION_SCHEMA.

SELECT c.CONSTRAINT_NAME, GROUP_CONCAT(CONCAT('`', k.COLUMN_NAME, '`')) AS cols FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS `c` INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS `k` ON c.TABLE_SCHEMA=k.TABLE_SCHEMA AND c.TABLE_NAME=k.TABLE_NAME AND c.CONSTRAINT_NAME=k.CONSTRAINT_NAME WHERE c.TABLE_SCHEMA=DATABASE() AND c.TABLE_NAME='mytable' AND c.CONSTRAINT_TYPE='UNIQUE' AND c.CONSTRAINT_NAME != '_revision' GROUP BY c.CONSTRAINT_NAME

Revisioning and replication
Baron Schwartz pointed out a race condition when relying on auto-increment keys in triggers with replication. Actions carried out through triggers on a master are not replicated to a slave server. Instead, triggers on the slave will be invoked, which should do the same action as on the master.

It probably isn’t needed to have a copy of the revisioning tables on the slave. This would mean that we could simply omit the triggers. Unfortunately this causes problems when changing the revision. In that case we are forced to move switching of a revision out of the database. Instead the application needs to select the data from all revisioning tables and write that to the original tables. Any other thoughts on solving this issue are welcome.

Download
–> Download mysql-revisioning script @github <--

As a developer you’re probably using a versioning control system, like subversion or git, to safeguard your data. Advantages of using a VCS are that you can walk to the individual changes for a document, see who made each change and revert back to specific revision if needed. These are features which would also be nice for data stored in a database. With the use of triggers we can implement versioning for data stored in a MySQL db.

The revisioning table
We will not store the different versions of the records in the original table. We want this solution to be in the database layer instead of putting all the logic in the application layer. Instead we’ll create a new table, which stores all the different versions and lives next to the original table, which only contains the current version of each record. This revisioning table is copy of the original table, with a couple of additional fields.

CREATE TABLE `_revision_mytable` LIKE `mytable`;

ALTER TABLE `_revision_mytable`
  CHANGE `id` `id` int(10) unsigned,
  DROP PRIMARY KEY,
  ADD `_revision` bigint unsigned AUTO_INCREMENT,
  ADD `_revision_previous` bigint unsigned NULL,
  ADD `_revision_action` enum('INSERT','UPDATE') default NULL,
  ADD `_revision_user_id` int(10) unsigned NULL,
  ADD `_revision_timestamp` datetime NULL default NULL,
  ADD `_revision_comment` text NULL,
  ADD PRIMARY KEY (`_revision`),
  ADD INDEX (`_revision_previous`),
  ADD INDEX `org_primary` (`id`);

The most important field is `_revision`. This field contains a unique identifier for a version of a record from the table. Since this is the unique identifier in the revisioning table, the original id field becomes a normal (indexed) field.

We’ll also store some additional information in the revisioning table. The `_revision_previous` field hold the revision nr of the version that was updated to create this revision. Field `_revision_action` holds the action that was executed to create this revision. This field has an extra function that will discussed later. The user id and timestamp are useful for blaming changes on someone. We can add some comment per revision.

The database user is probably always the same. Storing this in the user id field is not useful. Instead, we can set variable @auth_id after logging in and on connecting to the database to the session user.

Altering the original table
The original table needs 2 additional fields: `_revision` and `_revision_comment`. The `_revision` field holds the current active version. The field can also be used to revert to a different revision. The value of `_revision_comment` set on an update or insert will end up in the revisioning table. The field in the original table will always be empty.

ALTER TABLE `mytable`
  ADD `_revision` bigint unsigned NULL,
  ADD `_revision_comment` text NULL,
  ADD UNIQUE INDEX (`_revision`);

The history table
Saving each version is not enough. Since we can revert back to older revisions and of course delete the record altogether, we want to store which version of the record was enabled at what time. The history table only needs to hold the revision number and a timestamp. We’ll add the primary key fields, so it’s easier to query. A user id field is included again to blame.

CREATE TABLE `_revhistory_mytable` (
  `id` int(10) unsigned,
  `_revision` bigint unsigned NULL,
  `_revhistory_user_id` int(10) unsigned NULL,
  `_revhistory_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
  INDEX (`id`),
  INDEX (_revision),
  INDEX (_revhistory_user_id),
  INDEX (_revhistory_timestamp)
) ENGINE=InnoDB;

How to use
Inserting, updating and deleting data should work as normal, including the INSERT … ON DUPLICATE KEY UPDATE syntax. When updating the _revision field shouldn’t be changed.

To switch to a different version, we would do something like

UPDATE mytable SET _revision=$rev WHERE id=$id;

However if the record has been deleted, there will be no record in the original table, therefore the update won’t do anything. Instead we could insert a record, specifying the revision.

INSERT INTO mytable SET _revision=$rev;

We can combine these two into a statement that works either way.

INSERT INTO mytable SET id=$id, _revision=$rev ON DUPLICATE KEY UPDATE _revision=VALUES(_revision);

The above query shows that there an additional constraint. The only thing that indicates that different versions is of the same record, is the primary key. Therefore value of the primary key can’t change on update. This might mean that some tables need to start using surrogate keys if they are not.

On Insert
Let’s dive into the triggers. We’ll start with before insert. This trigger should get the values of a revision when the _revision field is set, or otherwise add a new row to the revision table.

CREATE TRIGGER `mytable-beforeinsert` BEFORE INSERT ON `mytable`
  FOR EACH ROW BEGIN
    DECLARE `var-id` int(10) unsigned;
    DECLARE `var-title` varchar(45);
    DECLARE `var-body` text;
    DECLARE `var-_revision` BIGINT UNSIGNED;
    DECLARE revisionCursor CURSOR FOR SELECT `id`, `title`, `body` FROM `_revision_mytable` WHERE `_revision`=`var-_revision` LIMIT 1;

    IF NEW.`_revision` IS NULL THEN
      INSERT INTO `_revision_mytable` (`_revision_comment`, `_revision_user_id`, `_revision_timestamp`) VALUES (NEW.`_revision_comment`, @auth_uid, NOW());
      SET NEW.`_revision` = LAST_INSERT_ID();
    ELSE
      SET `var-_revision`=NEW.`_revision`;
      OPEN revisionCursor;
      FETCH revisionCursor INTO `var-id`, `var-title`, `var-body`;
      CLOSE revisionCursor;

      SET NEW.`id` = `var-id`, NEW.`title` = `var-title`, NEW.`body` = `var-body`;
    END IF;

    SET NEW.`_revision_comment` = NULL;
  END

CREATE TRIGGER `mytable-afterinsert` AFTER INSERT ON `mytable`
  FOR EACH ROW BEGIN
    UPDATE `_revision_mytable` SET `id` = NEW.`id`, `title` = NEW.`title`, `body` = NEW.`body`, `_revision_action`='INSERT' WHERE `_revision`=NEW.`_revision` AND `_revision_action` IS NULL;
    INSERT INTO `_revhistory_mytable` VALUES (NEW.`id`, NEW.`_revision`, @auth_uid, NOW());
  END

If the `_revision` field is NULL, we insert a new row into the revision table. This action is primarily to get a revision number. We set the comment, user id and timestamp. We won’t set the values, action and previous id yet. The insert might fail or be converted into an update action by insert on duplicate key update. If the insert action fails, we’ll have an unused row in the revisioning table. This is a problem, since the primary key has not been set, so it won’t show up anywhere. We can clean up these phantom records once in a while to keep the table clean.

When `_revision` is set, we use a cursor to get the values from the revision table. We can’t fetch to values directly into NEW, therefore we first fetch them into variables and than copy that into NEW.

After insert, we’ll update the revision, setting the values and the action. However, the insert might have been an undelete action. In that case `_revision_action` is already set and we don’t need to update the revision. We also add an entry in the history table.

On Update
The before and after update trigger do more or less the same as the before and after insert trigger.

CREATE TRIGGER `mytable-beforeupdate` BEFORE UPDATE ON `mytable`
  FOR EACH ROW BEGIN
    DECLARE `var-id` int(10) unsigned;
    DECLARE `var-title` varchar(45);
    DECLARE `var-body` text;
    DECLARE `var-_revision` BIGINT UNSIGNED;
    DECLARE `var-_revision_action` enum('INSERT','UPDATE','DELETE');
    DECLARE revisionCursor CURSOR FOR SELECT `id`, `title`, `body`, `_revision_action` FROM `_revision_mytable` WHERE `_revision`=`var-_revision` LIMIT 1;

    IF NEW.`_revision` = OLD.`_revision` THEN
      SET NEW.`_revision` = NULL;

    ELSEIF NEW.`_revision` IS NOT NULL THEN
      SET `var-_revision` = NEW.`_revision`;

      OPEN revisionCursor;
      FETCH revisionCursor INTO `var-id`, `var-title`, `var-body`, `var-_revision_action`;
      CLOSE revisionCursor;

      IF `var-_revision_action` IS NOT NULL THEN
        SET NEW.`id` = `var-id`, NEW.`title` = `var-title`, NEW.`body` = `var-body`;
      END IF;
    END IF;

    IF (NEW.`id` != OLD.`id` OR NEW.`id` IS NULL != OLD.`id` IS NULL) THEN
-- Workaround for missing SIGNAL command
      DO `Can't change the value of the primary key of table 'mytable' because of revisioning`;
    END IF;

    IF NEW.`_revision` IS NULL THEN
      INSERT INTO `_revision_mytable` (`_revision_previous`, `_revision_comment`, `_revision_user_id`, `_revision_timestamp`) VALUES (OLD.`_revision`, NEW.`_revision_comment`, @auth_uid, NOW());
      SET NEW.`_revision` = LAST_INSERT_ID();
    END IF;

    SET NEW.`_revision_comment` = NULL;
  END

CREATE TRIGGER `mytable-afterupdate` AFTER UPDATE ON `mytable`
  FOR EACH ROW BEGIN
    UPDATE `_revision_mytable` SET `id` = NEW.`id`, `title` = NEW.`title`, `body` = NEW.`body`, `_revision_action`='UPDATE' WHERE `_revision`=NEW.`_revision` AND `_revision_action` IS NULL;
    INSERT INTO `_revhistory_mytable` VALUES (NEW.`id`, NEW.`_revision`, @auth_uid, NOW());
  END

If `_revision` is not set, it has the old value. In that case a new revision should be created. Setting `_revision` to NULL will have the same behaviour of not setting `_revision`. Next to the comment, user id and timestamp, we add also set the previous revision.

As said before, it’s very important that the value of primary key doesn’t change. We need to check this and trigger an error, if it would be changed.

On Delete
Deleting won’t create a new revisiong. However we do want to log that the record has been deleted. Therefore we add an entry to the history table with `_revision` set to NULL.

CREATE TRIGGER `mytable-afterdelete` AFTER DELETE ON `mytable`
  FOR EACH ROW BEGIN
    INSERT INTO `_revhistory_mytable` VALUES (OLD.`id`, NULL, @auth_uid, NOW());
  END

To conclude
Using triggers we can implement the basic versioning functionality to MySQL. Since this is completely done the by database, it can be added to an existing application, without having to change to application code (or with very little changes). Using the history table, we can get the data of the database on any moment in time.

There are some situations where this solution as a bit to basic. A record might span across multiple table, like an invoice with invoice lines. In that case, we don’t want to revision each individual invoice line, but the invoice as a whole. I’ll come around in a follow up with a solution for this. I can tell up front that this solution is unfortunately not as clean as these basics.

Continue reading
Please continue reading the follow up article ‘Versioning MySQL data: Multi-table records‘. At the bottom of that article you’ll find a download link for a script that adds revisioning to existing MySQL tables.

In the article ‘An alternative way of EAV modelling’, I discussed how to do EAV modelling by casting all values (except text) to integers. I’ll continue on that and talk about more advanced topics like multi-value fields.

As binary set
Not all questions have only a single option. Some fields we want to represent by radio-buttons, allow the user to select any number of options. For this we can mimic the behaviour of the SET field type of MySQL. A SET is almost similar to an ENUM, except that each bit represents an option. The value can have multiple bits enabled to represent multiple options.

Example: field options for field ‘programming language’

+-------+-------------+
| value | description |
+-------+-------------+
| 1     | C/C++       |
| 2     | PHP         |
| 4     | Java        |
| 8     | Python      |
| 16    | Ruby        |
+-------+-------------+

Choosing ‘C/C++’, ‘PHP’ and ‘Python’ would result in value 11.

There are 2 major disadvantages to this approach. First, with an integer, the number of options is limited to 4*8 = 32. Second, retrieving the description of options would cause joining field on field_option to be done like:

SELECT value_display(`field_name`, value_display(`field_type`, `value`.`value`, GROUP_CONCAT(`field_option`.`description`), `value`.`text`, `precision`, `date_format`)) FROM `value` INNER JOIN `field` ON `value`.`fid` = `field`.`fid` LEFT JOIN `field_option` ON `value`.`fid` = `field_option`.`fid` AND (IF(`field`.`field_type`='SET, `value`.`value` & `field_option`.`value`, `value`.`value` = `field_option`.`value`)) WHERE `value`.`item_id`=? GROUP BY `value`.`fid`;

Because of this, the index on the value isn’t used. Instead, a function has to be performed on each option of the field. This should still give decent enough performance, because this scales linearly and not exponential.

Multiple values
Another way is to store multiple values for the same property. This would mean replacing the primary key from the value table by a normal index.

CREATE TABLE `value` (
  `item_id` int(10) UNSIGNED NOT NULL DEFAULT '0',
  `fid` int(10) UNSIGNED NOT NULL DEFAULT '0',
  `value` int(11) NOT NULL DEFAULT '0',
  `text` text,
  KEY `item_field`  (`item_id`,`fid`),
  KEY `value` (`fid`,`value`),
  KEY `text` (`text`(255))
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

SELECT value_display(`field_name`, value_display(`field_type`, `value`.`value`, GROUP_CONCAT(`field_option`.`description`), `value`.`text`, `precision`, `date_format`)) FROM `value` INNER JOIN `field` ON `value`.`fid` = `field`.`fid` LEFT JOIN `field_option` ON `value`.`fid` = `field_option`.`fid` AND `value`.`value` = `field_option`.`value` WHERE `value`.`item_id`=? GROUP BY `value`.`fid`;

The disadvantage of this, is that structural integrity is no longer enforced by the database. Only for a field with the type ‘SET’, multiple values should be allowed. However the database will allow multiple values for other field types (like numeric fields) as well. It is up to the application to replace existing values.

Ranges with a single field
A completely different type of multi-value is ranges. A range has a top and bottom value. We can solve this by saving 2 values in the database. Of the values, the the highest one is always the upper limit and the lowest is the bottom limit.

delimiter |
CREATE FUNCTION `value_display` (`type` enum('NUMBER', 'ENUM', 'DATE', 'TIME', 'TEXT'), `value` INT, <strong>`max_value` INT</strong>, `option` VARCHAR(255), `text` TEXT, `precision` INT, `date_format` VARCHAR(50)) RETURNS VARCHAR(255) CHARACTER SET latin1 NO SQL
BEGIN
  // ...
END

SELECT value_display(`field_name`, value_display(`field_type`, MIN(`value`.`value`), IF(COUNT(*)>1, MAX(`value`.`value`, NULL), GROUP_CONCAT(`field_option`.`description`), `value`.`text`, `precision`, `date_format`)) FROM `value` INNER JOIN `field` ON `value`.`fid` = `field`.`fid` LEFT JOIN `field_option` ON `value`.`fid` = `field_option`.`fid` AND `value`.`value` = `field_option`.`value` WHERE `value`.`item_id`=? GROUP BY `value`.`fid`;

Range by 2 fields
Another was of looking at ranges is that the are simply 2 individual fields, eg `min weight` and `max weight`. When filtering you might want to get all items that have 70 kg in their weight range. You would translate this to `min weight` <= 70 and `max weight` >= 70. (In the example query `min weight` has fid=2 and `max weight` fid=3.)

SELECT `item`.* FROM `item` INNER JOIN `value` ON `item`.`id`=`value`.`item_id` AND `value`.`fid` IN (2, 3) WHERE (`value`.`fid`=2 AND `value`.`value` <= 70) OR (`value`.`fid`=3 AND `value`.`value` >= 70) HAVING count(*) =2 GROUP BY `item`.`id`;

Conclusion
We see that using multi-value fields forces to group on fid and generally complicates the queries. If you only need ranges it might be a good idea to use 2 fields instead to keep the queries simpler.

The code in this article has not been tested.

Officially PHP doesn’t support multiple inheritance. There are several ways around this, without having to duplicate code.

PHP 5.4 will support Traits. This concept is almost similar to mixins. For more information check the PHP manual.

Wrapper

The most commonly used method is to use a wrapper object.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52

<?php
 
abstract class FsNode
{
  public $path;
 
  public function __construct($path) {
    $this->path = $path;
  }
 
  public function rename($newname) {
    rename($this->path, $newname);
    $this->path = $newname;
  }
}
 
class File extends FsNode
{
  public function getContents() {
    return file_get_contents($this->path);
  }
}
 
class Dir extends FsNode
{
  public function scandir() {
    return scandir($this->path);
  }
}
 
class Symlink
{
  protected $node;
 
  public function __construct($node) {
    $this->node  = $node;
  }
 
  public function target($resolve=false) {
    return $resolve ? realpath($this->node->path) : readlink($this->node->path);
  }
 
  public function __call($method, $args) {
    return call_user_func_array(array($this->node, $method), $args);
  }
}
 
$dir = new Dir("/proc");
$linktodir = new Symlink(new Dir("/proc/self"));
 
var_dump($linktodir->scandir()); // Will be called through __call()
echo $linktodir->target(true), "\n";

<?php

abstract class FsNode
{
  public $path;

  public function __construct($path) {
    $this->path = $path;
  }

  public function rename($newname) {
    rename($this->path, $newname);
    $this->path = $newname;
  }
}

class File extends FsNode
{
  public function getContents() {
    return file_get_contents($this->path);
  }
}

class Dir extends FsNode
{
  public function scandir() {
    return scandir($this->path);
  }
}

class Symlink
{
  protected $node;

  public function __construct($node) {
    $this->node  = $node;
  }

  public function target($resolve=false) {
    return $resolve ? realpath($this->node->path) : readlink($this->node->path);
  }

  public function __call($method, $args) {
    return call_user_func_array(array($this->node, $method), $args);
  }
}

$dir = new Dir("/proc");
$linktodir = new Symlink(new Dir("/proc/self"));

var_dump($linktodir->scandir()); // Will be called through __call()
echo $linktodir->target(true), "\n";

A disadvantage is that is no longer possible to see if a node is a dir by using instanceof. Also, if most of the methods are defined in the wrapped class, this solution will hurt performance.

Mixin

A far more interesting approach is to use a mixins. When you call a non-static method, $this is always passed to that method. This is also the case if the calling object is not inherited from the called class. We can use that to our advantage to do the reverse of the wrapper.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49

abstract class FsNode
{
  public $mixin;
  public $path;
 
  public function __construct($path, $mixin=null) {
    $this->path = $path;
    $this->mixin = $mixin;
  }
 
  function rename($newname) {
    rename($this->path, $newname);
    $this->path = $newname;
  }
 
  public  function __call($method, $args) {
    if (isset($this->mixin) && ctype_alnum($method) && is_callable(array($this->mixin, $method))) {
      return eval("return {$this->mixin}::$method(" . (!empty($args) ? '$args[' . join('], $args[', array_keys($args)) . ']' : '') . ");");
    }
    trigger_error("Call to undefined method " . get_class($this) . "::$method()", E_USER_ERROR);
  }
}
 
class File extends FsNode
{
  public function getContents() {
    return file_get_contents($this->path);
  }
}
 
class Dir extends FsNode
{
  public function scandir() {
    return scandir($this->path);
  }
}
 
class Symlink extends FsNode
{
  public function target($resolve=false) {
    return $resolve ? realpath($this->path) : readlink($this->path);
  }
}
 
$dir = new Dir("/proc");
$linktodir = new Dir("/proc/self", 'Symlink');
 
var_dump($linktodir->scandir());
echo $linktodir->target(true), "\n"; // Will be called through __call()

abstract class FsNode
{
  public $mixin;
  public $path;

  public function __construct($path, $mixin=null) {
    $this->path = $path;
    $this->mixin = $mixin;
  }

  function rename($newname) {
    rename($this->path, $newname);
    $this->path = $newname;
  }

  public  function __call($method, $args) {
    if (isset($this->mixin) && ctype_alnum($method) && is_callable(array($this->mixin, $method))) {
      return eval("return {$this->mixin}::$method(" . (!empty($args) ? '$args[' . join('], $args[', array_keys($args)) . ']' : '') . ");");
    }
    trigger_error("Call to undefined method " . get_class($this) . "::$method()", E_USER_ERROR);
  }
}

class File extends FsNode
{
  public function getContents() {
    return file_get_contents($this->path);
  }
}

class Dir extends FsNode
{
  public function scandir() {
    return scandir($this->path);
  }
}

class Symlink extends FsNode
{
  public function target($resolve=false) {
    return $resolve ? realpath($this->path) : readlink($this->path);
  }
}

$dir = new Dir("/proc");
$linktodir = new Dir("/proc/self", 'Symlink');

var_dump($linktodir->scandir());
echo $linktodir->target(true), "\n"; // Will be called through __call()

caveat: The Symlink class is never instantiated. Properties defined in the Symlink class are ignored. Also, since Symlink doesn’t extends Dir, it’s not possible to access protected properties defined in Dir.

Compile time mixin

To see if a node is a symlink, you would need to do

1
2
3

if (isset($file->mixin) && ($file->mixin === 'Symlink' || is_subclass_of($file->mixin, 'Symlink'))) {
  //...
}

if (isset($file->mixin) && ($file->mixin === 'Symlink' || is_subclass_of($file->mixin, 'Symlink'))) {
  //...
}

It would be nicer if you could simply use instance of. This is only possible by defining all combination. We can still use mixins though to prevent having to duplicate code.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46

abstract class FsNode
{
  protected $mixin;
  // Same as above
}
 
class File extends FsNode
{
  // Same as above
}
 
class Dir extends FsNode
{
  // Same as above
}
 
interface Symlink
{}
 
class Symlink_Methods extends FsNode
{
  public function target($resolve=false) {
    return $resolve ? realpath($this->path) : readlink($this->path);
  }
}
 
class SymlinkFile extends File implements Symlink
{
  protected $mixin = 'Symlink_Methods';
}
 
class SymlinkDir extends Dir implements Symlink
{
  protected $mixin = 'Symlink_Methods';
}
 
$dir = new Dir("/proc");
$linktodir = new SymlinkDir("/proc/self");
 
var_dump($linktodir->scandir());
echo $linktodir->target(true), "\n"; // Will be called through __call()
 
if ($dir instanceof Dir)  ; // True
if ($dir instanceof Symlink)  ; // False
if ($linktodir instanceof Dir)  ; // True
if ($linktodir instanceof Symlink)  ; // True

abstract class FsNode
{
  protected $mixin;
  // Same as above
}

class File extends FsNode
{
  // Same as above
}

class Dir extends FsNode
{
  // Same as above
}

interface Symlink
{}

class Symlink_Methods extends FsNode
{
  public function target($resolve=false) {
    return $resolve ? realpath($this->path) : readlink($this->path);
  }
}

class SymlinkFile extends File implements Symlink
{
  protected $mixin = 'Symlink_Methods';
}

class SymlinkDir extends Dir implements Symlink
{
  protected $mixin = 'Symlink_Methods';
}

$dir = new Dir("/proc");
$linktodir = new SymlinkDir("/proc/self");

var_dump($linktodir->scandir());
echo $linktodir->target(true), "\n"; // Will be called through __call()

if ($dir instanceof Dir)  ; // True
if ($dir instanceof Symlink)  ; // False
if ($linktodir instanceof Dir)  ; // True
if ($linktodir instanceof Symlink)  ; // True

The hardened-php project has yet to release a suhosin patch for PHP 5.3.0. We’re already using PHP 5.3, therefore I’ve modified the 0.9.7 patch for 5.2.10 to work with 5.3.0.

-> Download Suhosin patch for PHP 5.3 <-

Since a few months I’ve done away with using the Gnome main menu. Instead I use Gnome Do. I removed the bottom toolbar long ago, because always use alt-tab.

I’m not using the top toolbar much either. It was just taking up valuable screen space. I contains only the notification area and a logout button. I was looking at a way to remove it completely. The answer came in the Compiz widget layer. By placing it on the widget layer, fullsize windows actually fill the full screen, but the notification area is still available for applications who need it.

To move Gnome panel to the Widget layer, open ‘CompizConfig Settings Manager’ and enable ‘Widget Layer’. Go to tab ‘Behaviour’ and add the following text for the ‘Widget Windows’ field:

(class=Gnome-panel & type=Dock)

The desktop will now be completely clean:

With we can display the widget layer, where the panel is found:

PS. The widgets you see on the widget layer are screenlets. Ubuntu has the screenlets package in the universe repository.

I’ve seen a lot of methods used to take a website temporarily off-line for maintenance. Most involve a using PHP to disable the site or renaming the index file. There is however a far better method of doing this, by placing the following in the vhost file or in an .htaccess file in the document root:

Header always set Retry-After "Thu, 18 Jun 2009 08:00:00 +0200"
Redirect 503 /

This way you are sure no part of the site is used. Also by returning a 503 http response, search-engine crawlers will not reindex your site right at the moment it is down. You can use ‘ErrorDocument’ to place a different text than the apache default.

Sun will be bought by Oracle. Will this be the beginning of the end of MySQL?

MySQL has a serious market share. For that oracle has already tried to buy MySQL back in 2006. In an interview responding to the offer in 2006, MySQL chief Marten Mickos told that the reason for declining was that they wanted to keep MySQL an independent product. From that I assume that oracle wanted to merge Oracle DB and MySQL technology. Even though MySQL will be part of a major merger for which the rules aren’t yet clear, you might think that Oracle hasn’t changed their ideas about what they want with MySQL in the last 3 years.

Won’t MySQL just lose most of its market share if it become something else. Other databases like PostgreSQL have been making mayor steps and are in many expects better than MySQL. MySQL has remained to be the only serious open-source RDBMS in respects of market share though. I believe this is mainly because MySQL is known, tried and tested. This might be a fragile thing though.

Based on Oracle’s decision, I might just take a more serious look at PostgreSQL. Changing is usually not so nice, but change often is.

Any thoughts? Leave a comment or trackback.

Associated websites often share user information, so a visitor only has to register once and can use that username and password for all sites. A good example for this is Google. You can use you google account for GMail, Blogger, iGoogle, google code, etc. This is nice, but it would be even nicer if logging in for GMail would mean I’m also logged in for the other websites. For that you need to implement single sign-on (SSO).

There are many single sign-on applications and protocols. Most of these are fairly complex. Applications often come with full user management solutions. This makes them difficult to integrate. Most solutions also don’t work well with AJAX, because redirection is used to let the visitor log in at the SSO server.

I’ve written a simple single sign-on solution (400 lines of code), which works by linking sessions. This solutions works for normal websites as well as AJAX sites.

Without SSO

Let’s start with a website that doesn’t have SSO.

The client requests the index page. The page requires that the visitor is logged in. The server creates a new session and sends redirect to the login page. After the visitor has logged in, it displays the index page.

How it works

When using SSO, when can distinguish 3 parties:

• Client – This is the browser of the visitor
• Broker – The website which is visited
• Server – The place that holds the user information

The broker will talk to the server in name of the client. For that we want the broker to use the same session as the client. However the client won’t pass the session id which it has at the server, since it’s at another domain. Instead the broker will ask the client to pass a token to the server. The server uses the token, in combination with a secret word, to create a session key which is linked session of the client. The broker also know the token and the secret word and can therefore generate the same session key, which it uses to proxy login/logout commands and request info from the server.

First visit

-> Try it! (jan/jan1)<-

When you visit a broker website, it will check to see if a token cookie already exists. It it doesn’t it, the broker sends a redirect to the server, giving the command to attach sessions and specifying the broker identity, a random token and the originally requested URL. It saves the token in a cookie.

The server will generate a session key based on the broker identity, the secret word of the broker and the token and link this to the session of the client. The session key contains a checksum, so hackers can go out and use random session keys to grab session info. The server redirects the client back to the original URL. After this, the client can talk to the broker, the same way it does when not using SSO.

The client requests the index page at the broker. The page requires that the visitor is logged in. The broker generates the session key, using the token and the secret word, and request the user information at the server. The server responds to the broker that the visitor is not logged. The broker redirects the client to the login page.

The client logs in, sending the username and password to the broker. The broker sends the username and password to the server, again passing the session key. The server returns that login is successful to the broker. The broker redirects the client to the index page. For the index page, the broker will request the user information from the server.

Visiting another affiliate

-> Try it! <-

You visit another broker. It also checks for a token cookie. Since each broker is on their own domain, they have different cookies, so no token cookie will be found. The broker will redirect to the server attach to the user session.

The server attaches a session key generated for this broker, which differs from the one for the first broker. It attaches it to the user session. This is the same session the first broker used. The server will redirect back to the original URL.

The client requests the index page at the broker. The broker will request user information from the server. Since the visitor is already logged in, the server returns this information. The index page is shown to the visitor.

Using AJAX / Javascript

-> Try it! <-
SSO and AJAX / RIA applications often don’t go well together. With this type of application, you do not want to leave the page. The application is static and you get the data and do all actions through AJAX. Redirecting an AJAX call to a different website won’t because of cross-site scripting protection within the browser.

With this solution the client only needs to attach the session by providing the server with a token generated by the broker. That attach request doesn’t return any information. After attaching the client doesn’t talk at all to the server any more. Authentication can be done as normal.

The client check for the token cookie. It it doesn’t exists, he requests the attach URL from the broker. This attach url includes the broker name and the token, but not a original request URL. The client will open the received url in an <img> and wait until the image is loaded.

The server attaches the browser session key to the user session. When it’s done it outputs a PNG image. When this image is received by the client, it knows the server has connected the sessions and the broker can be used for authentication. The broker will work as a proxy, passing commands and requests to the sso server and return results to the client.

To conclude

By connecting sessions, you give the broker the power to act as the client. This method can only be used if you trust all brokers involved. The login information is send through the broker, which he can easily store if the broker has bad intentions.

Don’t be square, please share!

Demo
Broker ‘Alex’
Broker ‘Binck’
AJAX broker – created by Lukasz ‘Uzza’ Lipinski using Ajax.org PlatForm.

Play around, logging in and out at different brokers. Refresh the other after. Available users:
jan / jan1
peter / peter1
bart / bart1
henk / henk1

Download
Download the code @ github
2009-11-16: Updated the software with bugfixes mentioned in comments + alternative for using symlinks.

If I overlooked security issues with this SSO implementation, please leave a comment below.

This is a simple implementation of SSO. If you want enterprise stuff have a look at Novell Single Sign-On.