I have the following MySQL session schema:

CREATE TABLE `SessionData` (
  `id` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `data` text COLLATE utf8_unicode_ci,
  `date` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `date` (`date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

So I want to prevent users reading each other session data via e.g. SQL Injection. I want to secure it using MySQL view, procedure or something like this. Is there is some common, secure way to do it? I think it's common thing to do, as with this and remote shell it's difficult to overwrite anything on the server, except for it's own session data, but not the files and the SQL and SQL Session, so it's like read only, but with write only to it's own row.

I make a single column, single row data set:

CREATE TABLE `SessionId` (
  `id` varchar(50) COLLATE utf8_unicode_ci DEFAULT '',
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

I insert an empty string into it:

INSERT INTO SessionId (id) VALUES ('');

So then I can create a view. In this case it's using "frontuser", as MySQL user.


  `SessionData`.`id`   AS `id`,
  `SessionData`.`data` AS `data`,
  `SessionData`.`date` AS `date`
FROM (`SessionId`
   JOIN `SessionData`
     ON ((`SessionId`.`id` = `SessionData`.`id`))))$$


How to INSERT session:

INSERT INTO SessionView (id, `data`, `date`)
    HEX(AES_ENCRYPT("Cookie Value", "Random Key from Config")),
    HEX(AES_ENCRYPT("128bit random block + Session Data", "Random Key from Config")),

How to Check Session

UPDATE SessionId SET id = HEX(AES_ENCRYPT("Cookie Value", "Random Key from Config"));
SELECT AES_DECRYPT(UNHEX(`data`), "Random Key from Config") FROM SessionView;
UPDATE SessionId SET id = '';

So I grant only SELECT, UPDATE and INSERT to SessionView view. Since the keys are randomized and there is no way to extract all keys, I can create the session but I cannot browse them all.

  • I should note that if you're using parameterised queries (e.g. MySQLi / PDO) properly, you shouldn't have to worry about SQL injection attacks at all.
    – Polynomial
    Jul 18, 2012 at 7:30
  • I have various kinds of apps connecting to MySQL, C#, Python, PHP, C++, and I want to confine the whole DB, so with SQL Shell it's impossible to get into all data without breaching root access to MySQL. Jul 18, 2012 at 7:46

1 Answer 1


One of the biggest security threats of storing session data in the database is that an attacker can use SQL Injection to obtain the Session id and use that to authenticate instead of cracking a password hash. The solution is to encrypt the session id. MySQL's aes_encrypt() is not a bad choice for this data type even though its ECB mode. Because the session id is random you don't have to worry about a repeated plain text with your encryption key (CBC+Random IV usually solves this problem). A hashes session id might be easier depending on your deployment.

Keep in mind that you have to keep the encryption key somewhere and an attacker maybe able to read it using the load_file() MySQL function with SQL Injection. Make sure file_privs are disabled for the MySQL user account that your web application uses.

However the data its self maybe sensitive. If that is the case, then this may also need to be encrypted, and aes_encrypt() would be a poor choice for this type of data. CBC+Random IV is a good choice. MySQL doesn't allow query stacking, so an attacker will never be allowed to insert a session record. If this was MS-SQL, then you would have to worry about this attack.

  • OK very cool, I will encrypt also customer information, so it's safer with SQL Injection too. Jul 17, 2012 at 18:40
  • How large is the session ID, block mode only really matters at more than one block anyway? AES is a 128bit block.
    – ewanm89
    Jul 17, 2012 at 22:48
  • It's 26 characters, like 00i3bofoblkmv1g2d8i6l4o3v7, session data is > 100 chars Jul 17, 2012 at 23:17
  • @ewanm89 not the plain text of first block is XOR'ed with the IV. Which means that the same plain text won't produce the same cipher text, but that doesn't matter if the plain text is always random.
    – rook
    Jul 17, 2012 at 23:44
  • To the data I add the the 256bit of data from /dev/urandom, so I have it all properly randomized. Jul 18, 2012 at 8:42

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .