This short exercise explores a simple example of how SQL injection can occur in database-driven web applications.

Preparation

1. For context, read the BBC News articles TalkTalk cyber-attack and Questions for TalkTalk before proceeding further.

2. Create a directory for this worksheet, then download ss.zip into it and unzip the file. Don’t examine any of the code yet; that will come later.

Exploring The Vulnerability

1. 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.

2. 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 flask run 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.

3. With the vulnerable application running, visit http://localhost:5000 in a web browser. You should see a login page.

   

4. 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 password abcd1234. Log in with these credentials, to see how the site behaves under normal circumstances, then log out again.

5. 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.

6. 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 SQL 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!

   

Fixing The Vulnerability

1. Copy 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 injection.

   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!

2. 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.)

3. Repeat the steps you performed when exploring the vulnerability in ss.py. You should find that SQL injection is no longer possible.

□