JavaDB:JDBC-INSERT-UPDATE-SQLInjection LIVE

From Juneday education
Jump to: navigation, search

Getting started

As usual, start by creating a new directory somewhere for this live session.

Use cd to enter this newly created directory.

Files

Use wget to download the files to your newly created directory.

Download:

Create the database:

$ sqlite3 login.db < login.sql

Java/JDBC part:

Download:

Live session

Background

Using user input as part of a dynamic SQL query is not very safe.

We'll see some examples of why.

Example login database

Let's say we have a login system where username and password are stored in a table login:

CREATE TABLE user(user_id INTEGER NOT NULL PRIMARY KEY, username TEXT NOT NULL UNIQUE, real_name TEXT NOT NULL);
INSERT INTO "user" VALUES(1,'rikard','Rikard Fröberg');
INSERT INTO "user" VALUES(2,'henrik','Henrik Sandklef');

CREATE TABLE login(user_id REFERENCES user(user_id) NOT NULL, password TEXT NOT NULL, PRIMARY KEY(user_id, password));
INSERT INTO "login" VALUES(1,'sikrit');
INSERT INTO "login" VALUES(2,'letmein');

So, username rikard has password sikrit and username henrik has password letmein'.

We could verify a login like this:

SELECT real_name
  FROM user NATURAL JOIN login
 WHERE username='rikard'
   AND password='sikrit';
Rikard Fröberg

If we get NULL, either the username or the password is wrong.

Verify it interactively in the SQLite3 shell!

Understanding the WHERE clause

What would the result of the following be?

SELECT real_name FROM user NATURAL JOIN login WHERE username='rikard' AND password='sikrit' OR 1=1;

What would the result of the following be?

SELECT real_name FROM user NATURAL JOIN login WHERE username='rikard' AND password='wrong'; SELECT user_id, password FROM login;

Note the semicolon!

What would the result of the following be?

sqlite> .headers on
sqlite> SELECT real_name FROM user NATURAL JOIN login WHERE username='rikard' AND password='wrong'; SELECT user_id, password AS real_name FROM login;

Note the semicolon and the "AS real_name" part!

Answer:

user_id|real_name
1|sikrit
2|letmein

The use of comments in SQL

A comment in SQL is everything after -- (two dashes):

sqlite> SELECT * FROM user WHERE username='rikard'; -- This is a comment!
1|rikard|Rikard Fröberg

What is the problem with all that?

If we have a login system written in Java, and the Java program issues the following SQL via JDBC:

SELECT real_name FROM user NATURAL JOIN login WHERE username='rikard' AND password='sikrit';

Where does the Java program get the strings for username and password from?

It gets it from user input!

How do you construct the SQL String inside Java?

A typical (but naïve) way would be a method like this:

private String verifyLogin(String username, String password) {
  String SQL = "SELECT real_name FROM user NATURAL JOIN login WHERE username='" +
               username +
               "' AND password = '" +
               password +
               "';";
  // The method executes the query and reads the column "real_name" from
  // the resultset - maybe creates a welcome message like:
  // String welcome = "Welcome " + rs.getString("real_name");
  // or an error message "Wrong username or password" if real_name is null
}

The same thing in a Bash script would look like this:

#!/bin/bash

USERNAME="$1"
PASSWORD="$2"

SQL="SELECT real_name FROM user NATURAL JOIN login WHERE username='$USERNAME' AND password = '$PASSWORD';"

REAL_NAME=$(sqlite3 login.db "$SQL")

WELCOME_MSG="Wrong username or password"
if [[ ! -z "$REAL_NAME" ]]
then
    WELCOME_MSG="Welcome $REAL_NAME"
fi                   

echo "$WELCOME_MSG"

We run the script like this:

$ ./login.sh rikard sikrit
Welcome Rikard Fröberg
$ ./login.sh rikard wrong
Wrong username or password
$

Can we trust user input?

What if the user input contains SQL?

Remember this:

SELECT real_name FROM user NATURAL JOIN login WHERE username='rikard' AND password='sikrit' OR 1=1;

What is the result of running the script like this:

$ ./login.sh whatever "' OR 1=1;--"

Think about what the SQL variable will be after this:

SQL="SELECT real_name FROM user NATURAL JOIN login WHERE username='$USERNAME' AND password = '$PASSWORD';"

Let's just print it from the script!

$ ./login.sh whatever "' OR 1=1; --"
SELECT real_name FROM user NATURAL JOIN login WHERE username='whatever' AND password = '' OR 1=1; --';

Is the above valid SQL syntax?

The bash script is even more vulnerable than the Java version

What if the user runs the bash script like this:

$ ./login.sh whatever "' or 1=1;drop table user;--"
Welcome Rikard Fröberg
Henrik Sandklef
$ sqlite3 login.db
SQLite version 3.11.0 2016-02-15 17:29:24
Enter ".help" for usage hints.
sqlite> .schema
CREATE TABLE login(user_id REFERENCES user(user_id) NOT NULL, password TEXT NOT NULL, PRIMARY KEY(user_id, password));
sqlite>

Oooops! What happened to the user table? It was dropped!

Restore your database like this:

$ sqlite3 login.db < login.sql

Don't worry about the errors, they come from the fact that the login table was intact and cannot be recreated, and that the user_id - password combination is primary key so the same values cannot be inserted again.

Now, verify that you can do a "real" login:

$ ./login.sh rikard sikrit
Welcome Rikard Fröberg

Java example

Here's a very simplified class for verifying a login using JDBC:

import java.sql.*;

public class Login {

  private static Connection con;
  private String welcomeMsg = "Wrong username or password";
  
  private final static String URL="jdbc:sqlite:login.db";
  
  static {
    try {
      con = DriverManager.getConnection(URL);
    } catch (SQLException e) {
      System.err.println("Error getting connection to " + URL);
      e.printStackTrace();
    }
  }

  public String message() {
    return welcomeMsg;
  }
  
  public boolean verifyLogin(String username, String password)
    throws SQLException {

    if (con == null) {
      System.err.println("No connection to db.");
      return false;
    }
    
    String SQL =
      "SELECT real_name FROM user NATURAL JOIN login " +
      " WHERE username = '" + username + "'" +
      "   AND password = '" + password + "'";

    Statement stm = con.createStatement();
    ResultSet rs = stm.executeQuery(SQL);
    try {
      if (rs.next()) {
        welcomeMsg = "Welcome " + rs.getString("real_name");
        return true;
      }
      return false;
    } finally {
      con.close();
    }
  }
}

It is used by a small test program which asks the user for username and password. The program is vulnerable to SQL injections.

Here's the main class:

import java.util.Scanner;

public class TestLogin {
  
  public static void main(String[] args) {
    String username;
    String password;
    System.out.print("Username: ");
    Scanner sc = new Scanner(System.in);
    username = sc.nextLine();
    System.out.print("Password: ");
    password = sc.nextLine();

    Login login = new Login();

    try {
    
      if (login.verifyLogin(username, password)) {
        System.out.println(login.message());
        System.out.println("You are now logged in.");
      } else {
        System.out.println("Sorry, we could not verify your login.");
        System.out.println(login.message());
      }
    } catch (Exception e) {
      System.err.println("Something went wrong: " + e.getMessage());
    }
  }
}

To compile and run the program, use the following command line:

$ # On Mac OS X and GNU/Linux:
$ javac TestLogin.java && java -cp .:sqlite.jar TestLogin

$ # On Windows/Cygwin:
$ javac TestLogin.java && java -cp ".;sqlite.jar TestLogin"

We'll discuss the program and how to abuse it during the live coding session.