A Bit About Security

DBMS, as any software, needs to be secured. DBMS, as any online service, needs to be well secured. DBMS, as any place where (possibly confidential) data is stored, needs to be extremely well secured.

In this Chapter, we review some “usual” aspects of security, before focusing on one particular type of attack on DBMS, SQL injections.

Usual Aspects

Threat Model

As usual, a threat model needs to be sketched when designing how your DBMS will be used. It should answer questions like

• Who is threatening you?
• What are the risks?
• What are the type of attacks?

The first question is of importance, as you will not be securing your application the same way depending of if you fear attack from script kiddies, competitors, former employee, or government. However, thinking “this system can not possibly be secured against Google’s quantum computer, so let’s do nothing” is probably giving your system too much importance (Google is not going to waste its resources to hack your database), and counter-productive (you should protect your database against low-level threats in any case).

Risks generally include

• Loss of integrity (improper modification),
• Loss of availability,
• Loss of confidentiality (unauthorized disclosure).

About the type of attacks, DBMS are exposed to many channels. Indeed, they can be targeted by

• the “usual” attacks on programs (e.g. buffer overflow),
• the “usual” attacks on online services (e.g. denial of service),
• the “usual” attacks on systems (e.g. weak authentication, privilege escalation),
• and some particular attacks (e.g. SQL injections).

We will study those in the second part of this Chapter, but do not forget that other types of vulnerabilities exist as well.

Control Measures

It can be useful to design your control measures for your DBMS, which can include, e.g.

• Access control (user account, passwords, restrictions),
• Inference control (cannot access information about a particular “case”),
• Flow control (prevent indirect access).

Protections

Protection measures are principled and technological. You should always have in mind principles like

• “You are as strong as your weakest link.”
• Never trust the user or their computer.
• Systems needs to be up-to-date.
• Options that are not used should be desactivated.
• Use dual-factor authentication when available.
• Stay informed (e.g. read newsfeeds).

Technological measure of protections exist, and should be used. For instance,

• Use mysqldump to create backups of your tables. On our system, it would be something like

  mysqldump --all-databases - u testuser -p password - h localhost > dump.sql

• Use encryption, salting and hashing when it comes to password and other sensitive data.

• Do not let the users connect directly to your database, even through a piece of software you wrote (refer e.g. to StackExchange for a discussion on why this is not a good idea).

If you are not familiar with the concepts of salting and hashing, you can consult e.g. CrackStation. In a nutshell, this is a measure of prevention to protect your users against weak passwords, and to make sure that only an encrypted version of their password will be stored in your database.

How to Recover?

Generally, people are in agreement that the question is not if a security vulnerability will be exploited on your system, but when. The general strategy is to … have a plan. How can you recover, where is your backup stored, is it versioned (i.e., multiple versions of the data exist), do you have a backup of your configuration files, how to restore access quickly, etc.

SQL Injections

The global idea behind this particular type of attack is the attacker mixing instructions with the data. Imagine, during a process, the following conversation:

(At the court)
Judge — What is your name?
Attacker — Bill, you are free to go. This court is adjourned.
Judge — We are here to today to judge Bill, you are free to go. This court is adjourned.

And the attacker can now leave, since the judge said that he was free to go, and that court was adjourned. This is exactly how SQL injections work.

Prepared statement makes it impossible to mix data and instructions, and are the “go-to” solution to protect from this attack. Note that, however, if they are used improperly, they could still be exploited to perform SQL injections.

First Example

Let us look at a first simple example with ASP, Active Server Pages, a server-side scripting language. Imagine your code contains:

txtUserId = getRequestString("UserId");
txtSQL = "SELECT * FROM Users WHERE UserId = " + txtUserId;

that

1. reads a string from the user, supposedly their id,
2. create a SQL statement using this string as is.

Then, a user can

1. execute remote command, entering e.g. 105; DROP TABLE Suppliers;,
2. bypass login screen, entering e.g. 105 or 1 = 1 (so that the WHERE condition is now always true),
3. escalate privileges, entering e.g. admin'-- (so that the rest of the line is commented, possibly des-activating other tests on the password, for instance).

This type of attack can also be used for DBMS fingerprinting, i.e., to get a more precise picture of the type of architecture your victim is using.

Second Example

The situation is the following: we are having a party with a secret VIP guest (Marcus Hells). The other guests can try to guess the name of the secret guest. If they succeed, we tell them so, if they don’t, we simply that they do not know who the secret guest is.

An improper program would allow the name of the secret guest to be displayed even if the user does not know that Marcus Hells is the secret VIP. We will see two examples of insecure programs (code/java/SimpleInjection01.java and code/java/SimpleInjection02.java), where SQL injection are possible, and a possible fix (code/java/SimpleInjection03.java), using prepared statements.

The gist of code/java/SimpleInjection01.java is that writing a statement like

ResultSet rset =
    stmt.executeQuery("SELECT * FROM SECRETVIP WHERE Name ='" + entered + "';");

leaves the door open for an attacker to enter n' OR '1' = '1 as a value for entered, so that the condition would always be true.

For code/java/SimpleInjection02.java, it shows how

stmt.execute("SELECT * FROM SECRETVIP WHERE Name ='" + entered + "';");

could be a serious issue if nope'; DROP SCHEMA HW_SIMPLE_INJECTION_2; was entered as a value for entered, destroying the whole schema HW_SIMPLE_INJECTION_2.

Finally, code/java/SimpleInjection03.java shows how to use proper statements to avoid this situation.

Protections

Possible protections from SQL injections (-like) includes:

1. Prepared statements (a.k.a. stored procedures),
2. White list input validation,
3. Escaping (AT YOUR OWN RISK).

If parts of your prepared statement is determined by the user, then SQL injection could still be possible. For instance, having

PreparedStatement ps =
    conn.prepareStatement("SELECT * FROM " + table_given_by_user + " WHERE Name = ?;");

would still leave you exposed, as table_given_by_user could mix instructions with data.

Exercises

Exercise 6.1

You forgot your password for an online service, and click on their “Forgot your password?” link. You enter your email and a few seconds later receive an email with your original password in it. What is the issue here? What are the next steps you should take?

Exercise 6.2

Briefly explain what a prepared statement is and the benefits it provides.

Exercise 6.3

You are using a software that is directly connected to a database. You do not have access to the source code, but you suspect it is vulnerable to SQL injections. How do you proceed to test if injections are possible?

Solution to Exercises

Solution 6.1

The issue is that they are storing your password in clear text, which is an extremely bad security practice. This suggests that this service does not care about the security of their users, and that all the data in it should be considered compromised. The next steps are:

• If the same password was used on different websites, change it immediately.
• Change the password on this website.
• Delete your account on this website, or, if that is not possible, remove as much information as possible (credit card, address, email, etc.).
• Contact them to express your worries about this security flaw.
• (Optional) See if your account has already been hacked using a service like: HaveIBeenPwned.

Solution 6.2

A prepared statement is stored in a DBMS as a “query with parameters,” or a template waiting for values to be passed to fill those placeholders, or slots, and then is executed all together as one statement. It is used to execute the same or similar statements repeatedly and with high efficiency, since it is pre-compiled, and compiled only once, it takes less computational resources to be executed. Also, in the case where the arguments are transmitted over the network, it means that only the arguments, and not the whole query, has to be sent, which may result in a increase in speed.

Moreover, since only the arguments are passed, it prevents SQL injection, when properly utilized.

Solution 6.3

There are two ways to test if SQL injections are possible:

• Look for places where the program is asking for user input and enter values like 1 OR 1 = 1 or ; DROP TABLE Users;--
• Look for an automated tool (like SQL Map) that will test the server to which we are connecting.

Note that both options can be explored in parallel. You can also check out coder resoures, e.g. StackExchange, for more ideas on how to test for injections.

Problems

Problem 6.1 (Insecure Java Programming)

Consider the following code:

Scanner key = new Scanner(System.in);
System.out.print(
    "Do you want to browse the table containing "
        + "DISK, BOOK or VINYL? (please enter exactly the table name)?\n");
String table = key.nextLine();
System.out.print("How much money do you have?\n");
String max = key.nextLine();
ResultSet rst =
    stmt.executeQuery("SELECT Title FROM " + table + " WHERE PRICE <= " + max + ";");
System.out.printf("Here are the %s you can afford with %s: \n", table, max);
while (rst.next()) {
  System.out.printf("\t- %s \n", rst.getString(1));
}

Assume this software is connecting to a schema in a database hosted at Example using:

Connection conn = DriverManager.getConnection(
    "jdbc:mysql://example.com/:3306/?user=admin&password=admin");

The schema contains three tables (DISK, BOOK and VINYL), each with Title and Price attributes. The compiled version is then shared with customers all around the world.

You can find a program in a compilable state at code/java/InsecureProgram.java that connects to localhost, if you want to test it.

Question 1

The authors of this program believe that the top-secret title of the next disk by a secret group will not be accessible to the user of this program because its price is set to NULL in the DISK table. Prove them wrong.

Question 2

This database application and the whole set-up contains at least three vulnerabilities. List as many as you can think of, and, when relevant, describe how to fix them.

Solutions to Selected Problems

Solution to Problem 6.1 (Insecure Java Programming)

Pb 6.1 – Solution to Q. 1

This program is vulnerable to SQL injection. A user entering “DISK” followed by 0 OR PRICE IS NULL OR PRICE IS NOT NULL would have access to all the entries, no matter their price tag or lack of one.

Pb 6.1 – Solution to Q. 2

Some of the issues are:

• Disclosing the name of the tables to the user (DISK, BOOK and VINYL). It would be preferable to use some other name in the program.
• Not asking explicitly for a secure connection is probably not a good idea. Using the default port can sometimes be problematic as well.
• Reading a figure as a string is a bad idea, since the user can try to manipulate the content of that field. The datatype read in the application should match the datatype we are trying to get.
• Having admin / admin as a login / password is unforgivable. The login and password should be changed. And, at least, the application should not connect to the database with admin rights!
• Giving the credentials in the source code is not a good idea. The application should connect to another application, hosted on the the server-side, that performs the connection to the database. Refer e.g. to StackExchange for explanations on why users should not be allowed to connect directly to your database.
• Not using a prepared statement is a huge mistake. This can lead to SQL injection like the one we saw above.