Tell me more ×
IT Security Stack Exchange is a question and answer site for IT security professionals. It's 100% free, no registration required.
up vote 2 down vote favorite

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.

DELIMITER $$
DROP VIEW IF EXISTS `SessionView`$$

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `SessionView` AS (
SELECT
  `SessionData`.`id`   AS `id`,
  `SessionData`.`data` AS `data`,
  `SessionData`.`date` AS `date`
FROM (`SessionId`
   JOIN `SessionData`
     ON ((`SessionId`.`id` = `SessionData`.`id`))))$$

DELIMITER ;

How to INSERT session:

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

How to Check Session

BEGIN;
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 = '';
COMMIT;

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.

share|improve this question
+1 great defense in depth question. – Rook Jul 17 '12 at 17:58
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 '12 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. – Andrew Smith Jul 18 '12 at 7:46

1 Answer

up vote 4 down vote accepted

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).

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.

share|improve this answer
OK very cool, I will encrypt also customer information, so it's safer with SQL Injection too. – Andrew Smith Jul 17 '12 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 '12 at 22:48
It's 26 characters, like 00i3bofoblkmv1g2d8i6l4o3v7, session data is > 100 chars – Andrew Smith Jul 17 '12 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 '12 at 23:44
To the data I add the the 256bit of data from /dev/urandom, so I have it all properly randomized. – Andrew Smith Jul 18 '12 at 8:42

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.