logo sykohpath.com

				where code goes to die

Web Development From Scratch, Day 8

After "Create", there is "Read".  Technically we covered part of this yesterday with "get_all_users" in our model, but we need to actually use it - mainly for a login form.  It's quite simple, really.  Use SQL statements to Read from the database.

First, we'll have to actually create the login form.  Nothing too fancy, just a simple "Username" and "Password" setup.

Normally, in an OOP environment, we'd use the same controller for this, but again, since that's a bit later, we're going to create a new controller, called "login.php".  In order to keep things from getting messy, we need to rename our "users.php" to "register.php".  Why didn't we do this earler? oops.  Shoulda planned for that, right?

1) rename controller/users.php to controller/register.php

We need to slightly modify our index.php as well.  Since we access our site by typing in /index.php, and we have that require our controller, how do we make it access our other controller?  /index.php/register would be a rather nice way to do it, right?

There...sort of is...a way to do it without using an actual framework.  We're going to have "ugly" URL's as a result, but there's ways around that, although this is actually beneficial from an SEO standpoint!

1) grab the full URL that was sent: http://<yoursite.com>/index.php/register
2) Parse that: [host]<yoursite.com>, [path]/index.php/register
3) break that up: [0]=> [1]=>index.php [2]=>register
4) If [2] is set, call that "controller", else default to the "index" controller.

Code Sample:
  1. <?php
  2. require "../../includes/db_connect.php"; //modify to your needs
  3. $url = "http://" . $_SERVER['HTTP_HOST'] . $_SERVER['REQUEST_URI'];
  4. $urlParse = parse_url($url);
  5. $urlParts = explode('/', $urlParse['path']);
  6. if(isset($urlParts[3])){
  7.   //check if file exists
  8.   $controller = "controller/" . $urlParts[3] . ".php";
  9.   if(!file_exists($controller)){
  10.    die("Controller not found: " . $controller);
  11.   }
  12. } else {
  13.   $controller = "controller/index.php";
  14. }
  15. require $controller; //pass to controller
  16. ?>

* Your initial database require should not change, if it's working from last time, don't change it to match what I have!
* We use $_SERVER superglobals to craft the full URL.  Assume we're hitting it from http (we should test for https, honestly)
* Parse_url gives us an array of the url...
* ...and we explode that array into the parts.
* I use the third position, since I'm hitting this from http://sykohpath.com/websitetest.com.git/index.php/register ([1]=>websitetest.com.git [2]=>index.php [3]=>register).  If you're at http://<yoursite.com>/index.php, you'll be using position 2 to grab the controller name ([1]=>index.php, [2]=>/<controller>)
* Need to test if that file even exists! If not, error out.

Since we set a default controller to "index.php", we want this page to be the first page that pulls up, and not directly to our register page.  So, let's whip up the following pages:

Code Sample:
  1. <?php
  2. if($urlParts[2] == "" || $urlParts[3] == ""){
  3.   //index.php was not supplied, so modify links to reflect that
  4.   $prefix = "index.php/";
  5. }
  6. require "view/index.php";
  7. ?>

Code Sample:
  1. <?php
  2. if(!isset($prefix)){ $prefix = ""; } //init
  3. ?><html>
  4. <head>
  5. <title>User Management System</title>
  6. </head>
  7. <body>
  8. <?php if($_SESSION["logged_in"]=="YES"): ?>
  9.   Welcome Back, <?php echo $_SESSION['name']; ?>!<br>
  10.   <a href="<?php echo $prefix; ?>logout">Logout</a><br>
  11. <?php else: ?>
  12.   <a href="<?php echo $prefix; ?>login">Login</a><br>
  13.   <a href="<?php echo $prefix; ?>register">Register</a><br>
  14. <?php endif; ?>
  15. <?php print_r($_SESSION); ?>
  16. </body>
  17. </html>

* Since this is a landing page which can be accessed through index.php via http://<yoursite.com>/ - we need to make sure the links are flexible to handle  http://<yoursite.com>/ as well as http://<yoursite.com>/index.php - we do this by checking our url parts that were set in our index.php for the controller checker.
* We have a check to see if the user is logged in.  If they are, show their name, and a link to logout.  If they aren't, show a link to login.
* For testing/learning purposes, a print_r($_SESSION) displays all that was set once the user is logged in. It's an unneccessary line.

After making those changes, try them out.  Go to index.php/test, as well as index.php/register - test should error, while register should pull up the registration form we made yesterday.  Try index.php/ and the new index page we made should pull up.

One last thing, change the form location in view/user_form.php to point to the new location:
<form name="user_registration" action="register" method="post">

Now to move on to the login form!  Not much to the view:

Code Sample:
  1. <html>
  2. <head>
  3. <title>User Login Form</title>
  4. </head>
  5. <body>
  6. <form name="user_login" action="login" method="post">
  7.   <table>
  8.    <tr>
  9.     <td>
  10.      Username
  11.     </td>
  12.     <td>
  13.      <input type="text" name="username">
  14.     </td>
  15.    </tr>
  16.    <tr>
  17.     <td>
  18.      Password
  19.     </td>
  20.     <td>
  21.      <input type="password" name="password">
  22.     </td>
  23.    </tr>
  24.    <tr>
  25.     <td>
  26.      Errors:
  27.     </td>
  28.     <td>
  29.      <b  echo $errors; ?></b>
  30.     </td>
  31.    </tr>
  32.    <tr>
  33.     <td>
  34.     </td>
  35.     <td>
  36.      <input type="submit" name="submit" value="Submit Form">
  37.     </td>
  38.    </tr>
  39.   </table>
  40. </form>
  41. </body>
  42. </html>

Next our controller, a little bit more complicated:

Code Sample:
  1. <?php
  2. require "model/user_model.php";
  3. //check if logged in!
  4. if($_SESSION['logged_in'] == "YES"){
  5.   require "view/index.php";
  6.   exit;
  7. }
  8. //login user
  9. if(isset($_POST['submit'])){
  10.   //setup array to match table fields=>form values
  11.   $form = array(
  12.    "username" => filter_var($_POST['username'], FILTER_SANITIZE_STRING),
  13.    "password" => filter_var($_POST['password'], FILTER_SANITIZE_STRING)
  14.    );
  15.   //secure/encrypt the password
  16.   $salt = "pepper";
  17.   $form['password'] = md5($salt + md5($form['password']));
  18.   //check if user and password matches
  19.   $errors = validate_user($websitetest_db, $form);
  20.   if(is_array($errors)){
  21.    //if so, cram that user into session
  22.    $_SESSION['logged_in'] = "YES";
  23.    $_SESSION['username'] = $errors['username'];
  24.    $_SESSION['name'] = $errors['name'];
  25.    require "view/index.php";
  26.    exit;
  27.   }
  28. }
  29. require "view/user_login.php";
  30. ?>

* This is VERY similar to the register.php controller.  We still sanitize our fields, and then encrypt the password.
* We pass our associative array (containing the username and password) to the validate_user function, which resides in the model.
* We are passed back an array if the user is validated, and we set the fields according to what was passed back - in this case, only set the flag that the user is logged_in, as well as the username and their name.
* On completion, call the index view, which contains a detection for if the user is logged in.
* If we are not passed back an array, it's an error, so simply call the login view again, which displays the error.

We add the following function to the model:

Code Sample:
  1. ...
  2. function validate_user($db_conn, $dataArray){
  3.   $sql = "SELECT * FROM users WHERE username='" . $dataArray['username'] . "'";
  4.   $result = mysqli_query($db_conn, $sql);
  5.   if(!$result){
  6.    die('SQL Error: ' . mysqli_error($db_conn));
  7.   }
  8.   if(mysqli_num_rows($result) > 0){
  9.    $row = mysqli_fetch_assoc($result);
  10.    if($row['password']==$dataArray['password']){
  11.     return $row; //pass user account back
  12.    } else {
  13.     return "Incorrect Password";
  14.    }
  15.   } else {
  16.    return "Username not found";
  17.   }
  18. }
  19. ...

* Ok, "finally" we deal with the "Read" part of CRUD.
* Using SQL, just pull all records that match the passed username. (Note that we haven't put in a duplicate username filter in our registration yet!)
* If there's more than 0 rows returned, it means that a matching record was found.  Otherwise, 0 rows returned means there were no records that match the passed username.
* Compare the pulled password with the passed password - either it matches, or it doesn't.

After all this, we'll have the following in our $_SESSION:
Array ( [logged_in] => YES [username] => testAccount [name] => Test Account )

Now, if we try to login as "test" (Bob Bobkins!) we'll see that it doesn't work...because the password it's checking against is unencrypted.  We'll deal with that one record in a later section (Delete!).

Our login system is almost complete.  We just need to "logout".  Painfully simple!

Code Sample:
  1. <?php
  2. //check if logged in!
  3. if($_SESSION['logged_in'] == "YES"){
  4.   unset($_SESSION);
  5. }
  6. require "view/index.php";
  7. ?>

GOOD NESS.  That was quite a bit for "Read", wasn't it?  Well, a login system is the...I don't want to say "backbone"...it's more like the "kidneys" of a user management system.

Next will be the user "Profile" once they are logged in.  This will cover "Update" part of CRUD.

php, tutorial, crud, update,



Web Development From Scratch, Day 7

Next up: Create Record in Database.

So the first thing is, to cram our database stuff into the model, make a view, and then tweak the controller to call both of them.  All we'll do for the view is make a simple form, which will tell our php script to create a record with the data entered.  We'll also create a second view which displays all the users in the database.  EASY STUFF, I promise.

Code Sample:
  1. <html>
  2. <head>
  3. <title>User Registration Form</title>
  4. </head>
  5. <body>
  6. <form name="user_registration" action="index.php" method="post">
  7.   <table>
  8.    <tr>
  9.     <td>
  10.      Username
  11.     </td>
  12.     <td>
  13.      <input type="text" name="username">
  14.     </td>
  15.    </tr>
  16.    <tr>
  17.     <td>
  18.      Password
  19.     </td>
  20.     <td>
  21.      <input type="password" name="password">
  22.     </td>
  23.    </tr>
  24.    <tr>
  25.     <td>
  26.      Password Confirm
  27.     </td>
  28.     <td>
  29.      <input type="password" name="password_confirm">
  30.     </td>
  31.    </tr>
  32.    <tr>
  33.     <td>
  34.      Email Address
  35.     </td>
  36.     <td>
  37.      <input type="text" name="emailaddr">
  38.     </td>
  39.    </tr>
  40.    <tr>
  41.     <td>
  42.      Full Name
  43.     </td>
  44.     <td>
  45.      <input type="text" name="fullname">
  46.     </td>
  47.    </tr>
  48.    <tr>
  49.     <td>
  50.      Errors:
  51.     </td>
  52.     <td>
  53.      <b  echo $errors; ?></b>
  54.     </td>
  55.    </tr>
  56.    <tr>
  57.     <td>
  58.     </td>
  59.     <td>
  60.      <input type="submit" name="submit" value="Submit Form">
  61.     </td>
  62.    </tr>
  63.   </table>
  64. </form>
  65. </body>
  66. </html>

* This .php file is almost all HTML.  Note that there are no opening and closing PHP tags.
* However, there is PHP in the "errors" area, which displays the contents of the "$errors" variable.
* Make sure to "git add view/user_form.php" so that it will be properly tracked.

Ok, back to our controller, which we need to "fix":

Code Sample:
  1. <?php
  2. require "model/user_model.php";
  3. if(isset($_POST['submit'])){
  4.   //setup array to match table fields=>form values
  5.   $form = array(
  6.    "username" => filter_var($_POST['username'], FILTER_SANITIZE_STRING),
  7.    "password" => filter_var($_POST['password'], FILTER_SANITIZE_STRING),
  8.    "email_address" => filter_var($_POST['emailaddr'], FILTER_SANITIZE_EMAIL),
  9.    "name" => filter_var($_POST['fullname'], FILTER_SANITIZE_STRING),
  10.    "created" => time(),
  11.    "modified" => time()
  12.    );
  13.   //validate our values
  14.   $errors = "";
  15.   if($form['password'] != $_POST['password_confirm']){
  16.    $errors .= "Passwords do not match!<br>";
  17.   }
  18.   if($form['email_address'] != $_POST['emailaddr']){
  19.    $errors .= "Email Address contains invalid characters!<br>";
  20.   }
  21.   //secure/encrypt the password
  22.   $salt = "pepper";
  23.   $form['password'] = md5($salt + md5($form['password']));
  24.   if($errors == ""){
  25.    //add user to the database
  26.    add_user($websitetest_db, $form);
  27.    //get all users from database
  28.    $allUsers = get_all_users($websitetest_db);
  29.    //show all users
  30.    require "view/user_display.php";
  31.   } else {
  32.    //errors found, show form again
  33.    require "view/user_form.php";
  34.   }
  35. } else {
  36.    //show the user registration form
  37.   require "view/user_form.php";
  38. }
  39. ?>

Wow, what the heck happened here!?
* Start off by including our model.
* Check to see if form was submitted. If not, show the user form, otherwise:
1) set up associative array to store form values.
2) use "filter_var" to sanitize the input. It is a VERY BAD IDEA to not sanitize input, since that opens up possibility of SQL injection, among other issues. (see Security below)
3) Check sanitized input with pure fields for email address. Error if fail.
4) Make sure Password matches the Password Confirm. Error if fail.
5) Hash the password with a very simple salting routine.  Salt is hardcoded, added to the password, and then an MD5 algorithm is applied.  See Security below.
6) if there are no errors, add user to the database, get a list of all users, and show the "show all users" view.
7) if there are errors, show the user registration form again, and pass the errors to it to be displayed.

Consider this statement:
$sql = "SELECT * FROM users WHERE name = '" . $_POST['username'] . "'";

As long as a user enters any name into the field, this sql statement works as intended.  However, the world is filled with assholes, so more than likely, someone will attempt sql injection.  All they have to do is enter something like:


Or something more creative, which will end up with the following full sql statement:


Just like that, your entire users table is gone.  Forever.  That's bad.

I mentioned before that storing passwords in plaintext is a bad idea.  Consider the previous SQL Injection.  A simple "'; SELECT password FROM users;" will then show all the passwords, plain as day, to the intruder. BAD.

So, we need to encrypt the password.  MD5 is NOT an encryption algorithm.  All it does is hash what is supplied.  I won't go into detail here, but consider the following:

'word' -> MD5 -> 'c47d187067c6cf953245f128b5fde62a'

So far so good, right?  MD5 can be reversed, however:

'c47d187067c6cf953245f128b5fde62a' -> MD5 -> 'word'

By adding a salt to the password, we introduce a layer of security:

'word' -> MD5 -> 'c47d187067c6cf953245f128b5fde62a' -> 'saltc47d187067c6cf953245f128b5fde62a' -> MD5 -> '758e43ddf53438199bb0f8aa82a604f7'

But wait, reversing that gives us the salt + md5!  Yeah, but try reversing 'saltc47d187067c6cf953245f128b5fde62a'...it fails.  Mainly because hashes are in Hex, 0-9A-F.  If you make your salt into hex as well, it'll really screw up the reverse process.

It's a complex area, so don't worry if you don't understand it.  Just be aware that you need to use some type of encryption for passwords

To see it in action, load up your database browser and browse the records.  Compare the password fields of "Bob Bobkins" vs one of the records put in by our new form (once you're done putting all the files up!).  Poor Bob's password is sitting there in plaintext.  Our encrypted passwords tower over his exposed weakness.  MD5 stands for MAGIC-DOMINATING-...uhh...FIVE.



Ok, what files do we have left?

Ah, the model.

Our old controller from yesterday has been crammed into a get-all-users function for this one:

Code Sample:
  1. <?php
  2. function get_all_users($db_conn){
  3.    $sql = "SELECT * FROM users";
  4.   $result = mysqli_query($db_conn, $sql);
  5.   if(!$result){
  6.    die('SQL Error: ' . mysqli_error($db_conn));
  7.   }
  8.   $userList = array();
  9.   while($row = mysqli_fetch_assoc($result)){
  10.    $userList[] = $row['name']; //store all names into the list
  11.   }
  12.   mysqli_free_result($result);
  13.   return $userList;
  14. }
  15. function add_user($db_conn, $dataArray){
  16.   $insert_fields = implode(",", array_keys($dataArray));
  17.   $insert_values = "'" . implode("', '", $dataArray) . "'";
  18.   $sql = "INSERT INTO users (" . $insert_fields . ") VALUES (" . $insert_values . ")";
  19.   $result = mysqli_query($db_conn, $sql);
  20.   if(!$result){
  21.    die('SQL Error: ' . mysqli_error($db_conn));
  22.   }
  23. }
  24. ?>

* Not much has changed for the "get_all_users" function.  Notice that we need to pass our database connection to the function in order for the database routines to work correctly.  This is normal.
* New function: add_user.  This takes an associative array, crafts it into a sql statement, and then runs the statement (errors if there is something wrong).

The "sql statement crafter" is a bit...odd...I admit. Normally, prepared statements are ideal with set fields - all we would pass is the data.  However, we're not to the OOP point yet, so we have to deal with procedural goofyness.  I mean, an alternative could be:

$sql = "INSERT INTO users (username, password...) VALUES ('" . $dataArray['username'] . "', '" . $dataArray['password'] . "',...)";

I don't like that.  Sure, everything could be crammed into one line that way, but I like to show off my leet programming skills (stop laughing).  We use the "implode" function to turn the array into a comma-separated string.  Note that the values have to be enclosed in single-quotes.


Whew, tiring.  Let's cover the last, and easiest file yet...display all users view:

Code Sample:
  1. <html>
  2. <head>
  3. <title>Users - Display all</title>
  4. </head>
  5. <body>
  6. <ul>
  7. <?php foreach($allUsers as $value): ?>
  8.   <li><?php echo $value; ?></li>
  9. <?php endforeach; ?>
  10. </ul>
  11. </body>
  12. </html>

* Again, no php tags since this is HTML for the most part.
* We use a foreach to go through the list of all users (Array) and output them in an unordered list.

And hey, that wraps up "create"

Tomorrow, we'll have users be able to log-in.  We MIGHT do "update" and "delete", but only if you're good.

php, tutorial, crud, create,