API Project - Part 3 - Data Access Classes

In this step we'll create a data access class will preform CRUD operations against the database In general there is one data access class for each table in your database, but it's not unusual for a data access component to pull data from 2 or more tables (by using a JOIN).

Our data access classes will not only handle CRUD operations for a table, they will also:

  1. Scrub data before inserting and updating it (to prevent SQL injections attacks)
  2. Scrub/sanitize data after selecting it from the database (to prevent XSS attacks)
  3. Convert column names to model property names (ORM - Object Relational Mapping)

Look at the DataAccess class (includes/dataaccess/DataAccess.inc.php), all of your data access classes will extend this class.

Note the following:

Take a look at create-test-database.inc.php (in the tests folder, not to be confused with create-dev-db.php). Don't worry about all the details in this file, but note that it drops and recreates your test database. It's important to do this every time you run tests so that you know your tests are running against the exact same database. So we will include/import this file at the top of each of our files that test data access classes.

IMPORTANT: check the database connection settings in create-test-database.php to make sure they are correct. If we are using XAMPP on Windows the password is blank, but on Linux it should be 'test'.

I did create a small (incomplete) test file for the DataAccess class, but you don't need to worry about the details in it because testing an abstract class presents some challenges that we don't need to worry about now. Go ahead and run the tests by navigating to this page in your browser: localhost/api/tests/DataAccessTests.php/

The RoleDataAccess Class

Now we'll extend the DataAccess class to create a data access class that allows us to select rows from the user_roles table and convert them into Role model objects. It will also allow us to insert, update, and delete rows. I have already started this file for you, it is RoleDataAccess.inc.php in the dataaccess folder. Take a look at the starter code in this file, and note the following:

Before we move on, read the comments for each method in the RoleDataAccess class so that you understand the following:

  1. What the method is supposed to do
  2. What the parameters are for each method (and their data types)
  3. What the method returns

I have already created the tests for the RoleDataAccess class, to see the results navigate to this page in your browser: http://localhost/api/tests/RoleDataAccessTests.php. You'll see that most of the tests are failing. In the next few steps, we'll try to get each test to pass one by one.

convertModelToRow()

Update the convertModelToRow() method to look like this:

function convertModelToRow($role){

    $row = [];

    $row["user_role_id"] = $role->id;
    $row["user_role_name"] = $role->name;
    $row["user_role_desc"] = $role->description;

    return $row;

}

Now you should be able to see that this method converts a Role object (the parameter) into an associative array (the return value). The keys in the associative array are column names from the user_roles table. You'll see later how the associative array is used when we insert or update rows. This method does object relational mapping for us (ORM), for when we want to insert or update Role model objects.

Reload the test page in the browser,and hopefully you'll see that another test is now passing. If you read the comments for the convertModelToRow() method, you'll note that it mentions something about 'scrubbing the data to prevent SQL injection attacks'. We have not yet added that code at this point (I don't want to complicate things now), but we'll do it later (we may have to wait until we have discussed security issues).

convertRowToModel()

ORM (object relational mapping) must work two ways. The next method will convert a row from the database into a Role model object. Update the convertRowToModel() method to look like this:

function convertRowToModel($row){
	
    $role = new Role();
    $role->id = $row['user_role_id'];
    $role->name = $row['user_role_name'];
    $role->description = $row['user_role_desc'];

    return $role;
}

This method will take a row from the database (when we fetch a row, it will come to us in the form of an associative array, where the keys are the table's column names) and return a Role model object.

Reload the test page, and hopefully you'll see another test passing.

Again, we should add a little more code to prevent security vulnerabilities, but I want to keep things simple for the moment.

getAll()

Now that we have implemented the methods that take care of the ORM (object relational mapping), we can go ahead and start working on the methods that handle the CRUD operations. Start by updating the getAll() method to look like this:

function getAll($args = []){

    $qStr = "SELECT 
                user_role_id, 
                user_role_name,
                user_role_desc 
            FROM user_roles";

    $result = mysqli_query($this->link, $qStr) or $this->handleError(mysqli_error($this->link));
    
    $allRoles = [];
    
    while($row = mysqli_fetch_assoc($result)){
        $allRoles[] = $this->convertRowToModel($row);
    }
    
    return $allRoles;
}

There's a lot to talk about in this code, but before we do reload the test page and hopefully you'll see another test passing.

Here are some things to note in the getAll() method:

getById()

Update the getById() to look like this:

function getById($id){
	
    $qStr = "SELECT 
                user_role_id, 
                user_role_name, 
                user_role_desc
            FROM user_roles 
            WHERE user_role_id =" . mysqli_real_escape_string($this->link, $id);

    $result = mysqli_query($this->link, $qStr) or $this->handleError(mysqli_error($this->link));

    if($result->num_rows == 1){
        $row = mysqli_fetch_assoc($result);
        //var_dump($row);die();
        $role = $this->convertRowToModel($row);
        return $role;
    }

    return false;

}   

Notes:

Reload the test page, and you should see another test passing.

insert()

Change the insert() method so that it looks like this:

function insert($role){
	    	
    $row = $this->convertModelToRow($role);

    $qStr = "INSERT INTO user_roles (
                user_role_name, 
                user_role_desc
            ) VALUES (
                '{$row['user_role_name']}',
                '{$row['user_role_desc']}'
            )"; 
    //die($qStr);
    $result = mysqli_query($this->link, $qStr) or $this->handleError(mysqli_error($this->link));
    
    if($result){
        $role->id = mysqli_insert_id($this->link);
        return $role;
    }else{
        $this->handleError("unable to insert role");
    }

    return false;
}

Notes:

Reload the test page, and hopefully another test is passing.

At this time, you could also use PHPMyAdmin to see the new row in the user_roles table.

update()

Update the update() method to look like this:

function update($role){

    $row = $this->convertModelToRow($role);

    $qStr = "UPDATE user_roles SET 
                user_role_name = '{$row['user_role_name']}', 
                user_role_desc='{$row['user_role_desc']}' 
                WHERE user_role_id = " . $row['user_role_id'];
    //die($qStr);

    $result = mysqli_query($this->link, $qStr) or $this->handleError(mysqli_error($this->link));
    //var_dump($result); die();
    if($result){
        return true;
    }else{
        $this->handleError("Unable to update user");
    }
    return false;
}

Hopefully you don't need notes to walk you through everything in this code at this point, because it's very similar to what we've already done. But make sure to look it over and let me know if you have any questions.

Reload the test page, and hopefully all tests are now passing!

delete()

We are not going to deal with deletes at this time because there are different ways to go about them (if you are curious, ask me in class). However, we will be covering deletes when we create the UserDataAccess class.

Preventing Security Vulnerabilities

I was tempted to leave our 'ORM' methods as they are now, because they are nice and simple. But we really should take every step possible to secure our code.

At this point in the course, I'm not sure if we will have already covered them in class. So I won't discuss the following code much, but feel free to ask me about any of it.

Update the convertModelToRow() method to look like this:

function convertModelToRow($role){

    $row = [];

    // THIS IS WHAT WE PREVIOUSLY HAD:
    // $row["user_role_id"] = $role->id; //The Role class allows the id to be a string???
    // $row["user_role_name"] = $role->name;
    // $row["user_role_desc"] = $role->description;

    // BUT we should try to prevent SQL injection attacks by using mysqli_real_escape_string()
    $row["user_role_id"] = intval(mysqli_real_escape_string($this->link,$role->id));
    $row["user_role_name"] = mysqli_real_escape_string($this->link,$role->name);
    $row["user_role_desc"] = mysqli_real_escape_string($this->link,$role->description);
            
    return $row;

}

And update convertRowToModel() to look like this:

function convertRowToModel($row){
    
    $role = new Role();
    
    // THIS IS WHAT WE PREVIOUSLY HAD:
    // $role->id = $row['user_role_id'];
    // $role->name = $row['user_role_name'];
    // $role->description = $row['user_role_desc'];

    // BUT we should try to prevent XSS attacks by using htmlentities()
    $role->id = $row['user_role_id'];
    $role->name = htmlentities($row['user_role_name']);
    $role->description = htmlentities($row['user_role_desc']);

    return $role;

}

As I mentioned, we can talk about these changes in class, just ask. And I do have code samples that demonstrate both SQL injection and XSS attacks (and how to prevent them).

Make sure to reload the test page to make sure that we didn't introduce and bugs in our code with these changes. One of the important benefits of unit testing is that they prevent regression bugs, which is when code that used to work properly is broken when a developer makes even a slight change to it.

At this point, you should be able to implement all the code for the UserDataAccess class. I have already written the test code for you, you just need to get them all to pass. When the test code is written before the real code, it is known as test driven development.