CS 1655: Secure Data Management and Web Applications (Spring 2013)


Nov 18, 2013 (3 years and 4 months ago)


CS 1655:Secure Data Management and Web Applications (Spring 2013)
Department of Computer Science,University of Pittsburgh
Assignment#2:Web-Database Security
Released:April 1st,2013 Due:11:59pm,Saturday,April 20th,2013
To gain familiarity with how to harden web-database applications to withstand sql injection and other
Description You are asked to implement three simple PHP scripts,as follows:
jobimport.php – This should download the following URL:
then parse the XML (e.g.,using the SimpleXML PHP library
) and insert the entries in the database.
Note that each entry in the XML file (i.e.,enclosed between <entry> and <=entry> tags,corre-
sponds to a single job posting.For every entry in the XML file,you need to also fetch and parse the
corresponding HTML page (that is specified in the <link> tag in the XML file).For example,
link/postings/68003 would correspond to
You should only focus on parsing the following attributes (attribute names are fromSQL database):
– posting
id – this is parsed fromthe id node of the XML entry.
– job
type – this is parsed fromthe Job Type field of the HTML page.
– job
title – this is parsed from the title node of the XML entry;should be either ”Faculty”
or ”Staff”.
– job
description – this is parsed from the HTML page,from the Position Details field
(if job
type == Faculty) or fromthe Position Description field (if job
type == Staff).
– dept
name – this is parsed fromthe name name of the XML entry.
– date
posted – this is parsed fromthe Date Posted field of the HTML page.
– job
classification – this is only applicable when job
type == Staff and is NULL otherwise,and
is parsed fromthe Job Classification field of the HTML page.
One important functionality of the jobimport.php script is that it will check if a job posting already
exists in the database (under the assumption that posting
id is unique among all job postings ever
posted) and not insert the same posting again.
For a detailed SQL schema,please refer to:
You are encouraged to use a smaller XML file for testing your scripts:
Note that there is no direct user input for this script,but data is imported fromexternal sources.
jobstats.php – This should utilize the data already stored in the database and produce aggregate statistics as
1.Show the breakdown of Faculty positions per Department as a table,listing the name of each
Department and the total number of Faculty positions posted for each Department in descending
2.Using Google Image Charts
generate a plot of the above list,using a simple barchart.
3.Show the breakdown of Staff positions per Department as a table,listing the name of each
Department and the total number of Staff positions posted for each Department in descending
4.Using Google Image Charts generate a plot of the above list,using a simple barchart.
Note that there is no direct user input for this script.
jobsearch.php – This script should first ask the user for a string up to 50 characters long (using a simple HTML
form) and then utilize the data stored in the database to find all job postings that contain this string
(i.e.,as a substring) in any field (i.e.,posting
classification).The data should be returned as a simple listing,with one job posting per line,
with the following information for each posting:
– posting
id (this should be a hyperlink to the source HTML file that contains additional infor-
mation about this job)
– job
– job
– dept
– date
– job
Note that for brevity we have left out the job
description field from this listing.You should also
display a count of how many matching results were found.
Security Your task is to build the above three scripts in a way so as to minimize the possibility of a suc-
cessful SQL injection attack through your application.So,in addition to testing the functionality of the
scripts we need to evaluate your security measures.For that,you should also submit a text file (called
readme.txt) explaining what you have done in terms of hardening your application against SQL injec-
tion and other types of attacks.Please be thorough in your description of which methods you employed,
and also briefly mention the places in your application that you employed them(but be concise – i.e.,just
describe the corresponding part of your programin high-level and not enumerate the line numbers).
What to submit
You should submit all the files that are necessary for your project to run on http://cs1520.cs.pitt.edu,using
the PHP setup provided for the class and the MySQL database that was assigned to you.You should also
submit the readme.txt file,as described above.
Academic Honesty
The work in this assignment is to be done independently,by you and only you.Discussions with other
students on the assignment should be limited to understanding the statement of the problem.Cheating in
any way,including giving your work to someone else,will result in an F for the course and a report
to the appropriate University authority for further disciplinary action.
How to submit your assignment
We will use a Web-based assignment submission interface.To submit your assignment:
 If you have more than one file to submit,prepare your assignment for uploading,by generating a
single zip file with all the files.
 Go to the class web page http://db.cs.pitt.edu/courses/cs1655/spring2013 and
click the Submit button.
 Use your pittID as the username and the password you specified at the contact information form for
authentication.There is a reminder service via email if you forgot your password.You must have
already submitted your contact information,if you have not yet you need to do so now.
 Upload your assignment file to the appropriate assignment (fromthe drop-down list).
 Check (through the web interface) to verify what is the file size that has been uploaded and make sure
it has been submitted in full.It is your responsibility to make sure the assignment was properly
You must submit your assignment before the due date (11:59pm,Saturday,April 20th,2013) to avoid
getting any late penalty.The timestamp of the electronic submission will determine if you have met the
deadline.There will be no late submissions allowed after 11:59pm,Saturday,April 20th,2013.
[Last updated on April 1,2013 at 11:26pmEST]