The labs on this site need JavaScript to run. The same content is written out below.

Fundamentals › Security

Input and injection

What happens when text typed by a user is read as code?

SQL is a command sent to the database as text. To find a member by the name a user typed into an input box, that name has to go somewhere in the SQL, and Java code has two ways to do it. One is to attach the input to the SQL text ("… name = '" + input + "'"). The other is to leave a ? in the SQL where the value goes and pass the input separately through a PreparedStatement (a tool that takes values separately). When you attach it, characters in the input such as a quote also become part of the SQL text, so the database reads them as SQL syntax. Then the input is no longer a value but a command. This is SQL injection. When you pass it with ?, even a quote or OR in the input is only part of the value to look for. In this lab, you put into each of the two ways an ordinary name, a text with a quote and OR, and an ordinary name with a quote, and see the SQL the database received and the rows that came back. (The code on the screen is shown as if it were inside the main method, and the import lines and the main declaration are left out. There is no input box; the text is written in the input variable. Web attacks in general and JdbcTemplate are covered in the in-depth labs.)

Checked against real execution · Compiled six combinations on JDK 21 and H2 2.4.240 and ran them in new Java processes and checked them; the rules were checked against OWASP and the Java SE 21 API documentation

Concepts in this lab

Statement and executeQuery

A Statement is an object used to send an SQL statement that has no values put into it to the database and run it, and it is made with conn.createStatement(). executeQuery(sql) sends a statement such as a SELECT and returns the result as one ResultSet, and the value it returns is never null. It gives a SQLException if a database access error occurs, if it is called on a closed Statement, or if the SQL it sends does not produce a single ResultSet. To Java, SQL is just a string, so a wrong text still compiles and is found out as an error at run time, when the database reads it. That making a Statement alone does not send SQL to the database and that SQL is sent when executeQuery is called was checked by running it on H2.

PreparedStatement and bind variables

A PreparedStatement is an object that holds an SQL statement compiled in advance and is used to run the same statement many times, and you create it with conn.prepareStatement(sql). You leave the place for a value in the SQL as ?, and pass the place number (starting from 1) and the value separately, as in setString(1, value). Deciding the SQL first and passing the values later like this is called using bind variables. OWASP explains that with this way the database always tells code and data apart whatever the input is, so even if an attacker inserts an SQL command, the intent of the query cannot be changed. In the H2 2.4.240 run for this lab as well, when a value was passed with ?, the SQL the database recorded kept the ? as it was and the text of the input did not appear in it.

SQL string literals and quotes

In SQL, a text value (a string literal) starts with a single quote and ends with a single quote. To put a single quote character inside a string, you write the quote twice as '' in H2 (for example 'John''s car'), and MySQL also allows putting before the quote in addition to ''. The quote is also the character that tells where a string ends, so if you attach input to the SQL text as it is, a quote inside the input ends the string, and the characters after it are read as SQL syntax. This is why an ordinary name with a quote (O'Neil) also causes a syntax error when it is attached. The way of passing a value does not use the quote in the SQL text, so it does not have this problem.

SQL injection

An attack in which user input is interpreted as part of a SQL statement, so a query that the attacker intended is executed. It happens when SQL is built by concatenating input values as strings. Parameterized queries with bind variables (PreparedStatement) separate the SQL structure from the values, so input is always treated only as a value, which prevents the attack.

Try these

• Default settings — The SQL keeps the ?, and kim goes separately as a value. One row, kim, comes back.
• Attaching, and kim — The attached SQL also returns one row, kim. From the result alone, you cannot tell the two ways apart.
• Attaching, and OR — The input is read as a condition of the SQL, so all three rows come back.
• Passing with ?, and OR — The input is handled only as a value, so no row has that name and 0 rows come back.
• Attaching, and O'Neil — Even though it is an ordinary name, the SQL syntax breaks and an error occurs.
• Passing with ?, and O'Neil — Even with the quote, one row, O'Neil, comes back without a problem.

Running with the default settings

1. String input = "kim": the text "kim" is stored in the input variable. It is an ordinary name. In this lab, we take this to be the text a user typed into an input box, and instead of an input box we wrote the text in the code. The member table of the database has three rows: kim, lee, and O'Neil.
2. String sql = "SELECT name FROM member WHERE name = ?": the SQL text is stored in the sql variable. The place for the value is left as ?, and the text of input does not go into this text. Nothing has been sent to the database yet.
3. conn.prepareStatement(sql): creates a PreparedStatement object that holds the sql text and stores it in ps. The SQL that ps holds has 1 ? place and no value yet. This SELECT is not in the execution record of the database yet either.
4. ps.setString(1, input): passes the text of input as the value of the 1st ? place. ps holds the SQL text (as it is, with the ? place) and the value of place 1 separately. The text of input is not put into the SQL text.
5. ps.executeQuery(): ps handles the value separately from the SQL text, so the value is not read as SQL syntax. The SQL that the database ran is "SELECT name FROM member WHERE name = ?", and the text of input is nowhere in it. The database looks in the member table for rows whose name equals the value. There is 1 row where name is "kim". Java receives the result as ResultSet rs.
6. while (rs.next()): reads the rows of rs one by one and prints name. The output is kim.

Result of this run

• If you pass input with ?, the SQL text goes to the database as SELECT name FROM member WHERE name = ? as it is, and kim goes separately as a value, so one row, kim, comes back.
• With one ordinary input, you cannot see what passing with ? prevents. Comparing it with the attaching way while changing the input is what the following questions do.

Code

// The member table has three rows (name: kim, lee, O'Neil)
// Connection: connect to the database that the address points to
Connection conn = DriverManager.getConnection("jdbc:h2:mem:shop");
// Input: let us say this is the text a user typed into an input box
String input = "kim";
// SQL: leave the place for the value as ?
String sql = "SELECT name FROM member WHERE name = ?";
// Send: pass the SQL text and the value separately
PreparedStatement ps = conn.prepareStatement(sql);
ps.setString(1, input);
ResultSet rs = ps.executeQuery();
// Read: print the name of each returned row
while (rs.next()) {
    System.out.println(rs.getString("name"));
}

Assumptions of this model

We ran and checked this on JDK 21.0.12.1 and H2 2.4.240 (in-memory database), and we did not check by running on MySQL. For all six combinations, we put the code on the screen into Main.java, which has a main method (we added only import java.sql.* and the main declaration), compiled it with javax.tools, and ran each combination in a new Java process. Before the run, in the same process, we created the member table (id INT primary key, name VARCHAR(20)) and the three rows (1, kim), (2, lee), (3, O'Neil); the screen leaves this preparation code out. We did not make an input box and wrote the text in the input variable. What we checked against is the three items of the result table. The SQL the database received is the SELECT left in H2's INFORMATION_SCHEMA.QUERY_STATISTICS (the execution record), and for the combination that could not run because of a syntax error, we judged that the error came from the parser of the database engine and that the error message quoted that SQL (the execution record was empty). The returned rows are the names the code printed, and the way the input was handled was obtained from the actual values. If the text of input is in the SQL the database received, it is "part of the SQL text", and if it is not, it is "handled only as a value"; what is in the parentheses was decided by whether an error occurred and whether more rows than the rows whose name equals input came back. In the first item of the result table, the value of the combination passed with ? is the text passed with setString in the code. That this value is in ps separately from the SQL text was checked from the form in which H2 shows ps as a string (the SQL text followed by {1: value}). In this form, the single quote inside the value is written twice because that is how H2 shows a value as an SQL text value, and it does not mean the value was put into the SQL text. As facts that the step sentences rely on, we also checked by running that this SELECT is not in the execution record of the database at the point after prepareStatement and setString and at the point after createStatement, and that ps has 1 ? place while the attached SQL has 0 ? places (the execution record shows no new record once it has been read, so we created a separate database for each point and read it only once). The error of the combination that attached O'Neil is H2's SQLState 42000 (syntax error), the error position in the message was right after Neil, and the place where the error occurred was H2's reading of a string (Tokenizer.readCharacterString). The explanation on the screen ("the second quote ends the string, and the last quote opens a string that is not closed") is an interpretation fitted to this observation and to the H2 documentation that a quote inside a string must be written twice, and we did not trace directly how H2 splits the text. This error code is H2's, so it may differ on MySQL. How the SQL text is left in a real database log, and the order of the result rows (an order the database decides because there is no ORDER BY), are only what we saw in H2. We opened the documents directly. We followed the OWASP SQL Injection Prevention Cheat Sheet on these points: attaching strings and building queries from user input is dangerous (What Is a SQL Injection Attack?); with prepared statements the SQL is decided first and the values are passed later, and the database tells code and data apart whatever the input is (Defense Option 1); and the Java example uses ? in the SELECT and passes the value with setString, and an input such as tom' or '1'='1 only looks for a name equal to that whole text (Safe Java Prepared Statement Example). In PreparedStatement of the Java SE 21 API documentation, we checked the explanation that it is a precompiled SQL statement, that the setter methods set the value of a ? place (an IN parameter), and that the place numbers start from 1; and in Statement, that it is an object used for executing a static SQL statement and that executeQuery(String) returns one ResultSet. The PreparedStatement documentation does not say how setString handles quotes, so the fact that the value is handled regardless of quotes is not something the documentation decides but a behavior we observed on this H2. On the grammar page of the H2 documentation we checked that a string value starts and ends with a single quote and that a single quote inside it is written twice, and in String Literals of the MySQL 8.4 documentation we checked the same rule (MySQL also allows putting \ before the quote). We read all the opened documents through the summaries that the tool returned, and we did not read the whole original text. To keep the code on the screen short, it does not have the lines that close the connection and the ResultSet or the code that handles exceptions, and that the program stops at the line where the exception occurred and the lines below do not run was checked from the output being empty. This lab covers only SQL injection, and it does not cover input validation, escaping on output, permissions of the database account, other techniques used in real attacks, or cases where input is read as code in places other than SQL (HTML, commands, and so on). Web attacks in general (including XSS and CSRF) are covered in "Three web attacks", and JdbcTemplate, which reduces the repeated code of JDBC where you write SQL directly, in "JdbcTemplate and exception translation".

Previous lab: Origins · Next lab: Processes