This short exercise explores a simple example of how SQL injection can occur in database-driven web applications.
For context, read the BBC News articles TalkTalk cyber-attack and Questions for TalkTalk before proceeding further.
Create a directory for this worksheet, then download
into it and unzip the file. Don’t examine any of the code yet; that
will come later.
To run the application, you will need to use Python 3, in an environment where the Flask web microframework is available. Installing the Anaconda distribution on your PC will give you a Python that includes Flask, along with hundreds of other useful packages. You can also enable access to Anaconda Python on SoC Linux machines like so:
module load legacy-eng module add anaconda3/2020.11
Note that this grants access for the current terminal session only.
Once you have set up the right Python environment, enter the following commands to configure and run the application:
export FLASK_APP=ss.py export FLASK_DEBUG=1 flask run
Note that you only need to enter the two
export commands once in any
given terminal session; thereafter, you will only need to do
to rerun the application.
Note also that the
export commands above are for the command shells
provided by Linux, macOS or WSL. In a standard Windows command shell you
will need to use
set in place of
export. In Windows Powershell,
you will need to use
$env:FLASK_APP = "ss.py" and
$env:FLASK_DEBUG = 1.
With the vulnerable application running, visit
in a web browser. You should see a login page.
Imagine you are one of the account holders of this service, Mr Andrew
Smith. Mr Smith has the username
aps and the sadly all-too-plausible
abcd1234. Log in with these credentials, to see how the
site behaves under normal circumstances, then log out again.
Now see if you can log in as Mr Smith without supplying his password. Try this without referencing the application source code initially. Think about the type of query the application might do to check that a valid username and password have been supplied, and about how you might prevent the password from being involved in the query.
Your input will need to include the username,
aps, plus additional
characters. You’ll find that the quote mark and the SQL comment symbol,
--, are particularly useful here.
If you have trouble figuring this out, open
ss.py in a text editor and
look at the code that constructs the query. Try writing out the string of
SQL that is constructed for various inputs supplied to the login page.
It would be even more convenient for the attacker if they didn’t need
to know a valid username to mount their attack. See if you can use an
OR clause to modify the query further so that the choice of
username becomes irrelevant.
If you have trouble figuring this out and haven’t already examined the
the code in
ss.py, do so now. Try writing out the string of SQL that is
constructed from the input supplied to the login page. This should help
you to see where the
OR should go.
Due to the particularly sloppy way in which this application has been implemented, you’ll get the added bonus of seeing all account details if you manage to modify the query successfully!
ss.py to a new file,
safe_ss.py. Edit this new file and modify
the application so that it is safe and no longer vulnerable to SQL
The application interfaces with its SQLite database using a module from the Python standard library. Locate the module documentation and use it to help you make the appropriate modifications. You should find that only two lines of application code need to be altered!
If the old version of the site is still running in your terminal window,
kill the server with
Ctrl+C. Then run the new version with
export FLASK_APP=safe_ss.py flask run
(On Windows, alter the
export command as noted previously.)
Repeat the steps you performed when exploring the vulnerability in
You should find that SQL injection is no longer possible.