blogblogblogblog SyKoHPaTh

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. Username
  8. <input type="text" name="username">
  9. Password
  10. <input type="password" name="password">
  11. Password Confirm
  12. <input type="password" name="password_confirm">
  13. Email Address
  14. <input type="text" name="emailaddr">
  15. Full Name
  16. <input type="text" name="fullname">
  17. Errors:
  18. <input type="submit" name="submit" value="Submit Form">
  19. </form>
  20. </body>
  21. </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!
  17. }
  18. if($form['email_address'] != $_POST['emailaddr']){
  19. $errors .= "Email Address contains invalid characters!
  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' 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>
    • <?php foreach($allUsers as $value): ?>
    • <?php echo $value; ?>
    • <?php endforeach; ?>
  6. </body>
  7. </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