- User Authentication Overview
- Generating Passwords
- Authenticating Users Against Text Files
- Authenticating Users by IP Address
- Authenticating Users Using HTTP Authentication
- Authenticating Users by Database Query
This chapter is from the book
This chapter is from the book
This chapter is from the book
Authenticating Users by Database Query
The most common method of authentication for database-backed sites is to use the database. Why bother with clunky text files when you have the speed and ease of an SQL database at your fingertips?
Database-based authentication can use the same features as file-based authentication, such as md5() or crypt() encryption. Usernames and passwords are stored in a table on the database. You can store other information in this table as well, such as email addresses or first and last names, as you saw in the example applications in Chapter 5.
This next script provides a bare-bones approach to using a database to authenticate users. It uses plain-text passwords, but you can easily include encrypted passwords using the techniques from the earlier scripts in this chapter.
Script 7-5 DB_authenticate.php
1. <?
2.
3. /* SQL REQUIRED FOR THIS SCRIPT *****
4. create table users (
5. id INT NOT NULL,
6. username VARCHAR(16),
7. password VARCHAR(8),
8. primary key(id));
9. *****/
10.
11. function connect() {
12. if(!$db = @mssql_pconnect("localhost","mssqluser","password")){
13. print("<h1>Cannot Connect to the DB!</h1>\n");
14. return 0;
15. } else {
16. mssql_select_db("php", $db);
17. return 1;
18. }
19. }
20.
21. function check_user($user, $password) {
22. if(connect()) {
23. $password = substr($password, 0, 8);
24. $sql = "select * from users where username = '$user' and password = '$password'";
25. $result = mssql_query($sql);
26. if (mssql_num_rows($result) == 1) {
27. setcookie("user",$user);
28. setcookie("password",$password);
29. return 1;
30. } else {
31. ?>
32. <h3>Sorry, you are not authorized!</h3>
33. <?
34. return 0;
35. }
36. }
37. }
38.
39. /***** MAIN *****/
40. if(!isset($user) or !check_user($user, $password)) {
41. ?>
42. <h1>You must log in to view this page</h1>
43. <form action = "DB_authenticate.php" method="post">
44. <P>Username: <input type="text" name="user"><br>
45. Password: <input type="password" name="password" maxlength="8" size="8"><br>
46. <input type="submit" name="submit" value="Submit">
47. </form>
48. <?
49. } else {
50. ?>
51. <h1>Authorized!</h1>
52. <?
53. }
54. ?>
Script 7-5. DB_authenticate.php Line-by-Line Explanation
|
LINE |
DESCRIPTION |
|
3–9 |
These lines provide the SQL statement to use to create a database table that works with this script. Once you have created your table, you can add users using standard SQL statements such as: insert into users values (1, 'username', Keep in mind that you need a unique ID for each user that you add. |
|
11 |
Create a function called connect(). This function is used to create a connection to the database. |
|
12 |
Attempt to connect to the database, in this case an MS SQL database. (You can replace all the MS SQL functions with MySQL functions simply by changing the first "s" in mssql to a "y"—"mysql".) |
|
13 |
If the script cannot connect to the database, then print an error. |
|
14 |
Return false (0) to the calling program, because the database connection failed. |
|
16 |
If the connection attempt was successful, then select the database on the database server that the script will use. |
|
17 |
Return true (1) to the calling program, because the connection was successful. |
|
18 |
Close the if statement started on line 12. |
|
19 |
End the function declaration. |
|
21 |
Create a function caller check_user() that checks the user-entered username and password against a valid username and password that is stored in a database. The script takes as arguments $user and $password, both entered by a user through a login form. |
|
22 |
Attempt to connect to the database using the connect function. If the connection succeeds, then continue to line 23. Otherwise, go to line 36, which is the end of the function. |
|
23 |
Modify the user-entered password so that it matches the format used by the database. In this case, the script just truncates the password to eight characters. You may want to use one of the md5() or crypt() functions here if you stored encrypted passwords in your database. |
|
24 |
Generate an SQL statement that attempts to select a row from the "users" table that matches the user-entered username and password. |
|
25 |
Run the SQL query and assign the result to the $result variable. |
|
26 |
Check to see if the result from the query has exactly one row, since usernames are supposed to be unique. (You didn't put duplicate usernames in the database, did you?) |
|
27–28 |
If the result did have exactly one row, meaning that the user-entered username and password match the same username and password in the database, then set two session cookies on users' browsers so that they remain "logged in" the entire time they are using your protected pages. |
|
29 |
Return true (1), since the authentication was successful. |
|
30–35 |
If the result did not contain exactly one row, then the user-entered username and password did not match any in the database. Print an error message and return false to the calling program. |
|
36 |
Close the if statement started on line 22. |
|
37 |
End the function declaration. |
|
39 |
Begin the main program. |
|
40–48 |
Check to see if the $user variable is set or if the user-entered username and password match any in the database via the check_pass() function. If the $user variable has not been set, then we know that the current user has not attempted to log in yet. If the $user variable is set, but the check_user() function returns false, then we know that the user entered an invalid password. In either case, print the login form so that the user may enter a correct username and password. |
|
49–53 |
If the script gets to this point, it is because the $user variable has been set and check_user() returned a true value. This must be a valid user! Display a note telling users that they are authorized. At this point, you would display your protected content. |
'password');