In the previous section, we looked at how user-controlled input gets into an application, and learned the varying methods that are at our disposal to process these data. We also looked at a few simple examples of the dangerous coding behaviors that can ultimately lead to vulnerable applications. The example source code I provided in the previous section should be sufficient to help you understand how developers produce code that is vulnerable to SQL injection. However, the examples were not extensive; each programming language offers a number of different ways to construct and execute SQL statements, and you need to be familiar with all of them. This section of the chapter presents a detailed list of these methods, along with examples of how they are used. We will start with the PHP scripting language.
PHP supports numerous database vendors; visit
http://www.php.net/manual/en/refs.database.vendors.php for a comprehensive list. We will concentrate on just a few common database vendors for the purpose of clarity. The following list details the relevant functions for MySQL, Microsoft SQL Server, Postgres, and Oracle databases:
• mssql_query( ): Sends a query to the currently active database • mysql_query( ): Sends a query to the currently active database
• mysql_db_query( ): Selects a database, and executes a query on it (depreciated in PHP Version 4.0.6)
• oci_parse( ): Parses a statement before it is executed (prior to oci_execute( )/ociexecute( )) • ora_parse( ): Parses a statement before it is executed (prior to ora_exec( ))
• mssql_bind( ): Adds a parameter to a stored procedure (prior to mssql_execute( )) • mssql_execute( ): Executes a stored procedure
• odbc_prepare( ): Prepares a statement for execution (prior to odbc_execute( )) • odbc_execute( ): Executes an SQL statement
• odbc_exec( ): Prepares and executes an SQL statement • pg_query( ): Execute a query (used to be called pg_exec)
• pg_exec( ): Is still available for compatibility reasons, but users are encouraged to use the newer name
• pg_send_query( ): Sends an asynchronous query
• pg_send_query_params( ): Submits a command and separate parameters to the server without waiting for the result(s)
• pg_query_params( ): Submits a command to the server and waits for the result
• pg_send_prepare( ): Sends a request to create a prepared statement with the given parameters, without waiting for completion
• pg_prepare( ): Submits a request to create a prepared statement with the given parameters, and waits for completion
• pg_select( ): Selects records specified by assoc_array
• pg_update( ): Updates records that matches condition with data • pg_insert( ): Inserts the values of an assoc_array into a given table
• pg_delete( ): Deletes records from a table specified by the keys and values in assoc_array The following lines of code demonstrate how you can use these functions in a PHP application:
// mssql_query() - sends a query to the currently active database $result = mssql_query($sql);
// mysql_query() - sends a query to the currently active database $result = mysql_query($sql);
// mysql_db_query() - selects a database, and executes a query on it $result = mysql_db_query($db, $sql);
// oci_parse() - parses a statement before it is executed $stmt = oci_parse($connection, $sql);
ociexecute($stmt);
// ora_parse() - parses a statement before it is executed if (!ora_parse($cursor, $sql)){exit;}
else {ora_exec($cursor);}
// mssql_bind() - adds a parameter to a stored procedure
mssql_bind($stmt, ‘@param’, $variable, SQLVARCHAR, false, false, 100); $result = mssql_execute($stmt);
// odbc_prepare() - prepares a statement for execution $stmt = odbc_prepare($db, $sql);
$result = odbc_execute($stmt);
// odbc_exec() - prepare and execute a SQL statement $result = odbc_exec($db, $sql);
// pg_query - execute a query (used to be called pg_exec) $result = pg_query($conn, $sql);
// pg_exec - is still available for compatibility reasons, but users are encouraged to use the newer name.
$result = pg_exec($conn, $sql);
// pg_send_query - sends asynchronous query pg_send_query($conn, $sql);
// pg_send_query_params - submits a command and separate parameters to the server without waiting for the result(s).
// pg_query_params - submits a command to the server and waits for the result. pg_query_params($conn, $sql, $params)
// pg_send_prepare - sends a request to create a prepared statement with the given parameters, without waiting for completion.
pg_send_prepare($conn, “my_query”, ‘SELECT ∗ FROM table WHERE field = $1’); pg_send_execute($conn, “my_query”, $var);
// pg_prepare - submits a request to create a prepared statement with the given parameters, and waits for completion.
pg_prepare($conn, “my_query”, ‘SELECT ∗ FROM table WHERE field = $1’); pg_execute($conn, “my_query”, $var);
// pg_select - selects records specified by assoc_array which has field=>value $result = pg_select($conn, $table_name, $assoc_array)
// pg_update() - updates records that matches condition with data pg_update($conn, $arr_update, $arr_where);
// pg_insert() - inserts the values of assoc_array into the table specified by table_name. pg_insert($conn, $table_name, $assoc_array)
// pg_delete() - deletes records from a table specified by the keys and values in assoc_array
pg_delete($conn, $table_name, $assoc_array)
Things are a little different in Java. Java makes available the java.sql package and the Java Database Connectivity (JDBC) API for database connectivity; for details on supported vendors, see http://java.sun.com/products/jdbc/driverdesc.html. We will concentrate on just a few common database vendors for the purpose of clarity. The following list details the relevant functions for MySQL, Microsoft SQL Server, PostgreSQL, and Oracle databases:
• createStatement( ): Creates a statement object for sending SQL statements to the database • prepareStatement( ): Creates a precompiled SQL statement and stores it in an object • executeQuery( ): Executes the given SQL statement
• executeUpdate( ): Executes the given SQL statement • execute( ): Executes the given SQL statement
• addBatch( ): Adds the given SQL command to the current list of commands • executeBatch( ): Submits a batch of commands to the database for execution
The following lines of code demonstrate how you can use these functions in a Java application:
// createStatement() - is used to create a statement object that is used for // sending sql statements to the specified database
statement = connection.createStatement();
// PreparedStatement – creates a precompiled SQL statement and stores it // in an object.
PreparedStatement sql = con.prepareStatement(sql);
// executeQuery() - sql query to retrieve values from the specified table. result = statement.executeQuery(sql);
// executeUpdate () - Executes an SQL statement, which may be an
// INSERT, UPDATE, or DELETE statement or a statement that returns nothing result = statement.executeUpdate(sql);
// execute() - sql query to retrieve values from the specified table. result = statement.execute(sql);
// addBatch() - adds the given SQL command to the current list of commands statement.addBatch(sql);
statement.addBatch(more_sql);
As you may expect, Microsoft and C# developers do things a little differently. See
www.connectionstrings.com for a comprehensive collection of providers. Application developers typically use the following namespaces:
• System.Data.SqlClient: .NET Framework Data Provider for SQL Server • System.Data.OleDb: .NET Framework Data Provider for OLE DB • System.Data.OracleClient: .NET Framework Data Provider for Oracle • System.Data.Odbc: .NET Framework Data Provider for ODBC
The following is a list of classes that are used within the namespaces:
• SqlCommand( ): Used to construct/send an SQL statement or stored procedure • SqlParameter( ): Used to add parameters to an SqlCommand object
• OleDbCommand( ): Used to construct/send an SQL statement or stored procedure • OleDbParameter( ): Used to add parameters to an OleDbCommand object
• OracleCommand( ): Used to construct/send an SQL statement or stored procedure • OracleParameter( ): Used to add parameters to an OracleSqlCommand object • OdbcCommand( ): Used to construct/send an SQL statement or stored procedure • OdbcParameter( ): Used to add parameters to an OdbcCommand object
The following lines of code demonstrate how you can use these classes in a C# application: // SqlCommand() - used to construct or send an SQL statement
// SqlParameter() - used to add parameters to an SqlCommand object SqlCommand command = new SqlCommand(sql, connection);
command.Parameters.Add(“@param”, SqlDbType.VarChar, 50).Value = input; // OleDbCommand() - used to construct or send an SQL statement
OleDbCommand command = new OleDbCommand(sql, connection);
// OleDbParameter() - used to add parameters to an OleDbCommand object OleDbCommand command = new OleDbCommand($sql, connection);
command.Parameters.Add(“@param”, OleDbType.VarChar, 50).Value = input; // OracleCommand() - used to construct or send an SQL statement
OracleCommand command = new OracleCommand(sql, connection);
// OracleParameter() - used to add parameters to an OracleCommand object OracleCommand command = new OracleCommand(sql, connection);
command.Parameters.Add(“@param”, OleDbType.VarChar, 50).Value = input; // OdbcCommand() - used to construct or send an SQL statement
OdbcCommand command = new OdbcCommand(sql, connection);
// OdbcParameter() - used to add parameters to an OdbcCommand object OdbcCommand command = new OdbcCommand(sql, connection);
command.Parameters.Add(“@param”, OleDbType.VarChar, 50).Value = input;