Login via MySQL
It seems not many people know how to do this, or they spend a long time figuring it out. I figured I might as well post it up for all to see.
This tutorial will teach you to set up a mysql table in a currently existing database, and use that mysql table to store usernames and passwords of your members. It will then show you how to verify that a user with a specific username and password exists.
This tutorial uses PHP, MySQL queries, and some HTML to achieve this.
Some important things to keep in mind are:
- The database never keeps track of an unencrypted member password.
- Members passwords must be case-sensitive, but usernames are optionally case-sensitive.
We will be storing the members’ passwords using the encryption method MD5. It is secure and easy to access in PHP.
So, first is to set up the table. I am presuming here that you already have a MySQL database set up. If you don’t, Google it.
We need to know the columns we will be using to specify our table. We need to store the members ID, the members username, and the members password. This is the bare minimum, and you can add your custom fields afterwards. A MySQL query should do the trick (you can run this from your database).
CREATE TABLE tblMembers( id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, username VARCHAR(32) NOT NULL, password VARCHAR(32) NOT NULL)
Feel free to insert a few test members.
The idea is this: we will store a password on the database as an MD5 hash (which is a case sensitive encryption) and we will encrypt the password being used as a login attempt to verify this password once we’ve found a matching username. If there is no matching username, then the user does not exist. If the password does not match the matched username, then the password is incorrect. If the password matches the username, then the verification is a success.
The PHP code needs to do all of the above. Below is a script that does so (but you can’t just copy and paste!).
<?
/*
* connect to mysql
* select database
* etc
*/
// error codes
define('__SUCCESS', true);
define('__ERRNUM_INVALIDUSERNAME', 100);
define('__ERRNUM_INCORRECTPASSWORD', 101);
function verify_credentials( $username, $password, $password_encrypted = FALSE, &$user = NULL)
{
// retrieve members if their usernames match
// lower() forces the username to lower case
// and so the comparison is case insensitive
$query = mysql_query(
sprintf(
"SELECT username, password FROM tblMembers WHERE lower(username) = '%s';",
mysql_real_escape_string(strtolower($username))
)
);
// if the query has no rows, it means the username does not exist
if (mysql_num_rows($query) == 0)
{
// return the error code for 'invalid username'
return __ERRNUM_INVALIDUSERNAME;
}
// if password is encrypted, don't encrypt again!
// if password isn't encrypt it, encrypt it!
$encrypted = ($password_encrypted) ? $password : md5($password);
// loop through the found members to find a matching password
while ($row = mysql_fetch_array($query))
{
// now we can access username and password of this row
// the username is irrelevant - it MUST match or it shouldn't
// have been found by the query
// so focus on the password
if ($encrypted == $row['password'])
{
// matching password!
// fill user if it was set
if (NULL != $user)
{
// give username and password to user
$user['username'] = $row['username'];
$user['password'] = $row['password'];
}
// report verification success
return __SUCCESS;
}
}
// if we're still in this scope, it means the password wasn't found
// so the password must have been incorrect
return __ERRNUM_INCORRECTPASSWORD;
}
/*
* close connection
*/
?>
You can call this function as follows:
$username = "Username";
$password = "password";
echo "Attempting login with username {$username} and password {$password}<br/>";
$result = verify_credentials($username, $password, FALSE, &$user);
if ($result == __SUCCESS)
{
echo "Login successful. User found with credentials: {$user['username']} : {$user['password']}<br/>";
} else if ($result == __ERRNUM_INVALIDUSERNAME)
{
echo "Login failed. User could not be found.<br/>";
} else if ($result == __ERRNUM_INCORRECTPASSWORD)
{
echo "Login failed. Incorrect password. Remember: Passwords are case sensitive.<br/>";
} else
{
// should never get here
echo "Serious error occurred!";
}
Using a few cookies and a mysql INSERT query, you should be able to make a login and registration from here. I’m not going to give you all the code, that would be too easy!
Happy coding!
Trackbacks & Pingbacks