View source for JavaDB:Exercise - JDBC-INSERT-UPDATE-SQLInjection

=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 [https://minhaskamal.github.io/DownGit/#/home?url=https://github.com/progund/javadb/tree/master/JDBC-INSERT_UPDATE/exercise 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 [https://minhaskamal.github.io/DownGit/#/home?url=https://github.com/progund/javadb/tree/master/JDBC-INSERT_UPDATE/exercise 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)
<pre>
.
|-- db
|   |-- app
|   `-- main
|-- doc
|   |-- db
|   |   `-- app
|   `-- resources
`-- driver
</pre>

The <code>db</code> directory should now have the following contents:
<pre>
db
|-- app
|   |-- DatabaseTest.java
|   |-- DBUtils.java
|   |-- MunicipalityDB.java
|   |-- Municipality.java
|   |-- MyMunicipalities.java
|   `-- package-info.java
`-- main
    `-- package-info.java
</pre>

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

Make sure that you also got the sql-file <code>restore_my_municipalities.sql</code> in the current directory.

Start by creating a database using the sql-file:

<source lang="Bash">
$ sqlite3 my_municipalities < restore_my_municipalities.sql
</source>

You will also have to put the driver in the driver directory. Download the [https://bitbucket.org/xerial/sqlite-jdbc/downloads 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 <code>-cp</code> flag.

==2. Create the <code>db/main/Main.java</code> file==
Create the <code>db/main/Main.java</code> file by opening it in a text editor. It doesn’t yet exist, so something like this would work (using cygwin):
<source lang="Bash">
$ cygstart notepad++ db/main/Main.java
</source>
or (using Atom for text editor):
<source lang="Bash">
$ atom.cmd db/main/Main.java
</source>
If the editor asks you ''"The file doesn’t exist. Do you want to create it?"'' then answer yes.

Start by adding a <code>package</code> declaration to the <code>Main</code> 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.

<div class="mw-collapsible mw-collapsed">
Expand using link to the right to see a hint.
<div class="mw-collapsible-content">
<source lang="Bash">
package db.main;
</source>
</div></div>
Add a declaration of a public class with the name Main .
<div class="mw-collapsible mw-collapsed">
Expand using link to the right to see a hint.
<div class="mw-collapsible-content">
<source lang="Bash">
package db.main;
public class Main{
}
</source>
</div></div>

In the class, add a standard main method.
<div class="mw-collapsible mw-collapsed">
Expand using link to the right to see a hint.
<div class="mw-collapsible-content">
<source lang="Bash">
package db.main;
public class Main{
  public static void main(String[] args){
  }
}
</source>
</div></div>

In the <code>main</code> method, start by creating a variable of type <code>MunicipalityDB</code> as an instance of <code>MyMunicipalities</code>. You should call the variable <code>db</code>.
<div class="mw-collapsible mw-collapsed">
Expand using link to the right to see a hint.
<div class="mw-collapsible-content">
<source lang="Bash" highlight="4">
package db.main;
public class Main{
  public static void main(String[] args){
    MunicipalityDB db = new MyMunicipalities();
  }
}
</source>
</div></div>

Look at the javadoc API documentation provided in the <code>doc</code> directory in order to find out where that class is (so that you can add the import statements needed) and to see the constructors.
<div class="mw-collapsible mw-collapsed">
Expand using link to the right to see a hint.
<div class="mw-collapsible-content">
<source lang="Bash" highlight="2-3">
package db.main;
import db.app.MunicipalityDB;
import db.app.MyMunicipalities;
public class Main{
  public static void main(String[] args){
    MunicipalityDB db = new MyMunicipalities();
  }
}
</source>
</div></div>

Back in main, next thing is to create a new <code>Municipality</code> instance called <code>m</code> . Once again, look at the API provided to find out where the <code>Municipality</code> class is located in order to see if you need any more <code>import</code> statements. Study the constructor in the API and give the <code>Municipality</code> via arguments to the constructor the following:
* <code>Teststad</code> (as the name)
* <code>http://teststad.se</code> (as the url)
* <code>Apache Super server</code> (as the server)
* <code>false</code> (as the indication of HTTPS support)
<div class="mw-collapsible mw-collapsed">
Expand using link to the right to see a hint.
<div class="mw-collapsible-content">
<source lang="Bash" highlight="4,8-12">
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);
  }
}
</source>
</div></div>

Write a <code>println</code> statement that prints this new municipality to the standard out stream.
<div class="mw-collapsible mw-collapsed">
Expand using link to the right to see a hint.
<div class="mw-collapsible-content">
<source lang="Bash" highlight="13">
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);
  }
}
</source>
</div></div>

Add this municipality to the storage (database) using the instance method <code>addCity()</code> (look at the API to see how this is done) of the db variable.
<div class="mw-collapsible mw-collapsed">
Expand using link to the right to see a hint.
<div class="mw-collapsible-content">
<source lang="Bash" highlight="14">
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);
  }
}
</source>
</div></div>

Add a println statement that prints the ID of the municipality to the standard out stream.
<div class="mw-collapsible mw-collapsed">
Expand using link to the right to see a hint.
<div class="mw-collapsible-content">
<source lang="Bash" highlight="15">
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());
  }
}
</source>
</div></div>
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.
<div class="mw-collapsible mw-collapsed">
Expand using link to the right to see a hint.
<div class="mw-collapsible-content">
<source lang="Bash">
$ sqlite3 my_municipalities
</source>
<source lang="SQL">
sqlite> SELECT * FROM municipalities WHERE name="Teststad";
291|Teststad|http://teststad.se|0|Apache Superserver
</source>
</div></div>
Pseudo code for this version of the main method of the class db.main.Main:
<source lang="Java">
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?
</source>
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:

<source lang="Bash">
$ javac db/*/*.java && java -cp .:driver/sqlite-jdbc-3.8.11.2.jar db.main.Main
</source>
Or if you are on Windows/Cygwin:
<source lang="Bash">
$ javac db/*/*.java && java -cp ".;driver/sqlite-jdbc-3.8.11.2.jar" db.main.Main
</source>
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.
<div class="mw-collapsible mw-collapsed">
Expand using link to the right to see a hint.
<div class="mw-collapsible-content">
<source lang="Bash">
$ sqlite3 my_municipalities < restore_my_municipalities.sql
</source>
</div></div>
'''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  <code>main</code> method):

Use the <code>db</code> variable to delete the municipality represented by <code>m</code> from the database.
<div class="mw-collapsible mw-collapsed">
Expand using link to the right to see a hint.
<div class="mw-collapsible-content">
<source lang="Bash" highlight="16">
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);
  }
}
</source>
</div></div>

(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 <code>m</code> to be a new <code>Municipality</code> that you get from asking the <code>db</code> 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 <code>m</code> was using.

You should not use any constructor to re-assign <code>m</code>, only use the correct instance method that <code>db</code> provides for fetching a municipality by name.
<div class="mw-collapsible mw-collapsed">
Expand using link to the right to see a hint.
<div class="mw-collapsible-content">
<source lang="Bash" highlight="17">
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");
  }
}
</source>
</div></div>

Verify that the fetch by name method (the one you were using) returned <code>null</code>, since the municipality previously represented by <code>m</code> was deleted.
<div class="mw-collapsible mw-collapsed">
Expand using link to the right to see a hint.
<div class="mw-collapsible-content">
<source lang="Bash" highlight="18">
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));
  }
}
</source>
</div></div>
Re-assign <code>m</code> to an existing municipality from the database using the same instance method for <code>db</code>. You may choose any city you like. Use the name of the city as argument to the method you are using on the <code>db</code> variable. You may use the SQLite interactive shell in order to find a name of a municipality.
<div class="mw-collapsible mw-collapsed">
Expand using link to the right to see a hint.
<div class="mw-collapsible-content">
<source lang="Bash" highlight="19">
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");
  }
}
</source>
</div></div>

Print this new representation that <code>m</code> should have to the standard out stream.
<div class="mw-collapsible mw-collapsed">
Expand using link to the right to see a hint.
<div class="mw-collapsible-content">
<source lang="Bash" highlight="20">
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);
  }
}
</source>
</div></div>

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 <code>MunicipalityDB</code> (using <code>MyMunicipalities</code> as the concrete implementing class)? Write down the steps and make sure you can explain them to a friend.
<div class="mw-collapsible mw-collapsed">
Expand using link to the right to see a hint.
<div class="mw-collapsible-content">
Instantiating a <code>MunicipalityDB</code> instance has the following effect:
* The instance variable <code>DBUtils db</code> is initiated to a new DBUtils instance (if none exist)
** As the <code>DBUtils</code> class is loaded, its static intitializer is run
*** In the static initializer, the driver from the JAR file is loaded into the JVM using <code>Class.forName("org.sqlite.JDBC");</code>
** The constructor of <code>DBUtils</code> gets a connection from <code>DriverManager</code>
</div></div>

What happens when you create an instance of a <code>Municipality</code> using the constructor in that class?
<div class="mw-collapsible mw-collapsed">
Expand using link to the right to see a hint.
<div class="mw-collapsible-content">
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.
</div></div>
Is there an automatic connection between a <code>Municipality</code> object and the Database?
<div class="mw-collapsible mw-collapsed">
Expand using link to the right to see a hint.
<div class="mw-collapsible-content">
No, there's no automatic connection between a <code>Municipality</code> instance, and the database. The <code>Municipality</code> 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 <code>Municipality</code> 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.
</div></div>
What happens when you use the <code>db</code> variable to call the various methods you called? Make sure you can explain what happens and in what order.
<div class="mw-collapsible mw-collapsed">
Expand using link to the right to see a hint.
<div class="mw-collapsible-content">
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
* <code>m</code> is queried for name, url, server and https which are saved in local variables to build up an insert statement string
* the <code>db</code> (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 <code>db</code> 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: <source lang="SQL" inline>SELECT LAST_INSERT_ROWID();</source>. It can be executed via JDBC.

Getting a Municipality by name:
* The string given as argument is bound to the parameter <code>name</code> (the address to the string argument is copied to the name parameter)
* A SELECT string is build using the <code>name</code> argument for selecting everything from municipalities where the name matches the parameter <code>name</code>
* the <code>db</code> 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)
</div></div>
==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:

<code>'; drop table municipalities;--</code> (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.
<div class="mw-collapsible mw-collapsed">
Expand using link to the right to see a hint.
<div class="mw-collapsible-content">
<source lang="Bash" highlight="21-22">
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);
  }
}
</source>
</div></div>

Compile and run.

Go to the interactive shell and try to <source lang="SQL" inline>SELECT * FROM municipalities</source>...
<div class="mw-collapsible mw-collapsed">
Expand using link to the right to see a hint.
<div class="mw-collapsible-content">
<source lang="SQL">
sqlite> SELECT * FROM municipalities;
Error: no such table: municipalities
</source>
</div></div>

Restore the database from the sql file.
<div class="mw-collapsible mw-collapsed">
Expand using link to the right to see a hint.
<div class="mw-collapsible-content">
<source lang="BASH">
$ sqlite3 my_municipalities < restore_my_municipalities.sql
</source>
</div></div>

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

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

Comment out the old string and use only this string for the call to updateHTTPSbyName()!
<div class="mw-collapsible mw-collapsed">
Expand using link to the right to see a hint.
<div class="mw-collapsible-content">
<source lang="Bash" highlight="22-23">
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);
  }
}
</source>
</div></div>

Go to the interactive shell and do:
<source lang="SQL">
sqlite> SELECT COUNT(*) FROM municipalities WHERE NOT HTTPS;
</source>
What was the result?

<div class="mw-collapsible mw-collapsed">
Expand using link to the right to see a hint.
<div class="mw-collapsible-content">
<source lang="SQL">
sqlite> SELECT COUNT(*) FROM municipalities WHERE NOT HTTPS;
290
</source>
</div></div>

What happened in both cases?
<div class="mw-collapsible mw-collapsed">
Expand using link to the right to see a hint.
<div class="mw-collapsible-content">
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:
* <code>UPDATE municipalities SET HTTPS=0 WHERE name=''; drop table municipalities;--'</code>
* <code>UPDATE municipalities SET HTTPS=0 WHERE name='' or 1=1;--'</code>
Try and understand how this happened.
</div></div>
This will be the topic in a future lecture!

=Links=
==Source code==
* Github repo [https://github.com/progund/javadb/tree/master/JDBC-INSERT_UPDATE/exercise JDBC INSERT UPDATE]

==Chapter links==
Next recommended page is [[JavaDB:PreparedStatement]].

{{Nav links|prev=JavaDB:JDBC-INSERT-UPDATE-SQLInjection|TOC=More_programming_with_Java#Database_and_JDBC_related|next=JavaDB:PreparedStatement}}