PHP MySQL Login Script

June 8th, 200644 Comments »

Login and user authentication is the most common feature in any dynamic website. Before we go any further, Download the PHP Login Script. The zip file contains the complete PHP source code of our authentication script and a SQL file to create and populate the required tables. Download the file, extract its contents and run the SQL file to create the members table.

Update (14 Aug 2008): Moved database connection details to config.php file. Edit config.php file to specify your own database connection details.

Update (05 Feb 2008): Fixed a bug in the registration form.

Update: A simple registration form has been added to the download package.

login-form.php page simply contains a form with two fields: login and password, and should be self-explanatory in what it does. login-exec.php script is where all the action is.

PHP Tip : Always try to keep the form and the submission/action page separate. Unless necessary, don’t make pages post information to themselves. I normally append “-form” to pages containing the form and append “-exec” to the page which handles the submission. This makes the application easier to manage when the application logic gets more complex.

Lets go through the code step by step. In the first few lines of login-exec.php, we simply start a session and open a connection to mysql database.

Escape special characters like “, ‘ , \

In the simplest case, special characters may simply break your query. In a more extreme case, a hacker might use SQL injections to gain access to your application. So it is important that we escape these special characters with a \ (backslash). That is, insert a backslash before each special character.

We can escape special characters (prepend backslash) using mysql_real_escape_string or addslashes functions. In most cases PHP will this do automatically for you. But PHP will do so only if the magic_quotes_gpc setting is set to On in the php.ini file. We first check whether this setting is on or not. If the setting is off, we use mysql_real_escape_string function to escape special characters. If you are using PHP version less that 4.3.0, you can use the addslashes function instead.

A MySQL connection is required before using mysql_real_escape_string() otherwise an error of level E_WARNING is generated.

If the magic quotes setting is on, we do not need escape special characters since PHP has already done it for us. We can check the magic_quotes_gpc by using get_magic_quotes_gpc function.

A simple function to escape special characters

You can use the function below to clean and prepare data for queries. The function goes through the following steps:

  1. Trims the string to remove leading and trailing spaces
  2. If you set the second parameter as true, it will also encode all characters which have HTML character entity equivalents.
  3. The function then checks for PHP version. If version is greater than or equal to 4.3.0, its uses the mysql_real_escape_string() function. Otherwise it uses addslashes() function.
  4. Since the mysql_real_escape_string() only works if there is a connection to the MySQL server, we first check whether we are connected to MySQL server by using the mysql_ping() function.

Query the database

Next we formulate the query which will test whether a user with this login and password exists.

Note that we are not storing passwords in the database as plain text. Instead we are storing the md5 hash of the password. Use md5 function to create a 32 character hash of any string. md5 is one way encryption. That is, once the password is encrypted, there is no way to decrypt it.

So if a md5 hash can not be decrypted, how do we compare the user submitted password with the one in the database? The answer is that we simply generate a md5 hash of the user submitted password and then compare this hash to the one stored in the database.

The query will return a result set with a single row if the login details are correct and zero rows if the login details are incorrect. Use mysql_num_rows to find out the number of rows in the result set and hence determine whether the login details were correct or not.

Store authentication status in session

Once we know that the login details are correct, we need to store this information somewhere, so that the subsequent pages know that the user has been authenticated successfully. We use PHP session for this purpose.

Retrieve the member’s ID from the result set and store it in the session as SESS_MEMBER_ID. Subsequent pages will just need to test for the existence of SESS_MEMBER_ID in the session to verify the authentication status of the user. After storing the member ID in the session, redirect the user to the member-index.php page.

If the login fails, redirect the user to login-failed.php page.

Preventing session fixation attacks

Once we have ascertained that the user supplied login details are correct, we store his ID in a session variable named SESS_MEMBER_ID. But we before we do that, we call the session_regenerate_id() function. This function generates a new session ID while keeping intact any information stored in the session.

How to authenticate individual pages

As mentioned above, the presence or absence of SESS_MEMBER_ID in the session will tell us whether the user is logged in or not. If a variable names SESS_MEMBER_ID exists in the session, then the user has been logged in and authenticated. I have moved this logic to a separate PHP script, auth.php

Now we can just include the auth.php file in any page we want to password protect. See member-index.php and member-profile.php page for examples.

How to logout the user

To logout the user, simply unset the SESS_MEMBER_ID variable. See the logout.php script for example.

PHP login script

Download the Login Script to get the full source code for the above examples.

Here is a brief description of the main files:

  • login-form.php – The login form
  • login-exec.php – This script queries the database to check the login credentials
  • auth.php – This script checks whether the user is logged in or not. Include this script at the top of any page you want to password protect.
  • member-index.php – Sample password protected page
  • member-profile.php – Sample password protected page
  • register-form.php – Registration form for creating new user accounts.
  • register-exec.php – Performs input validation and create new user account.
« PHP MySQL Basics Tutorial
Organize project through include files »

Categorized Under

PHP & MySQL PHP Sessions

About jatinder

» has written 17 posts

44 Comments

  1. Fred Riley says:

    This is a nice collection of scripts. I’ve been searching for some good authentication code and of all the scripts I’ve seen that support password encryption and guard against hack attacks, this is the easiest to understand, the most concise, and the easiest to adapt and deploy. Thanks – very useful, and you’ve saved me writing a system from scratch.

  2. I have been trying to put a Members Only area on my website to no avail. This script is so easy to understand and implement. Thank you so much for this.

  3. Furura says:

    Thank you very much for this script! I’ve been trying to find one that works! And this actually works! Thanks!

  4. Jake says:

    Thanks you for this script ill definitely use this one!

  5. Bright says:

    J! thanks alot for this script – really saved me from all hustle to get me project running! This is definitly a good work.

    B

  6. Adam Scott says:

    That is an amazing code. I have been around to many, many different sites and couldn’t get one that worked as easy and as well as yours. The only thing that I was trying to do was view the entered password, but…what would be the point of such a secure script if you are going to make the password unprotected…very very well done!!!!

  7. ramesh says:

    wonderful script, i like it very much, please provide some more new scripts in php mysql.

    thank you very much for the script

  8. Adelevie says:

    I am a php/mysql nub. How do I make it so member-index.php displays user information, ie, “Welcome, [firstname]”?

  9. Jatinder says:

    Adelevie, you can store user information like name or email in the session and then retrieve this information later on in other pages.

    Take a look at login-exec.php script, line 33. I am storing the user’s member_id (primary key) in the session.

    $_SESSION[‘SESS_MEMBER_ID’]=$member[‘member_id’];

    You can store values from other database columns as well using the same syntax. To learn more about using sessions, please read PHP Sessions Tutorial

  10. Casper says:

    Hello, another newbie here.

    When I use your register-form, it returns with a “First name missing”, “Last name missing” and so on.

    I removed the check from reg-exec, and without it, it does actually register with empty fields.

    Any idea what I might be doing wrong?

  11. Mathew says:

    Would it be possible to also include a, “I forgot my password” script?

  12. Jatinder says:

    Casper,

    Thank you for pointing out the bug. You can download the updated script from the download link in the article above.

  13. ztomsk says:

    I almost never feel the urge to post comments… but I just had to acknowledge your work. I gotta say I am impressed with the simplicity and functionality of your scripts. The code is very clean. Nice work for sure(and i’m sure you can do much more complex coding than this)… you get three claps from me.

  14. Meni says:

    How can I get the script if somebody forgets the password? Can it be send to user via email or simply reseted?

  15. Will says:

    Awesome script. I have one quick question: Is there a simple way I can have three individual private member areas? For example, if a user is a student they are redirected to private1.php, a teacher, they go to private2.php and an administrator to private3.php?

    I’d like it so nobody can see any page but there own (students cannot see teacher or admin pages).

    Thanks so much!

  16. Will says:

    Can anyone offer a Forgot Password script to work with this login? That would be great.

  17. Paul says:

    Thanks for the brilliant scripts and tutorial. Got this up and running withing 24hrs and thats from someone with no previous php/mysql knowledge.

    I am also now trying to work out how I can have client specific areas after the login. Although I’m thinking that Will ^^ could add an additional id to his db that he could assign a number that corresponds with each tier of pages that can be accessed?

  18. Jatinder says:

    [Will]

    I have received a number of requests for “Forgot Password” feature. I will add this feature as soon as I get some time to update this script.

  19. Thomas says:

    Jatinder,
    Great script thanks for sharing. Just a question regarding the storing of database access credentials in a separate php page as apposed to how you have it in your exec pages? Is there any real benefit or standard here?
    Thanks.

  20. Jatinder says:

    [Thomas]

    There are absolutely no benefits in storing access details in the exec pages. The only reason I did that was to keep the script as simple as possible because it is targeted at PHP newbies.

    In fact I have advocated the use of PHP includes in another of my articles.

    But recently I have received a number emails from users asking me how to change the database connection details. Therefore I have moved the connection details to a separate config.php file.

  21. Thanks for the awesome code. It was easy to adapt for use with my existing singleton database connection class. I disagree with your opinion on having separate form/code files, I think you’ll do better to make them all in one, especially for the purpose of a tutorial. I may be a new PHP developer, so there could be a thousand people yelling differently. I do however encourage separate files for classes, and I wish I had seen more object-oriented application here.

  22. Hi there, is it possible to log the user out when a tab/ window is closed?

    Thanks

  23. Jatinder says:

    @Michael Norris
    The login script is using temporary cookies. That is, the session should expire as soon as you close the browser.

    However, if the session persists for you even after restarting the browser, please check your “session.cookie_lifetime”
    setting.

  24. MrFrans says:

    I noticed something odd. You sanitize all user supplied values, but in the sql query you don’t use the sanitized version $password, but you use $_Post[’$password’]

  25. Jatinder says:

    @MrFrans

    We will be converting $_POST[’$password’] into a md5 hash. Therefore we don’t need to sanitize it.

  26. Alawson says:

    Not sure if this is still alive or not but I try it and it doesn’t work. Database authenticates, connects to the table but it says invalid username and password when in fact it is not the incorrect username or password. Just wondering what I could do for troubleshooting? Thanks.

    • jatinder says:

      Are you sure you are storing a md5 hash of the password in the database and not plain text? Other than that I can’t think of any reason for the login script to break.

  27. Mattw350 says:

    Nice work, I used the entire script on XAMPP ofline Cpanel, and it worked Perfectly. But when I tried using it in my Hostgator Cpanel, I had a session_start Warning error, which was constantly displayed on the front of my webpage (register-form.php):
    Warning: session_start() [function.session-start]: Cannot send session cookie – headers already sent by (output started at /home/matt/public_html/register-form.php:8) in /home/matt/public_html/register-form.php on line 98

    I would please like 2 know the case of this, and I’d also like to know if there’s a way I could redirect the error handler’s messages away from the register-form.php using header(“Location: errorpage.html”);

    ? Thanks !

  28. Matt says:

    Mr Jatinder has been of a great help to me, I was lucky to have come across his script, it makes everything about login, registration , creating of MSQL database easier, and i would recommend his work for php newbies. Thanks a Million times!
    You deserve more than this, I wish there was a “Donate” button on this thread.
    Just wondering if i could get the updated version of the script, cause I’d love to have members viewing their separate members-profile.php, rather than all registered members viewing the same member’s profile. Thanks. Awaiting your response.

  29. I have installed the script, and have the login script, and the register script set up, and working. But when I try to run the SQL in the database, I get a sintex error. Not knowing anything about how to set up a database, I’m lost as to how to fix it.

    Is it because its an older file, and I’m using a new version of the MySQL program.

  30. Mattw350 says:

    Thanks, it work perfectly, no more “headers already sent” sessoin error !

    Hey Michael Gochenour, i initially had similar problem trying to get MySQL work with it. This was what i did:

    On the mysql.sql file, change the line immediately after PRIMARY KEY :

    ) TYPE=MyISAM;

    Change That to:

    ) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=latin1

    Then got o your Server, click on “SQL”, then Paste the entire code below, and click ”
    “GO”

    #
    # Table structure for table ‘members’
    #

    CREATE TABLE members (
    member_id int(11) unsigned NOT NULL auto_increment,
    firstname varchar(100) default NULL,
    lastname varchar(100) default NULL,
    login varchar(100) NOT NULL default ”,
    passwd varchar(32) NOT NULL default ”,
    PRIMARY KEY (member_id)
    ) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;

    #
    # Dumping data for table ‘members’
    #

    INSERT INTO members (member_id, firstname, lastname, login, passwd) VALUES(“1″, “Jatinder”, “Thind”, “phpsense”, “ba018360fc26e0cc2e929b8e071f052d”);

  31. Mattw350 says:

    Wonderful Job Mr Jatinder, …just wondering if i could get the updated version of the script, cause I’d love to have members viewing their separate members-profile.php, rather than all registered members viewing the same member’s profile. Thanks. Awaiting your response.

  32. Lester says:

    Lovely script, thank you. There is, however, at least one modification that should be implemented. PHP.net advises against the use of SHA-1 and MD5 hash algorithms as the means of securing passwords. I would paraphrase, but their description is pretty clear:
    http://www.php.net/manual/en/faq.passwords.php#faq.passwords.fasthash

    • jatinder says:

      I wanted this script to be easy to understand and it is more of a teaching tool than a ready-to-deploy script. That is why I kept it as simple as possible. You can of course customize it your needs and requirements.

      You can use my PHP encryption class to encode/decode passwords.

  33. Mark says:

    Thanks for the script! Just what I was looking for!
    Only problem is when I submit anything on the login-form.php page It gets stuck on a blank php-exec.php page! Why is it not redirecting to the member-index.php page (or error page if I intentionally fill the from in wrong) like it should? :(

  34. Yvonne says:

    Thankyou Jatinder. I’m a newbie and I was able to easily follow your example and get my login/register functions working and live. Great post.

  35. Uros says:

    Can you show me how to connect to database?

  36. Ezequiel says:

    Hi Jatinder,

    This script is great, I have only a minor issue when testing on xampp. I get no error displayerd for bad login or empty fields. I noticed the login-form.php does not have the php code for displaying the errors, what I did was copying and pasting it from register-form.php with no luck. I don’t see any issue on the exec file… do you know what it can be? Sorry to bother but I’m out of ideas.

    • jatinder says:

      login-form.php just contains the login form, the form submission is handled by login-exec.php scropt and login-exec.php does check for empty fields and invalid login. On invalid login the visitor is redirected to login-failed.php script.

  37. Ray Rohatgi says:

    Very very impressive – Jatinder – managed to get it up and running in literally nano-seconds – you should really try and sell this stuff as there most be a lot of people out there who need a Logon Script which actually works and is properly coded etc

    Only thing which is missing is the Forgot Password bit – but as you have encrypted the PW then this could be quite tricky – you will probably need to reset the PW rather than telling the User what it is.

    Great Job Dude – I know where to find a decent PHP coder!


*

Popular Posts