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:
- Scrub data before inserting and updating it (to prevent SQL injections attacks)
- Scrub/sanitize data after selecting it from the database (to prevent XSS attacks)
- 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:
-
The constructor takes a link as a param (this is the connection to the database)
-
There are some utility methods that will be inherited by sub classes:
-
handleError() When an error occurs while running a SQL query, we'll call this method This will allow for consitent error handling and avoid using die() like your book does (ask me about this if you want, but I have a big problem with using 'or die()' when running SQL queries in PHP code).
-
sanitizeHTML() This will clean data coming from the database (for select statements) and prevent XSS attacks. We may not use this method at all, but if you allow HTML code to be stored in a your database, you will definitely want to use it.
-
convertDateForMySQL() This will format date strings (remember that MySQL stores dates in yyyy-mm-dd format)
-
-
There are also abstract methods that must be implemented by sub classes
-
convertModelToRow() Translates the model property names to column names (ORM). For example: converts 'id' to 'user_role_id'
-
convertRowToModel() Translates column names to model property names. For example: converts 'user_role_id' to 'id'
-
The rest of the abstract methods perform the CRUD operations
-
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:
- It imports/includes the DataAccess class
- It imports/includes the Role model class (we will need Role objects to pass into the methods in the class)
- The class extends the DataAccess class
- The constructor takes a link object as a param and then passes it to the super classes constructor (if you look at the super classes constructor, you'll see that it has code to validate the link)
- We will need to implement (add code to) the rest of the methods in the class.
Before we move on, read the comments for each method in the RoleDataAccess class so that you understand the following:
- What the method is supposed to do
- What the parameters are for each method (and their data types)
- 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:
- It defines the SQL query to run in the $qStr variable
- Then it runs the query (using the mysqli_query() function), it also adds or, but instead of doing or die() like your book does, we call our own error handler method (which is inherited from the super class). Let me know if you want to discuss why this is a much better approach than using die(). BTW - the mysqli_error() function will return a message for the last error that was encountered, we are just passing this error message into our handleError() method.
- It loops through the rows in the result set and converts each one into a Role object (by passing it through the convertRowToModel() method). Each Role object is then added to the $allRoles array
- It returns the $allRoles array.
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:
- The SQL query ($qStr) concatenates the $id parameter onto the end. But note that the $id is passed through the mysqli_real_escape_string() function before it is concatenated. Doing this can prevent SQL injection hacks. Remember back in the ORM methods that I mentioned we should be adding code to prevent hacks? This is what I was talking about. At some point in the semester we'll dig more into preventing various types of hacks (I have a demo on SQL injection attacks and how to prevent them).
- After running the query (by calling mysqli_query()) we check to make sure that there is one, and only one, row in the result set.
- If there is only one row, then we fetch it (with mysqli_fetch_assoc()) and then pass it into convertRowToModel() so that we can convert the row into a Role model object.
- If everything works, then the method should return a Role object. If not, then it will return false (I really wasn't sure if returning false was the best choice, but I went with it)
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:
-
The parameter, which is a Role object, is passed through convertModelToRow() so that we can turn it into a 'row' (an associative array whose keys match the column names in the user_roles table - ORM!)
-
The SQL query ($qStr) uses variable interpolation to place the values to be inserted into it (remember variable interpolation? If not, just ask!). If you want to see what the final SQL query looks like, just uncomment the line that says die($qStr); and then reload the test page.
-
We run the query (with mysqli_query()) and get the result.
-
If the result it truthy (indicating that the insert succeeded), then we get the ID that was generated by the database (by calling mysqli_insert_id()) and use it to set the id property of the Role object. Then we return the role.
-
If the result is not truthy, then we call our inherited handleError() method and return false.
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.