View Issue Details

IDProjectCategoryView StatusLast Update
0020479mantisbtdb postgresqlpublic2016-08-27 17:49
ReportergthomasAssigned Todregad 
PrioritynormalSeveritymajorReproducibilityalways
Status closedResolutionfixed 
Product Version1.3.0-rc.1 
Target Version1.3.0-rc.2Fixed in Version1.3.0-rc.2 
Summary0020479: Error 401 db_query bind params starts with $2
Description

APPLICATION ERROR 401
Sikertelen adatbázis-lekérdezés. Az adatbázis hibaüzenete: #-1: ERROR: could not determine data type of parameter $1 a következő lekérdezéshez: SELECT project_id, access_level FROM mantis_project_user_list_table WHERE user_id=$2

Sikertelen adatbázis-lekérdezés. Az adatbázis hibaüzenete: #-1: ERROR: operator does not exist: smallint = boolean
LINE 1: ... 3, 5, 6, 2, 9) ) AND ( ( mantis_bug_table.status in ($1, $2...
^
HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts. a következő lekérdezéshez: SELECT Count( DISTINCT mantis_bug_table.id ) as idcnt FROM mantis_bug_table JOIN mantis_project_table ON mantis_project_table.id = mantis_bug_table.project_id WHERE mantis_project_table.enabled = $1 AND ( mantis_bug_table.project_id in (1, 7, 4, 3, 5, 6, 2, 9) ) AND ( ( mantis_bug_table.status in ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20, $21, $22, $23, $24, $25, $26) ) )

Steps To Reproduce

For some users, the login page throws the first error.

For non-superuser users, the "View Issues" list produces query where the first bind param is "$2", not "$1".

I've implemented a crude hack: renumber the params in db_query. This works, but I don't know how to trace this problem to the root cause.

Additional Information

See attached patch

TagsNo tags attached.

Relationships

related to 0020483 closedcproensa Error 401 with PostgreSQL manage_user_page 
related to 0014398 closeddregad Support for PostgreSQL broken in 1.3 
has duplicate 0020896 closedcproensa APPLICATION ERROR 0000401 with view Issues - all Projects 
related to 0021649 closedcproensa Remove incorrectly placed db_param_push() 

Activities

gthomas

gthomas

2016-01-05 08:30

reporter  

MT20479.patch (1,235 bytes)
diff --git a/core/database_api.php b/core/database_api.php
index 9fdca22..15abacc 100644
--- a/core/database_api.php
+++ b/core/database_api.php
@@ -387,11 +387,30 @@ function db_query( $p_query, array $p_arr_parms = null, $p_limit = -1, $p_offset
 							'%b' => db_param(),
 							'%l' => db_param(),
 							) );
+	log_event( LOG_DATABASE, "q=$p_query" );
+	$i = 0;
+	$p_off = 0;
+	$t_matches = array();
+	$t_query = $p_query;
+	$t_res = array();
+	while( 1 == preg_match('/[$][0-9]+/', $t_query, &$t_matches, PREG_OFFSET_CAPTURE ) ) {
+		$i++;
+		$t_res[] = substr( $t_query, 0, $t_matches[0][1] ) . '$' . $i;
+		$t_query = substr( $t_query, $t_matches[0][1] + strlen( $t_matches[0][0] ) );
+		$t_matches = array();
+	}
+	$t_query = implode( "", $t_res ) . $t_query;
+	if( 0 != strcmp( $p_query, $t_query ) ) {
+		log_event( LOG_DATABASE, "!!! $p_query\n<>\n$t_query" );
+		$p_query = $t_query;
+	}
 
 	if( db_is_oracle() ) {
 		$p_query = db_oracle_adapt_query_syntax( $p_query, $p_arr_parms );
 	}
 
+	log_event( LOG_DATABASE, "Q=$p_query P=" . var_export( $p_arr_parms, TRUE ) );
+
 	if( ( $p_limit != -1 ) || ( $p_offset != -1 ) ) {
 		$t_result = $g_db->SelectLimit( $p_query, $p_limit, $p_offset, $p_arr_parms );
 	} else {
MT20479.patch (1,235 bytes)
gthomas

gthomas

2016-01-05 08:31

reporter   ~0052248

Ok, uploaded the patch (format has been dismissed...).

dregad

dregad

2016-01-05 09:34

developer   ~0052250

Edited the additional info field to point to attachment.

Looking at the error message, it seems you're using PostgreSQL, is this correct ?

It would be helpful if you could actually provide reproducible steps as I'm not able to reproduce the behavior on my dev box.

Maybe you can enable detailed errors reporting and post output here (after masking sensitive info if any), this way we get the call stack and variables.

gthomas

gthomas

2016-01-05 14:55

reporter  

MT20479-detailed_error.html (16,547 bytes)
gthomas

gthomas

2016-01-05 15:04

reporter   ~0052259

Thanks Damian!

Yes, it is PostgreSQL, v8.4.

I've attached the detailed report.
Maybe filter_api.php line 1127 (latest db_param() before the erroneous line 1196)?
How does db_param accumulate the count, and when it is reseted?

cproensa

cproensa

2016-01-06 09:38

developer   ~0052263

I am not using PostgreSQL, but i could find one trace that calls for db_param, for first time while building the query, but the param count was already 1.
Only could get this situation once, and haven't been able to reproduce again

The call stack was:

function access_cache_matrix_user

/var/www/html/git/mantis13_cpm/core/access_api.php.access_cache_matrix_user:162 
/var/www/html/git/mantis13_cpm/core/access_api.php.access_get_local_level:629   
/var/www/html/git/mantis13_cpm/core/access_api.php.access_get_project_level:282 
/var/www/html/git/mantis13_cpm/core/access_api.php.access_has_project_level:330 
/var/www/html/git/mantis13_cpm/core/helper_api.php.helper_get_current_project:358   
/var/www/html/git/mantis13_cpm/core/config_api.php.config_get:118   
/var/www/html/git/mantis13_cpm/core/lang_api.php.lang_language_exists:193   
/var/www/html/git/mantis13_cpm/core/user_pref_api.php.user_pref_get_language:626    
/var/www/html/git/mantis13_cpm/core/lang_api.php.lang_get_default:115   
/var/www/html/git/mantis13_cpm/core/lang_api.php.lang_get_current:255   
/var/www/html/git/mantis13_cpm/core/lang_api.php.lang_get:279   
/var/www/html/git/mantis13_cpm/plugins/MantisCoreFormatting/MantisCoreFormatting.php.MantisCoreFormattingPlugin->register:30    
/var/www/html/git/mantis13_cpm/core/classes/MantisPlugin.class.php.MantisPlugin->__construct:248    
/var/www/html/git/mantis13_cpm/core/plugin_api.php.plugin_register:923  
/var/www/html/git/mantis13_cpm/core/plugin_api.php.plugin_register_installed:965    
/var/www/html/git/mantis13_cpm/core/plugin_api.php.plugin_init_installed:989    
/var/www/html/git/mantis13_cpm/core.php.require_once:229    
/var/www/html/git/mantis13_cpm/view_all_bug_page.php.{main}:39  

Apparently, the upper callers were not building sql querys, so its not a case of nested builds

I am thinking of two issues:
1) Should core api code call db_param_push(), to keep safe of nested sql builds?
2) Sometimes, somewhere in the code a db_param call is used that doesnt get finalized with a db_query?

cproensa

cproensa

2016-01-07 17:11

developer   ~0052275

I managed to reproduce a similar situation:

Given projects A,B
Set A as current project
Go into advanced filters, and select specific project B (only that one)
Use the filter, and the error appears

APPLICATION ERROR 0000401
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.

Call trace:

/var/www/html/git/mantis13_pg/core/database_api.php.db_query:340    
/var/www/html/git/mantis13_pg/core/project_api.php.project_cache_row:106    
/var/www/html/git/mantis13_pg/core/project_api.php.project_exists:211   
/var/www/html/git/mantis13_pg/core/filter_api.php.filter_get_bug_rows:1182  
/var/www/html/git/mantis13_pg/view_all_bug_page.php.{main}:71   

The problem is:
filter_api.php.filter_get_bug_rows is building the filter query, generating some where conditions, and calling db_param() to fill some of its values.
In the middle of that process, project_exists() is called. As the project B is not cached, the api function performs a sql query, which also uses db_param().
Internal parameter count was already on one, and returns the next one, that is 2.

So, to fix this, we may need to chase a lot of api functions that performs sql queries to add a db_param_push().

dregad

dregad

2016-01-08 05:01

developer   ~0052278

to fix this, we may need to chase a lot of api functions that performs sql queries to add a db_param_push()

You're absolutely right.

I created that API call exactly for that purpose, as I faced the same issue with PostgreSQL (and Oracle too) in another part of the application (I think it was custom fields).

To be perfectly clean, every single API executing SQL queries should make use of this, but it was too much effort go through the code and check functions one-by-one.

dregad

dregad

2016-01-08 08:29

developer   ~0052279

PR https://github.com/mantisbt/mantisbt/pull/705

dregad

dregad

2016-01-08 08:37

developer   ~0052280

@gthomas,

I would appreciate if you could download the code from the mentioned pull request, and give it a test run to see if it resolves the issues you've encountered.

How does db_param accumulate the count, and when it is reseted?

There is a global variable in database API ($g_db_param), which acts like a stack. Everytime db_param() is called, the param count is incremented, and when the query is executed (calling db_query()), the count is reset to its previous value (or 0 if there is none).

gthomas

gthomas

2016-01-10 04:31

reporter   ~0052282

Thanks @cproense and @dregad!

I'll try the PR, but why do we use $number at all? With a global variable, this is very error prone.

Why not just a "?" or "<PLACEHOLDER>" or anything, and use a replacer (just as I did) just before execution? We already have such function for Oracle, already!

gthomas

gthomas

2016-01-10 15:06

reporter   ~0052286

As far as I could test it (only with specific users), f54b2de02e371e93624bb51d674ba24d0f1d1b06 solves this problem.

dregad

dregad

2016-05-15 09:00

developer   ~0053139

PR https://github.com/mantisbt/mantisbt/pull/705 has been rebased.

Related Changesets

MantisBT: master b6f81949

2016-05-21 04:19:28

dregad

Details Diff
Consistently push query params in all APIs

Fixes 0020479, https://github.com/mantisbt/mantisbt/pull/705
mod - core/access_api.php Diff File
mod - core/api_token_api.php Diff File
mod - core/authentication_api.php Diff File
mod - core/billing_api.php Diff File
mod - core/bug_api.php Diff File
mod - core/bug_revision_api.php Diff File
mod - core/bugnote_api.php Diff File
mod - core/category_api.php Diff File
mod - core/cfdefs/cfdef_standard.php Diff File
mod - core/config_api.php Diff File
mod - core/custom_field_api.php Diff File
mod - core/database_api.php Diff File
mod - core/email_api.php Diff File
mod - core/email_queue_api.php Diff File
mod - core/file_api.php Diff File
mod - core/filter_api.php Diff File
mod - core/history_api.php Diff File
mod - core/install_helper_functions_api.php Diff File
mod - core/news_api.php Diff File
mod - core/plugin_api.php Diff File
mod - core/print_api.php Diff File
mod - core/profile_api.php Diff File
mod - core/project_api.php Diff File
mod - core/project_hierarchy_api.php Diff File
mod - core/relationship_api.php Diff File
mod - core/sponsorship_api.php Diff File
mod - core/summary_api.php Diff File
mod - core/tag_api.php Diff File
mod - core/tokens_api.php Diff File
mod - core/user_api.php Diff File
mod - core/user_pref_api.php Diff File
mod - core/version_api.php Diff File

MantisBT: master f3ab14c5

2016-05-23 05:54:07

dregad

Details Diff
Use correct function name db_param_push()

Commit fbc379faaf27e6b853c8b08ac380834836b81032 referenced the wrong
name db_push_param().

Issue 0020479
mod - core/user_api.php Diff File

Issue History

Date Modified Username Field Change
2016-01-05 08:29 gthomas New Issue
2016-01-05 08:30 gthomas File Added: MT20479.patch
2016-01-05 08:31 gthomas Note Added: 0052248
2016-01-05 09:34 dregad Status new => feedback
2016-01-05 09:34 dregad Additional Information Updated View Revisions
2016-01-05 09:34 dregad Note Added: 0052250
2016-01-05 14:55 gthomas File Added: MT20479-detailed_error.html
2016-01-05 15:04 gthomas Note Added: 0052259
2016-01-05 15:04 gthomas Status feedback => new
2016-01-06 09:38 cproensa Note Added: 0052263
2016-01-06 11:10 dregad Summary 0000401 db_query bind params starts with $2 => Error 401 db_query bind params starts with $2
2016-01-06 11:10 dregad Description Updated View Revisions
2016-01-07 17:11 cproensa Note Added: 0052275
2016-01-08 05:01 dregad Note Added: 0052278
2016-01-08 05:06 dregad Status new => confirmed
2016-01-08 08:29 dregad Assigned To => dregad
2016-01-08 08:29 dregad Status confirmed => assigned
2016-01-08 08:29 dregad Note Added: 0052279
2016-01-08 08:30 dregad Category filters => db postgresql
2016-01-08 08:30 dregad Target Version => 1.3.0-rc.2
2016-01-08 08:37 dregad Note Added: 0052280
2016-01-08 08:37 dregad Relationship added related to 0020483
2016-01-08 08:40 dregad Relationship added related to 0014398
2016-01-10 04:31 gthomas Note Added: 0052282
2016-01-10 15:06 gthomas Note Added: 0052286
2016-05-14 13:47 cproensa Relationship added has duplicate 0020896
2016-05-15 09:00 dregad Note Added: 0053139
2016-05-21 04:20 dregad Changeset attached => MantisBT master 6e444903
2016-05-21 04:20 dregad Changeset attached => MantisBT master b6f81949
2016-05-21 04:20 dregad Status assigned => resolved
2016-05-21 04:20 dregad Resolution open => fixed
2016-05-21 04:20 dregad Fixed in Version => 1.3.0-rc.2
2016-05-23 05:56 dregad Changeset attached => MantisBT master f3ab14c5
2016-05-23 05:56 dregad Changeset removed MantisBT master 6e444903 =>
2016-06-12 00:42 vboctor Status resolved => closed
2016-08-27 17:49 dregad Relationship added related to 0021649