View Issue Details

IDProjectCategoryView StatusLast Update
0014014mantisbtfilterspublic2016-11-15 11:43
Reporterschoppi71 Assigned Todregad  
PrioritynormalSeveritymajorReproducibilityalways
Status closedResolutionfixed 
PlatformLinux SuSE Linux Enterprise ServOSSuSe Linux Enterprise ServerOS Version10 SP2 64-Bit
Product Version1.2.0 
Target Version1.2.10Fixed in Version1.2.10 
Summary0014014: Search with number > 2147483647 fails on 64-bit systems with PostgreSQL
Description

I have the same problem and I can reproduce it. I'm using Mantis 1.2.8 with PostgreSQL-DB 8.3.7 on Linux SuSE Linux Enterprise Server 10 SP2 64-Bit.
The error will occur if I insert a numeric value bigger then 2147483647 in the 'Search'-field of the 'View issue'-page. The strange thing is that if I try out the same select directly on the database within the SQL-sheet of pgAdminIII then it will work...

Same problem like 0012880.

Errormessage:
Datenbankabfrage fehlgeschlagen. Die Reportmeldung lautet #-1: ERROR: value "2147483648" is out of range for type integer für die Abfrage: SELECT Count( DISTINCT mantis_bug_table.id ) as idcnt FROM mantis_bug_text_table, mantis_project_table, mantis_bug_table LEFT JOIN mantis_bugnote_table ON mantis_bug_table.id = mantis_bugnote_table.bug_id LEFT JOIN mantis_bugnote_text_table ON mantis_bugnote_table.bugnote_text_id = mantis_bugnote_text_table.id WHERE mantis_project_table.enabled = 1 AND mantis_project_table.id = mantis_bug_table.project_id AND ( mantis_bug_table.status in (10, 20, 30, 40, 50, 60, 70, 80) ) AND mantis_bug_table.bug_text_id = mantis_bug_text_table.id AND ( ( (summary ILIKE '%2147483648%') OR (mantis_bug_text_table.description ILIKE '%2147483648%') OR (mantis_bug_text_table.steps_to_reproduce ILIKE '%2147483648%') OR (mantis_bug_text_table.additional_information ILIKE '%2147483648%') OR (mantis_bugnote_text_table.note ILIKE '%2147483648%') OR mantis_bug_table.id = 2147483648 OR mantis_bugnote_table.id = 2147483648 ) ).

TagsNo tags attached.
Attached Files
14014.patch (643 bytes)   
diff --git a/core/filter_api.php b/core/filter_api.php
index f8de0f8..4fe2f2e 100644
--- a/core/filter_api.php
+++ b/core/filter_api.php
@@ -1956,7 +1956,7 @@ function filter_get_bug_rows( &$p_page_number, &$p_per_page, &$p_page_count, &$p
 			$t_where_params[] = $c_search;
 			$t_where_params[] = $c_search;
 
-			if( is_numeric( $t_search_term ) ) {
+			if( is_numeric( $t_search_term ) && $t_search_term > 0 && $t_search_term <= 0x7FFFFFFF ) {
 				$c_search_int = (int) $t_search_term;
 				$t_textsearch_where_clause .= " OR $t_bug_table.id = " . db_param();
 				$t_textsearch_where_clause .= " OR $t_bugnote_table.id = " . db_param();
14014.patch (643 bytes)   
14014-2.patch (886 bytes)   
diff --git a/core/filter_api.php b/core/filter_api.php
index f8de0f8..75f0dc2 100644
--- a/core/filter_api.php
+++ b/core/filter_api.php
@@ -1956,7 +1956,14 @@ function filter_get_bug_rows( &$p_page_number, &$p_per_page, &$p_page_count, &$p
 			$t_where_params[] = $c_search;
 			$t_where_params[] = $c_search;
 
-			if( is_numeric( $t_search_term ) ) {
+			// PostgreSQL on 64-bit OS hack (see #14014)
+			if( PHP_INT_MAX > 0x7FFFFFFF && db_is_pgsql() ) {
+				$t_search_max = 0x7FFFFFFF;
+			} else {
+				$t_search_max = PHP_INT_MAX;
+			}
+			// Note: no need to test negative values, '-' sign is removed above
+			if( is_numeric( $t_search_term ) && $t_search_term <= $t_search_max ) {
 				$c_search_int = (int) $t_search_term;
 				$t_textsearch_where_clause .= " OR $t_bug_table.id = " . db_param();
 				$t_textsearch_where_clause .= " OR $t_bugnote_table.id = " . db_param();
14014-2.patch (886 bytes)   
14014-3.patch (1,243 bytes)   
diff --git a/core/filter_api.php b/core/filter_api.php
index f8de0f8..b9bd6eb 100644
--- a/core/filter_api.php
+++ b/core/filter_api.php
@@ -1957,11 +1957,20 @@ function filter_get_bug_rows( &$p_page_number, &$p_per_page, &$p_page_count, &$p
 			$t_where_params[] = $c_search;
 
 			if( is_numeric( $t_search_term ) ) {
-				$c_search_int = (int) $t_search_term;
-				$t_textsearch_where_clause .= " OR $t_bug_table.id = " . db_param();
-				$t_textsearch_where_clause .= " OR $t_bugnote_table.id = " . db_param();
-				$t_where_params[] = $c_search_int;
-				$t_where_params[] = $c_search_int;
+				// PostgreSQL on 64-bit OS hack (see #14014)
+				if( PHP_INT_MAX > 0x7FFFFFFF && db_is_pgsql() ) {
+					$t_search_max = 0x7FFFFFFF;
+				} else {
+					$t_search_max = PHP_INT_MAX;
+				}
+				// Note: no need to test negative values, '-' sign is removed above
+				if( $t_search_term <= $t_search_max ) {
+					$c_search_int = (int) $t_search_term;
+					$t_textsearch_where_clause .= " OR $t_bug_table.id = " . db_param();
+					$t_textsearch_where_clause .= " OR $t_bugnote_table.id = " . db_param();
+					$t_where_params[] = $c_search_int;
+					$t_where_params[] = $c_search_int;
+				}
 			}
 
 			$t_textsearch_where_clause .= ' )';
14014-3.patch (1,243 bytes)   

Relationships

related to 0015721 closedgrangeway Functionality to consider porting to master-2.0.x 
related to 0021802 closedcproensa Attempting to auto-link very long numbers can cause database errors 
has duplicate 0012880 closeddregad #<long number> in summary/description cause crash 

Activities

dregad

dregad

2012-03-07 13:30

developer   ~0031406

I am still unable to reproduce this behavior on my dev box. When I enter '2147483648' (or a higher value) in the search field, I get no error. Spooling the SQL yields the following:

16 0.0055 filter_api.php:998 filter_get_bug_count()
SELECT Count( DISTINCT mantis_bug_table.id ) as idcnt FROM mantis_bug_text_table, mantis_project_table, mantis_bug_table LEFT JOIN mantis_bugnote_table ON mantis_bug_table.id = mantis_bugnote_table.bug_id LEFT JOIN mantis_bugnote_text_table ON mantis_bugnote_table.bugnote_text_id = mantis_bugnote_text_table.id WHERE mantis_project_table.enabled = 1 AND mantis_project_table.id = mantis_bug_table.project_id AND ( mantis_bug_table.project_id = 1 ) AND ( mantis_bug_table.status in (10, 20, 30, 40, 50, 80) ) AND mantis_bug_table.bug_text_id = mantis_bug_text_table.id AND ( ( (summary ILIKE '%2147483648%') OR (mantis_bug_text_table.description ILIKE '%2147483648%') OR (mantis_bug_text_table.steps_to_reproduce ILIKE '%2147483648%') OR (mantis_bug_text_table.additional_information ILIKE '%2147483648%') OR (mantis_bugnote_text_table.note ILIKE '%2147483648%') OR mantis_bug_table.id = 2147483647 OR mantis_bugnote_table.id = 2147483647 ) )

Note that the argument to mantis_bugnote_table.id has been set to 2147483647, which is the max value for a 32-bit integer. Looking at the code, this is done in filter_api.php, line 1964:<pre>
if( is_numeric( $t_search_term ) ) {
$c_search_int = (int) $t_search_term;
</pre>

Re-reading your post, I realize that you're running a 64-bit OS. This probably means that PHP_INT_MAX = 2^63-1, which is of course higher than the max value of mantis_bug_table.id/mantis_bugnote_table.id fields (pgsql integer type = 2^31-1) so the (int) typecast does not sanitize the number value properly.

schoppi71

schoppi71

2012-03-08 03:18

reporter   ~0031410

This could be the reason. Do I have a possibility to force PHP using PHP_INT_MAX=2147483647 ?

dregad

dregad

2012-03-09 07:44

developer   ~0031416

You can't change the value of PHP_INT_MAX as it's a PHP core predefined constant.

Ideally we should be able to query the DB back-end for the largest acceptable value for a given field and use that to cap the value of $t_search_term, but unfortunately I do not think this can be done with the existing APIs.

So, at this time I believe that the only solution is to change filter_api.php to ensure the number is within range for a 32-bit signed integer, but that's an ugly hack.

schoppi71

schoppi71

2012-03-09 07:54

reporter   ~0031417

Yes this is an ugly hack because there could be long numbers as Strings in notes or descriptions and this occurences won't be found.

Maybe it will be possible not adding this two lines to the select statement if the number exceeds the range for a 32-bit signed integer?

OR (mantis_bugnote_text_table.note ILIKE '%2147483648%')
OR mantis_bug_table.id = 2147483647 OR mantis_bugnote_table.id = 2147483647 ) )

macarbiter

macarbiter

2012-03-09 08:09

reporter   ~0031418

Why not, after casting to an int, perform a bitwise and against 2^32-1, which will ensure it is never larger than a 32-bit number?

dregad

dregad

2012-03-09 10:50

developer   ~0031419

@macarbiter - I was thinking along the same lines, something like

  • $c_search_int = (int) $t_search_term;
  • $c_search_int = min( $t_search_term, 0x7FFFFFFF );

I guess doing a bitwise is probably more efficient (although the performance gain is almost meaningless here)

  • $c_search_int = $t_search_term & 0x7FFFFFFF;

As I do not have access to a 64-bit system atm, can one of you guys test if the above resolves the problem ?

dregad

dregad

2012-03-10 04:29

developer   ~0031421

On second thoughts, the solution schoppi71 mentioned in 0014014:0031417 i.e. not to include the where clauses on the bug and note id fields when the search number is > 2^31-1, probably makes more sense than capping the value as described in 0014014:0031419.

This is because when the value is capped, the filter will search the db for bug/note id = 2^31-1, which is not what the user asked for and therefore could cause confusion (in the situation of a user having > 2.1 billion bugs or note id's in their system which I guess is a somewhat unlikely scenario).

Please test the attached patch and let me know your feedback.

atrol

atrol

2012-03-10 05:22

developer   ~0031422

Last edited: 2012-03-10 05:24

Do we really need to deal with bits?
Is this the one and only place where we have to deal with such kind if issues?
Hard to believe.

I am no PHP specialist, but if we have to deal with bits we might also have to deal with things like byte/bit ordering on different processors.

schoppi71

schoppi71

2012-03-12 03:58

reporter   ~0031428

Thanks dregad, the patch works.

dregad

dregad

2012-03-12 07:19

developer   ~0031430

@schoppi71

Thanks for testing. Having confirmed that on my side as well on a 64-bit VM test box over the week-end, I will make adjustments to the patch to make the hack fixing the error PostgreSQL-specific, in order to avoid regressions since the problem does not exist on MySQL (and I cannot test on other DB platforms).

@atrol

This is not about "dealing with bits", but rather taking into consideration the word size of the underlying operating system [1]. I do not think byte ordering has anything to do with the issue at hand.

Normally I agree with you that we should ignore this kind of things in a high-level language. However in that specific case, we do need to do something about it due to the way PostgreSQL handles integers (default 32-bit, error if out of range since 8.0) which impacts normal usage of MantisBT.

This may indeed not be the only place where a similar issue can occur (technically speaking, anywhere we deal with integers above the 32-bit limit in relation with DB fields could potentially be affected). However, it's a bit difficult to find out for sure due without many hours of research, to the nature of the problem which is very platform-specific.

[1] http://en.wikipedia.org/wiki/Word_%28computer_architecture%29

atrol

atrol

2012-03-12 13:04

developer   ~0031435

@dregad,

I agree that the issue is not caused by byte ordering.
I meant that your patch might be affected by byte/bit ordering. [1]
If 0x7FFFFFFF gives the same int value in PHP independent of most significant bit and byte there should be no problem with the patch.

I don't like the idea to have such kind of workarounds at this place of code.
If it's just a patch which will not be commited I don't have a problem with it.

Hoping that a newer version of adodb might fix the issue for us.

[1] http://en.wikipedia.org/wiki/Endianness

dregad

dregad

2012-03-12 13:35

developer   ~0031436

0x7FFFFFFF gives the same int value in PHP independent of most significant bit and byte

That's just a different (but standard PHP way) of writing constant 2147483647 using hexadecimal notation, which I think makes the code more self-descriptive.

I don't like the idea to have such kind of workarounds at this place of code.

Neither do I, but I really can't think of any other, better way to work around the problem.

Hoping that a newer version of adodb might fix the issue for us.

Very unlikely. To begin with, PHP drivers for RDBMS do not offer primitive functions to retrieve the maximum possible value for a given DB field.

Not to mention that dhx and grangeway are moving away from adodb (see also 0013713:0030836)...

I'll upload a revised patch shortly. Let me know if you have an issue with that going into core.

atrol

atrol

2012-03-12 15:12

developer   ~0031437

Let me know if you have an issue with that going into core.

The patch is fine for me.
The performance can be improved by executing the additional code only
for numeric search terms
<pre>
if( is_numeric( $t_search_term ) {
if( PHP_INT_MAX > 0x7FFFFFFF && db_is_pgsql() ) {
$t_search_max = 0x7FFFFFFF;
} else {
$t_search_max = PHP_INT_MAX;
}
if( $t_search_term <= $t_search_max ) {
...
}
}
</pre>

dregad

dregad

2012-03-14 12:52

developer   ~0031457

Last edited: 2012-03-14 12:55

Good point about putting the additional check within the main if. Thanks for your feedback. Revised patch added (tested on 32-bit mysql + pgsql, will test on 64-bit at when at home later)

grangeway

grangeway

2013-04-05 17:57

reporter   ~0036292

Marking as 'acknowledged' not resolved/closed to track that change gets ported to master-2.0.x branch

Related Changesets

MantisBT: master 04a5fb5e

2012-03-09 18:21

dregad


Details Diff
Fix 0014014: Search number > 2147483647 fails on 64-bit systems

When a numeric search term is entered, the Filter API will only check
for matches in the bug and bugnote id fields when the search term is
within a valid range.

This was never an issue on 32-bit systems, but on 64-bit OS, PostgreSQL
throws an "integer out of range" error when executing the query because
the search term is cast to (int) and PHP_INT_MAX is greater than the
largest value allowed for the numeric DB fields (4-byte int, 2^31-1).

This issue does not exist on MySQL as the value is capped to the maximum
allowed; behavior was not tested on Oracle, DB2 or MSSQL.

The fix for PostgreSQL behavior is a hack, but I can't think of any
better solution (ideally, we should be able to query the DB for the
maximum allowed value for a field).
Affected Issues
0014014
mod - core/filter_api.php Diff File

MantisBT: master-1.2.x 4618dcd4

2012-03-09 18:21

dregad


Details Diff
Fix 0014014: Search number > 2147483647 fails on 64-bit systems

When a numeric search term is entered, the Filter API will only check
for matches in the bug and bugnote id fields when the search term is
within a valid range.

This was never an issue on 32-bit systems, but on 64-bit OS, PostgreSQL
throws an "integer out of range" error when executing the query because
the search term is cast to (int) and PHP_INT_MAX is greater than the
largest value allowed for the numeric DB fields (4-byte int, 2^31-1).

This issue does not exist on MySQL as the value is capped to the maximum
allowed; behavior was not tested on Oracle, DB2 or MSSQL.

The fix for PostgreSQL behavior is a hack, but I can't think of any
better solution (ideally, we should be able to query the DB for the
maximum allowed value for a field).
Affected Issues
0014014
mod - core/filter_api.php Diff File

MantisBT: master-1.3.x da64caba

2016-11-07 14:41

cproensa

Committer: dregad


Details Diff
Use DB_MAX_INT constant

Replace the fix applied for issue: 0014014
Use a simpler check against DB_MAX_INT constant
Affected Issues
0014014
mod - core/filter_api.php Diff File