Many sites today requires a login to be able to see your profile, post messages or download various items. This login form is usually the same, you need to enter a username and a password. The process of checking if the supplied query strings are correct is quite simple. I usually check 2 things:

  1. Does the username exist at all in the database table?
  2. Does the username and password match a row in the database table?

This is done over and over using the PHP language mixed with a various number of MySQL queries. Instead of using a lot of the PHP control structures we are going to write a SQL function in the MySQL database, which will handle all the if and elses.  In the end we will be able to call the MySQL database only once and get all the information we need to continue guiding our user to the correct place. This is how the MySQL call will look like in PHP:

SELECT fkt_login('your_username', 'your_password');

In return we will use a global MySQL @variable and a return statement. The return statement will let us know if the function call was successful or denied, by returning the userid or 0. The @variable will contain the possible error message, or if we want to make it a bit more customizable, an index matching a post in a key=>value array.

When writing and executing functions and stored procedures you can use the MySQL command Line. Although, it is much easier to open the MySQL Query Browser (Read more about the MySQL Query Browser). With the MySQL Query Browser running you simply click File -> New Script Tab. The current window will change a bit and now you are able to write a function or stored procedure. A function or stored procedure is a piece of code you save in the MySQL database, like ‘normal’ PHP functions. You are able to call these functions/procedures in the same way you call normal select, insert, update and delete statements. The different between a function and stored procedure is that a function have a return statement, a procedure do a lot of things on the side and doesn’t return anything. Even though I just pointed out the difference you are able to have a return statement in a procedure. Anyway, having the login function inside the database we can take it with us if we change the programming language or want to be able to update different MySQL queries via our database. I try to abstract as much as possible into different functions and procedures so my PHP programming turns easy and nice.

OK, back to the code. Before we start you should have two things in mind.

  1. I’m pretty bad at using good variable and method names
  2. Always remember to check incoming form parameters using the addslahes() PHP method.

So, the first line in our SQL Login function looks like this:

DELIMITER $$

Why did we add this? I will tell you. 🙂 When we are writing SQL functions or procedures we want to use the normal delimiter, ; , inside our code. To avoid confusion we change the current delimiter to $$, meaning our newly created script tab will have the $$ as the delimiter between statements.

DROP FUNCTION IF EXISTS login $$

This next line is used for debugging and working with the function/procedure. Every now and then you might want to update your current function. If you already have a function defined with the same name you will get an error. This line will prevent this. This is also the first example of the redefined use of the delimiter.

CREATE FUNCTION login(username VARCHAR(50), password VARCHAR(50)) RETURNS int(2)

This is where the fun begins! We start with a CREATE FUNCTION statement followed by the function name (login) and the parameters connected to the function (username and password). We need to declare the data types of the incoming parameters. You can use the same data types in the parameters list found in MySQL. At the very end of the line we declare the RETURNS data type, in our case an int.

BEGIN

Short line. Now we say: ‘This is where our function begins’.

DECLARE user_exist int;
DECLARE user_approved int;
DECLARE user_id int;

These three lines declare three different local variables: user_exist, user_approved and user_id. Each variable has a data type connected to it.

SELECT COUNT(*) FROM user WHERE user.username = username INTO user_exist;

This piece of code might look a bit different from what you are used to. Notice the last part, INTO user_exist. When we work with functions and procedures we cannot save record sets in variables. Therefore we need to do account (since it only returns one result). In our function this line counts how many users we have in our table with the username exactly the same as the incoming username parameter.

IF (user_exist = 0) THEN
SET @msg = "Username does not exist";
return 0;

These three lines includes an IF, SET and RETURN. We check if we have found any user matching the incoming parameter. If we didn’t find any user with that username we SET the @msg variable to an error message and returns 0. There are a few things to look out for. Notice in the IF statement, we do not do a double equal (==), we just do one. We could just set @msg to an index found in an error array instead of entering hard-coded language specific text right in the function. We return 0 since the function call failed.

ELSE
SELECT COUNT(*) FROM user WHERE user.username = username AND user.password = password INTO user_approved;
IF (user_approved != 0) THEN
SELECT id FROM user WHERE user.username = username AND user.password = password INTO user_id;
SET @msg = "Login valid";
return user_id;
ELSE
SET @msg = "Username and Password did not match";
return 0;
END IF;
END IF;

Many lines! We continue the IF statement from the previous line. We will come to this ELSE if we have found a user matching the incoming username. Now we see if the incoming password match the saved password in the database. We save this check in the user_approved variable. IF we find a user matching our username AND password we select the user id INTO the user_id variable. We set the @msg to ‘Login valid’ and returns the user_id. By using a global @msg variable and a return statement we can return both the user_id and a message at the same time. If we don’t find a match between the user and password we SET the @msg to another error message and returns 0. We end this if and the if from the previous post.

END $$
DELIMITER ;

At the very end we END our function with the END $$ statement, using the redefined delimiter. And lastly we set the default DELIMITER back to ;.

A rather simple and straight forward function. Some people might like the first check when we only look if the username exist, regardless of the password. This returned message can be used if you want to tell the user that this username really doesn’t exist instead of using the ‘Username / Password doesn’t match’ error. As I said before, remember to check your incoming parameters BEFORE sending them to the SQL function. You can do this by using the addslashes() PHP function and if it’s a password you can use either the md5() or sha() methods to crypt the passwords.

Hope you like it! Comments, questions, suggestions or anything else are very welcome! I try to improve daily, your ideas might enlighten me! 🙂

July

, , , , , , , , ,

July 1, 2008

1 Comment