// you’re reading...

MySQL

Using MySQL Stored Procedures for User Authentication

Scenario: You want to authenticate users into a private web site by accepting an e-mail address and a password. You don’t want to do a bunch of security on the front end however you do want to use a token on the client for various purposes. Also, you want the tokens to expire automatically and be removed from the database.

*Note: you should always encrypt or hash passwords in your database. They are clear text in this example for demonstration purposes, but could easily be wrapped in a MD5() function without changing behavior.

Here is my solution: Utilize MySQL stored procedure functionality to manage both user authentication and the lifespan of your users tokens.

-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 
-- Users table
CREATE TABLE `users` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`partner_id` mediumint(8) unsigned NOT NULL,
`email` varchar(165) DEFAULT NULL,
`password` varchar(32) NOT NULL,
`status` tinyint(3) unsigned DEFAULT NULL,
`date_created` datetime NOT NULL,
PRIMARY KEY (`id`),
KEY `partner_id` (`partner_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED;
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 
-- Tokens table
CREATE TABLE `user_tokens` (
`user_id` int(10) unsigned NOT NULL,
`token_hash` varchar(32) NOT NULL,
`date_created` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED;
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 
-- Create a user account
INSERT INTO users VALUES (1000,1500,'user@test.com','password1',1,NOW());
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 
-- Create sproc to generate and return a user token
DROP PROCEDURE IF EXISTS sproc_getUserToken;
DELIMITER $$
CREATE PROCEDURE sproc_getUserToken(IN username VARCHAR(32), IN password VARCHAR(32))
BEGIN
DECLARE idInt int(10) unsigned;
DECLARE tokenHash VARCHAR(32);
DELETE FROM user_tokens WHERE date_created < DATE_SUB(NOW(), INTERVAL 30 MINUTE);
SELECT IFNULL(id, 0) INTO idInt FROM users WHERE email = username AND password = password LIMIT 1;
IF idInt > 0 THEN
SELECT MD5(CONCAT(NOW(),username,password,RAND())) INTO tokenHash;
INSERT INTO user_tokens (user_id,token_hash,date_created) 
VALUES (idInt,tokenHash,NOW());
SELECT tokenHash AS Token;
ELSE
SELECT 0 AS Token;
END IF;
END;
$$
DELIMITER ;
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 
-- Generate some tokens
CALL sproc_getUserToken('user@test.com','password1'); SELECT * FROM user_tokens;
CALL sproc_getUserToken('user@test.com','password1'); SELECT * FROM user_tokens;
CALL sproc_getUserToken('user@test.com','password1'); SELECT * FROM user_tokens;

As you can see, our user_tokens table has some content. Now, let’s test our deletion logic!

UPDATE user_tokens SET date_created = DATE_SUB(NOW(), INTERVAL 31 MINUTE);
mysql> CALL sproc_getUserToken('user@test.com','password1');
+----------------------------------+
| Token                            |
+----------------------------------+
| a17ded108eb20cf60170d3ec2d3e502c |
+----------------------------------+
1 row in set (0.00 sec)
mysql> CALL sproc_getUserToken('not-in@system.com','password1');
+-------+
| Token |
+-------+
|     0 |
+-------+
1 row in set (0.00 sec)

Since that seems to have worked, let’s move on to using the token to authenticate some users:

-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 
-- Create the auth sproc
DROP PROCEDURE IF EXISTS sproc_authenticateToken;
DELIMITER $$
CREATE PROCEDURE sproc_authenticateToken(IN tokenVar VARCHAR(32))
BEGIN
DECLARE userID INT UNSIGNED;
DELETE FROM user_tokens WHERE date_created < DATE_SUB(NOW(), INTERVAL 30 MINUTE);
SELECT IFNULL(user_id,0) INTO userID FROM user_tokens WHERE token_hash = tokenVar;
IF userID > 0 THEN
SELECT 1 AS auth;
ELSE
SELECT 0 AS auth;
END IF;
END;
$$
DELIMITER ;
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 
-- Test
mysql> CALL sproc_authenticateToken('a17ded108eb20cf60170d3ec2d3e502c');
+------+
| auth |
+------+
|    1 |
+------+
1 row in set (0.00 sec)
mysql> CALL sproc_authenticateToken('SOME Junk');
+------+
| auth |
+------+
|    0 |
+------+
1 row in set (0.00 sec) 
SELECT * FROM user_tokens;

Your app tests for 1 or 0 and you’re done!

Discussion

2 comments for “Using MySQL Stored Procedures for User Authentication”

  1. Randy,

    I see some security issues with this code. You should not be using the password or username as components in the token generation. Tokens should be purely random. Also, you should not be storing passwords in plain text at the database level. I recommend, at a minimum, attaching a random salt value (different for each user) and then using a strong hashing function such as sha256. Iterative hashing of the password would be even better.

    Posted by Asaph | April 4, 2011, 1:35 pm
  2. Very valid points, Asaph. If you look closely, you’ll notice the RAND() function as part of the hash creation. One could use RAND() by itself, but your mileage may vary.

    Storing passwords as plain text is remedied in this scenario by wrapping the password in a MD5(), SHA1(), SHA2(), etc. or your own code inspired. You’ll notice that the password field was set as a 32 character VARCHAR. This is really intended to store passwords as MD5 hashes. However, for this example, I didn’t want to get hung up in an encryption “yack-shave” to mask the intent of using a ACID layer API to remove expiring tokens at a policy level.

    I’ll add to the requirements at the top of this document so the intent is more clear. Thanks!

    Posted by Randy | April 5, 2011, 8:27 am

Post a comment

Help support my site and buy a domain name at http://domainsemailhosting.com/