Now that we’ve built ourselves a nice little comment system for our site, it’s time to take a detour and look at something else. We’re going to create another system which will allow us to specify a name and password to use to authenticate ourselves to the site, to confirm that we are authorised users. We’ll then use this authorised status to let ourselves delete unwanted comments from the site, while refusing regular users this ability.
We’ll start with a new page, user_login.php (note that all these pages are being created within the same folder, in this case, the same folder that you created the form submission script)
This file will contain
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 | <?php session_start(); if(isset($_POST["submit"])){ include("db_connect.php"); $username = $_POST['username']; if(get_magic_quotes_gpc()) $username = stripslashes($username); //apply the mysql escape function to "clean" the data $username = mysql_real_escape_string($username); $password = md5($_POST['password']); $result = mysql_query("SELECT COUNT(*) FROM users WHERE username = '$username' AND password = '$password'"); $row = mysql_fetch_array($result); if($row["COUNT(*)"] == 1){ $_SESSION["username"] = $username; echo "You are now logged in</br>"; } else { unset ($_SESSION["username"]); echo "Incorrect username or password<br/>"; } } ?> <form method="POST"> Name : <input type="text" name="username"/><br> Password : <input type="password" name="password"/><br/> <input name="submit" type="submit" value="Log in"/> </form> |
You will also need to create a new table in your database named users. Use the following SQL code to create this
DROP TABLE IF EXISTS 'users'; CREATE TABLE IF NOT EXISTS 'users' ( 'id' int(11) NOT NULL AUTO_INCREMENT, 'username' varchar(64) NOT NULL, 'password' varchar(64) NOT NULL, PRIMARY KEY ('id'), KEY 'user' ('username','password') ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
Notice how we’re creating an index on the “username” and “password” fields. This allows us to search quickly through the users table to find matching usernames and passwords when we try and log in.
Now, try accessing the file user_login.php and see what happens when you click “Log in”. You should be presented with a screen saying “Incorrect username or password”. This is exactly what should happen, since we’ve not yet defined a user within our user table.
Using your database management tool, perform the following query. This will insert a user with the username “admin” and the password “pass”
INSERT INTO 'users' ( 'username', 'password' ) VALUES ( 'admin', MD5( 'pass' ) );
You may be wondering here, what’s with the “MD5″ thing? You can see it in the file user_login.php too, at the top, where we’re grabbing the value of $password to use in the query. Also, if you check the users table now, you’ll see that our new user “admin” has been stored along the password “1a1dc91c907325c69271ddf0c944bc72″.
This is because the password is being stored in an encrypted format using the MD5 algorithm. The benefits of this are that it is virtually impossible to gain access to the user’s password by looking at the database, but it is utterly trivial to perform a check on a submitted password to see if it matches the record held in the database. In our case, when we try and log in with the password “pass”, it won’t compare the word “pass” to what’s held in the database, it will compare the value of md5(pass) to what’s held in the database.
Try it now, and see. You should now see the phrase “You are now logged in” when you enter admin/pass
I need to explain something else here. Namely, the use of the function session_start() on line 2, and the use of the array $_SESSION further down in the script.
What we are doing here is making it so that when a valid login attempt is made, the server will remember this attempt, and keep that authenticated user logged in. By setting the value of $_SESSION['username'] to the user’s login name when they succeed, and by destroying the value of $_SESSION['username'] via unset() when they fail to log in, we can ensure that only people who know a password and username combination from the database can be allowed to set the variable $_SESSION['username']
This then brings us to an interesting conclusion. Now that we have a variable which tells us, with no doubt whatsoever, whether a particular user has been authenticated or not, we can use that variable in our scripts, to do some pretty interesting things
With a little modification, and a bit of know-how, we can now adjust our initial file, mysql_form.php to allow us to delete unwanted comments, only if we have been authenticated as an administrator. The following changes to mysql_form.php will achieve this
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 | <?php session_start(); //it is _vitally important_ that there is no whitespace //in the output before session_start() include("db_connect.php"); if($_GET['delete']=="yes" && is_numeric($_GET['id'])){ //perform this action if we receieve a "delete" variable in the query string //_and_ also a numeric id representing the comment to delete //we know $_GET['id'] is_numeric, so we can assign it to $id here $id = $_GET['id']; if(isset($_SESSION['username'])) { $result = mysql_query("DELETE FROM comments WHERE id = '$id'"); if($result == true){ echo "Comment $id successfully deleted"; } else { echo "Comment $id could not be deleted"; } } else { echo "You are not authorised to delete this comment"; } } $comments = mysql_query("SELECT * FROM comments ORDER BY id DESC LIMIT 0, 10"); ?> A simple PHP/MySQL form <h2>Please enter your name and a comment</h2> <form action="form_submit.php" method="post"> Name : <input size="32" type="text" name="username" /><br> Comment : <textarea cols="40" rows="5" name="comment"></textarea><br> <input type="submit" value="Send comment" /> </form> <h2>Here are the comments submitted so far</h2> <?php if($row = mysql_fetch_array($comments)){ do { echo "<h3>{$row['name']}</h3>"; echo "<p>{$row['comment']}</p>"; if(isset($_SESSION['username'])){ echo "<a href='mysql_form.php?delete=yes&id={$row["id"]}'>delete?</a>"; } } while ($row = mysql_fetch_array($comments)); } else { echo "There are no comments"; } ?> |
A Note About SQL Injection Attacks : This is a very important consideration when creating forms to accept user data, and something I’ll cover in more detail in the next tutorial. See how in the above code sections, I use the function mysql_real_escape_string()? That function modifies the submitted data in such a way so that a malicious user cannot engineer a string designed to interfere with our database. Such attacks can be devastating to a website without proper data backups in place, and you should try and familiarise yourself with ways to prevent such attacks.
Now you should see, based on whether or not you’ve logged in successfully through user_login.php, either a list of comments with a “delete?” link underneath each one, or just the comments on their own. Clicking on each “delete?” link will remove a comment from the database, and then when the page tries to display all the recent comments, it will not find that one.
If you try accessing the delete link directly when you’re not logged in (to log out, just try entering an incorrect username and password) it will fail, and you will receive a warning, e.g.
mysql_form.php?delete=yes&id=1
If this is your first working PHP/MySQL application, then congratulations! You’ve already covered a great deal of the material needed to get started on building even the most complex of websites. A lot of sites around today use the exact same method for authorising users, although they implement many more security measures and have a lot more complex form submission techniques.
To see some of these security concerns, and learn how to start building foolproof websites, check out the next tutorial, and you’ll soon be on your way to building your own amazing website!
September 14th, 2008 at 9:34 am
nice to see beginning……..
September 15th, 2008 at 1:15 pm
It’s insecure? For beginners out there this is not a secure example to PHP, maybe the owner would like to explain about SQL injections?
September 15th, 2008 at 1:30 pm
@Ben
Thanks for the comment, that’s a perfectly valid point - perhaps I should make it clearer in the article, but I have mentioned near the end…
“Note that I’ve intentionally left out some important security concerns in this example, which I will outline in detail in the next tutorial. These points are extremely important and I’d recommend reading through the tips in the next tutorial to get a good grasp of what they are and how they can affect your site”
This is referring to the article - http://www.roughguidetophp.com/cleaning-up-user-data-in-mysql - where I outline the basics behind preventing SQL injection attacks in the last section
Perhaps I’ll rewrite some sections of this article to make the point clearer though, and to emphasise the importance of preventing SQL injections with rewritten code to cover this issue (a very serious issue indeed, as you’ve mentioned, and to push the point further, yep, any beginners should be aware of such things when writing their scripts)
Update : Tutorial updated to reflect this
September 28th, 2008 at 7:26 pm
Thank you for this tutorial. Most tutorials that are for basic learning that someone like I can understand usually will have a work around such as the SQL Injection attacks.. Although this is a very good tutorial for me to run on my local box to get a result from learning this stuff. Very well written tutorial.
October 8th, 2008 at 10:05 am
Nice tutorial for beginners, thank you.
October 9th, 2008 at 12:31 am
Great Job.
Also I both agree & disagree with Ben’s comment.
I think the code is insecure however the educational benefit from reading this page is great. It is not for creating a profecional web site rather than learning the basic concepts of PHP.
I repeat … GREAT JOB.
Thanks
October 11th, 2008 at 7:54 am
Nice
October 24th, 2008 at 6:24 am
I just have a little problem near the end here… when I put
INSERT INTO `users` (`username`, `password`)
VALUES (`admin`, MD5( `pass` ));
into the database, it says:
#1054 - Unknown column ‘admin’ in ‘field list’
November 9th, 2008 at 8:54 pm
@Morgan
To fix this you want to use ` quotes for the table name and fields and ‘ quotes for the value names (ie admin and password) I changed it to INSERT INTO `users` (`username`, `password`)
VALUES (’admin’, MD5( ‘pass’ )); .
you can find some hints here:http://www.dech.co.uk/2006/02/mysql-trouble-fix-unknown-column-in-field-list/
hope this helps,
Brandon
November 24th, 2008 at 9:17 pm
Anyone have tips on where I could download a complete PHP/MySQL comment system? Thanks.
December 7th, 2008 at 2:44 am
whats wrong if after you submit, “The comment could not be added, there was an error” this error appears?
December 11th, 2008 at 4:28 pm
Hey
great tutorial just one thing, I am using this with a news system and the comments appear on every news item instead of the one that it has been posted on. hope you can help
thanks a lot
Jake
December 18th, 2008 at 11:57 am
This is one of the best tutorials I have found on the net. I started learning PHP/SQL recently. This site has been very helpful.
Thank you
December 28th, 2008 at 10:02 pm
Hey for some reason my test run failed.
It says
The requested URL ‘/filepath/’ was not found on this server.
What am I doing wrong?
Thanks
December 28th, 2008 at 10:15 pm
I got it to work. I had the wrong filename.