User Tools

  • Logged in as: anonymous (anonymous)
  • Logout

Site Tools


mantisbt:executing_db_queries

Table of Contents

Executing Database Queries

MantisBT relies on ADOdb library for database abstraction.

Whenever coding new APIs or modifying existing ones Developers must be careful to always call db_param_push() before any use of db_query().

Design flaw

Our current database API suffers from a design flaw which causes issues when concurrently processing queries within a single session. This occurs when the following conditions are met:

  • The queries being built use parameters
  • The ADOdb driver for the underlying RDBMS uses positional parameters (e.g. PostgreSQL)
  • A second query must be built before the first one has been executed

Example:

$t_query_1 = 'SELECT * FROM {user} WHERE id = ' . db_param();
$t_query_2 = 'SELECT * FROM {project} WHERE id = ' . db_param();
$t_result_2 = db_query( $t_query_2, array( 3 ) );
$t_query_1 .= ' OR id = ' . db_param();
$t_result_1 = db_query( $t_query_1, array( 1, 2 ) );

This works fine under MySQL, as the engine relies on ? as placeholders for parameters:

-- Executed SQL for query 1
SELECT * FROM mantis_user_table WHERE id = ? OR id = ?
-- Executed SQL for query 2
SELECT * FROM mantis_project_table WHERE id = ?

Under PostgreSQL on the other hand, we have a problem due to the use of positional parameters:

-- Generated SQL for query 1
SELECT * FROM mantis_user_table WHERE id = $1 OR id = $3
-- Executed SQL for query 2
SELECT * FROM mantis_project_table WHERE id = $2

Executing these queries triggers errors, because the parameters array does not match their numbering in the query's SQL.

  1. APPLICATION ERROR #401: Database query failed. Error received from database was #-1: ERROR: could not determine data type of parameter $2 for the query: SELECT * FROM mantis_user_table WHERE id = $1 OR id = $3.
  2. APPLICATION ERROR #401: Database query failed. Error received from database was #-1: ERROR: could not determine data type of parameter $1 for the query: SELECT * FROM mantis_project_table WHERE id = $2.

Workaround

To avoid this issue, the Database API has been modified to use a stack for query parameters (via MantisDbParam class), using the following functions:

  1. db_param_push(): this new API pushes the current parameter count onto the stack
    It should systematically be called prior to preparing any new query (regardless of whether it uses parameters or not)
  2. db_query() has been modified to pop the previous parameter count after successful execution
    A new optional parameter allows to leave the parameters on the stack.
  3. db_query_pop() : new API to pop the previous parameter count from the stack.
    It is only necessary to call this when a query is built but not executed, e.g. after an error.

All MantisBT core APIs have been modified to use the new functions, making them safe to be called from anywhere.

Fixing the above example:

# Start building the 1st query
db_param_push();
$t_query_1 = 'SELECT * FROM {user} WHERE id = ' . db_param();
 
# Execute a 2nd query while the 1st is still being built
db_param_push();
$t_query_2 = 'SELECT * FROM {project} WHERE id = ' . db_param();
$t_result_2 = db_query( $t_query_2, array( 3 ) );
 
# Begin a 3rd query
db_param_push();
$t_query_3 = 'SELECT * FROM {bug} WHERE id = ' . db_param();
# Finally we don't need to execute the 3rd query...
db_param_pop(); 
 
# Finish building query 1 and run it
$t_query_1 .= ' OR id = ' . db_param();
$t_result_1 = db_query( $t_query_1, array( 1, 2 ) );

PostgreSQL results:

-- Executed SQL for query 1
SELECT * FROM {USER} WHERE id = $1 OR id = $2
-- Executed SQL for query 2
SELECT * FROM {project} WHERE id = $1

References

mantisbt/executing_db_queries.txt · Last modified: 2016/05/15 07:09 by dregad