1 What is code injection?

Suppose our program reads data from an untrusted source. Our program builds a command to run incorporating this data. The command could be an SQL command (“SQL injection”) or could also be any other interpreted command. Classic examples include (Unix) shell scripting.

2 Example — shell script

A web server can run CGI scripts. The web server passes the environment variable QUERY_STRING. The query string is the part after the ?

https://example.com/testme?i_am_the_query_string

If the web server is configured to run this Bash script for “testme”

#!/bin/bash
echo ${QUERY_STRING}

The intention is this script just writes back the text given to it…

i am the query string

but if someone sends the string

; rm -rf /home/*

then “bad things” happen because the command that is run is

echo ; rm -rf /home/*

• echo should just output its arguments…
• ; is the command separator in (most) Unix shells, including “bash”. This means there are no arguments to “echo” so it will just print a blank line
• The next argument is rm -rf /home/* which will try to delete all the home directories….

3 Example – SQL injection

OWASP has lots of excellent resources relating to injection attacks (last checked August 2021).

4 Practical SQL injection example with PHP and SQLite

This example allows you to experiment with an SQL database and the effects of SQL injection. We use SQlite as the database as it limits the potential harm to a single file.

The example application is a visitor book. The user should be able to view current entries, and add entries: their name, a comment, and it should be automatically stamped with the date/time.

We will

• create two tables
  • visitors — storing the visitor data
  • sekrets — some dummy date that we wish to keep both confidential and prevent its destruction
  • two PHP scripts that imitate a (poorly written) application

Requirements:

• PHP
• SQlite3

This has been tested on a Debian Linux computer (PHP 7.3) but should work on other similar computers.

1. Create the tables. In the same directory as the index.html file and the PHP scripts, run the following sqlite3 command:

   sqlite3 example.db ".read createtables.sql"

2. In one terminal window, run the built-in PHP server:

   php -S localhost:8000
   

3. Using your web browser, navigate to http://localhost:8000. This should bring up a menu offering a “view” link and a form to fill in.

4. Examine our first PHP script, viewbook.php. Click the link in the menu to check you’re content with how it works — it should simply dump out the database.

5. You can also access the database by running sqlite3.

   sqlite3 example.db
   select * from visitors;

6. Now look at updatebook.php. This takes two fields, a name and a comment.

7. Fill in the form (with something simple, like “Alice” and “this is a comment”. Click submit, then go back and view the visitor’s book again. You should see your new entry.

8. Now a malicious user arrives. Note that the previous query (shown when submitting the form) looked like

   INSERT INTO visitors VALUES (datetime('now'), 'Alice', 'This is a comment');

9. The update script doesn’t produce any output from the database (we’ll extract some data shortly!) but we can vandalise it. We want the SQL command to look something like

   INSERT INTO visitors VALUES (datetime('now'), 'Evil', 'Trash sekrets'); DROP TABLE sekrets;

10. So we update visitor comment from What can I do to this demo? to

    Trash sekrets'); DROP TABLE sekrets;

11. You may see an error in the PHP server, as the SQL interpreter sees a trailing '); after our DROP TABLE instruction. However, running sqlite3 and asking for the list of tables will show that our table of valuable secrets has gone!

    sqlite3 example.db
    .tables

12. Reset the database by deleting example.db and re-running the creation step.

13. Our attacker would now like to access the secrets rather than simply vandalise them. The updatebook script doesn’t read and display the visitor book and the viewbook script doesn’t update it. But we can make the updatebook script dump the secrets into the visitor book then use the viewbook script to read them. We want to persuade the updatebook script to run something like this:

14. Try out latest evil comment and then view the visitor’s book:

    ' || (select group_concat(valuable) from sekrets));

15. …and we can now see the secrets in the visitor’s book.

5 How should we fix this?

The root cause here is trusting data from an untrustworthy source.

Blacklisting aka deny-listing or block-listing is where we drop or sanitise characters from the user input, e.g., we delete or escape characters such as ' which we have used to insert the attack commands. Whitelisting aka allow-listing instead says, “these characters are safe” and allows only those. Both approaches can have issues, such as how to safely process special characters that you do want to store in your database.

Prepared statements are a good method for SQL.

6 Exercise: prepared statements and fixing updatebook.php

See the PHP SQLite3 document describing the prepare statement. Modify the updatebook.php script to block the attacks demonstrated in the inputs data2.dat and data3.dat.

7 Additional exercise

• Modify viewbook.php to take an argument in the query string. Use this argument to search for items in the visitor book. Then exploit it to modify/extract data. Try to extract the contents of the sekrets table without using the updatebook script.

8 Running on MS Windows 10 with PowerShell

I strongly recommend using Debian Linux (or a derivative)… as apt install php-cgi php-sqlite3 sqlite3 is easy… but if you must use Windows:

• Tested with PHP 8 (downloaded from https://windows.php.net/) and SQLite3 (downloaded from https://sqlite.org/download.html)

• Set a PHPRC environment variable, e.g., $env:PHPRC = “c:.ini”

• Ensure that php.ini file contains

  [ExtensionList]
  extension_dir = "c:\users\myname\desktop\phpdir\ext"
  extension=php_sqlite3.dll

9 Files for this worksheet

• In the directory “files”
  • createtables.sql
  • index.html
  • viewbook.php
  • updatebook.php
• (For staff)
  • updatebookfixed.php

10 Questions?

If you have questions, comments, etc., then email Phil at p.brooke@northumbria.ac.uk — I check this account most work days.

This work is licensed under a Creative Commons Attribution-NonCommercial 4.0 International License.