158.247 Database Design
Assignment 2
Preparation
In this assignment you will run a small webserver with a web page that queries a local postgres database. To begin
with, create the following table in your local database 158.247 (schema public):
create table users(
name varchar(20) not null
, pwd varchar(20) not null
, permissions varchar(20)
, constraint pk_users primary key (name)
);
insert into users values (’johndoe’, ’pa55word’, ’read’);
insert into users values (’admin’, ’hArd2gu3ss’, ’read,write’);
insert into users values (’janedoe’, ’top5ecret’, ’read’);
Next create a new login role 158.247-app with password foobar, and grant it access with the query:
CREATE USER "158.247-app" WITH PASSWORD ’foobar’;
GRANT USAGE ON SCHEMA public TO "158.247-app";
GRANT SELECT ON TABLE users TO "158.247-app";
The program generating the page is written in python, and requires some setup.
1) If you don’t have python installed yet, do that first. See https://www.python.org/downloads/.
Windows users should chose python 2.7 (for ease of installing a suitable C++ compiler later), and may need to
add the python directories (C:\Python27;C:\Python27\Scripts or similar) to their system path manually.
2) Install the python module psycopg2 for connecting to postgres, by running one of
> python -m pip install psycopg2-binary
> python3 -m pip install psycopg2-binary
Note that packages for python 2 and 3 are installed separately. You’ll need psycopg2 installed for the version you
are running login.py with, which is python 3 by default (configured at the top of login.py). More detailed
installation instructions by OS can be found at the end of this document. A documentation of the module can
be found at http://initd.org/psycopg/docs/.
On stream you will find two files available for download: login.html and login.py. Place login.html into
a directory of your choice (in the following it will be assumed that this is your working directory). Then create a
subdirectory cgi-bin and place login.py in there.
Finally you need to setup a web server to serve your script. Python already includes an HTTP server module,
which can be used for this task by running one of the following commands (in the directory where login.html is
located), for python 2 or 3 respectively:
> python -m CGIHTTPServer 8247
> python -m http.server --cgi 8247
> python3 -m http.server --cgi 8247
Python’s http server will try to execute any script it finds in cgi-bin and send its output to port 8247. Linux and
OS/X users need to ensure login.py has execution permission. This can be done with the command
> chmod a+x login.py
If you now point your browser at http://localhost:8247/login.html, you should see a login page. When
you enter a username and password, request will be handled by login.py which checks the course database for a
matching user name and password, and grants or denies access accordingly.
1
Tasks
1. You are to first run SQL injection attacks on the given code, then modify it to prevent them. When designing
your attacks, don’t assume that you know anyone’s password.
(a) Design a string that, when entered into the password field, will grant you access, regardless of what name
is entered. Do not modify login.html or login.py for this. [2 marks]
(b) Design another string that, when entered into the password field, will cause the webpage to return the
admin password instead of permissions. [2 marks]
Tip: Remember the UNION operator.
(c) Modify login.py to prevent SQL injection attacks. [2 marks]
Tip: Check the psycopg2 documentation.
2. Consider table reservations below, storing restaurant bookings.
CREATE TABLE reservations(
customer varchar(20) NOT NULL
, res_date date NOT NULL
, res_start time NOT NULL
, res_end time NOT NULL
, CONSTRAINT pk_reservations PRIMARY KEY (customer, res_date)
);
Some sample data for testing purposes is given below:
INSERT INTO reservations VALUES
(’Alice’, ’2020-04-01’, ’11:00’, ’16:00’)
, (’Bob’, ’2020-04-01’, ’11:00’, ’12:15’)
, (’Eve’, ’2020-04-01’, ’12:15’, ’15:00’)
, (’Jim’, ’2020-04-01’, ’11:00’, ’13:00’)
, (’Joe’, ’2020-04-01’, ’14:00’, ’15:30’)
, (’Ron’, ’2020-04-02’, ’13:00’, ’15:00’)
(a) Opening hours are from 8am to 10pm each day. Add a constraint to ensure that reservation times are
sensible. [2 marks]
(b) Create a database function res count which takes as input a date and time and returns the number
of reservations active at the given date and time. Include reservations starting at the given time, but
exclude those ending at it. [3 marks]
(c) Due to Covid-19 restrictions, at most 3 reservations may be active at any point in time. Create a view
fully booked which lists all maximal time periods (res date, res start, res end) during which no further
reservations are possible. [7 marks]
Hint: Identify for each start time where maximal permitted reservations are reached the minimal end
time at which they drop below that number again. Then eliminate non-maximal intervals.
3. Create a website which lets a user enter a day range for making a reservation, then displays a list of all time
periods where the maximal number of permitted reservations has already been reached. The user should then
be able to enter reservation details to make a booking. When storing this booking in the database, you must
ensure that the limit of 3 concurrent reservations is not exceeded. [12 marks]
Feel free to use login.html and login.py as starting points, but rename them to reservation.html and
reservation.py to avoid confusion when marking. For processing reservation requests, create a new file
reservation insert.py.
Tip: Use in reservation.html for entering start and end dates. To format the
query result for display, you can use an html table.
Submit your source code, web page and answers (the “special password strings”) via stream.
Include your name and student ID.
2
Detailed Setup Instructions
Linux (Ubuntu)
Python should already be installed. The module psycopg2 requires libpq-dev to be installed first:
> sudo apt-get install libpq-dev
Afterwards installation proceeds using the following command:
> sudo python3 -m pip install psycopg2-binary
Note: The login.py script uses the python3 interpreter by default.
Windows
For installing python and pip (which comes with python since version 2.7.9) see
http://docs.python-guide.org/en/latest/starting/install/win/
Installing the psycopg2 module:
• Install a VisualC++ compiler for python, available at http://aka.ms/vcpython27.
• Now the module can be installed by running one of
> python -m pip install psycopg2-binary
> python3 -m pip install psycopg2-binary
If you’re unsure where to enter these commands, search for command line, command prompt or terminal and
educate yourself a little. The > symbol is not part of the commands.
OS/X
OS/X offers a nice package manager and development environment (similar to linux), but not out of the box. Hence,
we begin by installing XCode, which provides compilers and libraries needed for any serious coding as well as a
development environment, and homebrew, a package manager for easy installs.
• Install XCode, available here: https://developer.apple.com/xcode/download/
Note: It’s a large download (several GB). You can try to skip this step as I’m not 100% certain it’s needed
for the following steps, but chances are you’ll want it anyway in the long run.
• Install homebrew, available here: http://brew.sh.
• Install Python – do this even if your system already has it installed, as by default essential tools such as pip
(python package installer) are not included (if the command pip works, you can skip this step):
> brew install python
• Install the psycopg2 module:
> python3 -m pip install psycopg2-binary
3
Troubleshooting
Below are some potential fixes for errors you might encounter:
• Python.h: No such file or directory – install the python-dev package.
• ”POST /cgi-bin/login.py HTTP/1.1” 200 - env: python3: No such file or directory – change the first line in
login.py from #!/usr/bin/env python3 to #!/usr/bin/env python.
• Symbol not found: PyCodecInfo GetIncrementalDecoder – close and re-open the terminal.
• ”POST /cgi-bin/login.py HTTP/1.1” 200 -: No such file or directory (under linux or OS/X) – you may have
accidentally introduced windows line-endings (\r) or other funny characters. A tool like dos2unix can fix this.
Other common errors which probably mean you haven’t been following instructions:
• localhost refused to connect (in browser) – you forgot to start the web server
• ”GET /login.html? HTTP/1.1” 404 – start the web server in the directory where login.html is located
• login.py is displayed in the browser, not executed – don’t open login.html by double-clicking it (this will open
it as a file), your browser bar should read http://localhost:8247/login.html and http://localhost:
8247/cgi-bin/login.py respectively.
• Import Error: No module named psycopg2 – psycopg2 hasn’t been installed correctly. You can see installed
packages with the command:
> python3 -m pip list
• psycopg2.ProgrammingError: relation ”users” does not exist – You didn’t create the table or forgot to grant
schema access to the newly created user