0014014: Search with number > 2147483647 fails on 64-bit systems with PostgreSQL

Notes
(0031406) dregad (developer) 2012-03-07 13:30	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: if( is_numeric( $t_search_term ) ) { $c_search_int = (int) $t_search_term; 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.

(0031410) schoppi71 (reporter) 2012-03-08 03:18	This could be the reason. Do I have a possibility to force PHP using PHP_INT_MAX=2147483647 ?

(0031416) dregad (developer) 2012-03-09 07:44	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.

(0031417) schoppi71 (reporter) 2012-03-09 07:54	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 ) )

(0031418) macarbiter (reporter) 2012-03-09 08:09	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?

(0031419) dregad (developer) 2012-03-09 10:50	@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 ?

(0031421) dregad (developer) 2012-03-10 04:29	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.

(0031422) atrol (developer) 2012-03-10 05:22 edited on: 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.

(0031428) schoppi71 (reporter) 2012-03-12 03:58	Thanks dregad, the patch works.

(0031430) dregad (developer) 2012-03-12 07:19	@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 [^]

(0031435) atrol (developer) 2012-03-12 13:04	@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 [^]

(0031436) dregad (developer) 2012-03-12 13:35	> 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.

(0031437) atrol (developer) 2012-03-12 15:12	> 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 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 ) { ... } }

(0031457) dregad (developer) 2012-03-14 12:52 edited on: 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)

(0036292) grangeway (developer) 2013-04-05 17:57	Marking as 'acknowledged' not resolved/closed to track that change gets ported to master-2.0.x branch

Related Changesets
MantisBT: master 04a5fb5e Timestamp: 2012-03-09 23:21:22 Author: 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).
	mod - core/filter_api.php	[ Diff ] [ File ]

MantisBT: master-1.2.x 4618dcd4 Timestamp: 2012-03-09 23:21:22 Author: 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).
	mod - core/filter_api.php	[ Diff ] [ File ]

Issue History
Date Modified	Username	Field	Change
2012-03-07 08:49	schoppi71	New Issue
2012-03-07 12:24	dregad	Relationship added	has duplicate 0012880
2012-03-07 12:27	dregad	Status	new => acknowledged
2012-03-07 13:30	dregad	Note Added: 0031406
2012-03-08 03:18	schoppi71	Note Added: 0031410
2012-03-09 07:44	dregad	Note Added: 0031416
2012-03-09 07:54	schoppi71	Note Added: 0031417
2012-03-09 08:09	macarbiter	Note Added: 0031418
2012-03-09 10:50	dregad	Note Added: 0031419
2012-03-09 10:55	dregad	Assigned To	=> dregad
2012-03-09 10:55	dregad	Status	acknowledged => confirmed
2012-03-09 10:55	dregad	Product Version	=> 1.2.0
2012-03-09 10:55	dregad	Target Version	=> 1.2.10
2012-03-09 11:01	dregad	Summary	Search with numeric number bigger then 2147483647 will fail on PostgreSQL => Search with number > 2147483647 fails on 64-bit systems with PostgreSQL
2012-03-10 04:29	dregad	Note Added: 0031421
2012-03-10 04:30	dregad	File Added: 14014.patch
2012-03-10 05:22	atrol	Note Added: 0031422
2012-03-10 05:24	atrol	Note Edited: 0031422	View Revisions
2012-03-12 03:58	schoppi71	Note Added: 0031428
2012-03-12 07:19	dregad	Note Added: 0031430
2012-03-12 13:04	atrol	Note Added: 0031435
2012-03-12 13:35	dregad	Note Added: 0031436
2012-03-12 13:36	dregad	File Added: 14014-2.patch
2012-03-12 15:12	atrol	Note Added: 0031437
2012-03-14 12:52	dregad	Note Added: 0031457
2012-03-14 12:54	dregad	File Added: 14014-3.patch
2012-03-14 12:55	dregad	Note Edited: 0031457	View Revisions
2012-03-16 13:00	dregad	Changeset attached	=> MantisBT master 04a5fb5e
2012-03-16 13:00	dregad	Changeset attached	=> MantisBT master-1.2.x 4618dcd4
2012-03-16 13:00	dregad	Status	confirmed => resolved
2012-03-16 13:00	dregad	Resolution	open => fixed
2012-03-16 13:00	dregad	Fixed in Version	=> 1.2.10
2012-04-01 20:11	vboctor	Status	resolved => closed
2013-04-05 17:57	grangeway	Status	closed => acknowledged
2013-04-05 17:57	grangeway	Note Added: 0036292
2013-04-05 18:26	grangeway	Relationship added	related to 0015721
2013-04-06 03:41	dregad	Status	acknowledged => closed
2013-04-06 07:23	grangeway	Status	closed => acknowledged
2013-04-06 09:22	dregad	Tag Attached: 2.0.x check
2013-04-06 09:23	dregad	Status	acknowledged => closed