001: /*
002: * Copyright 2004-2008 H2 Group. Licensed under the H2 License, Version 1.0
003: * (license2)
004: * Initial Developer: H2 Group
005: */
006: package org.h2.samples;
007:
008: import java.io.BufferedReader;
009: import java.io.InputStreamReader;
010: import java.sql.Connection;
011: import java.sql.DriverManager;
012: import java.sql.PreparedStatement;
013: import java.sql.ResultSet;
014: import java.sql.SQLException;
015: import java.sql.Statement;
016:
017: /**
018: * SQL Injection is a common security vulnerability for applications that use
019: * database. It is one of the most common security vulnerabilities for web
020: * applications today. This sample application shows how SQL injection works,
021: * and how to protect the application from it.
022: */
023: public class SQLInjection {
024:
025: private Connection conn;
026: private Statement stat;
027:
028: public static void main(String[] args) throws Exception {
029: new SQLInjection().run("org.h2.Driver", "jdbc:h2:test", "sa",
030: "sa");
031: // new SQLInjection().run("org.postgresql.Driver",
032: // "jdbc:postgresql:jpox2", "sa", "sa");
033: // new SQLInjection().run("com.mysql.jdbc.Driver",
034: // "jdbc:mysql://localhost/test", "sa", "sa");
035: // new SQLInjection().run("org.hsqldb.jdbcDriver",
036: // "jdbc:hsqldb:test", "sa", "");
037: // new SQLInjection().run(
038: // "org.apache.derby.jdbc.EmbeddedDriver",
039: // "jdbc:derby:test3;create=true", "sa", "sa");
040: }
041:
042: /**
043: * Run the test against the specified database.
044: *
045: * @param driver the JDBC driver name
046: * @param url the database URL
047: * @param user the user name
048: * @param password the password
049: */
050: void run(String driver, String url, String user, String password)
051: throws Exception {
052: Class.forName(driver);
053: conn = DriverManager.getConnection(url, user, password);
054: stat = conn.createStatement();
055: try {
056: stat.execute("DROP TABLE USERS");
057: } catch (SQLException e) {
058: // ignore
059: }
060: stat.execute("CREATE TABLE USERS(ID INT PRIMARY KEY, "
061: + "NAME VARCHAR(255), PASSWORD VARCHAR(255))");
062: stat.execute("INSERT INTO USERS VALUES(1, 'admin', 'super')");
063: stat.execute("INSERT INTO USERS VALUES(2, 'guest', '123456')");
064: stat.execute("INSERT INTO USERS VALUES(3, 'test', 'abc')");
065:
066: loginByNameInsecure();
067:
068: if (url.startsWith("jdbc:h2:")) {
069: loginStoredProcedureInsecure();
070: limitRowAccess();
071: }
072:
073: loginByNameSecure();
074:
075: if (url.startsWith("jdbc:h2:")) {
076: stat.execute("SET ALLOW_LITERALS NONE");
077: stat.execute("SET ALLOW_LITERALS NUMBERS");
078: stat.execute("SET ALLOW_LITERALS ALL");
079: }
080:
081: loginByIdInsecure();
082: loginByIdSecure();
083:
084: try {
085: stat.execute("DROP TABLE ITEMS");
086: } catch (SQLException e) {
087: // ignore
088: }
089:
090: stat.execute("CREATE TABLE ITEMS(ID INT PRIMARY KEY, "
091: + "NAME VARCHAR(255), ACTIVE INT)");
092: stat.execute("INSERT INTO ITEMS VALUES(0, 'XBox', 0)");
093: stat.execute("INSERT INTO ITEMS VALUES(1, 'XBox 360', 1)");
094: stat.execute("INSERT INTO ITEMS VALUES(2, 'PlayStation 1', 0)");
095: stat.execute("INSERT INTO ITEMS VALUES(3, 'PlayStation 2', 1)");
096: stat.execute("INSERT INTO ITEMS VALUES(4, 'PlayStation 3', 1)");
097:
098: listActiveItems();
099:
100: if (url.startsWith("jdbc:h2:")) {
101: stat.execute("DROP CONSTANT IF EXISTS TYPE_INACTIVE");
102: stat.execute("DROP CONSTANT IF EXISTS TYPE_ACTIVE");
103: stat.execute("CREATE CONSTANT TYPE_INACTIVE VALUE 0");
104: stat.execute("CREATE CONSTANT TYPE_ACTIVE VALUE 1");
105: listActiveItemsUsingConstants();
106: }
107:
108: listItemsSortedInsecure();
109: listItemsSortedSecure();
110:
111: if (url.startsWith("jdbc:h2:")) {
112: listItemsSortedSecureParam();
113: storePasswordHashWithSalt();
114: }
115:
116: conn.close();
117: }
118:
119: /**
120: * Simulate a login using an insecure method.
121: */
122: void loginByNameInsecure() throws Exception {
123: System.out.println("Insecure Systems Inc. - login");
124: String name = input("Name?");
125: String password = input("Password?");
126: ResultSet rs = stat
127: .executeQuery("SELECT * FROM USERS WHERE " + "NAME='"
128: + name + "' AND PASSWORD='" + password + "'");
129: if (rs.next()) {
130: System.out.println("Welcome!");
131: } else {
132: System.out.println("Access denied!");
133: }
134: }
135:
136: /**
137: * Utility method to get a user record given the user name and password.
138: * This method is secure.
139: *
140: * @param conn the database connection
141: * @param userName the user name
142: * @param password the password
143: * @return a result set with the user record if the password matches
144: */
145: public static ResultSet getUser(Connection conn, String userName,
146: String password) throws Exception {
147: PreparedStatement prep = conn
148: .prepareStatement("SELECT * FROM USERS WHERE NAME=? AND PASSWORD=?");
149: prep.setString(1, userName);
150: prep.setString(2, password);
151: return prep.executeQuery();
152: }
153:
154: /**
155: * Utility method to change a password of a user.
156: * This method is secure, except that the old password is not checked.
157: *
158: * @param conn the database connection
159: * @param userName the user name
160: * @param password the password
161: * @return the new password
162: */
163: public static String changePassword(Connection conn,
164: String userName, String password) throws Exception {
165: PreparedStatement prep = conn
166: .prepareStatement("UPDATE USERS SET PASSWORD=? WHERE NAME=?");
167: prep.setString(1, password);
168: prep.setString(2, userName);
169: prep.executeUpdate();
170: return password;
171: }
172:
173: /**
174: * Simulate a login using an insecure method.
175: * A stored procedure is used here.
176: */
177: void loginStoredProcedureInsecure() throws Exception {
178: System.out
179: .println("Insecure Systems Inc. - login using a stored procedure");
180: stat
181: .execute("CREATE ALIAS IF NOT EXISTS "
182: + "GET_USER FOR \"org.h2.samples.SQLInjection.getUser\"");
183: stat
184: .execute("CREATE ALIAS IF NOT EXISTS "
185: + "CHANGE_PASSWORD FOR \"org.h2.samples.SQLInjection.changePassword\"");
186: String name = input("Name?");
187: String password = input("Password?");
188: ResultSet rs = stat.executeQuery("CALL GET_USER('" + name
189: + "', '" + password + "')");
190: if (rs.next()) {
191: System.out.println("Welcome!");
192: } else {
193: System.out.println("Access denied!");
194: }
195: }
196:
197: /**
198: * Simulate a login using a secure method.
199: */
200: void loginByNameSecure() throws Exception {
201: System.out
202: .println("Secure Systems Inc. - login using placeholders");
203: String name = input("Name?");
204: String password = input("Password?");
205: PreparedStatement prep = conn
206: .prepareStatement("SELECT * FROM USERS WHERE "
207: + "NAME=? AND PASSWORD=?");
208: prep.setString(1, name);
209: prep.setString(2, password);
210: ResultSet rs = prep.executeQuery();
211: if (rs.next()) {
212: System.out.println("Welcome!");
213: } else {
214: System.out.println("Access denied!");
215: }
216: }
217:
218: /**
219: * Sample code to limit access only to specific rows.
220: */
221: void limitRowAccess() throws Exception {
222: System.out.println("Secure Systems Inc. - limit row access");
223: stat.execute("DROP TABLE IF EXISTS SESSION_USER");
224: stat.execute("CREATE TABLE SESSION_USER(ID INT, USER INT)");
225: stat.execute("DROP VIEW IF EXISTS MY_USER");
226: stat.execute("CREATE VIEW MY_USER AS "
227: + "SELECT U.* FROM SESSION_USER S, USERS U "
228: + "WHERE S.ID=SESSION_ID() AND S.USER=U.ID");
229: stat
230: .execute("INSERT INTO SESSION_USER VALUES(SESSION_ID(), 1)");
231: ResultSet rs = stat
232: .executeQuery("SELECT ID, NAME FROM MY_USER");
233: while (rs.next()) {
234: System.out
235: .println(rs.getString(1) + ": " + rs.getString(2));
236: }
237: }
238:
239: /**
240: * Simulate a login using an insecure method.
241: */
242: void loginByIdInsecure() throws Exception {
243: System.out.println("Half Secure Systems Inc. - login by id");
244: String id = input("User ID?");
245: String password = input("Password?");
246: try {
247: PreparedStatement prep = conn
248: .prepareStatement("SELECT * FROM USERS WHERE "
249: + "ID=" + id + " AND PASSWORD=?");
250: prep.setString(1, password);
251: ResultSet rs = prep.executeQuery();
252: if (rs.next()) {
253: System.out.println("Welcome!");
254: } else {
255: System.out.println("Access denied!");
256: }
257: } catch (SQLException e) {
258: System.out.println(e);
259: }
260: }
261:
262: /**
263: * Simulate a login using a secure method.
264: */
265: void loginByIdSecure() throws Exception {
266: System.out.println("Secure Systems Inc. - login by id");
267: String id = input("User ID?");
268: String password = input("Password?");
269: try {
270: PreparedStatement prep = conn
271: .prepareStatement("SELECT * FROM USERS WHERE "
272: + "ID=? AND PASSWORD=?");
273: prep.setInt(1, Integer.parseInt(id));
274: prep.setString(2, password);
275: ResultSet rs = prep.executeQuery();
276: if (rs.next()) {
277: System.out.println("Welcome!");
278: } else {
279: System.out.println("Access denied!");
280: }
281: } catch (Exception e) {
282: System.out.println(e);
283: }
284: }
285:
286: /**
287: * List active items.
288: * The method uses the hard coded value '1', and therefore the database
289: * can not verify if the SQL statement was constructed with user
290: * input or not.
291: */
292: void listActiveItems() throws Exception {
293: System.out
294: .println("Half Secure Systems Inc. - list active items");
295: ResultSet rs = stat
296: .executeQuery("SELECT NAME FROM ITEMS WHERE ACTIVE=1");
297: while (rs.next()) {
298: System.out.println("Name: " + rs.getString(1));
299: }
300: }
301:
302: /**
303: * List active items.
304: * The method uses a constant, and therefore the database
305: * knows it does not contain user input.
306: */
307: void listActiveItemsUsingConstants() throws Exception {
308: System.out.println("Secure Systems Inc. - list active items");
309: ResultSet rs = stat
310: .executeQuery("SELECT NAME FROM ITEMS WHERE ACTIVE=TYPE_ACTIVE");
311: while (rs.next()) {
312: System.out.println("Name: " + rs.getString(1));
313: }
314: }
315:
316: /**
317: * List items using a specified sort order.
318: * The method is not secure as user input is used to construct the
319: * SQL statement.
320: */
321: void listItemsSortedInsecure() throws Exception {
322: System.out.println("Insecure Systems Inc. - list items");
323: String order = input("order (id, name)?");
324: try {
325: ResultSet rs = stat
326: .executeQuery("SELECT ID, NAME FROM ITEMS ORDER BY "
327: + order);
328: while (rs.next()) {
329: System.out.println(rs.getString(1) + ": "
330: + rs.getString(2));
331: }
332: } catch (SQLException e) {
333: System.out.println(e);
334: }
335: }
336:
337: /**
338: * List items using a specified sort order.
339: * The method is secure as the user input is validated before use.
340: * However the database has no chance to verify this.
341: */
342: void listItemsSortedSecure() throws Exception {
343: System.out.println("Secure Systems Inc. - list items");
344: String order = input("order (id, name)?");
345: if (!order.matches("[a-zA-Z0-9_]*")) {
346: order = "id";
347: }
348: try {
349: ResultSet rs = stat
350: .executeQuery("SELECT ID, NAME FROM ITEMS ORDER BY "
351: + order);
352: while (rs.next()) {
353: System.out.println(rs.getString(1) + ": "
354: + rs.getString(2));
355: }
356: } catch (SQLException e) {
357: System.out.println(e);
358: }
359: }
360:
361: /**
362: * List items using a specified sort order.
363: * The method is secure as a parameterized statement is used.
364: */
365: void listItemsSortedSecureParam() throws Exception {
366: System.out.println("Secure Systems Inc. - list items");
367: String order = input("order (1, 2, -1, -2)?");
368: PreparedStatement prep = conn
369: .prepareStatement("SELECT ID, NAME FROM ITEMS ORDER BY ?");
370: try {
371: prep.setInt(1, Integer.parseInt(order));
372: ResultSet rs = prep.executeQuery();
373: while (rs.next()) {
374: System.out.println(rs.getString(1) + ": "
375: + rs.getString(2));
376: }
377: } catch (Exception e) {
378: System.out.println(e);
379: }
380: }
381:
382: /**
383: * This method creates a one way hash from the password
384: * (using a random salt), and stores this information instead of the
385: * password.
386: */
387: void storePasswordHashWithSalt() throws Exception {
388: System.out.println("Very Secure Systems Inc. - login");
389: stat.execute("DROP TABLE IF EXISTS USERS2");
390: stat.execute("CREATE TABLE USERS2(ID INT PRIMARY KEY, "
391: + "NAME VARCHAR, SALT BINARY, HASH BINARY)");
392: stat.execute("INSERT INTO USERS2 VALUES"
393: + "(1, 'admin', SECURE_RAND(16), NULL)");
394: stat.execute("DROP CONSTANT IF EXISTS HASH_ITERATIONS");
395: stat.execute("DROP CONSTANT IF EXISTS HASH_ALGORITHM");
396: stat.execute("CREATE CONSTANT HASH_ITERATIONS VALUE 100");
397: stat.execute("CREATE CONSTANT HASH_ALGORITHM VALUE 'SHA256'");
398: stat
399: .execute("UPDATE USERS2 SET "
400: + "HASH=HASH(HASH_ALGORITHM, STRINGTOUTF8('abc' || SALT), HASH_ITERATIONS) "
401: + "WHERE ID=1");
402: String user = input("user?");
403: String password = input("password?");
404: stat.execute("SET ALLOW_LITERALS NONE");
405: PreparedStatement prep = conn
406: .prepareStatement("SELECT * FROM USERS2 WHERE NAME=? AND "
407: + "HASH=HASH(HASH_ALGORITHM, STRINGTOUTF8(? || SALT), HASH_ITERATIONS)");
408: prep.setString(1, user);
409: prep.setString(2, password);
410: ResultSet rs = prep.executeQuery();
411: while (rs.next()) {
412: System.out.println("name: " + rs.getString("NAME"));
413: System.out.println("salt: " + rs.getString("SALT"));
414: System.out.println("hash: " + rs.getString("HASH"));
415: }
416: stat.execute("SET ALLOW_LITERALS ALL");
417: }
418:
419: /**
420: * Utility method to get user input from the command line.
421: *
422: * @param prompt the prompt
423: * @return the user input
424: */
425: String input(String prompt) throws Exception {
426: System.out.print(prompt);
427: return new BufferedReader(new InputStreamReader(System.in))
428: .readLine();
429: }
430:
431: }
|