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()
.
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:
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.
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 db_query()
has been modified to pop the previous parameter count after successful execution db_query_pop()
: new API to pop the previous parameter count from the stack. 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