01: /*
02: * Copyright 2004-2008 H2 Group. Licensed under the H2 License, Version 1.0
03: * (license2)
04: * Initial Developer: H2 Group
05: */
06: package org.h2.test.db;
07:
08: import java.sql.Connection;
09: import java.sql.PreparedStatement;
10: import java.sql.ResultSet;
11: import java.sql.SQLException;
12: import java.sql.Statement;
13:
14: import org.h2.test.TestBase;
15:
16: /**
17: * Tests the ALLOW_LITERALS feature (protection against SQL injection).
18: */
19: public class TestSQLInjection extends TestBase {
20:
21: Connection conn;
22: Statement stat;
23:
24: public void test() throws Exception {
25: deleteDb("sqlInjection");
26: reconnect("sqlInjection");
27: stat.execute("DROP TABLE IF EXISTS USERS");
28: stat
29: .execute("CREATE TABLE USERS(NAME VARCHAR PRIMARY KEY, PASSWORD VARCHAR, TYPE VARCHAR)");
30: stat.execute("CREATE SCHEMA CONST");
31: stat.execute("CREATE CONSTANT CONST.ACTIVE VALUE 'Active'");
32: stat
33: .execute("INSERT INTO USERS VALUES('James', '123456', CONST.ACTIVE)");
34: check(checkPasswordInsecure("123456"));
35: checkFalse(checkPasswordInsecure("abcdef"));
36: check(checkPasswordInsecure("' OR ''='"));
37: check(checkPasswordSecure("123456"));
38: checkFalse(checkPasswordSecure("abcdef"));
39: checkFalse(checkPasswordSecure("' OR ''='"));
40: stat.execute("SET ALLOW_LITERALS NONE");
41:
42: try {
43: check(checkPasswordInsecure("123456"));
44: error();
45: } catch (SQLException e) {
46: checkNotGeneralException(e);
47: }
48: check(checkPasswordSecure("123456"));
49: checkFalse(checkPasswordSecure("' OR ''='"));
50: conn.close();
51:
52: if (config.memory) {
53: return;
54: }
55:
56: reconnect("sqlInjection");
57:
58: try {
59: check(checkPasswordInsecure("123456"));
60: error("Should fail now");
61: } catch (SQLException e) {
62: checkNotGeneralException(e);
63: }
64: check(checkPasswordSecure("123456"));
65: checkFalse(checkPasswordSecure("' OR ''='"));
66: conn.close();
67: }
68:
69: boolean checkPasswordInsecure(String pwd) throws SQLException {
70: String sql = "SELECT * FROM USERS WHERE PASSWORD='" + pwd + "'";
71: ResultSet rs = conn.createStatement().executeQuery(sql);
72: return (rs.next());
73: }
74:
75: boolean checkPasswordSecure(String pwd) throws Exception {
76: String sql = "SELECT * FROM USERS WHERE PASSWORD=?";
77: PreparedStatement prep = conn.prepareStatement(sql);
78: prep.setString(1, pwd);
79: ResultSet rs = prep.executeQuery();
80: return (rs.next());
81: }
82:
83: private void reconnect(String name) throws Exception {
84: if (!config.memory) {
85: if (conn != null) {
86: conn.close();
87: conn = null;
88: }
89: }
90: if (conn == null) {
91: conn = getConnection(name);
92: stat = conn.createStatement();
93: }
94: }
95: }
|