MySQL Store: Unnecessary reduction of the session token key space due to in-case-sensitive column creation

[Khoulaiz]

Hey!

This is the create statement of the mysql session store:

CREATE TABLE IF NOT EXISTS `sessions` (`token` CHAR(43) PRIMARY KEY, `data` BLOB NOT NULL, `expiry` TIMESTAMP(6) NOT NULL) engine=InnoDB charset=UTF8;

The default for CHAR columns in mysql is to use in-case-sensitive comparison, which leads to the result that the session cookie values for mysql stores are in-case-sensitive. This is not really a problem since the session key is still darn long, but it is not necessary to make it in-case-sensitive.

Example for verification (z === Z):

mysql> SELECT token FROM sessions WHERE token = 'zJnVhBHFCAI-YT6bnEZVzuo0VOIbLJh2s_faKlyGS5U';
+---------------------------------------------+
| token                                       |
+---------------------------------------------+
| ZJnVhBHFCAI-YT6bnEZVzuo0VOIbLJh2s_faKlygS5U |
+---------------------------------------------+

Easiest fix for this is to define a collate in the create statement like this:

CREATE TABLE IF NOT EXISTS `sessions` (`token` CHAR(43) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin PRIMARY KEY, `data` BLOB NOT NULL, `expiry` TIMESTAMP(6) NOT NULL) engine=InnoDB charset=UTF8;

Since the collate is now set to be binary, it is case sensitive;

This would be an easy fix in the mysql driver in the initDatabase function.

It is also possible to alter the table for existing databases:

alter table sessions modify token char(43) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;