CS 1655:Secure Data Management and Web Applications (Spring 2013)
Department of Computer Science,University of Pittsburgh
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 ﬁle (i.e.,enclosed between <entry> and <=entry> tags,corre-
sponds to a single job posting.For every entry in the XML ﬁle,you need to also fetch and parse the
corresponding HTML page (that is speciﬁed in the <link> tag in the XML ﬁle).For example,
link/postings/68003 would correspond to
You should only focus on parsing the following attributes (attribute names are fromSQL database):
id – this is parsed fromthe id node of the XML entry.
type – this is parsed fromthe Job Type ﬁeld of the HTML page.
title – this is parsed from the title node of the XML entry;should be either ”Faculty”
description – this is parsed from the HTML page,from the Position Details ﬁeld
type == Faculty) or fromthe Position Description ﬁeld (if job
type == Staff).
name – this is parsed fromthe name name of the XML entry.
posted – this is parsed fromthe Date Posted ﬁeld of the HTML page.
classiﬁcation – this is only applicable when job
type == Staff and is NULL otherwise,and
is parsed fromthe Job Classification ﬁeld 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 ﬁle 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 ﬁrst 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 ﬁnd all job postings that contain this string
(i.e.,as a substring) in any ﬁeld (i.e.,posting
classiﬁcation).The data should be returned as a simple listing,with one job posting per line,
with the following information for each posting:
id (this should be a hyperlink to the source HTML ﬁle that contains additional infor-
mation about this job)
Note that for brevity we have left out the job
description ﬁeld 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 ﬁle (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 brieﬂy 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 ﬁles 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 ﬁle,as described above.
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 ﬁle to submit,prepare your assignment for uploading,by generating a
single zip ﬁle with all the ﬁles.
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 speciﬁed 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 ﬁle to the appropriate assignment (fromthe drop-down list).
Check (through the web interface) to verify what is the ﬁle 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]