JavaDB:Assignment - SQL injection

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.

Some notes

This assignment was originally part of a course on databases on an introductory level. The course focused on SQL and SQLite but included some JDBC for completion. The database used here is used a lot in the book Introduction to Databases and is a database with a table with information on Swedish municipalities. The table municipalities contains columns with information on a municipality. The name of the municipality, the URL to the municipality web site, whether the municipality supports HTTPS on its web site etc. In case you get confused by the database, please check out the book mentioned above to see the database explained in more detail. In particular the chapter on scraping data from the web shows where the data originally came from and how it was gathered.

Goal of the assignment

The goal of this assignment is for you to practice and verify that you can use the basics of the Java JDBC API for connecting to a database from Java, to execute SQL queries from Java and to update the database from Java.

Concepts included in the assignment

  • Using third party APIs (JAR files from third party supplier)
  • Using return values from methods and create alternative paths based on the values
  • Modifying an existing API (classes provided by the teachers) to support COMMIT/ROLLBACK
  • Executing a COMMIT or ROLLBACK based on return values from method calls
  • Using command line arguments in a Java application, understanding the use of quotation around arguments

Files to be handed in when submitting your assignment

  • test-runs.txt - A file with your command lines for running the application and the output from the application
  • name.txt - a text file containing your name on a single line
  • - The main class you'll be working with during the assignment
  • - a file provided by the teachers which you will modify during the assignment
  • - another file provided by the teachers for your modification
  • - another file provided by the teachers for your modification

The files must be placed in a directory called answer-handin03 (for historical reasons), and this directory should be included in the zip file your are submitting.

Create the zip file (the only file your are submitting) so that it includes all the files above in the directory.

The zip file will contain six files. Before submitting the file, verify that it includes the files:

$ unzip -l 
  Length      Date    Time    Name
---------  ---------- -----   ----
        0  2016-02-20 19:43   answer-handin03/
        0  2016-02-20 19:43   answer-handin03/
        0  2016-02-20 19:43   answer-handin03/
        0  2016-02-20 19:43   answer-handin03/name.txt
        0  2016-02-20 19:43   answer-handin03/test-runs.txt
        0  2016-02-20 19:43   answer-handin03/
        0  2016-02-20 19:43   answer-handin03/
---------                     -------
        0                     7 files

Note that length will be something other than 0, since the above is just a dummy example output.

The tasks

Task 1. Download the archive with the files and set up your environment

Download the archive (you will need to use a browser for this) and unpack it in a fresh empty directory. Create the database my_municipalities using the SQL file included in the archive.

All the source files is in this directory on github. You can clone the whole javadb repository if you know how to use git, and copy the directory assignment-sql-injection as an alternative approach:

$ mkdir javadb-from-git
$ cd javadb-from-git
$ git clone
$ cp -a javadb/assignment-sql-injection [to where you want the directory]

Still having download problems? We have a direct link for you:

Here's the driver (change the name to sqlite-jdbc.jar after downloading) file here

Task 2. Confirm that the environment works

Compile the files and run the Main class. Study the output and verify that the JDBC connection to the database works.

Enter the database interactively and investigate what tables exist, how they are defined and check that the tables aren't empty.

Verify that you can delete rows from some table, and exit the interactive shell and recreate the database from the SQL file again.

To compile the application you can do the following:

$ javac */*/*.java && java -cp .:driver/sqlite-jdbc-3.15.1.jar db.main.Main
# Windows/Cygwin:
$ javac */*/*.java && java -cp ".;driver/sqlite-jdbc-3.15.1.jar" db.main.Main

Some tasks (below) requires running the application with arguments. If you need to refresh using arguments with Java, see tips and tricks at the end.

Task 3. Add three abstract methods to the interface MunicipalityDB

Open the interface in your editor and add three abstract methods:

  • setAutoCommit (return type: void, paramters: autoCommit (of type boolean))
  • commit (return type: void, parameters: none)
  • rollback (return type: void, parameters: none)

Recompile the whole application. The compiler will complain that MyMunicipalities doesn't implement all methods in the interface MunicipalityDB. Don't worry about that. It's normal, since you just added three methods to it. In the next task, we'll fix that.

Task 4. Implement the methods in MyMunicipalities

Implement the methods from the previous task, in the concrete class Since the methods have "return type" void, you can leave them empty - that is no code in the bodies of the methods (between { and } ).

Compile the application again to verify that the compiler now is satisfied (that you were able to implement the new methods).

The reason for leaving the method bodies empty is to verify that the compilation chain works. You can now relax and work with the actual code for the methods one by one. It is not unusual to work like this. Fix one problem at the time.

Task 5. Modify DBUtils and MyMunicipalities

Copy the same empty methods from MyMunicipalities to the class DBUtils.

The class MyMunicipalities holds a reference to a DBUtils instance, as an instance variable. When we implement the methods from above, we'll use the DBUtils reference as an abstraction. The idea is to put the low-level database stuff in DBUtils to keep MyMunicipalities a little simpler and cleaner.

Edit and implement the still empty methods like this:

  • setAutoCommit

Use the parameter and forward it to the setAutoCommit instance method of the db instance variable, calling the method with the same name. That is, you should use db in the method body, and call setAutoCommit() and use the parameter autoCommit as the argument.

  • commit

Use db and call commit() without any arguments.

  • rollback

Use db and call rollback() without any arguments.

Recompile the application to verify that the recent small changes worked. If you get any compilation errors, fix them.

Task 6. Implement the methods in DBUtils

Now it's time to do the actual work for the new methods. Edit and use the following code for setAutoCommit:

  }catch(SQLException sqle){
    System.err.println("Error setting autocommit to " + autoCommit);

Try to understand what the code does and how it works. Note that autoCommit is the boolean parameter to the method.

For the method commit(), you can use the following code:

  // Commit only works if autocommit is false
}catch(SQLException sqle){
  System.err.print("Error issuing commit: ");

And, finally, for rollback(), you can use the following code:

    System.out.println("Issuing rollback");
}catch(SQLException sqle){
  System.err.print("Error issuing rollback: ");

Compile the application and fix compilation errors, if you get any.

Task 7. Use the new methods in MyMunicipalities from Main

In the class db.main.Main, create a local String variable in the main method, and call it name.

Assign name the value from args[0] (which is the first argument to the main method, from the command line). It's good practice to first make sure that args isn't empty (i.e. to make sure that there were arguments). We don't want to get an ArrayIndexOutOfBoundsException when trying to access args[0]! See tips and tricks below, if you need to see some code for inspiration.

Create an int variable called numRows in main. Use the MyMunicipalities reference db to call updateHTTPSbyName() with the name variable as the first argument, and false as the second argument. Assign numRows the result of this code.

Some explanations are in place. We are trying to update a municipality in the database to have HTTPS support set to false. We are using the name we got as argument to the application, to tell the method which municipality to update. This is of course a little unsafe, since we don't have any clue what value the name variable has. The method updateHTTPSbyName() method takes two arguments, the name of the municipality and the new value for the HTTPS column. The method returns an int representing how many rows were updated.

Pseudo code for the above would be:

numRows = put the method call here;

In order to make sure that only one municipality in the database was updated, we'll look at the return value from the method. Use an if-statement which does something like this:

if numRows == 1, then print somthing to standard out and call commit on the db variable, otherwise, print an information message to standard out which explains how many rows were updated and call rollback() on the db variable.

NOTE: You must call the setAutoCommit(false) method on your db variable for commit and rollback to work (as mentioned in the lecture ;-) ).

Recompile the application.

Now run the application and use an argument which is an SQL injection which will trick the main method to update all municipalities rather than just one municipality as intended.

In order to figure out how to formulate such an argument, please refer to the chapter on SQL injections.

Note, that in order to give the SQL injection string as the single argument, you need to enclose it in quotation marks. See tips and tricks at the end for some examples.

Verify that your code detects that too many rows were updated and does a rollback.

Next, run the application again with one argument of the name of some municipality in the database which has HTTPS with the value of "1" in the database. Remember to use quotation marks around the municipality name if it contains a blank (consists of two words). Otherwise, the JVM will consider the first part of the name as one argument and the next part of the name as the next argument.

Verify both that your code detects that this was a valid name (and that only one municipality was updated) and that it does a commit. Go into the interactive shell for the database and verify that the municipality actually got its HTTP value changed from 1 to 0.

Create the file test-runs.txt and paste the command lines and printouts from your test runs. You should have at least two test runs in the file, one with the SQL injection, and one with a valid municipality name. It should be obvious from the printouts that your code detects the SQL injection and does a rollback, and that it accepts the valid name.

Put the file in the directory you will zip and submit.

Put the Java source code files you have modified in the directory too.

Don't forget the name.txt with your name in it.

Tips and tricks

The zip file

In order to zip the directory answer-handin03 and all the (6) files in it, cd to the directory which contains answer-handin03.

Confirm that the six files are in the directory.

$ ls answer-handin03  name.txt  test-runs.txt

When you have confirmed that the files are there and have the exact names as listed above (even with the correct character case), create the actual zip file:

$ zip -r answer-handin03

Giving arguments to a Java application

$ javac example/ && java example.Args "Hello arg" "next arg" "one more"
  • Argument number 0 is: Hello arg
  • Argument number 1 is: next arg
  • Argument number 2 is: one more

The example application:

$ cat example/ 
package example;
public class Args{
  public static void main(String[] args){
    if(args.length != 0){ // otherwise, args is empty!
      int argNum=0;
      for(String arg : args){
        System.out.println("Argument number " + (argNum++) +
                           " is: " + arg); 
      }// end for
    }// end if
  }// end main
}// end class

From the Java application, in order to use the arguments, you use:

String arg = args[0]; // First argument goes to args[0]
String next= args[1]; // Next argument goes to args[1] etc
int numberOfarguments = args.length;

Note that the example program above would crash if you don't give it two arguments, since the args array must have at least two elements, or you'll get an array index out of bounds exception.

Database and development tips

When you test run a program which writes to the database (or updates it), remember to reset the database between the runs, if you need a fresh start. Otherwise you will run your application against a corrupt database. For instance, if the application fails to detect the SQL injection, you will have a corrupt database.

Also make sure that you use sqlite3 to interactively investigate if anything was updated between the test runs. You can also use the interactive shell for finding out useful test data (like the name of a municipality which has HTTPS set to 1).

Classpath and packages

Remember that the package declaration of a class corresponds to a relative path. If you wish to run the main class with a qualified name of example.Args, you must be in the directory above example - the same directory where example is. You are in the correct directory if you can list the contents of example by simply typing ls example in bash. Or if you can print the contents of the Java file (provided it also is in example by simply typing cat example/

To compile such a class, you'd type:

$ javac example/

But to run it, you should use the qualified name (including package):

$ java example.Args [you could have arguments here]

When you are running a Java application which needs an external JAR file (like the driver for SQLite JDBC for instance), you can use the flag -cp to set the class path to include the relative path to the JAR file. You will need to include . in the class path (meaning current directory). Between all paths in the class path you will use a colon (on GNU/Linux and Mac OS) or a semicolon (on Windows).

If you run Cygwin on Windows, enclose the classpath in quotation marks, so that bash doesn't get confused by the semicolon. Example for running on Windows:

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

The same thing on GNU/Linux or Mac OS:

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

(You need to use the correct name of the JAR file - look in the driver directory)

General tips

Remember to compile, and to compile often. Compile after the slightest change of your code. Remember that the longer you wait to compile, the likelier it is that you get a lot of compilation errors.

Read all the compilation errors carefully. Start at the top of the list of errors, and fix one error at the time and recompile. Often a small error at the top creates a lot of errors which - if you're lucky - will disappear if you change the first error. If you for instance forget to close a block with a } the compiler typically gets confused and you'll get tons of errors. That's why it's good to start at the top of the list of errors.

Java methods

A concrete Java method consists of the following parts:

  • Access level if any (public|protected|private) - no access modifier is called package private or default
  • Return type (or void if the method doesn't return anything)
  • name (the name of the method, typically starting with a lower case letter)
  • parameter list within parentheses
  • The body of the method (between { and }

The parameter list is a pair of parentheses and inside it, 0 or more parameters.

A parameter consists of:

  • A type
  • A name (which will function as a local variable in the block of the method)


public String toString(){
  return firstName + lastName;

The method above consists of

  • Access modifier public
  • Return type: String
  • Parameter list: () - zero parameters in this case
  • Body:
  return firstName + lastName;

A method with void instead of return type doesn't require a return statement.

Example of a method with parameters:

public void setDebug(boolean debug){
  this.debug = debug;
  • Access modifier: public
  • Return type: void (actually void is not a type per se, but rather means "no type")
  • Parameter list:
    • Name: debug, type: boolean
  • Body:
  this.debug = debug;

An abstract method doesn't have a body and has a semicolon instead. Example:

public abstract void init();

Methods in an interface are implicitly abstract and may look as the one above. You don't need to use the keyword abstract for methods in an interface, but it is allowed. Investigate the interface to see examples.


Good luck and don't be afraid to ask if you need help.