My task in this College assignment was to develop a comprehensive college database system for managing student records and programs. The system should be implemented using the LAMP stack (Linux, Apache, MySQL, PHP).
My code starts off on the login page that’s essentially both a form that has an SELECT FROM statement attached to it, where the user must input a username and password in order to gain access to not only the website but all of the database. Once the user has entered this information and clicks the LOGIN button at the bottom of the page, the information gets sent through an if statement and if the information is correct, they’ll have access to the rest of the site.
They’ll then be redirected to the main menu page, this page will consist of various buttons that will link to other parts of the site being my tables, for instance programmes, modules, students, grades, and the ability to add and remove certain aspects of each of these tables. Additionally, there is a button at the bottom to use to logout which will destroy the sessions cookies and log the user out.
Each of the sections where I list the contents of the tables are done by SELECT * FROM statements where they select all of the information in a given table and then display it in a table on the webpage.
The next section is the add section where a user can add certain details to a table, this is done by using a form to ask the user specific information to input, this information then gets checked by an if statement and if it is all correct and fine to go into the table then it’ll be passed into the database using an INSERT INTO statement.
Furthermore, I then also created a remove section that once again asks a user via a form page to insert an ID, for example this could be a student id, a module id, or a programmes id. This information then gets passed on to another page where a DELETE FROM statement is initiated which checks if the information the user entered is valid and if it is then it’ll delete all necessary information from the table that they chose to delete data from.
Lastly, is the search section which simply searches for a specific student’s id and if the student id exists then it’ll display all the grades the student has gotten in all of the modules that the student takes. This is done by using once again a SELECT FROM statement.
This section will display all of my PHP and HTML code along with an explanation of how it all works, this section will include pages such as login, logout, main menu, styling, adding to database, deleting from database, inserting into database, selecting databases (displaying tables), removing information, and searching for information such as student IDs when looking for results.
Login Page: In order to create the login page, I initially had to create an if statement that checked if the username that was entered by the user in the form was correct. If this username was correct then it gives the user access to the server, database, and all information inside of it as it would be passed through a check system and would check if the information entered was correct.
Logout Page: I made a simple logout page by simply checking if the user was already logged in and if so would unset/destroy the login session which would cause the user to lose access to both the server and database.
Main Menu Page: The main menu page that I created consisted of all of the pages that is on my server, it includes buttons with a href pieces of code in order to link to the other pages, for tables, for programmes, modules, students, grades, and a logout button too. When a user clicks on a button it then submits a value to go directly to the page the user wants to go to.
Form Pages: This is how I added information to my database, I initially asked the user to submit data about programmes, modules, students and grades and specific variables within each of those tables. Once the user entered all of this information for whatever form page they were on, then they could click the button at the bottom of the page to submit the data, this would then go to another page called an insert page where it would insert the information input by the user into the tables and into the database itself.
Delete Pages: The delete pages which I created are to be used by the user to submit a specific id, whether that be a student id, module id, program id, or grade id. By them entering and submitting this value via the button at the bottom of the page it would then send the information to the remove pages and delete the information from the databases and tables.
Insert Pages: The insert pages I created are used in order to insert the data taken from the form pages and place them inside of the database and tables within the database. I used the INSERT INTO __ VALUES (‘______’)”; piece of code for this to work.
Displaying Table Data Page: I was able to do this by creating a SQL select statement which would allow me to select specific parts of each table and display them in a table within php. Example of the SQL statement I used: Select Grades_ID, Students_ID, Modules_ID, Grades_Result FROM Grades; This statement selecting all four of the above variables from the grades table and displayed them in a table, I proceeded to the same for the other instances where this was required such as modules, programmes, student grades & students.
Remove Pages: The remove pages took the data from the delete pages as that were the user entered the information and then removes the information, they wanted deleted from the database. I done this by using the DELETE FROM ______ WHERE =’$__’”; piece of code.
Search Pages: This page consisted of me searching for student ids that I had already created and searching for the grades they got in their respective modules. This page is a form page but then gets sent to a page called list_student_grades.php to list whatever the user wanted to list.
Style Pages: Lastly, but certainly not least is the style page. I created this page in order to make all of my code look unison no matter where the user goes. I initially started with changing the font family, size, and border to then moving on to creating the flowing gradient background that is now available on my site. I done this by using keyframes and created various positions for the background to move back and forth to all whilst changing colours. I then moved onto creating styling for all code, from inputs to headers to tables to make them look more visually appealing and easier to read.
Below is all of the tables that I created for the programmes, modules, students and grades section that was required in this assignment, I created various integers and varchars that enables the user to input information into my form pages and it then gets passed through to these tables that can be seen below. Additionally, I altered some tables so that some fields would be passed into other tables as foreign keys, this is apparent in the modules table where the programmes_id is a foreign key, the students table where the programmes¬_id is also labelled as a foreign key, and lastly, the grades table where both the modules_id and the students_id are labelled as a foreign key. Foreign keys are great as they avoid operations such as select, delete, update, or alter statements that might break linkages between tables. A field (or group of fields) in one table that refers to the primary key in another table is known as a foreign key. The table with the main key is referred to as the parent table, while the table with the foreign key is referred to as the child table.
Session security in PHP depends on how safe the application you create makes them. PHP sessions provide the user with a pseudorandom string (the "session ID") that they can use to identify themselves, but if the attacker manages to intercept that string, they can impersonate the user.
One way login sessions can be secured is by using HTTPS and TLS, which is a network protocol that creates a secure connection with a verified peer over an untrusted network. It is with this that attackers are prevented from reading any sort of session ID cookie.
The only piece of the session that the user has in their browser is the Session ID in the cookie, and ideally it also means that users cannot view or edit session data locally, as it's all stored on the server.
Modern PHP's session generation is fairly solid as well, making it very difficult for an outside entity to guess a valid session ID, and by using a combination of HTTPS/TLS or even SSL it's nearly impossible for someone to steal Session cookies in-transit.
Below is a snippet of code that I used in my assignment in order to secure login sessions:
It starts off with an if statement which checks if the data from the login form was submitted, isset() will check if the data exists.
I then create four variables by the name of $servername for the name of the server, $username for the username that is required to connect to the MySQL server, a $password variable which holds the password needed, and lastly a $dbname which is put in place to hold the name of the database, in this case it’s called “college”.
Afterwards, I created a new variable by the name of $connection which is equal to a new MySQLi function that features the variables we entered above, and if all of the variables add up to what we entered within the login form, then it will start up and run a new connection to the MySQL Server and give us access tot the server and database itself.
Input validation and parametrized queries with prepared statements are the only effective defences against SQL Injection attacks. The application code shouldn't ever make direct use of the input. Not just web form inputs like login forms must be sanitized by the developer; every input must be done so. Single quotes and other potentially harmful code components must be removed. It's always a good idea to disable the display of database problems if on an openly used website as SQL Injection may be used to learn more about your database by exploiting database faults.
Other steps to preventing SQL Injection would be by initially validating the input by the user, preparing a query that uses parameter names preceded with colons as placeholders, then create a prepared statement, this is to execute the SQL statement with a high efficiency. After this you must bind the parameters to the prepared statement, execute your query, fetch the result, and then validate your application. By doing this you should prevent any and all SQL Injection Vulnerabilities that may affect your database and in doing so you have a safer site that can be used by many.
The requirements were to create a web interface allowing school administrators to perform the following actions stated below.
