JavaDB:Exercise - PreparedStatement

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.


This exercise will show you how to use a PreparedStatement in order to avoid an SQL injection attack. You will add a new method to an exisiting interface and an exisiting implementation of that interface, which uses a PreparedStatement to update a table in a database. You will then try to make an SQL injection in order to verify that the new method is actually not vulnerable to this kind of behavior. Then you will use the old (existing) unsafe version of the method to mess up your database with the SQL injection. Hopefully you will be convinced, after this, that it is worth learning how to use PreparedStatement rather than the less safe Statement interface, in places of your code where you are using data passed to a method to be used for building up an SQL statement.

As an optional exercise, you can also update the javadoc documentation for the new method.

Note that the previous chapter, as well as this chapter, only serve as brief introductions to this topic. You will need to read the external links of the previous chapter and study the material there in order to get a deeper understanding about PreparedStatement.

You may see these two chapters as teasers for you to get curious to learn more about using PreparedStatement and think about the possibilities of SQL injection attacks.


Task 1 - Setup for this exercise

Download the files for this exercise here (you need to open this link in a browser).

Verify that the file is a non-empty zip file:

$ file Zip archive data, at least v1.0 to extract

The file command should answer as above (or similar) but not "empty" or "HTML" or something other than a zip file.

Plan B:

If the file download really doesn't work, you can try this link instead to an old zip file we created as a backup plan:

Change the name of the plan B zip file to so that the rest of these instructions work for you.

Create a directory, somewhere in your home directory, called Ex12 and cd to that directory. Move the zip file to this directory. Extract the files.

Expand to see a hint

$ mv Ex12
$ cd Ex12
$ unzip
$ cd exercises
$ ls
db doc restore_municipalities.sql

Note: You need to download (or copy from previous exercises or assignments) the SQLite JDBC driver (the JAR file) also, in order to run the code in these exercises.

Here's a link:

Note also that the name of the driver JAR file might be different from the example command lines on this page, since the name contains the version number (in this case 3.21.0). A simple way around this is to rename the jar file to simply sqlite-jdbc.jar and use that shorter name when you put it on the class path:

$ mv sqlite-jdbc-3.21.0.jar sqlite-jdbc.jar

Task 2 - Uncomment preparedStatement

Open db/app/ and uncomment the method with the following signature:

public PreparedStatement preparedStatement(String sql)

Task 3 - Add a method to MyMunicipalities

Now that we have a method for getting a PreparedStatement from the DBUtils class, we can add a method to the MyMunicipalities class called updateHTTPSbyNameSafely .

Open db/app/MyMunicipalities and add a method with the following signature:

public int updateHTTPSbyNameSafely(String name, boolean https)

In the method body, do the following:

Create a local variable of type String called sql and initiate it to:

"UPDATE municipalities SET HTTPS=? WHERE name= ?"

Create a local variable called result of type int and initiate it to 0.

Create a try-catch block with the following code in the try-body:�

  PreparedStatement pStm = db.preparedStatement(sql);
  pStm.setInt(1, (https?1:0));
  return result;

Write the catch-clause to catch any Exception e, and in it, add the following:

 System.err.println("Error creating prepared stm: "+e.getMessage());
 return -1;

The pseudo-code for this method would be:

create and init the string sql to the string described above
create and init the int result to 0
  PreparedStatement pStm = db.preparedStatement(sql);
  etc etc until return result;
}catch(Exception e){
  System.err... error message;
  return -1;

Remember: Pseudo code is not real code. It is just fake-code using human language to explain the logic of some chunk of code. You will need to translate it to actual Java code.

Task 4 - Update the MunicipalityDB interface

Open the db/app/MunicipalityDB interface file (db/app/ and add the method with the same signature as the updateHTTPSbyNameSafely method in task #3 above. It should be an abstract method (it is implemented by you in the MyMunicipalities concrete class).

Task 5 - Create a class called Main in db.main

Create a java-file called in the db/main directory.

Verify that using the updateHTTPSbyNameSafely method will not ruin the database when called with the following arguments:

("' or 1=1;--", false)

Expand using link to the right to see a hint.

  • The Main class should have a package declaration declaring that is it part of the db.main package .
  • The Main class should have the following import statements:
    • import;
    • import;
  • The Main class should have a main-method.
  • The Main class's main method should create a MunicipalityDB variable (for instance called db) as a new MyMunicipalities() instance.
  • The call to the updateHTTPSbyNameSafely method should be called on the db variable (since it is an instance method!).

You must ensure that you have a database in the current directory - use:

$ sqlite3 my_municipalities < restore_my_municipalities.sql

to create it.

You can ensure that the attempted SQL-injection didn't work, both by printing out the return value from the updateHTTPSbyNameSafely method (it should return 0 as in zero rows updated) and by entering the my_municipalities database manually and interactively and run some select statements confirming that not all rows were updated to have HTTPS set to 0 (zero).

Expand using link to the right to see a hint.

select count(*),HTTPS from municipalities group by HTTPS;
-- should not return 290 counts of 0!!!
sqlite> select count(*) from municipalities where not HTTPS;
-- should not return 290

Task 6 - Change back to the unsafe method

Now, change the main method to use the unsafe method updateHTTPSbyName with the same arguments, and verify that the SQL injection actually worked and set the whole table to having each row have HTTPS=0.

Verify this both by printing the return value of the call to updateHTTPSbyName and by investigating the table manually by entering the sqlite database and issuing SELECT commands.

You must re-compile the Main class for the changes to take effect. So don't forget to recompile!

Task 7 - Investigate and try to understand the source code

Now, re-visit the code you wrote using prepared statement and try to understand it. If you don't, consult the supervisor or teacher (and read the external links from the previous chapter again!).

Task 8 - Voluntary extra exercise

After you have made changes as of above to the MunicipalityDB and MyMunicipalities interface and class, you may regenerate the javadoc documentation. In the same directory, issue the following command:

$ javadoc -d doc -linkoffline '' ''

The command above should be issued on one single line. If it looks like several lines here, it is because it is too long to fit one line.

Open the index.html in your browser and verify that the new method is documented. If it lacks description etc (it probably does!) then add javadoc comments (just look at the unsafe method and be inspired by that - but add information that this is safer, because it uses a PreparedStatement rather than a normal plain Statement). Re-run the javadoc command again and refresh your web browser and confirm that it has your new information from your comments.


Source code

Chapter links

Next recommended page is: JavaDB:Commit - Rollback.

« PreviousBook TOCNext »