JavaDB:Exercise - JDBC-INSERT-UPDATE-SQLInjection

From Juneday education
Jump to: navigation, search

Work in progress

This chapter is a work in progress. Remove this section when the page is production-ready.

Introduction

This exercise will show you how to execute SQL commands which alter the data of a database. It will also show you that you must protect against SQL injections (how to do that is left for a later chapter) because it will give you instructions for how to hack and destroy your own database.

Exercises

1. Setup

Download the files from here or create the directory structure below by yourself.

Create a new directory for this exercise, move the file to that directory and cd down to that directory. These steps are important. You must be in your new directory when you extract the stuff from the file you downloaded above.

Extract the zip file (or create the directories by hand) and verify that you have the directories as:

(you need to enter (cd) the exercise directory)

.
|-- db
|   |-- app
|   `-- main
|-- doc
|   |-- db
|   |   `-- app
|   `-- resources
`-- driver

The db directory should now have the following contents:

db
|-- app
|   |-- DatabaseTest.java
|   |-- DBUtils.java
|   |-- MunicipalityDB.java
|   |-- Municipality.java
|   |-- MyMunicipalities.java
|   `-- package-info.java
`-- main
    `-- package-info.java

We will work with the Java file db/main.Main.java (which you will create yourself) in order to learn how to use the class db.app.MyMunicipalities .

Make sure that you also got the sql-file restore_my_municipalities.sql in the current directory.

Start by creating a database using the sql-file:

$ sqlite3 my_municipalities < restore_my_municipalities.sql

You will also have to put the driver in the driver directory. Download the sqlite JAR file with the JDBC driver and put it in the driver directory.

When you later in this exercise run the application, you will need to put the JAR file on the class path using the -cp flag.

2. Create the db/main/Main.java file

Create the db/main/Main.java file by opening it in a text editor. It doesn’t yet exist, so something like this would work (using cygwin):

$ cygstart notepad++ db/main/Main.java

or (using Atom for text editor):

$ atom.cmd db/main/Main.java

If the editor asks you "The file doesn’t exist. Do you want to create it?" then answer yes.

Start by adding a package declaration to the Main class you are writing now. The package should be the same as the relative path to the file (from current directory) but with a . (dot) between directories, as usual.

Expand using link to the right to see a hint.

package db.main;

Add a declaration of a public class with the name Main .

Expand using link to the right to see a hint.

package db.main;
public class Main{
}

In the class, add a standard main method.

Expand using link to the right to see a hint.

package db.main;
public class Main{
  public static void main(String[] args){
  }
}

In the main method, start by creating a variable of type MunicipalityDB as an instance of MyMunicipalities. You should call the variable db.

Expand using link to the right to see a hint.

package db.main;
public class Main{
  public static void main(String[] args){
    MunicipalityDB db = new MyMunicipalities();
  }
}

Look at the javadoc API documentation provided in the doc directory in order to find out where that class is (so that you can add the import statements needed) and to see the constructors.

Expand using link to the right to see a hint.

package db.main;
import db.app.MunicipalityDB;
import db.app.MyMunicipalities;
public class Main{
  public static void main(String[] args){
    MunicipalityDB db = new MyMunicipalities();
  }
}

Back in main, next thing is to create a new Municipality instance called m . Once again, look at the API provided to find out where the Municipality class is located in order to see if you need any more import statements. Study the constructor in the API and give the Municipality via arguments to the constructor the following:

  • Teststad (as the name)
  • http://teststad.se (as the url)
  • Apache Super server (as the server)
  • false (as the indication of HTTPS support)

Expand using link to the right to see a hint.

package db.main;
import db.app.MunicipalityDB;
import db.app.MyMunicipalities;
import db.app.Municipality;
public class Main{
  public static void main(String[] args){
    MunicipalityDB db = new MyMunicipalities();
    Municipality m
      = new Municipality("Teststad",
                         "http://teststad.se",
                         "Apache Superserver",
                         false);
  }
}

Write a println statement that prints this new municipality to the standard out stream.

Expand using link to the right to see a hint.

package db.main;
import db.app.MunicipalityDB;
import db.app.MyMunicipalities;
import db.app.Municipality;
public class Main{
  public static void main(String[] args){
    MunicipalityDB db = new MyMunicipalities();
    Municipality m
      = new Municipality("Teststad",
                         "http://teststad.se",
                         "Apache Superserver",
                         false);
    System.out.println("m: " + m);
  }
}

Add this municipality to the storage (database) using the instance method addCity() (look at the API to see how this is done) of the db variable.

Expand using link to the right to see a hint.

package db.main;
import db.app.MunicipalityDB;
import db.app.MyMunicipalities;
import db.app.Municipality;
public class Main{
  public static void main(String[] args){
    MunicipalityDB db = new MyMunicipalities();
    Municipality m
      = new Municipality("Teststad",
                         "http://teststad.se",
                         "Apache Superserver",
                         false);
    System.out.println("m: " + m);
    db.addCity(m);
  }
}

Add a println statement that prints the ID of the municipality to the standard out stream.

Expand using link to the right to see a hint.

package db.main;
import db.app.MunicipalityDB;
import db.app.MyMunicipalities;
import db.app.Municipality;
public class Main{
  public static void main(String[] args){
    MunicipalityDB db = new MyMunicipalities();
    Municipality m
      = new Municipality("Teststad",
                         "http://teststad.se",
                         "Apache Superserver",
                         false);
    System.out.println("m: " + m);
    db.addCity(m);
    System.out.println("m.id(): " + m.id());
  }
}

If the ID was something other than 0 then you have succeeded in adding it to the database.

Check using the sqlite3 interactive shell that the city was inserted with the correct values.

Expand using link to the right to see a hint.

$ sqlite3 my_municipalities
sqlite> SELECT * FROM municipalities WHERE name="Teststad";
291|Teststad|http://teststad.se|0|Apache Superserver

Pseudo code for this version of the main method of the class db.main.Main:

MunicipalityDB db = new MyMunicipalities
Municipality m = new Municipality (lots of parameters)
println m
use db to add m to the database
println the id of m ------------ does it print something else than 0?

Remember, pseudo code is not real code - it is just fake code with human language serving as an explanation or a hint for you.

To compile and run this project you may use this command line:

$ javac db/*/*.java && java -cp .:driver/sqlite-jdbc-3.8.11.2.jar db.main.Main

Or if you are on Windows/Cygwin:

$ javac db/*/*.java && java -cp ".;driver/sqlite-jdbc-3.8.11.2.jar" db.main.Main

Note the double quotes and the semicolon needed on Windows - remember, Windows uses a semicolon to separate paths in the class path of Java.

You can restore the database any time between your test runs using the same command line as when you created it the first time.

Expand using link to the right to see a hint.

$ sqlite3 my_municipalities < restore_my_municipalities.sql

Since this exercise adds steps successively, you should restore the database between runs, if you want to start from scratch.

3. Add more stuff to the main method

Add the following (keep working after the last statement of the current version of the main method):

Use the db variable to delete the municipality represented by m from the database.

Expand using link to the right to see a hint.

package db.main;
import db.app.MunicipalityDB;
import db.app.MyMunicipalities;
import db.app.Municipality;
public class Main{
  public static void main(String[] args){
    MunicipalityDB db = new MyMunicipalities();
    Municipality m
      = new Municipality("Teststad",
                         "http://teststad.se",
                         "Apache Superserver",
                         false);
    System.out.println("m: " + m);
    db.addCity(m);
    System.out.println("m.id(): " + m.id());
    db.deleteCity(m);
  }
}

(Use the interactive shell for sqlite3 to verify that it is gone now after you compile and run this version of the program)

Re-assign m to be a new Municipality that you get from asking the db variable to get a municipality from the db by its name (look in the API to find a suitable method) and request the municipality with the same name as the old m was using.

You should not use any constructor to re-assign m, only use the correct instance method that db provides for fetching a municipality by name.

Expand using link to the right to see a hint.

package db.main;
import db.app.MunicipalityDB;
import db.app.MyMunicipalities;
import db.app.Municipality;
public class Main{
  public static void main(String[] args){
    MunicipalityDB db = new MyMunicipalities();
    Municipality m
      = new Municipality("Teststad",
                         "http://teststad.se",
                         "Apache Superserver",
                         false);
    System.out.println("m: " + m);
    db.addCity(m);
    System.out.println("m.id(): " + m.id());
    db.deleteCity(m);
    m = db.getByName("Teststad");
  }
}

Verify that the fetch by name method (the one you were using) returned null, since the municipality previously represented by m was deleted.

Expand using link to the right to see a hint.

package db.main;
import db.app.MunicipalityDB;
import db.app.MyMunicipalities;
import db.app.Municipality;
public class Main{
  public static void main(String[] args){
    MunicipalityDB db = new MyMunicipalities();
    Municipality m
      = new Municipality("Teststad",
                         "http://teststad.se",
                         "Apache Superserver",
                         false);
    System.out.println("m: " + m);
    db.addCity(m);
    System.out.println("m.id(): " + m.id());
    db.deleteCity(m);
    m = db.getByName("Teststad");
    System.out.println("m == null? " + (m==null));
  }
}

Re-assign m to an existing municipality from the database using the same instance method for db. You may choose any city you like. Use the name of the city as argument to the method you are using on the db variable. You may use the SQLite interactive shell in order to find a name of a municipality.

Expand using link to the right to see a hint.

package db.main;
import db.app.MunicipalityDB;
import db.app.MyMunicipalities;
import db.app.Municipality;
public class Main{
  public static void main(String[] args){
    MunicipalityDB db = new MyMunicipalities();
    Municipality m
      = new Municipality("Teststad",
                         "http://teststad.se",
                         "Apache Superserver",
                         false);
    System.out.println("m: " + m);
    db.addCity(m);
    System.out.println("m.id(): " + m.id());
    db.deleteCity(m);
    m = db.getByName("Teststad");
    System.out.println("m == null? " + (m==null));
    m = db.getByName("Bromölla kommun");
  }
}

Print this new representation that m should have to the standard out stream.

Expand using link to the right to see a hint.

package db.main;
import db.app.MunicipalityDB;
import db.app.MyMunicipalities;
import db.app.Municipality;
public class Main{
  public static void main(String[] args){
    MunicipalityDB db = new MyMunicipalities();
    Municipality m
      = new Municipality("Teststad",
                         "http://teststad.se",
                         "Apache Superserver",
                         false);
    System.out.println("m: " + m);
    db.addCity(m);
    System.out.println("m.id(): " + m.id());
    db.deleteCity(m);
    m = db.getByName("Teststad");
    System.out.println("m == null? " + (m==null));
    m = db.getByName("Bromölla kommun");
    System.out.println("m: " + m);
  }
}

Compile and run using the same command line as before.

Verify that your main method worked as expected. If it didn’t try to fix that.

Remember, you can always recreate the database from scratch using the sql file we provided.

4. Read the API documentation and also the source code

Make sure that you can describe what steps are taking place in your main method. What happens when you instantiate the MunicipalityDB (using MyMunicipalities as the concrete implementing class)? Write down the steps and make sure you can explain them to a friend.

Expand using link to the right to see a hint.

Instantiating a MunicipalityDB instance has the following effect:

  • The instance variable DBUtils db is initiated to a new DBUtils instance (if none exist)
    • As the DBUtils class is loaded, its static intitializer is run
      • In the static initializer, the driver from the JAR file is loaded into the JVM using Class.forName("org.sqlite.JDBC");
    • The constructor of DBUtils gets a connection from DriverManager

What happens when you create an instance of a Municipality using the constructor in that class?

Expand using link to the right to see a hint.

The four instance variables name, url, server and https are initialized from the arguments given to the constructor. The database is not involved at all at this stage.

Is there an automatic connection between a Municipality object and the Database?

Expand using link to the right to see a hint.

No, there's no automatic connection between a Municipality instance, and the database. The Municipality class is not aware of any database. You can think of such instances as "plain old Java objects", only holding information about a municipality and providing methods for accessing the values and changing them.

Note, there are many alternatives to such classes - there is absolutely no obligation to provide accessor methods and mutator methods (methods for accessing and changing) every private instance variable in an object of a class. In this exercise we are using such a class only as a carrier of data when dealing with the database.

The design of the classes in this exercise uses the fact that a Municipality object can be used as an argument for the methods accessing the database. Adding a municipality to the database is done by passing a reference to the add() method of the database layer. If the adding is successful, the reference passed as argument is changed - its id variable is set to reflect the ID of the entry in the database.

What happens when you use the db variable to call the various methods you called? Make sure you can explain what happens and in what order.

Expand using link to the right to see a hint.

Some examples below.

Adding a municipality:

  • The parameter is bound - a copy of the reference (address) to the municipality given as argument is assigned to the parameter m
  • m is queried for name, url, server and https which are saved in local variables to build up an insert statement string
  • the db (of type DBUtils) variable is used to execute the insert statement
  • the result of the execution of the insert statement (number of rows inserted) is printed to standard out as a debug information
  • the db variable is used again to query the database for the ID of the municipality with the name of m's name
  • if the select query for the ID worked, m's id is set to this id

Note: An alternative way to get the ID of the recently inserted municipality would be to call this just after inserting: SELECT LAST_INSERT_ROWID();. It can be executed via JDBC.

Getting a Municipality by name:

  • The string given as argument is bound to the parameter name (the address to the string argument is copied to the name parameter)
  • A SELECT string is build using the name argument for selecting everything from municipalities where the name matches the parameter name
  • the db is used to execute the query with the select string
  • if the result set is not empty, the name, url, server and https columns are fetched from the first row found and used as arguments for the constructor of a new Municipality
  • the id of the new municipality is set to the id from the row
  • the reference to the new municipality is returned (or null if the query returned no rows)

5. Exploit the vulnerability in this code

As hinted on the lecture, the code in the database classes is vulnerable for exploits.

As the last exercise, try to destroy the database. That is your job as a software tester, anyway, to try and destroy things (if that is your future career). Not really but almost. Even if you don't aim to become a software tester, it is always good to try and break the things you create and fix them so that they don't break so easily.

Create a String name with the following string value:

'; drop table municipalities;-- (you must start the string with the single quote!)

Use this variable as one of the arguments to the instance method db has that will update the https value for a named municipality and use false as the other argument.

Expand using link to the right to see a hint.

package db.main;
import db.app.MunicipalityDB;
import db.app.MyMunicipalities;
import db.app.Municipality;
public class Main{
  public static void main(String[] args){
    MunicipalityDB db = new MyMunicipalities();
    Municipality m
      = new Municipality("Teststad",
                         "http://teststad.se",
                         "Apache Superserver",
                         false);
    System.out.println("m: " + m);
    db.addCity(m);
    System.out.println("m.id(): " + m.id());
    db.deleteCity(m);
    m = db.getByName("Teststad");
    System.out.println("m == null? " + (m==null));
    m = db.getByName("Bromölla kommun");
    System.out.println("m: " + m);
    String name = "'; drop table municipalities;--";
    int rows = db.updateHTTPSbyName(name, false);
  }
}

Compile and run.

Go to the interactive shell and try to SELECT * FROM municipalities...

Expand using link to the right to see a hint.

sqlite> SELECT * FROM municipalities;
Error: no such table: municipalities

Restore the database from the sql file.

Expand using link to the right to see a hint.

$ sqlite3 my_municipalities < restore_my_municipalities.sql

Now do the same but with this value for the name variable:

' or 1=1;-- (you must start the string with the single quote!)

Comment out the old string and use only this string for the call to updateHTTPSbyName()!

Expand using link to the right to see a hint.

package db.main;
import db.app.MunicipalityDB;
import db.app.MyMunicipalities;
import db.app.Municipality;
public class Main{
  public static void main(String[] args){
    MunicipalityDB db = new MyMunicipalities();
    Municipality m
      = new Municipality("Teststad",
                         "http://teststad.se",
                         "Apache Superserver",
                         false);
    System.out.println("m: " + m);
    db.addCity(m);
    System.out.println("m.id(): " + m.id());
    db.deleteCity(m);
    m = db.getByName("Teststad");
    System.out.println("m == null? " + (m==null));
    m = db.getByName("Bromölla kommun");
    System.out.println("m: " + m);
    //String name = "'; drop table municipalities;--";
    String name = "' or 1=1;--";
    int rows = db.updateHTTPSbyName(name, false);
  }
}

Go to the interactive shell and do:

sqlite> SELECT COUNT(*) FROM municipalities WHERE NOT HTTPS;

What was the result?

Expand using link to the right to see a hint.

sqlite> SELECT COUNT(*) FROM municipalities WHERE NOT HTTPS;
290

What happened in both cases?

Expand using link to the right to see a hint.

You employed what is called an SQL injection attack. This works on unsafe code, typically code which builds up an SQL statement by concatenating strings which have not been checked for bad or malicious data.

The resulting SQL strings which were build up using your malicious arguments to the method were actually:

  • UPDATE municipalities SET HTTPS=0 WHERE name=; drop table municipalities;--'
  • UPDATE municipalities SET HTTPS=0 WHERE name= or 1=1;--'

Try and understand how this happened.

This will be the topic in a future lecture!

Links

Source code

Chapter links

Previous chapter|Next chapter