Keeping data secure is very important. Any application that talks to a database needs protection against SQL injection, an attack method where an SQL query is entered into a form where text is expected. Luckily, this attack is easy to prevent. As a software engineer/programmer, it is your responsibility to keep your database secure against SQL injection (SQLi for short).
For this exercise, we will use a lightweight database, SQLite. Before downloading anything, check to see if SQLite is installed. SQLite is included with most Linux distributions, and is probably available through your distro's package manager. Type this into the terminal/command prompt to check if SQLite is installed:
sqlite3 --version
If you are a Windows user, download the command-line tools from the
SQLite website:
http://sqlite.org/download.html
Next, you will need to extract the zip file containing
the SQLite executable files. On my machine, I extracted this file to
C:\, so I can find it easily. Now you need to tell
Windows where to find to find the SQLite command line tools, so add
the location of the extracted folder to your PATH. Now, you should be
able to manipulate SQLite databases from your command prompt.
Using SQLite, lets make a basic table with two columns. You can add more columns if you want, but I want to keep this example as simple as possible. Create a new directory called "sqli-demo", then add a database inside that directory.
mkdir sqli-demo
sqlite3 sqli
This opens the SQLite command line interface
and creates a new database called "sqli".
create table users(id int, name varchar(20));
Creates a
table called "users" with two columns.
insert into users values(1234, "jim");
insert into users values(4321, "sue");
Insert some dummy
data into the users table.
.quit
Exit the SQLite command-line interface.
To demonstrate SQLi, we will need to write a program that takes user input and interacts with the database using that input. Many real-world programs that use a database would typically run on a web server and get user input over the internet, but let's keep it simple and just create a command-line application.
To run queries against an SQLite database, we need a JDBC driver written specifically for SQLite. You can find the driver here, look for the download link in the readme. Once you have the JDBC jar, copy the jar into the "sqli-demo" directory.
Now, create a new Java file inside "sqli-demo". I named my Java file "Insecure.java". Inside Insecure.java, write the following code:
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.util.Scanner;
public class Insecure {
private final static String URL = "jdbc:sqlite:sqli";
public static void main(String[] args) throws Exception {
System.out.println("Connecting to DB...");
try(Connection conn = DriverManager.getConnection(URL)) {
System.out.println("Connected to DB.");
Scanner input = new Scanner(System.in);
System.out.println("Enter id:");
String id = input.nextLine();
System.out.println("Enter name:");
String name = input.nextLine();
input.close();
Statement stmt = conn.createStatement();
stmt.executeUpdate("insert into users values(" + id +
",'" + name + "')");
}
}
}
The code above connects to the database we created earlier, then adds a record to the users table. Be aware that any exception will crash the program, this program is just meant to be a simple demonstration.
Note that the line stmt.executeUpdate("insert... directly
injects user input into a SQL statement.
At this point you should have the following files in your sqli-demo directory:
Insecure.javasqli - This is the the database, SQLite stores
databases in flat files.sqlite-jdbc-3.21.0.jarTo compile and run, use the following commands:
javac -cp "sqlite-jdbc-3.21.0.jar" Insecure.java
(Windows) java -cp "sqlite-jdbc-3.21.0.jar;." Insecure
(Mac/Linux) java -cp "sqlite-jdbc-3.21.0.jar:." Insecure
The cp option is short for "classpath". This option tells
Java where to find any external libraries neccessary for compilation and
execution.
Once the program is running, you will be prompted for an id and name that will be stored in the user table.
For example, we can enter an id of 111 and the name
Frank'); drop table users; --
Now, let's open up the database in the SQLite CLI and see if our changes
were made:
sqlite3 sqli
select * from users;
But as we can see, no records were returned, since we injected a command to drop the users table! Lets look at the SQL statement that got passed to the database:
"insert into users values(111,'Frank'); drop table users; --')"
As we can see, the program sends the database a command that inserts a new record into the users table, then drops the users table, and then comments out the remaining apostrophe and parenthesis.
This is a huge problem, because the program we wrote is only supposed to insert a row into a table. But a user can inject arbitrary SQL statements, and is free to alter and delete data.
Preventing SQLi is easy, so there is no excuse for SQLi vulnerable code.
In JDBC, the PreparedStatement class provides built in SQLi
protection.
Lets write a secure version of Insecure.java, using
PreparedStatement:
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.util.Scanner;
public class Insecure {
private final static String URL = "jdbc:sqlite:sqli";
public static void main(String[] args) throws Exception {
System.out.println("Connecting to DB...");
try(Connection conn = DriverManager.getConnection(URL)) {
System.out.println("Connected to DB.");
Scanner input = new Scanner(System.in);
System.out.println("Enter id:");
int id = Integer.parseInt(input.nextLine());
System.out.println("Enter name:");
String name = input.nextLine();
input.close();
PreparedStatement stmt = conn.prepareStatement(
"insert into users values(?,?)");
stmt.setInt(1, id);
stmt.setString(2, name);
stmt.executeUpdate();
}
}
}
Notice that now we are using a PreparedStatement instead of
a Statement. PreparedStatements can take
parameters, denoted by question marks. To bind a value to a question
mark, use one of the setString, setInt, etc.
methods. These methods take two parameters, the index of the question
mark (starting with 1) and the value to bind.
Before we test this program, we need to recreate the users table, since we dropped it earlier.
To compile and run, use the following commands:
javac -cp "sqlite-jdbc-3.21.0.jar" Insecure.java
(Windows) java -cp "sqlite-jdbc-3.21.0.jar;." Insecure
(Mac/Linux) java -cp "sqlite-jdbc-3.21.0.jar:." Insecure
Now let's try to inject some SQL: choose an id
111 and the name
Frank'); drop table users; --
sqlite3 sqli
select * from users;
You should see a row with an id of 111 and the name
Frank'); drop table users; --. This is good! The
PreparedStatement didn't interpret our injected SQL as
a command, and we didn't lose any data.
Whenever you access a database programmatically, always make sure all input has been cleaned to avoid SQL injection. Most programming languages and databases have libraries that will do this automatically, so it is up to you, the developer to learn how to use these libraries and keep your data safe.
SHA256 sum:
a2315c8e06935b8d254955c5b67f643711f0f688f915ef75b051750675edfaba