Blog 11 – Procedures

The security aspect can be very difficult when dealing with an open website and storing data. Website can have a number of flaws that allow people to exploit the site. There are however a number of way to combat this. Stored procedures is one of these. A procedure is a subroutine that is stored in the databases catalog. Procedures are more flexible and secure than standard queries.
Stored procedures have ability to do not only standard queries but also IF/ELSE and THEN statements, to name a few. With this capability, procedures allow for data processing at the database end and not the application end. This means that instead of the website receiving a lump of data, from the database and then sorting through it, the procedure can sort the data more efficiently at the database. Therefore the application only receives the data that is needed. This also is more secure.
If the website used queries to get a lump of unsorted data, then sorted it, before displaying the data on the site, it creates a range of insecurities.

  • The website has more data than is needed. If a hacker managed to get the website code and adjust it to print out all the unsorted data then sensitive data may be revealed.
  • Due to the query being in the code if a hacker could adjust the code like above then they could also adjust the query to show more infomation.

The advantage with procedures is that it cuts the risk of theses security flaws occurring. A procedure on a database can only do what it is designed to do. There is no way to modify it unless access to the database is gained and if user accounts are setup properly then this shouldn’t happen. Using procedure that sort and only give data that is needed means that then is less risk of releasing sensitive data.
Another advantage is the way data is accessed. When using a query, it has direct access to the tables and their data. This is a huge security problem. It is difficult limit this access to through user accounts and in the end the user still needs direct access to the data. Views or temporary tables may be used but they can be very draining of the databases resources, as they require constant upkeep. Procedures on the other hand become a middleman. The website calls the procedure, which access the data. This means that the application does not have direct access to the data, only the procedure. User accounts can then be setup to only allow execute on the selected procedures that they need. Therefore is a hacker gets access to the website code then they can still only get data out that the procedures allow.

For above reasons when creating the website I have decided to use procedures. This means that I need to come up with a series of procedures that will be used to display data to the teachers. Below is the procedures that I have identified;

Drops;

As procedures are stored in the databases catalog when making changes to a procedure the original needs to be dropped. When creating each procedure I added added a respective drop procedure command at the the top of the SQL page.

DROP PROCEDURE IF EXISTS InsertRecord;
DROP PROCEDURE IF EXISTS SelectUserLogin;
DROP PROCEDURE IF EXISTS CheckUserLogin;
DROP PROCEDURE IF EXISTS CheckAttendance;
DROP PROCEDURE IF EXISTS SelectCourses;
DROP PROCEDURE IF EXISTS SelectClasses;
DELIMITER $$

Users Login;

For the website there is a login system needed so that teachers can login and see attendance records. Using the database diagram I developed earlier I created a procedure that returns the person’s (teacher) ID from the database that matches their login credentials. This will allow the website to determine if they are correct, or report to the user if they are incorrect. I am returning just the ID of the person if they exist in the database as it will be used for later procedures. The ID will be kept in a Session using PHP.

CREATE PROCEDURE SelectUserLogin (IN pUsername VARCHAR(40), IN pPassword VARCHAR(40))
BEGIN

# SELECT THE USERS LOGIN

SELECT ID FROM person_login WHERE Username = pUsername AND Password = pPassword;

END $$

Check Session;

For each page there is a session check that takes place. The web site checks that the user has a session and if they do that it matches a user that is in the database.

CREATE PROCEDURE CheckUserLogin (IN pUsername VARCHAR(40))
BEGIN

# SELECT THE USERS LOGIN

SELECT Username FROM person_login WHERE Username = pUsername;

END $$

Attendance Call;

This is the largest and probably most complicated procedure that I have ever made. I worked out what needed to happen when a teacher wanted to view the student attendance records for a class. There where three steps that needed to happen;

1.
The attendance table in the database is where the attendance records are kept. When a new course, class and class roll are made there is no record of students in the attendance table. Therefore the first part of the procedure is to insert any new students into the attendance table when the procedure runs. When a new student is added to the attendance table they are automatically listed as A or absent, this will be updated if they attend the class. Also if any new students are added to the role it will also add them. Below is the query;

# CHECKS ATTENDANCE HAS ALL STUDENTS IN THE CLASS IN IT

-- IF NOT EXISTS (SELECT ID FROM course_roll c WHERE c.Course_TF_ID = pCourseTFID) = (SELECT ID FROM attendance a WHERE a.Class_ID = pClassID) THEN
 INSERT INTO attendance (ID, Class_ID) SELECT STID, pClassID FROM 
 (SELECT ID AS STID FROM course_roll c WHERE c.Course_TF_ID = pCourseTFID GROUP BY c.ID) t1
 LEFT OUTER JOIN
 (SELECT ID AS STID FROM attendance a WHERE Class_ID = pClassID GROUP BY a.ID) t2
 USING (STID)
 WHERE t2.STID IS NULL;
-- END IF;

2.
The next step is the most complicated. It needs to compare and update the student’s attendance record from A (Absent) to Y (Attended). But this only needs to happen if there they have swiped their Student ID card in the class. Due to the way the database is designed, when the details students UID, the room the card was swiped in and location it was swiped is recorded. When it is swiped there is no matching to a class or course done. It all needs to happen in this procedure. Due to the length and complexity of the procedure I broke it down into parts.
The first part finds all the students ID’s that swiped their cards in the classroom that matches the classroom of that the class is scheduled in. It matches the Unit number of the Arduino device to a classroom and then that classroom to the room of the class. From that it selects the students ID that match the condition.

(SELECT a.ID STID FROM person_uid a
INNER JOIN records r ON a.UID = r.UID,
 (SELECT n.Unit_No, n.Unit_No as RM_MATCH FROM room_unit n
 WHERE Room_Code = (SELECT Room_Code FROM class WHERE Class_ID = pClassID)
 GROUP BY n.Unit_No) u
WHERE (r.Unit_No = u.Unit_No) and r.Unit_No = RM_MATCH
GROUP BY a.ID) T1

The second part matches the students in the records table that swiped there card between the time that the scheduled class was running.

(SELECT b.ID STID FROM person_uid b
INNER JOIN records r ON b.UID = r.UID
WHERE `Scan_Datetime` BETWEEN (SELECT Class_Start FROM class WHERE Class_ID = pClassID)
AND (SELECT Class_End FROM class WHERE Class_ID = pClassID)
GROUP BY b.ID) T2

The last part was to bring it all together. The whole query updates the student’s attendance to Y when their ID is in both of the above queries. That means that they swiped there card in the classroom that the class was scheduled between when the class started and ended. I’ve added at the end of the query to only update the attendance if the teacher hasn’t already modified it.

# UPDATES ATTENDANCE BASED ON RECORDS WHERE THE TEACHER HASNT MODIFIED IT
 
UPDATE attendance a SET a.Attendance_Status = 'Y' WHERE a.ID IN
(SELECT STID FROM
(SELECT a.ID STID FROM person_uid a
INNER JOIN records r ON a.UID = r.UID,
 (SELECT n.Unit_No, n.Unit_No as RM_MATCH FROM room_unit n
 WHERE Room_Code = (SELECT Room_Code FROM class WHERE Class_ID = pClassID)
 GROUP BY n.Unit_No) u
WHERE (r.Unit_No = u.Unit_No) and r.Unit_No = RM_MATCH
GROUP BY a.ID) T1
 INNER JOIN 
(SELECT b.ID STID FROM person_uid b
INNER JOIN records r ON b.UID = r.UID
WHERE `Scan_Datetime` BETWEEN (SELECT Class_Start FROM class WHERE Class_ID = pClassID) AND (SELECT Class_End FROM class WHERE Class_ID = pClassID)
GROUP BY b.ID) T2
USING (STID))
AND Modified = 'N';

3.
The last part is to select the attendance records and return them. This means that the website can then display the student attendance records to the teacher. Below is the query;

# SELECT THE ATTENDANCE RECORDS FOR THE CLASS

SELECT p.ID, p.First_Name, p.Last_Name, a.Attendance_Status FROM person p 
INNER JOIN attendance a ON p.ID = a.ID 
WHERE a.Class_ID = pClassID;

Below is the query as a whole;

CREATE PROCEDURE CheckAttendance (IN pCourseTFID INT(11),IN pClassID INT(11))
BEGIN

# CHECKS ATTENDANCE HAS ALL STUDENTS IN THE CLASS IN IT

-- IF NOT EXISTS (SELECT ID FROM course_roll c WHERE c.Course_TF_ID = pCourseTFID) = (SELECT ID FROM attendance a WHERE a.Class_ID = pClassID) THEN
 INSERT INTO attendance (ID, Class_ID) SELECT STID, pClassID FROM 
 (SELECT ID AS STID FROM course_roll c WHERE c.Course_TF_ID = pCourseTFID GROUP BY c.ID) t1
 LEFT OUTER JOIN
 (SELECT ID AS STID FROM attendance a WHERE Class_ID = pClassID GROUP BY a.ID) t2
 USING (STID)
 WHERE t2.STID IS NULL;
-- END IF;

# UPDATES ATTENDANCE BASED ON RECORDS WHERE THE TEACHER HASNT MODIFIED IT
 
UPDATE attendance a SET a.Attendance_Status = 'Y' WHERE a.ID IN
(SELECT STID FROM
(SELECT a.ID STID FROM person_uid a
INNER JOIN records r ON a.UID = r.UID,
 (SELECT n.Unit_No, n.Unit_No as RM_MATCH FROM room_unit n
 WHERE Room_Code = (SELECT Room_Code FROM class WHERE Class_ID = pClassID)
 GROUP BY n.Unit_No) u
WHERE (r.Unit_No = u.Unit_No) and r.Unit_No = RM_MATCH
GROUP BY a.ID) T1
 INNER JOIN 
(SELECT b.ID STID FROM person_uid b
INNER JOIN records r ON b.UID = r.UID
WHERE `Scan_Datetime` BETWEEN (SELECT Class_Start FROM class WHERE Class_ID = pClassID) AND (SELECT Class_End FROM class WHERE Class_ID = pClassID)
GROUP BY b.ID) T2
USING (STID))
AND Modified = 'N';

# SELECT THE ATTENDANCE RECORDS FOR THE CLASS

SELECT p.ID, p.First_Name, p.Last_Name, a.Attendance_Status FROM person p 
INNER JOIN attendance a ON p.ID = a.ID 
WHERE a.Class_ID = pClassID;

END $$

Select Courses for Teacher;

When the teacher logs into the website it shows a list of courses that the teacher is running. Therefore a procedure is needed to select the courses. The below procedure does this selecting the courses that match the teachers ID.

CREATE PROCEDURE SelectCourses (IN pTeacherID INT(11))
BEGIN

# SELECT THE COURSES FOR THE TEACHER

SELECT c.Course_TF_ID, Course_Code, Start_Date, End_Date FROM course_timeframe c
INNER JOIN person_course_tf t ON c.Course_TF_ID = t.Course_TF_ID
WHERE t.ID = pTeacherID;

END $$

Select Classes For Teacher;

When the teacher has selected a course, the website needs to show the classes in that course. The below simple select procedure preforms this action.

CREATE PROCEDURE SelectClasses (IN pClassTFID INT(11))
BEGIN

# SELECT THE CLASS FOR THE TEACHER

SELECT Class_ID, Class_Start, Class_End, Room_Code FROM class
WHERE Course_TF_ID = pClassTFID;

END $$

EDIT;

While designing the website some of the above procedures changed due to problems in-counted. I have adjusted the above procedures accordingly.

Update Attendance;

Before I started the website I was procedure based off just getting the data out of the database and not making changes to it. Once I had got the website running I wanted to create a way for the teacher to modify the students attendance records. For this I needed a procedure to update the attendance record. When the teacher has changed a record manually and click save on the site it will call the below procedure, which updates the attendance record. It also changes the Modified record to Y where a change has been made.

CREATE PROCEDURE UpdateAttendance (IN pClassID INT(11), IN pID INT(8), IN pAttendanceStatus CHAR(1))
BEGIN

# UPDATE ATTENDANCE RECORDS 

UPDATE attendance a SET a.Attendance_Status = pAttendanceStatus, a.Modified = 'Y' WHERE a.Class_ID = pClassID AND a.ID = pID AND a.Attendance_Status != pAttendanceStatus;

END $$

Call Attendance Records for CSV File;

When the site was working I wanted to add another feature and that was downloadable CSV files of the attendance records. The below procedure selects the attendance records. It is just the last part of the Attendance Call procedure. I decided that the Attendance Call procedure didn’t need to be called again for this action as it was done when the attendance page was loaded anyway.

CREATE PROCEDURE CSVAttendance (IN pCourseTFID INT(11),IN pClassID INT(11))
BEGIN

SELECT p.ID, p.First_Name, p.Last_Name, a.Attendance_Status FROM person p 
INNER JOIN attendance a ON p.ID = a.ID 
WHERE a.Class_ID = pClassID;

END $$

Arduino Procedure;

An over site was I forgot to create a procedure for the Arduino inserting the records into the records table. This was a simple copy the query and turn it into a procedure. I then modified the Add_UID.php page so that it called the procedure and didn’t just run a query.

CREATE PROCEDURE InsertRecord (IN pUID VARCHAR(8), IN pUnitNo INT(11), IN pTimeStamp TIMESTAMP)
BEGIN

# INSERT RECORD FROM ARDUINO

Insert into prjrfid701.records (UID, Unit_No, Scan_DateTime) values (pUID, pUnitNo, pTimestamp);

END $$
Advertisements

One thought on “Blog 11 – Procedures

  1. Pingback: Blog 16 – Security | Digital Insaniti

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s