设计实现一个使用 PostgreSQL
数据库的小型Web服务器。
Preparation create table users(
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 (schema public):
create table users(
name varchar(20) not null,
pwd varchar(20) not null,
permission 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 app with password foobar, and grant it access
with the query:
CREATE USER “app” WITH PASSWORD ‘foobar’;
GRANT USAGE ON SCHEMA public TO “app”;
GRANT SELECT ON TABLE users TO “app”;
—|—
The program generating the page is written in python, and requires some setup.
- 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. - 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.
Tasks
- 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.
- (b) Design another string that, when entered into the password field, will cause the webpage to return the admin password instead of permissions. Tip: Remember the UNION operator.
- (c) Modify login.py to prevent SQL injection attacks. Tip: Check the psycopg2 documentation.
- Consider table reservations below, storing restaurant bookings.
CREATE TABLE reservations(
customer varchar(20) NOT NULL,
res_date data 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.
- (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.
- (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. 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.
- 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.
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 [input type=”date” …] 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.
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
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
