Let's Begin Coding

Make sure you read through the above link; I'd write it out here but I'd likely run out of room. The first thing we're going to do is create our users table. This is simple enough that I won't explain it; you should understand what's going on in the following SQL.

CREATE TABLE users
(
 uid SMALLINT NOT NULL AUTO_INCREMENT,
 username VARCHAR(10) NOT NULL DEFAULT '',
 password VARCHAR(30) NOT NULL DEFAULT '',
 PRIMARY KEY(uid),
 UNIQUE username(username)
);
 
INSERT INTO users (username, password)
VALUES
 ('user1', SHA1('hispass')),
 ('another', SHA1('theirpass')),
 ('you', SHA1('yourpass')),
 ('me', SHA1('mypass'));

The only thing you may be unfamiliar with is the UNIQUE keyword. MySQL supports a variety of indexes; all are used to make querying more efficient. PRIMARY KEY is one, and you're bound to be familiar with it. UNIQUE is another, which has the constraint of not allowing duplicate entries with the same value.

Now what we need is a function that will authenticate a user for us. What this function needs to is simple:

1. Accept a username and password as parameters.
2. Perform a SELECT statement on the users table, looking for row with the correct username and password
3. If found, return true.
4. Otherwise return false.

One small detail we need to be mindful of during this is the output of the SHA1() function. Our password field in the users table is only 30 characters long so we'll need to make sure we only take 30 characters from SHA1(). We do this with SUBSTR().

CREATE FUNCTION valid_login(user VARCHAR(10), pass VARCHAR(20))
 RETURNS BOOLEAN DETERMINISTIC
BEGIN
 DECLARE valid BOOLEAN;
 DECLARE retRows INT;
 
 SELECT COUNT(*)
  INTO retRows
  FROM users
  WHERE username = user AND password = SUBSTR(SHA1(pass) FROM 1 FOR 30);
 
 IF(retRows > 0) THEN
  SET valid = TRUE;
 ELSE
  SET valid = FALSE;
 END IF;
 
 RETURN(valid);
END;

Read through that and make sure you understand what's happening. It's a deterministic function because the same username and password will always have the same response, either true or false. We SELECT into an INT variable the number of rows returned from our validation query. That query is merely a check for a row with the same username and password given in the parameters. If the return is not zero, then we set the valid variable to true. Otherwise set it to false. And in the end, return our answer.

An example of using the function:

SELECT valid_login('user1', 'hispass');
 

That will return a result of 1. In other words, it's valid. That one line right there is all we'll do in our authentication query within the web application. Pretty damn simple isn't it?