Blog 17 – Website finishing touches

In Blog 15 – Website continued I got the base of the site working with the features needed to demonstrate the system. All I need to do is set up an example class under with a timeframe that I am going to swipe a card over the arduino unit, and it will show in the attendance records. I mentioned that I wanted to add an ability to adjust the attendance records manually. I also wanted to add a way to download CSV files. This would contain a copy of the attendance records for the class. Below I go over how they work.

Pages;

csvdownload.php
At the bottom of the attendance page I added a download CSV button.29-10-csv-button Since only one MySQLi query can be called per page and to get the information to go in the CSV file and it requires a MySQLi query, I created another PHP file to perform this task. As I had no clue on how to approach this I googled it. I came across this post “Query mysql and export data as CSV in PHP“. The second code suggestion was the solution I ended up using. It starts out by getting the data from the database and putting it into a array. If that is successful, the PHP tells the file some html commands to make it a temporary page and to create a CSV file that is to be downloaded. The data stored in the array is then inserted into the file before it is downloaded.
29-10-downloaded-csv

When the file is opened it contains the attendance details of the class.

29-10-opened-csv-file

attendancesave.php
Ive also added a Submit button. This is for the saving of any changes made by the teacher. When they click the button it takes posts the form, including the table and its cells, to the attendance save file. The PHP in this then works through each row of the table and updates the attendance table if there are any changes made by the teacher. To make this I used the example posted to “how to insert html table data into mysqli database in single command“.  I have made the file redirect the teacher back to the home page. This give means that the teacher has to go back into the attendance. I have done this so the table has a chance to update.

Edit;

I am continuing to make little changes to the code to improve the site so it is subject to change. The code posted below is accurate as of 28/10/2016.

Code;

Current Final Version of the code as of 28/10/2016. I have *** out any sensitive code.

csvdownload.php

attendancesave.php

Advertisements

Blog 17 -SSL (HTTPS) turned on

I mentioned in Blog 16 – Security that I wanted to enable SSL (Secure Sockets Layer).
Standard HTTP is a communication channel between the web server and the browser. HTTP is not encrypted and leaves and communication between the server and browser open for eavesdropping. If someone was to intercept the traffic, they could easily see what was being passed back and forward. For a website that handles sensitive information this can’t happen. One way to help alleviate this risk is to add SSL, or otherwise know as, HTTPS to the web server. HTTPS is an encrypted communication channel using trusted key pairs. Before data is sent from either end, it is encrypted, then sent, and unencrypted at the other end. If someone was to intercept the traffic then they won’t be able to read the data being sent back a forward.

The AWS linux server that I am using in to host my web server supports the HTTPS protocol. Using this tutorial: Configure Apache Web Server on Amazon Linux to use SSL/TLS I enabled HTTPS on my web server.

Below is the steps to enable HTTPS;

19-10-check-and-update

First I checked to see if the web server was running. Then checked to see if there were any updates. As you can see there were many. I need to do an update check regularly from now on I think.

19-10-install-ssl

Once the update has finished I could install the SSL (HTTPS) service.

19-10-restart-httpd

When the SSL service had been installed, I restarted the web server so that the SSL service could start.
I could then go to my security group for the web server and add a HTTPS rule. I have set both inbound and outbound open to HTTPS traffic. This means that the web site can be accessed from anywhere. I have locked HTTP traffic to only 2 IP address now, for testing purposes.

28-10-https-inbound28-10-https-outbound

Now if I go to the web site, but add https:// before the URL, the communication between the browser should be encrypted.

However when I visit the website I am greeted by this;

28-10 HTTPS security message.PNG

Further down in the tutorial there another step;  Obtain a CA-signed Certificate.
I do not have a CA-signed Certificate. This I why I am receiving this warning when visiting the website. The Certificate verifies that you are the owner of the web site. Due to my site not having one of these certificates, Google Chrome cannot identify if I am the owner. It thinks that the site could be a copy of the original, used to siphon information. To get a CA-signed certificate you need to apply to get one.
This involves buying/obtaining a domain name for the website, like http://www.attendance.co.nz and making the web server use this domain.
They you need to apply, “certificates generally cost money because of the labor involved in validating the requests, so it pays to shop around” and it also takes time.
As this project is funded by me and I had already absorbed the cost for the Arduino components I didn’t want to fund the buying of a domain name and the cost to get the certificate. If this system was being developed to be deployed then these steps can be taken as they will be incorporated into the development price.

The website still operates as before, with HTTPS enabled. It shows that the option is there and has been explored.

Blog 16 – Security

There were two more security aspects that I wanted to explore.

1 – Security Group Rules

The first being the security groups that allow access to the Web Server and MySQL database. At the moment I have added inbound IP address, to the AWS security group, for the locations that I am working at. This is for access to the web servers SSH and HTTP connections. For the MySQL server I just set it to accept traffic from anywhere to make it easier when connecting to it to configure and the so the Webserver can call it as well.

19-10 Inbound Rules.PNG

Now that I have some data in the database allowing the MySQL server access from anywhere is not best practise, as anyone can attempt to connect to it. Therefore I need add access restrictions. For this I am going to remove the accept all traffic condition and add the address of the Webserver as when the system is operating no other inbound access rules are needed. I will also add my personal IP address so that if I need to configure it I can connect using MySQL workbench. I can remove and add the address that I am connecting from at anytime by logging into the AWS console and changing the inbound rules.

To find the IP address of the Webserver, I went to it’s dashboard. I needed its private IP address. This is an internal IP address used by the server.

28-10 Private IP.PNG

I then went to the security group section and added a new security group for the database. Before I had both the web server the database in the same security group. This meant that some rules that I could apply might apply to both. I decided to separate them so I have better control over their access rules. I then went to the security group settings and added the IP address to the inbound rules. This means the server will be the only one that can access the database. I did add my local public IP address as I still need to connect and configure the database. I repeated this for the outbound rules too.

28-10-new-sg28-10-new-sg-out-bound
This means the the database can only receive execute procedure calls from the web server and can only send information back to the web server. If someone wanted to try connect to the database they would need to create a server with the same internal IP address, which is virtually impossible, as the internal IP address are allocated by AWS.

For added security when I’ve finished the project I can turn off the publicly accessible option on the database. This means that only internal connections, like the web server can access the database.

For the web servers IP inbound rules I kept them the same. I added the same rules to the outbound rules. I also included the security group of the Database in both inbound and outbound rules. This allows the web server to contact the database to perform queries. In a future blog I am going to go over enabling HTTPS on the web server. When I have done this I will set a rule that allows HTTPS access from anywhere. This means that anyone that wants to access the website can only do it over a secure connection. I will then remove the HTTP rules as they are no longer needed.

28-10 Outbound rules webserver.PNG

2 – MySQL Accounts

The second security aspect that I wanted to include was creating MySQL accounts for certain users. As I briefly discussed in Blog 11 – Procedures when you create procedures you can have users that only have access to execute procedures.
When I started creating the website I used the Admin account when connecting the website to the database. This was so that I could test different queries if I encountered a problem. But now that the website is up and functioning I can move to connection user account that have limited access. I went into MySQL workbench and using the database configuration tools made a new user with only execute permissions.

19-10-mysql-new-account

For the user login details I made sure that the password was long and included both capitals, lowercase, and special characters. The login name represented the accounts use.

19-10-connection-limits

Although the above limits that I have set on connections are very high, they are still a restriction. I based these connection limits off what could be the highest use case scenario. However if the server was attacked this will provide some limit.

19-10-no-admin-privilages

I made sure that the user had no admin privileges at all.

19-10-schema-rights

I added the project’s Schema as the only Schema that the user can access. They don’t need access to any others that may be on the MySQL server. In this screenshot I selected both SELECT and EXECUTE privileges. I have since removed the SELECT privilege as it isn’t needed anymore. All interaction by the website are done through procedures now, therefore only EXECUTE is needed.

19-10-connection-denied

I also tested to make sure the account worked correctly. I went and tried to perform an UPDATE query on the schema and it was denied. This confirmed that the user account worked. You can also see that the Schema for the project is the only one shown. I have other schema yet they don’t show as the user doesn’t have permission to access them.

Blog 15 – Website continued

In Blog 13 – Website Start I showed that I had the start of the website done, the login page. I had also covered how that I was going to have one page show all the information. However I ran into some problems with this method. I started out with one table, the teachers courses, on the page and it worked. When the page loaded a MySQLi query was executed which created and filled a table. I then tried to add a second table to the same page that would load the classes. However when I went to the website after adding the second MySQLi query I would be greeted with this error;

27-10 MySQLi not working.PNG

On researching the issue, I found that “you can’t have two simultaneous queries because mysqli uses unbuffered queries by default for prepared statements”(Source). The main reason MySQLi is designed like this is for security reasons. It is so that a web page using MySQLi can’t repeatedly call the database in a kind of DDoS attack.

Therefore I’ve changed to a 3 page site, where each page does a different process. The first page loads the teachers courses, the second loads the classes in the course, and the third show the class attendance.

Website design;

For the website design I went for a simple css design. As it is proof of concept I doesn’t need to actually represent my website design, but I did use it for the basic layout. In the top right it shows the current user’s username and there ID from the database. This is taken from the session data created in the login process. In the bottom bar is the sign out. This logs the user out and takes them back to the login page.

29-10 Website design.PNG

Pages;

home.php
This shows the courses. A query is called that returns the courses the teacher is linked to in the database. This then dynamically creates a table using PHP. This means that the number of results returned will be the size of the table. In the table the course code, start and end dates are shown. I then went with a system of having a button in each table row. This has the course ID, as it is identified in the database, on it. The table is contained in a form so when a button is clicked it posts to the PHP code the course ID which stores it in the session. It then opens the class.php page.

class.php
This page works the same as the home.php except if calls the classes of the course that is saved in the session data.

attendance.php
This is where the magic happens. When this page loads it calls the updated attendance records, for the class in the course that the teacher is teaching. I used the same dynamic HTML table as in the last two pages. Except in this table I replaced the button with a drop down. In the drop down it contains the 4 different attendance status;

  1. Absent
  2. Attended
  3. Late
  4. Explained

I have used a drop down as in the future I would like to implement the ability change and submit a different attendance status. The drop down box is programed to make its default the status that is stored in the database. You can see this in the screenshot below.

 

29-10 Attendance.PNG

Edit;

I have adjusted the code to use procedures and not queries.

If you look at the code there maybe mention of features not yet explained in this blog post. They will be covered in a future post.

Code;

Current Final Version of the code as of 28/10/2016. I have *** out any sensitive code.

home.php

<?php
 include('session.php');
 require_once("DB files/config.php");
 $UserID = $_SESSION['user_id'];


// Retrieve all records and display them
 $CourseCall = "CALL SelectCourses(\"$UserID\")";
 $resultCC = mysqli_query($conn, $CourseCall) or die;
 
 // Used for row color toggle
 $oddrow = true;
 
 if($_SERVER["REQUEST_METHOD"] == "POST") {
 // username and password sent from form
 
 $selectedcourse = $_REQUEST['courseselection'];
 
 $_SESSION['selected_course'] = $selectedcourse;
 header("location:class.php");
 
 }


?>
<html>
<head>
<meta charset="utf-8">
<title>Home</title>
<link href="desgin.css" rel="stylesheet" type="text/css">
</head>

<body>

 

Smart Student Portal

 

 

Welcome user;

Youre ID; =

</div> </div>

Courses

</div>

Start Date
End Date
Select a Course  ‘; echo ”.$row[“Course_Code”].”; echo ”.$row[“Start_Date”].”; echo ”.$row[“End_Date”].”; echo ”; echo ”; } $conn -> close(); ?>

Course Code

 

</div>

class.php

<?php

include('session.php');
require_once("DB files/config.php");
$CourseTFID = $_SESSION['selected_course'];


$ClassCall = "CALL SelectClasses(\"$CourseTFID\")";
$resultCC = mysqli_query($conn, $ClassCall) or die;

if($_SERVER["REQUEST_METHOD"] == "POST") {
 // username and password sent from form

$selectedclass = $_REQUEST['classselection'];

$_SESSION['selected_class'] = $selectedclass;
 header("location:attendance.php");

}

?>

<html>
<head>
<meta charset="utf-8">
<title>Classes</title>
<link href="desgin.css" rel="stylesheet" type="text/css">
</head>

<body>

 

Smart Student Portal

 

 

Welcome user;

Youre ID; =

</div> </div>

Classes

</div>

Class End
Room Number
Select a Class  ‘; echo ”.$row[“Class_Start”].”; echo ”.$row[“Class_End”].”; echo ”.$row[“Room_Code”].”; echo ”; echo ”; } $conn -> close(); ?>

Class Start

 

</div>

attendance.php

<?php

error_reporting(E_ERROR | E_WARNING | E_PARSE);

include('session.php');
require_once("DB files/config.php");
$CourseTFID = $_SESSION['selected_course'];
$ClassID = $_SESSION['selected_class'];


$AttendanceCall = "CALL CheckAttendance(\"$CourseTFID\",\"$ClassID\")";
$resultAT = mysqli_query($conn, $AttendanceCall) or die(mysqli_error($conn));

?>

<html>
<head>
<meta charset="utf-8">
<title>Attendance</title>
<link href="desgin.css" rel="stylesheet" type="text/css">
</head>

<body>

 

Smart Student Portal

 

 

Welcome user;

Youre ID; =

</div> </div>

Attendance

</div>

First Name
Last Name
Attendance Status  ‘; echo ”.$row[“ID”].”; echo ”.$row[“First_Name”].”; echo ”.$row[“Last_Name”].”; echo ‘ Attendended Absent Late Explained ‘; echo ”; } $conn -> close(); ?>

ID

 

</div>

 

 

Blog 14 – Finishing up the Arduino

Version 2.1

As mentioned in Blog 6 – Pause to plan in version 2.1 I wanted to move the timestamp aspect from the website to the Arduino. In version 2.0 the timestamp was the time that the web server submitted the record to the database. This meant that it wasn’t the exact time that student swiped their student ID card.
I went and found a time library and downloaded it. This library is a software library, this means that it uses the onboard processor to keep time. It also had the function to contact the public time libraries to keep it up to date. I started to implement the library into my original arduino code.  I was reading a couple forum about how to implement the time library and came across some problems with the library.

The first being that the as the time library is software based it uses the oscillator/crystal and the tick rate of the processor to keep time. Theses are both not perfectly accurate, as outlined in this forum post. The oscillator/crystal used to help keep time is affected by environmental conditions around the Arduino. The oscillator/crystal vibration rate of 16Mhz changes slightly when the temperature changes. This in turn affects the time. Also the software works based on the processor tick rate of 1ms but is actually 1.024ms. Therefore this leads to the time slowly becoming inaccurate. Also if the command Delay is used in a program, like it is in mine, It can also greatly affect the time. This is due the delay command actually stops the processor for the time specified. It uses the oscillator/crystal as the timer. When the processor stops, so does the timekeeping function.  There are ways to combat this issue, like updating the time from the internet time servers periodically, but this leads into my second problem.

The international time servers that you can use to get the time are publicly run and keeping them online costs money. Having the Arduino unit call the time server once a week to get the time would be fine. But this would lead to huge inaccuracies in the time that the students ID card was scanned. Therefore the unit would need to call the time server once or twice a day. Each time the unit calls the time server it cost them money and adds extra load to the server, so time servers should only be call occasionally. If one IP address is calling the time server once or twice a day then it may be added to the blocked list, but it is unlikely. However if there where one or two of the Arduino units in each classroom at NMIT, let’s say a total of 100 units, calling the time server 200 times a day, then the IP address will certainly be blocked. The above problem is discussed in this forum post.

Considering the above problems mentioned I have decided to skip version 2.1. The problems it causes are to great to give me a reason to change from version 2.0. I have decided to make sure time is kept accurate in the records in the database I will use to error reporting feature to make sure that the timestamp is inserted into the database at the time that the card is swiped. If there is a connection failure it will be shown by the arduino unit to the student, using features implemented in version 2.2. This time is takes to perform the insert is at max 2 seconds. So the difference from when the card was swiped to the time in the database is marginal enough that it doesn’t matter for this application. This means sticking with using the web server to provide the time. This works as the time is taken from the web server’s operating system, which has a very accurate time keeping system.

Version 2.2

I needed to add some sort of visual interaction with the user. I decided to use an RGB LED to indicate the Arduino system is working. I also decided to add a buzzer for when the students UID was successfully added to the database records. I decided the buzzer needed as most students would just touch their ID cards to the unit and walk off without checking that it sent. The buzz would help confirm if it is sent. If the buzzer doesn’t sound then it has failed.
Below are the different stages and actions;

2016-10-28 (1).png

This is the new code;

/*
 Ethernet shield attached to pins 10, 11, 12, 13
*/

#include <SPI.h>
#include <Ethernet.h>
#include <MFRC522.h>

#define RST_PIN 5 // Configurable
#define SS_PIN 53 // Configurable

// pins for the LEDs:
const int redPin = 11;
const int greenPin = 12;
const int bluePin = 13;

const int buzzer = 9; //buzzer to arduino pin 9

MFRC522 mfrc522(SS_PIN, RST_PIN); // Create MFRC522 instance.

int sendTime = 60000;
int bstate = 0;
String read_rfid;
String rfid_uid;
int Unit_No = 1;

// common-cathode LED, setting color "constrain(color, 0, 255);"
int red = 100 - constrain(red, 0, 255);
int green = 50 - constrain(green, 0, 255);
int blue = 100 - constrain(blue, 0, 255);
int off = 0 - constrain(red, 0, 0);

//Mac and ip address of php server
byte mac[] = { 0x00, 0xAB, 0xBA, 0xBC, 0xDD, 0x02 };
char server[] = "ec2-52-65-157-177.ap-southeast-2.compute.amazonaws.com";

EthernetClient client;


void setup() {

Serial.begin(9600); // Initialize serial communications with the PC
 while (!Serial); // Do nothing if no serial port is opened (added for Arduinos based on ATMEGA32U4)
 SPI.begin(); // Init SPI bus
 mfrc522.PCD_Init(); // Init MFRC522 card
 mfrc522.PCD_DumpVersionToSerial(); // Show details of PCD - MFRC522 Card Reader details
 Serial.println(F("Scan PICC to send UID"));
 rfid_uid = "";

pinMode(redPin, OUTPUT); // make the pins outputs for RGB
 pinMode(greenPin, OUTPUT); // make the pins outputs for RGB
 pinMode(bluePin, OUTPUT); // make the pins outputs for RGB

pinMode(buzzer, OUTPUT); // Set buzzer - pin 9 as an output

Ethernet.begin(mac);
 delay(1000);
 startEthernet();
 analogWrite(greenPin, green);
}

//------------------------------------------------------

/*
 Helper routine to dump a byte array as hex values to Serial.
*/
void dump_byte_array(byte *buffer, byte bufferSize) {
 read_rfid = "";
 for (byte i = 0; i < bufferSize; i++) {
 read_rfid = read_rfid + String(buffer[i], HEX);
 }
}

//------------------------------------------------------

void loop() {
 //Let Reader start
 delay(200);

// Look for new cards
 if ( ! mfrc522.PICC_IsNewCardPresent())
 return;

// Select one of the cards
 if ( ! mfrc522.PICC_ReadCardSerial())
 return;

dump_byte_array(mfrc522.uid.uidByte, mfrc522.uid.size);

if ( read_rfid != rfid_uid) {
 if (read_rfid != rfid_uid) {
 analogWrite(greenPin, off);
 analogWrite(bluePin, blue);
 Serial.println(read_rfid);
 rfid_uid = read_rfid;
 DataToMySQL();
 }
 }
 else {
 analogWrite(greenPin, off);
 analogWrite(redPin, red);
 delay(500);
 analogWrite(redPin, off);
 analogWrite(greenPin, green); 
 }
}

//------------------------------------------------------

void DataToMySQL() {

bstate++;

EthernetClient client;

if (client.connect(server, 80))
 {
 // print to serial monitor
 Serial.println("connected...");
 Serial.println("ARDUINO: forming HTTP request message");

// send data the server through GET request
 client.print("GET /add_UID_v2.php?UnitNo=");
 client.print(Unit_No);
 client.print("&UID=");
 client.print(read_rfid);
 client.println(" HTTP/1.1");
 client.print("HOST: ");
 client.println(server);
 client.println();
 Serial.println("ARDUINO: HTTP message sent");

// give server some time to receive and store data
 // before asking for response from it
 delay(500);

// get the response from the page and print it to serial port
 // to ascertain that data was received properly
 if (client.available())
 {
 Serial.println("ARDUINO: HTTP message received");
 Serial.println("ARDUINO: printing received headers and script response...\n");
 tone(buzzer, 500);
 delay(400); // ...for .4 sec
 noTone(buzzer); 
 analogWrite(bluePin, off);
 analogWrite(greenPin, green);

while (client.available())
 {
 char c = client.read();
 Serial.print(c);
 }
 }
 else
 {
 Serial.println("ARDUINO: no response received / no response received in time");
 analogWrite(bluePin, off);
 analogWrite(redPin, red);
 delay(1500);
 analogWrite(redPin, off);
 analogWrite(greenPin, green);
 
 }

client.stop();
 }
}

//----------------------------------------------------
void startEthernet()
{
 client.stop();

Serial.println("Connecting Arduino to network...");

delay (1000);

//connect to network and obtain an IP address using DHCP
 if (Ethernet.begin(mac) == 0)
 {
 Serial.println("DHCP failed, reset Arduino to try again");
 Serial.println();
 analogWrite(redPin, red);
 }
 else
 {
 Serial.println("Arduino connected to network using DHCP.");
 Serial.print("IP address is ");
 Serial.println(Ethernet.localIP());
 Serial.println();
 }

delay(1000);
}

Although this is almost the final version of the code it is the largest it will be. I wanted to test it the code will run on a smaller and cheaper Arduino device, such as the nano, a $5 USD chip. I change the IDE compiler options so that it thought it was compiling it for a Nano. The results came in that it could. With plenty of room to spare. This means that this system, using the cheapest parts could be put together for about $20 USD per unit, much cheaper than the $35-$50 USD my unit costs. Below are the test results;

28-10 Will run on nano.PNG

Version 2.3

This is not any changes to the code but a mounting example solution. I wanted a way to securely hold all the Arduino components. The best solution I could find, that was cheap, was a tupperware container from Kmart. It was $1, and the right size to fit the Arduino Mega I am using. The container was also thin enough that the RFID tag could be read through the container lid meaning that all the components could be mounted inside it.

This slideshow requires JavaScript.

I started off by arranging the components in the container and then drilling the holes required to fit them. Those components I then attached using what I had lying around.
To hold the Arduino in the container I found some tiny screw and screwed them through the mounting holes on the Arduino. You can see they aren’t screwed all the way in, this because they will come out the base of the container and be a hazard.
To secure the RFID reader in the container I used two zip ties. They worked well to hold the reader tight against the lid.
To secure the buzzer and LED I used a hot glue gun.
The big red button on the side is a hard reset button. I added this so that the unit can be reset without the need to open the case or disconnect the power to the device.

Version 3.0

3.0 although not implement yet will have just remove the command that starts the serial port. This means that if the USB port was connected to a computer and the serial reader started, there would still be nothing displayed. This is a simple change, and when done will complete the Arduino unit.

Blog 13 – Website Start

This project is a proof of concept project for an infrastructure and networking major. To make a whole website would turn it into a Software Development project. Therefore I have decided to create a 2 page site that will demonstrate the functionality of the system I have created. I have decided that having a login page will be needed identify a teacher. It will be used to identify their courses and classes. The second page will display the teachers courses, classes and the attendance of the class. I intend to have a table shown at the top with the courses and when the teacher clicks on a course a table loads below it with the classes in that course. Then another table is loaded below it, when they click on a class, that will show the classes attendance.
In this blog I will go over the creating of the Login page.

Before I started developing the website I needed a program to develop it in. I could use NotePad++, but I find it hard to use as it only has basic features. It also has no form of error checking. I looked into free solutions. One that appeared to look good was Microsoft Expressions. I downloaded and tested it. I found that it was outdated and had very little support for PHP. This is due to it is now integrated into Visual Studio, but as a paid edition. In the end I settled on the open source Eclipse. They have a PHP version of their development software. I downloaded and installed it. It worked well with PHP and had error checking. The layout was simple enough to manage. To keep the local files I edited in Eclipse up to date on the web server I used WinSCP and it’s auto synchronize function.

Now that I had a developer software I went looked online for examples of PHP login system. I found a PHP – MySQL Login tutorial on Tutorials Point. This had the features that I wanted to implement. It used PHP sessions to keep the user logged in and provided a way to store data in the session. It also had code to logout the user. But the reason I went for this tutorial as my example to work from is it had code to verify the session. This meant that if a user hadn’t logged in but typed in the exact URL of one of the pages behind the login wall, they would be instantly redirected back to the login page.

I copied the code into PHP files I had created in Eclipse. It was then just a simple process of modifying the MySQL connection details and changing the queries to match the database. I then added a test account into the database. Once this was done I tested the login and the session handling.
While modifying the login.php code I noticed it had no was to stop SQL injection. This is when a person puts code into text inputs. In some cases the website or database will preform the codes action. This can allow a person to gain access to sensitive data. When looking for example login systems I can across this a tutorial called PHP Login Form with Sessions. In its login code it had a method to combat SQL injection. For someone to SQL inject they have to use slashes, so so stop it you remove any slashes. Below is the section I added;

 // To protect MySQL injection for Security purpose
 $myusername = stripslashes($myusername);
 $mypassword = stripslashes($mypassword);

 

Edit;

Since writing this blog I have been updating it with any additions I have made to the code. I have changed the queries from standard select statements to ones that now call there respective procedure. I’ve also changed the MySQL connection account from the admin one to the user account with just execute permissions.
I have also added an index page.

<html>

<head>
<meta charset="utf-8">
<meta http-equiv="refresh" content="0; url=login.php">
<title>index</title>
</head>

<body>
</body>

</html>

When a user visits the website, the web server will direct them to the index page. Before I didn’t have one so you will have to type in the URL + /login.php. With the above index page it automatically redirects the user to the login page.

Code;

Current Final Version of the code as of 28/10/2016. I have *** out any sensitive code.

config.php

<?php

$host="******.********.ap-southeast-2.rds.amazonaws.com";
$port=3306;
$socket="";
$user="**********";
$password="**********";
$dbname="prjrfid701";

// Create connection
$conn = mysqli_connect($host, $user, $password, $dbname, $port, $socket);
// Check connection
if (!$conn) {
 die("Connection failed: " . mysqli_connect_error());
}

?>

login.php

<?php

error_reporting(E_ERROR | E_WARNING | E_PARSE);

require_once("DB files/config.php");
 session_start();
 
 if($_SERVER["REQUEST_METHOD"] == "POST") {
 // username and password sent from form 
 
 $myusername = $_REQUEST['username'];
 $mypassword = $_REQUEST['password'];
 
 // To protect MySQL injection for Security purpose
 $myusername = stripslashes($myusername);
 $mypassword = stripslashes($mypassword);
 
 $loginsql = "CALL SelectUserLogin (\"$myusername\",\"$mypassword\")";
 $result = mysqli_query($conn, $loginsql);
 $row = mysqli_fetch_assoc($result);

// If result matched $myusername and $mypassword, table row must be 1 row
 
 if(mysqli_num_rows($result) == 1) {
 $_SESSION['login_user'] = $myusername;
 $_SESSION['user_id'] = $row[ID];
 header("location:home.php");
 }else {
 $error = "Your Login Name or Password is invalid";
 }
 }
?>
<html>
 
 <head>
 <title>Login Page</title>
 <style type = "text/css">
 body {
 font-family:Arial, Helvetica, sans-serif;
 font-size:14px;
 }
 
 label {
 font-weight:bold;
 width:100px;
 font-size:14px;
 }
 
 .box {
 border:#666666 solid 1px;
 }
 .show{
 display:box; 
 }
 .hide{
 display:none;
 }
 </style>
 
 </head>
 
 <body bgcolor = "#FFFFFF">
 
Login

 

UserName :
Password :

 

</div> </div> </div> </body> </html>

^^ in the above text is an example of code injection. I copied in text but the browser is reading it and trying to turn it into a page. This works the same with MySQL queries.

session.php

<?php
 require_once('DB files/config.php');
 session_start();
 
 $user_check = $_SESSION['login_user'];
 
 $ses_sql = mysqli_query($conn,"Call CheckUserLogin \"$user_check\"");
 
 $row = mysqli_fetch_assoc($ses_sql);
 
 if(!isset($_SESSION['login_user'])){
 header("location:login.php");
 }
?>

 

Blog 12 – Report Rewrite

In the first week of the 2 week term holidays I started the report. I had a basic layout and the background and project plan almost done. I had also started taking notes for other sections as well. I then lost the file. I’ve had this happen before.
It was saved in Onedrive and I had the file open. I closed my laptop and left it. When I came back to work on it the laptop had restarted, for an update I believe, and the word file containing my project was gone. When I opened word and went to recent documents and tried opening the document, it said unable to find the file.
I checked Onedrive both on my computer and online and it have disappeared from there too. It was also missing from Onedrive 30 day history. This was the same as what happened before.
Luckily I hadn’t done too much work on it, so I restarted it using the template provided on the project blog. I have redone the work and am roughly up to where I was up to before. Although this time I believe I have improved my layout and design. I have decided to base the bulk section of the report on my blogs. They provide a timeline of how I did the work. I will use them as a base and turn them into a format used in reports
I am also saving this new report in a different location, my dropbox. I haven’t had any troubles with dropbox before. When the report gets a bit bigger I may consider saving it to another place as well, just for extra peace of mind.

 

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 $$

Blog 10 – DB implementation

Working from the DB design that I have developed I used MySQL Workbench to implement the database. I initial started with the original design and have been updating it along to match changes to the database that I felt were necessary. These changes were highlighted in the previous blog.

To create the database I setup a new schema “prjrfid701” to create the database in. I then used the built in table creator to construct the tables and their relationships. Before the table creator implements the table it shows the SQL that will be used to create it. I copied this down into a sql file so that I could build the database at a later date. I then went to a website called Mockaroo which allows to to create 1000 rows of test data. I used this to build test data in tables I thought would need it, like the person and person_uid tables. The test data will help with creating procedures that will be used to implement the section of the website that I’m going to implement.

12-10-creating-db-1

Creating the tables using the built in table creator. When you have finished it builds the SQL to create the table for you.

12-10-creating-db-2

That the table creator also allows you to add the foreign keys to the attributes. I also set restrictions, like cascade and restrict, on any relationships I felt it was necessary.

12-10-creating-db-3

When the table creator generated the code I copied it down to a sql file so that I could run the file and recreate the tables and relationships when needed.

12-10-creating-db-4

Tables created in the schema

12-10-creating-db-5

I then added in test data to tables that needed it. This helped in creating the procedures.

12-10-creating-db-6

Creating test data for Person

12-10-creating-db-7

Creating the tables using the built in table creator. When you have finished it builds the SQL to create the table for you.

In the next blog I will be creating a series of procedures for a web page proof of concept example.

Blog 9 – Database changes

This is a quick blog to show some changes that I made to the database design. These were changes that I realized needed to be made as I was writing blog 8. Below is the previous design;

28-09-database-design

And this is the new design;

10-10 Database design v2.1.PNG

Changes;

  • I changed course_class to course_timeframe. I found that the course_class name did not best represent the entity. Course_timeframe was a better choice as this entity determines the when the course starts and when it ends, like a timeframe. Changing the name of the entity also meant that I needed to change the ID in the table.
  • In the class entity I have removed the class date attribute and changed the class start and end time from a time attribute to a datetime attribute. This is because before I was thinking about a class being just one day but sometimes they are trips or classes continued over a number of days so this means that there is a start date and an end date for the class
  • The attendance entity has now become more useful. Before I had added it as it was needed but I didn’t know how to implement it. Now it identifies a person with a class and also allows for the teacher to modify the student’s attendance record if needed. If modified then the it is marked as in the modified attribute. This will be used when checking and creating the attendance record.

There will need to be a bit more refinement as the website as developed. I am trying to create a model that I think will work for this project without fully implementing it. Therefore there will be some considerations in the database design that I have missed.

EDIT;

More changes since;

12-10 Database v2.2.PNG

When coming up with some of the procedures for the website I realized that having the Person (Teacher) linked to a course_timeframe through the Course entity meant that only the course could be only run by one teacher at once. If one course was being twice at the same time by two different teachers there was no way to differentiate the two. That is why I added Person_Course_TF. I know that it is repeating data effectively due to Course_Roll being identical. But Course roll is to determine the students on the course and Person _Course_TF is to identify the teachers teaching the course. This means that one or more teachers and teach the course and there can be more than one course running at once. It also made writing the procedures easier.