FIT2094-FIT3171 Databases
Session 12 Tutorial Activities DB Connectivity, Web Technology
FIT Database Teaching Team
Complete session 12 activities in week 6 listed below
12.1 DB Connectivity using PHP
12.2 Frameworks and Security Consideration 12.3 Web Modification Exercise
12.4 SETU and Assignment 2
FIT2094-FIT3171 2021 Summer B
FIT2094-FIT3171 Databases
Author: FIT Database Teaching Team
License: Copyright © Monash University, unless otherwise stated. All Rights Reserved.
COPYRIGHT WARNING
Warning
This material is protected by copyright. For use within Monash University only. NOT FOR RESALE. Do not remove this notice.
Page 1 of 8

12.1 DB Connectivity using PHP
Now that you are familiar with designing, creating and managing tables we will look at the manner in which such data can be accessed. To date you have been using SQL Developer, clearly, in practice, your users will not have access to/use this item of software. Access to your created tables by normal users will be via an application or web front end.
For this tutorial, we are going to develop a basic web front end. To do this we are going to make use of ​PHP​ (recursive acronym for PHP: Hypertext Preprocessor) one of the most ​widely used programming languages​, especially for web development. Please note that this is a very basic introduction so that you become aware of the possibilities. PHP is a full OO language with a wide range of features. Development under PHP is commercially carried out via ​PHP Frameworks​.
PHP enables the mixing of PHP code (marked between ) and standard HTML code within a single file. When accessed via the web server the PHP code is handled via the PHP processor on the web server and replaced with appropriate output. For this unit we are not expecting you to become PHP experts, this is simply an exercise to increase your awareness of how a database can be accessed via the web. If you wish to delve further into PHP immediately there are a large number of good tutorials available on the web. A good starting point is https://www.w3schools.com/php/
The steps in using PHP to access table data are:
● connect to the database
● define a SQL query string
● parse the SQL query against the database
● execute the statement
● fetch and display the data, and finally
● free the resources being used and close the connection.
You have been given an account on the server ​http://fit-db.infotech.monash.edu/​ – this server can be accessed in two ways:
● via the web by using a URL of the form: http://fit-db.infotech.monash.edu/~yourauthcateusername/ or
● via SFTP for transferring files to the server.
Note that this server can only be accessed within the Monash network or via ​Monash VPN​.
To create a connection to the server open an SFTP client such as ​FileZilla​. With FileZilla select Open the Site Manager:
and pick ​SFTP​ as access type (​Protocol​) and enter the server name (​Host​):
Page 2 of 8

Before selecting “Connect”, click on the NewSite under My Sites on the left and give your connection an appropriate name (here DB Infotech). Also please ensure you use the “Logon Type” “Ask for password” so that FileZilla will prompt you for your password at each connection and not remember it. Use your ​Monash username and password​ and click “Connect”.
After clicking on “Connect”, your connection will open and FileZilla will show your local files on the left and the server (remote) files on the right. Navigate using the right until you are located in your public_html folder. Documents placed in this folder will be published via the web server running on http://fit-db.infotech.monash.edu/​.
To create the PHP files we will need you should make use of a text editor such as ​Atom​ or Notepad++​ (MS Windows ) or ​Brackets
The first PHP script we wish to create is a form which will ask you to input your Oracle connection details and call the page which will display the data from the database. The file should be called login_uni_student.php​ and have the form:

Login to your Oracle Account

Now we wish to create the actual PHP script which will access and display the student data in the student table of the UNIVERSITY database that you used in previous sessions’ tutorials.
The following code contains all the required material to carry out a select of the student table and display the results in an ​HTML table​. You should look through the code and understand the details of what has been coded:
Page 3 of 8

Student list UNIVERSITY database

” ; print $e[‘message’] ;
exit;
} ?>

” ; print $e[‘message’] ;
exit;
}
// oci_define_by_name maps SQL Columns in a queryto PHP variable names // MUST be done before executing, Oracle names must be in UPPER case oci_define_by_name($stmt,”STUDID”,$studid); oci_define_by_name($stmt,”SNAME”,$stuname); oci_define_by_name($stmt,”SBDATE”,$stubdate);
Page 4 of 8

oci_define_by_name($stmt,”STUDEMAIL”,$stuemail);
// Execute the STATEMENT $r = oci_execute($stmt); if (!$r) {
$e = oci_error($stmt);
print “Error execute of statement:
” ; print $e[‘message’] ;
exit;
}
// Fetch the results of the query while (oci_fetch($stmt)) {
print(”

“); }
// Close table print (“

“);
// Show the number of rows
$no_rows = oci_num_rows($stmt);
print “

Rows found:” . $no_rows . “

“;
// Free resources associated with Oracle statement oci_free_statement($stmt);
// Close the Oracle connection
oci_close($conn);
?>

After you have checked through this code, copy and paste the code into a new text document called ​disp_uni_student.php​ saved in the same location as your login_uni_student.php file.
We now wish to transfer both of these files to the remote server into a folder below public_html called uni. Right-click in your remote public_html folder and create a ​uni​ folder (select “Create directory and enter it” in Filezilla):
Page 5 of 8

Finally, locate your login_uni_student.php and disp_uni_student.php files in your local site file browser and transfer them to your remote site uni folder via upload:
Well done, you are now ready to examine your work. Go to the URL: http://fit-db.infotech.monash.edu/~yourauthcateusername/uni/login_uni_student.php
(replace yourauthcateusername with your actual username), and you should see something like:
Insert your Oracle username and password then click submit and it will redirect you to disp_uni_student.php and you should see something like:
Page 6 of 8

12.2 Frameworks and Security Consideration
Nowadays, the use of frameworks is quite popular to develop entire interfaces and web applications (e.g. a Customer-Relationship Management / CRM app, using a database backend).
1. Name some other popular web frameworks that can utilise Oracle as a database back-end.
2. A common programming technique used in many frameworks (including Oracle) is Object-Relational Mapping (ORM). Briefly describe what it means.
SQL Injection is a very common vulnerability when it comes to building web frontends (e.g. in PHP) for databases. To understand how serious the issue is, let’s assume you have a website which lets you enter a first name as a search query:
The website then uses your search string (e.g. “Peter”) and places it in a SQL SELECT statement so that it can show you results, using the following SQL query.
(Your search string is highlighted).
SELECT * FROM users WHERE first_name = ‘​Peter​’;
Aside from above example, you can also use this site to learn more about SQL injection:
https://www.w3schools.com/sql/sql_injection.asp
1. Discuss what SQL Injection means. In the example above, how can a malicious user craft a special search string in order to, say, view everything in another table they’re not supposed to view?
2. How can you, as a Database Developer, prevent these from happening to your own database apps?
12.3 FIT2094 Web Modification Exercise
Download session12_phpfiles.zip from Moodle. The zip file contains two files: login_uni_scheduled_class.php, and disp_uni_scheduled_class.php. These php files aim to display all lectures scheduled in semester 1 2020, when the unit detail is clicked, the information of the unit’s lecturer will be displayed in a pop up window. The expected output is something like:
Page 7 of 8

and when the unit name is clicked the pop up window appears like:
You should look through the code and understand the details of what has been coded, then complete the $query part with an SQL select statement. Check if your web page works and displays correct data by uploading the files onto fit-db.infotech.monash.edu server.
12.4 SETU and Assignment 2
Spend the rest of the time of this tutorial to fill up the SETU and continue working on your Assignment 2.
Page 8 of 8