Drupal 7 special chars not working in db_select

Question

I have implemented custom search to get some values from node.

When I'm using special chars like "/, ?, !", no result is being displayed.

Example value : "24/7" or "site?" or "test!", etc...

$search_text = '24/7';
$query->condition(db_or()
->condition('n.title', '%' . db_like($search_text) . '%', 'LIKE')
->condition('fld.body_value', '%' . db_like($search_text) . '%', 'LIKE'));

I'm running my site in Drupal 7. PHP version is 5.5.9. Using Mysql DB. OS windows.

Note: Query is correct. Its giving proper result for other than special chars.

Edit: $query->arguments(); is returning below image.

I was running LIKE command in phpmyadmin. Please find that screen shot below.

So what is the modification do we need to do at PDO to support this issue?

Answer 1

It doesn't look like anything wrong with query. I'm using drupal 7 with basic setup and one node(nid=3) has 24/7 in title & body and one node(nid=4) has only in body.

  // Make sure to decode, if the search string is part of URL.
  // $search_text = urldecode($_GET['query']);

  $search_text = '24/7';
  $query = db_select('node', 'n');
  $query->join('field_data_body', 'b', 'b.entity_id=n.nid');
  $query->fields('n', array('nid'))
    ->condition(db_or()
    ->condition('n.title', '%' . db_like($search_text) . '%', 'LIKE')
    ->condition('b.body_value', '%' . db_like($search_text) . '%', 'LIKE')
  );
  $result = $query->execute()->fetchAll();
  print_r(db_like($search_text));
  print_r($query->arguments());
  print_r($result);

and the result is:

// print_r(db_like($search_text));
24/7

// print_r($query->arguments());
Array
(
    [:db_condition_placeholder_0] => %24/7%
    [:db_condition_placeholder_1] => %24/7%
)

// print_r($result);
Array
(
    [0] => stdClass Object
        (
            [nid] => 2
        )

    [1] => stdClass Object
        (
            [nid] => 3
        )

)

Note: If you are getting the search string from URL as query string make sure, you have done urldecode() before send to SQL.