Package com.tagmatasecurity.safequerylib

The SafeQueryLib package contains the SafeQuery library, which provides a safe mechanism for constructing SQL query strings dynamically, thereby preventing SQL Injection attacks.

See: Description

Package com.tagmatasecurity.safequerylib Description

The SafeQueryLib package contains the SafeQuery library, which provides a safe mechanism for constructing SQL query strings dynamically, thereby preventing SQL Injection attacks.
Version:
1.0

Background

Traditionally, SQL queries are constructed dynamically by combining constant strings with program data. The constant strings contain the structural elements of the SQL query, and the data is converted to string form and then concatenated with the constant strings. Subsequently, the completed string is transmitted to the database server by way of a connector library for execution.

This approach is simple and straightforward but suffers from one fatal flaw. By the time the query string arrives at the server, the underlying parts have become commingled into an inseparable whole. Thus, the database server has no way of determining which parts of the query were constructed originally from constant strings and which parts represent program data. Consequently, the database server is unable to filter or otherwise validate the original data portions of the query. It is this core problem that makes SQL Injection attacks possible.

Although researchers have tried a great variety of approaches for detecting or preventing SQL Injection attacks, the SafeQuery library takes a fundamental approach by storing the information regarding which portions of the query were added intentionally by the programmer, such as the constant strings, and which portions were derived from data, such as may have come from user input. Once this information is stored, injection attacks can be prevented easily.

This initial version of the SafeQuery library renders the query safe from injection attacks by automatically converting the simple concatenated query string into a PreparedStatement object. Notably, the user of the SafeQuery library is never exposed to the PrepardStatement object and is not required to learn how it works. In fact, the user can continue to construct queries dynamically as before, with just a few simple grammatical changes. More importantly though, the SafeQuery approach can readily be integrated into legacy code because of how remarkably similar its API calls are to traditional dynamic query string construction.

How to Use the SafeQuery Library-- Java version

The SafeQuery class is the sole class from the SafeQuery library that you will use in your code to create queries. To use the library, you must first create a SafeQuery object. Before calling the constructor of a SafeQuery object however, you must first create a connection object that connects your application to your database. Details of how to do so can be found in the JDBC tutorial on the Java website, and an example is provided below. The connection object is passed to the constructor of the SafeQuery object.:

Example of Creating a Connection Object

        Connection conn= null;
        try 
        {
                Class.forName( "com.mysql.jdbc.Driver" );
                String url= "jdbc:mysql://localhost:3306/safe_query_test";
                String user= "database-user";
                String passwd= "db-user-passwd";
                conn= DriverManager.getConnection( url, user, passwd );
        }
        catch( ClassNotFoundException e ) // requ9ired in case connector library is not found
        {
                System.err.println( "Class not found; message: " + e.getMessage() );
        }
        catch ( SQLException e ) // required in case credentials are wrong
        {
                System.err.println( e.getMessage() );
        }

        SafeQuery query= new SafeQuery( conn );
 

In traditional query string concatenation, you construct a query string by adding all of the desired pieces in string format, pass the query string to a call to the appropriate execute method, and then retrieve the results. The SafeQuery library uses the same basic approach but divides the concatenation phase into two distinct tasks: adding the structure of the query and adding the data. This slightly modified approach is reflected by the names of the two predominant methods, addStructure() and addData(). Because of the design of string concatenation in Java, the SafeQuery class relies solely on named methods to construct the query string; whereas, in traditional query string construction, you could, and probably do, use the "+" operator to concatenate strings. Thus, to add structure to a query, you pass a string to the addStructure() method:

        query.addStructure( "Select * from users where id = ");
 
To add data, you typically pass your data to the addData() method:
        query.addData( 2 );
 

Because the addData() method is overloaded, you do not need to tell it what type of data you are passing to the call. Please note that you must not add quote marks to your data unless it comprises part of your data.

Additionally, there are two variations on adding data. First, you can set a data value to null by calling the addNull() method. You must pass the corresponding SQL data type to this method; these types are found in the java.sql.Types class. The other exception is that you may call addNData() with a string, which will be converted by the database server to the national character set. For more details, please see the documentation of your database server on configuring a national data set.

Once you passed all of the structural pieces and data to the SafeQuery object, you then call the appropriate execute method. For INSERT, UPDATE, or DELETE queries, as well as query strings that manipulate the structure of your database (such as when adding a table by use of CREATE TABLE), you must call either the generic execute() method or call executeUpdate(). For retrieving database records, you may use execute() or executeSelect().

In order to retrieve the results of your query, you must call either getResultSet() to retrieve the current ResultSet object or getUpdateCount() to obtain the number of rows affected. You must call getMoreResults() to advance the database server to the next result set or row count. For more information about the results of queries, please see the Java JDBC tutorial.

Finally, you may call the clear() method on the SafeQuery object in order to remove all of the structure and data added. You can then reuse the object to create another query. Please note that if you have used the SafeQuery object successfully to execute a query, then construct a new query, but execution fails, the results of the previous query are destroyed. You can, however, create multiple SafeQuery objects.

Complete code example:

import com.tagmatasecurity.SafeQuery;

import java.sql.*;

public class SafeQueryTest
{
        public static void main( String [] args )
        {
                // create a Connection object to connect to the database
                Connection conn= null;
                try 
                {
                        Class.forName( "com.mysql.jdbc.Driver" );
                        String url= "jdbc:mysql://localhost:3306/safe_query_test";
                        String user= "safe-query-agent";
                        String passwd= "safe-query-test";
                        conn= DriverManager.getConnection( url, user, passwd );
                        //System.err.println( "Connection established!!!" );
                }
                catch( ClassNotFoundException e )
                {
                        System.err.println( "Class not found; message: " + e.getMessage() );
                }
                catch ( SQLException e )
                {
                        System.err.println( e.getMessage() );
                }

                SafeQuery query= new SafeQuery( conn );
                query.addStructure( "Select * from users where id > ");
                query.addData( 2 );
                query.addStructure( " and dept = " );
                query.addData( "Entertainment" );
                try
                {
                        if ( query.execute() )
                        {
                                // one or more result sets present
                                do
                                {
                                        outputResults( query.getResultSet() );
                                } while( query.getMoreResults() );
                        }
                        else
                        {
                                // one or more row counts returned, or no results
                                int row_count= query.getUpdateCount();
                                if ( row_count > -1 )
                                {
                                        do
                                        {
                                                System.out.println( "Row count: " + row_count );
                                                query.getMoreResults();
                                                row_count= query.getUpdateCount();
                                        } while( row_count > -1 );
                                }
                                else
                                        System.out.println( "[no rows selected or changed]" );
                        }                                                       
                }
                catch ( SQLException e )
                {
                        System.err.println( e.getMessage() );
                }               
        }       
        public static void outputResults( ResultSet set ) throws SQLException
        {
                if ( set == null )
                        return; // bail         
                while ( set.next() )
                {
                        System.out.print( set.getInt(1) );
                        System.out.print( " " + set.getString(2) );
                        System.out.println( " " + set.getString(3) );
                }
        }
}
 

Internal Information About the SafeQuery Library

You can obtain the source code for the SafeQuery library from Tagmata Software Security. Once you have the source, you will see that the SafeQuery class depends on a number of small classes that encapsulate the handling for the data types used and one class that handles the structure of the query. Conceptually, the SafeQuery library constructs a query in segments, which compromise the "atoms" of a somewhat more complex query "molecule," so to speak. A segment may contain a constant string or any type of data. All of the data handler classes are implementations of the DataSafeQuerySegment interface, which contains a single method, setData(). The purpose of each data handler class is to store the data and later call the appropriate PreparedStatement method to bind the data to the appropriate parameter in the query string. The StructureSafeQuerySegment class merely stores the structural pieces of the query for later assembly.

If you wish to modify the SafeQuery library, you must learn the details of how Prepared Statements work, generally, and how the PreparedStatement class, specifically, is utilized. Also, please note the terms for modification of the library in the Tagmata Open and Host Software License.