sapphire.util

Class SafeSQL

java.lang.Object

sapphire.util.SafeSQL

public class SafeSQL
extends java.lang.Object

This utility class is designed to facilitate converting queryProcessor.getSQLDataSet call to getPreparedSQLDataSet calls to prevent SQL injection attack and improve database performance.

Constructor Summary

Constructors

Constructor and Description
SafeSQL()

Method Summary

Modifier and Type	Method and Description
java.lang.String	addIn(java.util.Collection<java.lang.String> collection) Adds literals in collection to bind variables in SQL
java.lang.String	addIn(java.lang.String valuelist) Replace a single literal in SQL and remember the value
java.lang.String	addIn(java.lang.String valuelist, java.lang.String delimiter) Replace a single literal in SQL and remember the value
java.lang.String	addVar(java.lang.Object value) Replace a single literal in SQL and remember the value
static java.lang.String	convertToSQLInClause(java.lang.String input, java.lang.String delimiter, boolean isOracle) Utility method to convert String input of a list of items separated by delimiter to be used as literals with escaped in a SQL in clause
static java.lang.String	encodeForSQL(java.lang.String input, boolean isOracle) Utility method to encode String literal when building query where clause for SDIRequest as PreparedStatement cannot be used in this case
java.lang.String	getPreparedSQL()
java.lang.Object[]	getValues() Return all previously added values since instantiation or last reset calle as an object array from calling addValue and addIn calls to be use as bind variable values in the QueryProcessor getPreparedSQLDataSet call
static java.lang.Object[]	joinArrays(java.lang.Object[] array1, java.lang.Object[] array2) Utility method to join two object arrays.
static java.lang.String	replaceAllWithInVars(java.lang.String query, java.lang.String tokenToReplace, java.lang.String replaceWithvaluelist, java.lang.String listdelimitor, SafeSQL safeSQL) Utility method to allow the passed in SafeSQL to replace any replaced token with ?,?,?,...
static java.lang.String	replaceAllWithVars(java.lang.String query, java.lang.String tokenToReplace, java.lang.String replaceWithvalue, SafeSQL safeSQL) Utility method to allow the passed in SafeSQL to replace any replaced token with ? and registerd the replace value to be included in the getValues() return.
void	reset() Reset the bind variable values for building a new SQL.
void	setPreparedSQL(java.lang.String preparedSQL) Set the preparedSQL string.

Methods inherited from class java.lang.Object

clone, equals, finalize, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait

Constructor Detail

SafeSQL

public SafeSQL()

Method Detail

addVar

public java.lang.String addVar(java.lang.Object value)

Replace a single literal in SQL and remember the value

Parameters:

value - The value in a sql literal to replace with a ?

Returns:

a string "?"

addIn

public java.lang.String addIn(java.lang.String valuelist)

Replace a single literal in SQL and remember the value

Parameters:

valuelist - The list of literal values delimited by "','" or "', N'" in a sql in clause to replace with a corresponding number of "?,?,?.." and remember the values if valuelist start with ' or N' it will be trimmed. if valuelist ends with ', it will be trimmed.

Returns:

a string "?,?,?.."

addIn

public java.lang.String addIn(java.lang.String valuelist,
                              java.lang.String delimiter)

Replace a single literal in SQL and remember the value

Parameters:

valuelist - The list of literal values delimited by the delimiter param in a sql in clause to replace with a corresponding number of "?,?,?.." and remember the values

delimiter -

Returns:

a string "?,?,?.."

addIn

public java.lang.String addIn(java.util.Collection<java.lang.String> collection)

Adds literals in collection to bind variables in SQL

Parameters:

collection - The collection of literal values

Returns:

a string "?,?,?.."

getValues

public java.lang.Object[] getValues()

Return all previously added values since instantiation or last reset calle as an object array from calling addValue and addIn calls to be use as bind variable values in the QueryProcessor getPreparedSQLDataSet call

Returns:

bind variable value array

reset

public void reset()

Reset the bind variable values for building a new SQL. This must be done after before using the instance that has been used in a previous getPreparedSQLDataSet call already

getPreparedSQL

public java.lang.String getPreparedSQL()

Returns:

the preparedSQL set by the setPreparedSQL method.

setPreparedSQL

public void setPreparedSQL(java.lang.String preparedSQL)

Set the preparedSQL string. This is useful for a method wraps all prepared sql building logic and return the SafeSQL instance to be used in the getPreparedSqlDataSet( safeSQL.getPreparedSQL(), safeSQL.getValues() )

Parameters:

preparedSQL -

replaceAllWithVars

public static java.lang.String replaceAllWithVars(java.lang.String query,
                                                  java.lang.String tokenToReplace,
                                                  java.lang.String replaceWithvalue,
                                                  SafeSQL safeSQL)

Utility method to allow the passed in SafeSQL to replace any replaced token with ? and registerd the replace value to be included in the getValues() return.

Parameters:

query - the input query string

tokenToReplace - the string token in the query to replace with the replaceWithvalue. It's important to include the single quote as part of the token. For example, when replace with a string literal the token is "[sdcid]", the token should be changed to "'[sdcid]'" when replace it with bind variables. If original token is something like '[sdcid]%', don't use this method

replaceWithvalue -

safeSQL - - the safeSQL object to insert ? in place of tokens and remember the replaceWithvalue in bind var values when safeSQL.getValues() is called

replaceAllWithInVars

public static java.lang.String replaceAllWithInVars(java.lang.String query,
                                                    java.lang.String tokenToReplace,
                                                    java.lang.String replaceWithvaluelist,
                                                    java.lang.String listdelimitor,
                                                    SafeSQL safeSQL)

Utility method to allow the passed in SafeSQL to replace any replaced token with ?,?,?,... and registerd the replace values to be included in the getValues() return.

Parameters:

query - the input query string

tokenToReplace - the string token in the query to replace with the replaceWithvalue. It's important to include the single quote as part of the token. For example, when replace with a string literal the token is "[sdcid]", the token should be changed to "'[sdcid]'" when replace it with bind variables. If original token is something like '[sdcid]%', don't use this method

replaceWithvaluelist - - semicolon separated list values to replace with bind variables in the inclause token

safeSQL - - the safeSQL object to insert ? in place of tokens and remember the replaceWithvalue in bind var values when safeSQL.getValues() is called

joinArrays

public static java.lang.Object[] joinArrays(java.lang.Object[] array1,
                                            java.lang.Object[] array2)

Utility method to join two object arrays. This is useful when collection bind variables from build sql fragments

Parameters:

array1 - the first array

array2 - the second array

encodeForSQL

public static java.lang.String encodeForSQL(java.lang.String input,
                                            boolean isOracle)

Utility method to encode String literal when building query where clause for SDIRequest as PreparedStatement cannot be used in this case

Parameters:

input - the String to be encoded

convertToSQLInClause

public static java.lang.String convertToSQLInClause(java.lang.String input,
                                                    java.lang.String delimiter,
                                                    boolean isOracle)

Utility method to convert String input of a list of items separated by delimiter to be used as literals with escaped in a SQL in clause

Parameters:

input - the String to be converted