View Issue Details

IDProjectCategoryView StatusLast Update
0013280mantisbtcustom fieldspublic2014-09-23 18:05
ReporterDentxinho Assigned Todregad  
PrioritylowSeveritytextReproducibilityalways
Status closedResolutionfixed 
Product Version1.2.7 
Target Version1.2.9Fixed in Version1.2.9 
Summary0013280: Sort by accented custom field produces incorrect log entries
Description

Sorting issues by a custom field that have accents produces a LOG_FILTERING entry that has a SQL error.
The query runs without error

Steps To Reproduce
  • set $g_log_level = LOG_DATABASE; and $g_show_queries_list = ON;

  • Create a custom field, say "field" and configure it to show on view_all_bugs_page

  • Go to view_all_bugs_page and sort by "field"

  • Result on the log: query 'filter_api.php:2010 filter_get_bug_rows()' correct;

  • Change the custom filed to "fíeld" and its related configurations

  • Go to view_all_bugs_page and sort by "fíeld"

  • Result on the log: query 'filter_api.php:2010 filter_get_bug_rows()' incorrect. The error is on "WHERE mantis_project_table.enabled = ?1AND mantis_project_table.id = mantis_bug_table.project_id";

Additional Information

Dump of $p_query 1 line before database_api.php:373

'SELECT DISTINCT mantis_bug_table.*, mantis_custom_field_string_table_1.value fíeld FROM mantis_project_table, mantis_bug_table LEFT JOIN mantis_custom_field_string_table mantis_custom_field_string_table_1 ON mantis_bug_table.id = mantis_custom_field_string_table_1.bug_id AND mantis_custom_field_string_table_1.field_id = 1 WHERE mantis_project_table.enabled = ? AND mantis_project_table.id = mantis_bug_table.project_id AND ( mantis_bug_table.project_id = 2 ) ORDER BY fíeld DESC, mantis_bug_table.las'... (length=564)

Dump of $p_query 1 line after database_api.php:373

'SELECT DISTINCT mantis_bug_table.*, mantis_custom_field_string_table_1.value fíeld FROM mantis_project_table, mantis_bug_table LEFT JOIN mantis_custom_field_string_table mantis_custom_field_string_table_1 ON mantis_bug_table.id = mantis_custom_field_string_table_1.bug_id AND mantis_custom_field_string_table_1.field_id = 1 WHERE mantis_project_table.enabled = ?1AND mantis_project_table.id = mantis_bug_table.project_id AND ( mantis_bug_table.project_id = 2 ) ORDER BY fíeld DESC, mantis_bug_table.las'... (length=564)

PS: queries truncated by xdebug

TagsNo tags attached.

Relationships

related to 0015721 closedgrangeway Functionality to consider porting to master-2.0.x 
related to 0014097 closeddregad German 'Umlaute' with closing question mark failed 
related to 0015575 closeddregad Turning on $g_show_queries_list causes Mantis to crash with an error 

Activities

dregad

dregad

2011-09-01 11:14

developer   ~0029614

I had some difficulties to reproduce the behavior you described, as I had nothing in the log file, until I realized that you were probably referring to the output of $g_show_queries_list = ON;

After investigation, I think the problem is caused by preg_match function, which returns a byte-based offset instead of utf8-character-based one, resulting in the parameter replacement happening in the wrong place on the string.

Dentxinho

Dentxinho

2011-09-01 14:15

reporter   ~0029616

Actually I was referring to $g_log_level = LOG_FILTERING, but the output of $g_show_queries_list = ON gets the error too.

Why not to do a simple str_replace or sprintf to replace "?" with $replace on database_api.php:373 instead of the current tricky code?

dregad

dregad

2011-09-02 04:36

developer   ~0029621

This is a bit academical, but... $g_log_level = LOG_FILTERING does not output any SQL queries; LOG_DATABASE does, but only if $g_show_queries_list = ON. The code you pointed out is simply not executed when it is OFF.

If your installation behaves differently and assuming it's worth the effort, then post the actual relevant section of the log file, and point me in the code where the log entry is being written as I don't see it.

With regards "tricky code" to not using str_replace, I would think it's due to the variable number of parameters and special handling for different value (e.g. NULL, etc) and database types.

Dentxinho

Dentxinho

2011-09-02 07:41

reporter   ~0029623

dregad, you're right, my installation behaves exactly like this. That output is written on the log file only if both LOG_DATABASE and $g_show_queries_list are enabled.

database_api.php:381 outputs $p_query that is set on database_api.php:373. The string with incorrect SQL query lies in the value of $p_query.

--

Anyone can fix the text of this issue?

dregad

dregad

2011-09-02 08:27

developer   ~0029624

I corrected the steps to reproduce; will provide patch in next few days when I have time to get to it.

grangeway

grangeway

2013-04-05 17:56

reporter   ~0036077

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

Related Changesets

MantisBT: master-1.2.x 5835572f

2011-09-08 20:17

dregad


Details Diff
Fix 0013280: Incorrect queries logged when sorting by custom field having accented chars

The offsets returned by preg_match are byte-based, which causes issues with
UTF-8 characters as the subsequent calls to utf8_substr operate on the wrong
part of the string; this results in logging of invalid SQL queries when
$g_show_queries_list = ON.

This commit fixes the problem by realigning the offsets prior to performing the
query parameter substitution.

It also simplifies the code by removing parenthesis in the regexp pattern,
which is not necessary since we are only matching a single element, this way
there is no need to deal with a multi-dimentional array.
Affected Issues
0013280
mod - core/database_api.php Diff File

MantisBT: master 36a34464

2011-09-08 20:17

dregad


Details Diff
Fix 0013280: Incorrect queries logged when sorting by custom field having accented chars

The offsets returned by preg_match are byte-based, which causes issues with
UTF-8 characters as the subsequent calls to utf8_substr operate on the wrong
part of the string; this results in logging of invalid SQL queries when
$g_show_queries_list = ON.

This commit fixes the problem by realigning the offsets prior to performing the
query parameter substitution.

It also simplifies the code by removing parenthesis in the regexp pattern,
which is not necessary since we are only matching a single element, this way
there is no need to deal with a multi-dimentional array.
Affected Issues
0013280
mod - core/database_api.php Diff File