I'm not sure if this is the right place to post this but I'm sure I'll be corrected soon enough.

I have a problem with a view where if I enter a term into an exposed filter for the CCK field Full Name (where it only searches the last name BTW) it throws an error if the string begins with a lower case `b`. This doesn't happen with any other letter or with an upper case `B`. The error message, when I use the string `bingo` looks like this:

    * user warning: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'node'ingo%')))' at line 1 query: SELECT count(node.nid) FROM node node LEFT JOIN content_type_person node_data_field_fullname ON node.vid = node_data_field_fullname.vid WHERE (node.status = '1') AND (node.type IN ('person')) AND ((UPPER(node_data_field_fullname.field_fullname_last) LIKE UPPER(''node'ingo%'))) in /home/lucita7/public_html/bis/includes/database.mysql.inc on line 172.

    * user warning: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'node'ingo%'))) ORDER BY node_data_field_fullname_field_fullname_last ASC LIMIT ' at line 1 query: SELECT node.nid, node_data_field_fullname.field_fullname_last AS node_data_field_fullname_field_fullname_last FROM node node LEFT JOIN content_type_person node_data_field_fullname ON node.vid = node_data_field_fullname.vid WHERE (node.status = '1') AND (node.type IN ('person')) AND ((UPPER(node_data_field_fullname.field_fullname_last) LIKE UPPER(''node'ingo%'))) ORDER BY node_data_field_fullname_field_fullname_last ASC LIMIT 0, 15 in /home/lucita7/public_html/bis/includes/database.mysql.inc on line 172.

As you can see it converts `b` into `'node'` (with the single quotes) like this...


(UPPER(node_data_field_fullname.field_fullname_last) LIKE UPPER(''node'ingo%')

The export of the view reads thus...

  $view = new stdClass();
  $view->name = 'person_list';
  $view->description = 'List of persons';
  $view->access = array (
);
  $view->view_args_php = '';
  $view->page = TRUE;
  $view->page_title = 'Listing of Faculty';
  $view->page_header = '';
  $view->page_header_format = '3';
  $view->page_footer = '';
  $view->page_footer_format = '3';
  $view->page_empty = '';
  $view->page_empty_format = '3';
  $view->page_type = 'teaser';
  $view->url = 'directory/person';
  $view->use_pager = TRUE;
  $view->nodes_per_page = '15';
  $view->sort = array (
    array (
      'tablename' => 'node_data_field_fullname',
      'field' => 'field_fullname_last',
      'sortorder' => 'ASC',
      'options' => '',
    ),
  );
  $view->argument = array (
  );
  $view->field = array (
  );
  $view->filter = array (
    array (
      'tablename' => 'node',
      'field' => 'status',
      'operator' => '=',
      'options' => '',
      'value' => '1',
    ),
    array (
      'tablename' => 'node',
      'field' => 'type',
      'operator' => 'OR',
      'options' => '',
      'value' => array (
  0 => 'person',
),
    ),
    array (
      'tablename' => 'node_data_field_fullname',
      'field' => 'field_fullname_last_default',
      'operator' => 'word',
      'options' => '',
      'value' => '',
    ),
    array (
      'tablename' => 'term_node_13',
      'field' => 'tid',
      'operator' => 'OR',
      'options' => '',
      'value' => array (
),
    ),
  );
  $view->exposed_filter = array (
    array (
      'tablename' => 'node_data_field_fullname',
      'field' => 'field_fullname_last_default',
      'label' => 'Last name',
      'optional' => '1',
      'is_default' => '0',
      'operator' => '1',
      'single' => '1',
    ),
    array (
      'tablename' => 'term_node_13',
      'field' => 'tid',
      'label' => 'Country',
      'optional' => '1',
      'is_default' => '0',
      'operator' => '1',
      'single' => '1',
    ),
  );
  $view->requires = array(node_data_field_fullname, node, term_node_13);
  $views[$view->name] = $view;

Many thanks in advance...

Comments

ricflomag’s picture

Same bug here, i was about to fill a separate report, so i am posting it here:

How to reproduce:

  • Create a simple view, let's say a page view providing a teaser list of all the nodes of your site.
  • Add a filter on the title of the nodes, choosing the "Contains" operator. Expose the filter.
  • Go to the url of the view, enter any string beginning with a "b" (lowercase) in the text input for filtering nodes by their title, and hit Enter.
  • Drupal displays an error message that looks like this:
        * user warning: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'node.title'ugcatchme%'))' at line 1 query: SELECT count( DISTINCT(node.nid)) FROM node node WHERE (UPPER(node.title) LIKE UPPER(''node.title'ugcatchme%')) in --drupal-path--/includes/database.mysql.inc on line 172.
        * user warning: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'node.title'ugcatchme%')) LIMIT 0, 10' at line 1 query: SELECT DISTINCT(node.nid) FROM node node WHERE (UPPER(node.title) LIKE UPPER(''node.title'ugcatchme%')) LIMIT 0, 10 in --drupal-path--/includes/database.mysql.inc on line 172.
    

The bug occurs when replacing the place holders in _db_query_callback(). The constant DB_QUERY_REGEXP (http://api.drupal.org/api/constant/DB_QUERY_REGEXP/5) includes a %b place holder that erroneously matches our string that begins with a "b", because the query contains LIKE UPPER('%bugcatchme%'). DB_QUERY_REGEXP contains other place holders (%s, %f, %d), but %b is the only one that causes an error.

I have not investigated any further, but as the other place holders do work well, i guess it is not difficult to have %b fixed.

jjrosent’s picture

I can also confirm this. Ran across this ticket while researching the same issue on our system.

heathergaye’s picture

Same problem here. The bug affects exposed filters that match on a partial string: for my "contains" filters, I can recreate the "b" error, and if I enter a filter value starting with a lowercase s, f, or d, the view won't return any results.

I've bunged in a temporary revolting hack on my site, converting the filter arguments to uppercase before the SQL string is constructed. I'll post the line of code anyone wants to know, but I haven't worked out the patch thing yet, and it's a pretty doofy solution anyway.

merlinofchaos’s picture

Status: Active » Closed (duplicate)