edu.osu.cse.SQLprevention
Class SQLGuard

java.lang.Object
  extended by edu.osu.cse.SQLprevention.SQLGuard

public class SQLGuard
extends java.lang.Object

SQLGuard provides an interface for injection-safety in SQL strings.
By using a parse tree representation of the SQL string, the class can compare the structure both before and after user input has been supplied, and raise an error if the two trees do not match.
SQLGuard is part of a Java implementation of the parse tree validation technique proposed by Buehrer, Weide and Sivilotti. It is designed to eliminate SQL Injection attacks in web applications.
This implementation makes use of the publically available SQL parser for Java, ZQL (http://www.experlog.com/gibello/zql/).

How To Use:
(install ZQL)
Connection Conn = SafeDriverManager.getConnection("jdbc:mysql://localhost/database1?user=user1&password=pass");
String query = SQLGuard.init() + "SELECT * FROM table where col1 = " + SQLGuard.wrap(id) ;
Statement s2 = Conn.createStatement();
RS = s2.executeQuery(query);

Copyright 2005 The Ohio State University

Version:
0.6, August 28, 2005.
Author:
Paolo Sivilotti., Greg Buehrer.

Field Summary
private static java.util.concurrent.ConcurrentHashMap keys
          a concurrent hash table to store thread-key pairs.
private static int keySize
          the number of bits to use for keys
private static java.lang.String lastQuery
          an SQL string of the last query executed through SQLGuard
private static java.util.Random rand
          used to generate random keys
 
Constructor Summary
SQLGuard()
          The default constructor, it instantiates the keys hashmap.
 
Method Summary
(package private) static boolean check(java.lang.String sql)
          Confirms well-formedness of an SQL string.
private static boolean compareFrom(java.util.Vector from1, java.util.Vector from2)
          Confirms well-formedness of an SQL string's FROM clause.
private static boolean compareFromItem(Zql.ZFromItem from1, Zql.ZFromItem from2)
          Confirms whether two FROM items are a match.
private static boolean compareSelect(java.util.Vector sel1, java.util.Vector sel2)
          Compares two SELECT vectors, returning false if there is not an exact match.
private static boolean compareSelectItem(Zql.ZSelectItem sel1, Zql.ZSelectItem sel2)
          Confirms whether two SELECT items are equal.
private static boolean compareWhere(Zql.ZExp where1, Zql.ZExp where2)
          Confirms well-formedness of an SQL string's WHERE clause.
private static boolean compareZExpression(Zql.ZExp exp1, Zql.ZExp exp2)
          Confirms whether two EXPRESSIONS are a match.
static int getKeySize()
          gets the size of the key (in bits).
static java.lang.String getLastQuery()
          Gets the last query string which was executed through SQLGuard.
static java.lang.String init()
          Generates a new key for this query.
(package private) static java.lang.String prepareForQuery(java.lang.String sql)
          Removes the key from the sql string, and then removes all wrappings from the query.
static java.lang.String removeWrappers(java.lang.String sql, java.lang.String replacement, java.lang.String local_key)
          Unwraps all wrappings in the sql query.
static void setKeySize(int keySize)
          Sets the keySize in bits, must be a multiple of 4.
static void setLastQuery(java.lang.String lastQuery)
           
static java.lang.String wrap(boolean n)
          Wraps a boolean variable.
static java.lang.String wrap(char n)
          Wraps a char variable.
static java.lang.String wrap(double n)
          Wraps a double variable.
static java.lang.String wrap(float n)
          Wraps a float variable.
static java.lang.String wrap(int n)
          Wraps an int variable.
static java.lang.String wrap(long n)
          Wraps a long variable.
static java.lang.String wrap(short n)
          Wraps a short variable.
static java.lang.String wrap(java.lang.String s)
          Wraps a string variable.
 
Methods inherited from class java.lang.Object
clone, equals, finalize, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait
 

Field Detail

keys

private static java.util.concurrent.ConcurrentHashMap keys
a concurrent hash table to store thread-key pairs. The defaults are used, namely 16 positions and a .75 fill factor.


keySize

private static int keySize
the number of bits to use for keys


lastQuery

private static java.lang.String lastQuery
an SQL string of the last query executed through SQLGuard


rand

private static java.util.Random rand
used to generate random keys

Constructor Detail

SQLGuard

SQLGuard()
The default constructor, it instantiates the keys hashmap.

Method Detail

init

public static java.lang.String init()
Generates a new key for this query. This key will be used for wrapping user input in the sql query. Each new call to init creates a new key for the calling thread.

Returns:
The key represented as a string, which must be appended to front of the querystring.

wrap

public static java.lang.String wrap(java.lang.String s)
Wraps a string variable. Adds the key to either side of the variable. For example a3e41831bc2143e1grega3e41831bc2143e1, where a3e41831bc2143e1 is the key, and greg is the variable.

Parameters:
s - a string representing a user supplied portion of the sql query
Returns:
a wrapped string

wrap

public static java.lang.String wrap(long n)
Wraps a long variable. Adds the key to either side of the variable. For example a3e41831bc2143e1555a3e41831bc2143e1, where a3e41831bc2143e1 is the key, and 555 is the variable.

Parameters:
n - a long, which is a user-supplied portion of the sql query
Returns:
a wrapped string representation of the input

wrap

public static java.lang.String wrap(double n)
Wraps a double variable. Adds the key to either side of the variable. For example a3e41831bc2143e1555.25a3e41831bc2143e1, where a3e41831bc2143e1 is the key, and 555.25 is the variable.

Parameters:
n - a double, which is a user-supplied portion of the sql query
Returns:
a wrapped string representation of the input

wrap

public static java.lang.String wrap(char n)
Wraps a char variable. Adds the key to either side of the variable. For example a3e41831bc2143e1Xa3e41831bc2143e1, where a3e41831bc2143e1 is the key, and X is the variable.

Parameters:
n - a char, which is a user-supplied portion of the sql query
Returns:
a wrapped string representation of the input

wrap

public static java.lang.String wrap(short n)
Wraps a short variable. Adds the key to either side of the variable. For example a3e41831bc2143e1555a3e41831bc2143e1, where a3e41831bc2143e1 is the key, and 555 is the variable.

Parameters:
n - a short, which is a user-supplied portion of the sql query
Returns:
a wrapped string representation of the input

wrap

public static java.lang.String wrap(boolean n)
Wraps a boolean variable. Adds the key to either side of the variable. For example a3e41831bc2143e1truea3e41831bc2143e1, where a3e41831bc2143e1 is the key, and true is the variable.

Parameters:
n - a boolean, which is a user-supplied portion of the sql query
Returns:
a wrapped string representation of the input

wrap

public static java.lang.String wrap(float n)
Wraps a float variable. Adds the key to either side of the variable. For example a3e41831bc2143e1555.25a3e41831bc2143e1, where a3e41831bc2143e1 is the key, and 555.25 is the variable.

Parameters:
n - a float, which is a user-supplied portion of the sql query
Returns:
a wrapped string representation of the input

wrap

public static java.lang.String wrap(int n)
Wraps an int variable. Adds the key to either side of the variable. For example a3e41831bc2143e1555a3e41831bc2143e1, where a3e41831bc2143e1 is the key, and 555 is the variable.

Parameters:
n - an int, which is a user-supplied portion of the sql query
Returns:
a wrapped string representation of the input

prepareForQuery

static java.lang.String prepareForQuery(java.lang.String sql)
Removes the key from the sql string, and then removes all wrappings from the query.

Parameters:
sql - is an sql query
Returns:
a clean sql query

removeWrappers

public static java.lang.String removeWrappers(java.lang.String sql,
                                              java.lang.String replacement,
                                              java.lang.String local_key)
Unwraps all wrappings in the sql query.

Parameters:
sql - is an sql query replacement is a regular expression of what to put back in it is a subgroup of the pattern, namely the one we wrapped
Returns:
a clean sql query

check

static boolean check(java.lang.String sql)
Confirms well-formedness of an SQL string. Compares the parse trees for the SQL string before and after the user input has been inserted. If the two trees do not match exactly, it returns false.

Parameters:
sql - a string representing an SQL query, decorated with special markers to set off user input
Returns:
true if and only if the parse trees match (ie the parse trees with and without the special markers)

compareSelect

private static boolean compareSelect(java.util.Vector sel1,
                                     java.util.Vector sel2)
Compares two SELECT vectors, returning false if there is not an exact match. The two must be the same size, and each pair must match.

Parameters:
sel1 - The SELECT vector with empty leaf nodes.
sel2 - The SELECT vector with user input supplied.
Returns:
true if they match, false if the vectors do not match.

compareSelectItem

private static boolean compareSelectItem(Zql.ZSelectItem sel1,
                                         Zql.ZSelectItem sel2)
Confirms whether two SELECT items are equal. They must have the same number of subclauses.

Parameters:
sel1 - The first SELECT item.
sel2 - The second SELECT item, the one filled in by the user.
Returns:
true if and only if the FROM items match.

compareFrom

private static boolean compareFrom(java.util.Vector from1,
                                   java.util.Vector from2)
Confirms well-formedness of an SQL string's FROM clause. They must have the same number of subclauses.

Parameters:
from1 - The first FROM clause (not filled in with user input).
from2 - The second FROM clause, filled in with user input.
Returns:
true if and only if the FROM clause structure was maintained.

compareFromItem

private static boolean compareFromItem(Zql.ZFromItem from1,
                                       Zql.ZFromItem from2)
Confirms whether two FROM items are a match. They must have the same number of subclauses, and for each clause:
a) the column and table names match exactly
b) or both column names are missing and the table names match.

Parameters:
from1 - The first FROM item.
from2 - The second FROM item, the one filled in by the user
Returns:
true if and only if the FROM items match

compareZExpression

private static boolean compareZExpression(Zql.ZExp exp1,
                                          Zql.ZExp exp2)
Confirms whether two EXPRESSIONS are a match. It is the guts of the comparing, since most clauses are expressions.
It is recursive
They must have the same number of subclauses, and for each clause
a) they must both be the same constant
b) or they must both be expressions, with matching operators and sub expressions
c) or the first must be a ? and the second must be a constant (a value).

Parameters:
exp1 - The first EXPRESSION item.
exp2 - The second EXPRESSION item, the one filled in by the user
Returns:
true if and only if the EXPRESSION items match

compareWhere

private static boolean compareWhere(Zql.ZExp where1,
                                    Zql.ZExp where2)
Confirms well-formedness of an SQL string's WHERE clause.

Parameters:
where1 - The first WHERE clause (not filled in with user input).
where2 - The second WHERE clause, filled in with user input.
Returns:
true if and only if the WHERE clause structure was maintained.

getKeySize

public static int getKeySize()
gets the size of the key (in bits).

Returns:
Returns the keySize in bits.

setKeySize

public static void setKeySize(int keySize)
Sets the keySize in bits, must be a multiple of 4. The default is 64 bits.

Parameters:
keySize - The keySize to set (in bits).

getLastQuery

public static java.lang.String getLastQuery()
Gets the last query string which was executed through SQLGuard. It is set after a successful check call, and before statement.execute is called. Since it is global, multiple threads share it.

Returns:
Returns the lastQuery.

setLastQuery

public static void setLastQuery(java.lang.String lastQuery)
Parameters:
lastQuery - The lastQuery to set.