Wiki

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== Executing Database Queries ======

MantisBT relies on [[http://adodb.org|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:
<code php>
$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 ) );
</code>

This works fine under MySQL, as the engine relies on ''?'' as placeholders for parameters:
<code sql>
-- 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 = ?
</code>

Under PostgreSQL on the other hand, we have a problem due to the use of positional parameters:
<code sql>
-- 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
</code>

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

  - 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.
  - 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:

  - ''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)
  - ''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.
  - ''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:
<code php>
# 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 ) );
</code>

PostgreSQL results:
<code sql>
-- 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
</code>

===== References =====

  * Issue [[Mantis>20479]]
  * https://github.com/mantisbt/mantisbt/pull/705
  * https://github.com/mantisbt/mantisbt/pull/143