Talk about the merits of database bind variables (② Merit: Prevention of SQL injection)

2018/7/14 Full revision

====================================================================

** 0. Introduction **

Last post So what are database bind variables? I explained, but this time I would like to tell you the first advantage of using it. In a nutshell, it can prevent SQL injection (* 1).

** 1. Prerequisite case **

I will reissue the table introduced in the previous post.

Information is obtained by specifying the user ID and password from that table, and although the ID knows "11111", put "'OR' A'='A" in place of YYY in PASSWORD. Consider the case of issuing SQL. SQL looks like this.

SELECT * FROM USERTABLE WHERE ID=11111 AND PASSWORD='' OR 'A' = 'A';

Let's see what the result will be with and without the bind variable.

** 2. If you don't use bind variables **

If you do not use bind variables, the above SQL issued will be interpreted as follows:

Next 1. Or 2. If any of the conditions is met, all information is extracted from USERTABLE.
Condition 1.
USERID is 11111 and PASSWORD is blank (corresponds to "ID = 11111 AND PASSWORD =''" in the WHERE clause).

Condition 2.
The value "A" and the value "A" are equal (corresponding to "'A' ='A'" in the WHERE clause).

There is no user information in this table with ID 11111 and PASSWORD blank, so first condition 1. Does not apply to. Second, condition 2 applies to everyone. By the way, the Java code introduced in Last post is modified / added as follows and SQL is issued.

System.out.println("***** main method start *****");
・ ・ ・(Details omitted)・ ・ ・
//Prepare a Statement.
statement = connection.createStatement();
//Declare SQL. Configure SQL with variable id and password.
String sql = "select * from app.usertable where id = 11111 and password = '' OR 'A' = 'A'";
//Get the result.
resultSet = statement.executeQuery(sql);
//Display the result.
while(resultSet.next()) {
                System.out.println("ID：　" + resultSet.getInt(1));
                System.out.println("Full name:" + resultSet.getString(3));
                System.out.println("mail address:" + resultSet.getString(4));
}

・ ・ ・(Details omitted)・ ・ ・
System.out.println("***** main method end *****");

The result is as follows. I tried the DBMS with Derby (JavaDB) and MySQL and got the same result.

***** main method start *****
ID：　22222
Name: Luffy
Email address: [email protected]
ID：　55555
Name: Sakuragi Hanamichi
Email address: [email protected]
ID：　11111
Name: Momotaro Tsurugi
Email address: [email protected]
ID：　33333
Name: Kinni tickle
Email address: [email protected]
***** main method end *****

Oops! ?? I was able to retrieve all the information in the table. Normally, you have to enter the correct values for ID and PASSWORD to get the corresponding information from USERTABLE, but by entering "'OR' A'='A" for PASSWORD, USERTABLE is illegally set. You will be able to retrieve all the information. Here is an example of SQL injection.

** 3. When using bind variables **

So what happens when you use bind variables for this SQL? The Java code introduced in Last post is slightly changed as follows.

System.out.println("***** main method start *****");
・ ・ ・(Details omitted)・ ・ ・
//Declare SQL. For column id and password,?By inserting ", these are declared to be bind variables.
String sql = "select * from app.usertable where id = ? and password = ?";
//Prepare PreparedStatement to issue SQL that stores bind variables.
preparedStatement = connection.prepareStatement(sql);
//Assign a value for id. This "1" refers to the first bind variable id.
preparedStatement.setInt(1, 11111);
//Assign a value for password. This "2" refers to the second bind variable password.
preparedStatement.setString(2, "'' OR 'A' = 'A'");
//Get the result.
resultSet = preparedStatement.executeQuery();
//Display the result.
while(resultSet.next()) {
                System.out.println("ID：　" + resultSet.getInt(1));
                System.out.println("Full name:" + resultSet.getString(3));
                System.out.println("mail address:" + resultSet.getString(4));
}

・ ・ ・(Details omitted)・ ・ ・
System.out.println("***** main method end *****");

The SQL is issued by executing the above code, and the result is as follows. I also tried DBMS with Derby (JavaDB) and MySQL, and the result was the same.

***** main method start *****
***** main method end *****

Oh? Isn't nothing displayed? What the hell happened?

The following flow shows what happens when SQL with bind variables is issued.

(1) The value of the bind variable and the SQL excluding it are issued to the DBMS separately. (2) The value of the bind variable is interpreted before being merged into SQL, and "'" is escaped. In other words, since "'" is interpreted as the character "'", it is converted into the form "''" with two single quotation marks. (3) The value of the bind variable processed in (2) and the SQL are merged and executed.

The SQL executed in ③ is SELECT * FROM USERTABLE WHERE ID=11111 AND PASSWORD=''' OR ''A'' = ''A'; It will be as follows. How is it? You can see that it is different from the SQL when it was injected. That's right. The PASSWORD is interpreted as "'' OR'' A'' =" A "". A user with such a password does not exist in USERTABLE, so it will not be possible to retrieve all information.

The above is an example. There is also a way to escape not only "'" but also ">" and "<", so please refer to the database manual etc. (* 2).

** 4. Conclusion **

By the way, next time at the end of this series, I would like to talk about the second advantage of bind variables.

footnote

(※1) Please refer to the following URL for the explanation about SQL injection. https://ja.wikipedia.org/wiki/SQL%E3%82%A4%E3%83%B3%E3%82%B8%E3%82%A7%E3%82%AF%E3%82%B7%E3%83%A7%E3%83%B3

(※2) Click here for information on Oracle Database. http://www.oracle.com/technetwork/jp/database/articles/pickup/index-1622026-ja.html

(※3) I also referred to the following URL when creating this article.

IPA Information-technology Promotion Agency "How to call secure SQL" http://www.ipa.go.jp/files/000017320.pdf

Hatena Diary [Security] Escape in Oracle http://d.hatena.ne.jp/teracc/20071230