3

I can't get a query to be case-insensitive using MySQL and Drupal 7. Here's my module code:

$results = db_select('people_table', 'p')->fields('p');
if (array_key_exists('department', $_GET)) {
    $results->condition('Department', '%' . db_like($_GET['department']) . '%', 'LIKE');
}
return $results->orderBy('Name', 'ASC')->execute();

With ?department=Chemistry in the URL, I get three results. With ?department=chemistry in the URL, I get no results. When I try $results->condition('UPPER(Department)'..., I get this error:

PDOException: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'UPPERDepartment' in 'where clause': SELECT p.* FROM {people_table} p WHERE (UPPERDepartment LIKE :db_condition_placeholder_0 ESCAPE '\\') ORDER BY Name ASC;

So it looks like it eats my parentheses. How can I do a case-insensitive LIKE?

Edit: the collation on the Department column as well as on the whole table is utf8_bin. This answer says "The only special one is utf8_bin which is for comparing characters in binary format." I don't know why this collation was chosen, since all the data in the table is English text. I might just change the collation to utf8_general_ci.

Community
  • 1
  • 1
Sarah Vessels
  • 30,930
  • 33
  • 155
  • 222
  • You have an SQL-injection in your code, see: http://stackoverflow.com/questions/332365/xkcd-sql-injection-please-explain I guess you've managed to bypass the PDO successfully :-) – Johan Oct 20 '11 at 12:40
  • `LIKE` is always case insensitive BTW, even on a binary collation. – Johan Oct 20 '11 at 12:42
  • How is there a SQL injection here? I thought the point of using the `condition()` function with `db_select()` is that it would escape your input. – Sarah Vessels Oct 20 '11 at 13:27
  • 1
    @SarahVessels: there is no SQL injection vulnerability here don't worry about it, PDO and the Drupal database layer protect you fully as you suspect – Clive Oct 20 '11 at 13:51

3 Answers3

5

what is collation of Department field?, it should be with prefix *_ci (case insensitive) for ex utf8_unicode_ci

Aurimas Ličkus
  • 9,886
  • 4
  • 24
  • 26
  • `utf8_bin`. It's a `longtext`. – Sarah Vessels Oct 20 '11 at 13:17
  • Checking the collation was the right answer. I changed all the text fields from `utf8_bin` to `utf8_general_ci` and now `$results->condition('Department', '%' . db_like($_GET['department']) . '%', 'LIKE');` works fine. :) – Sarah Vessels Oct 20 '11 at 13:53
4

Instead of using condition you can use the where method to add a custom WHERE clause:

$results->where('UPPER(Department)'...
Clive
  • 36,918
  • 8
  • 87
  • 113
  • Well, I didn't get any errors, but I also didn't get any results. :/ `$results->where("UPPER(Department) LIKE '%:dept%'", array(':dept' => db_like($_GET['department'])));` – Sarah Vessels Oct 20 '11 at 13:21
  • 1
    You need to wrap $_GET['department'] in strtoupper or you're just testing an uppercase string with a normal string – Clive Oct 20 '11 at 13:49
  • Derp, that would probably do the trick... I think your solution may have worked as well, though changing the collation seemed a better fix since the original `utf8_bin` made no sense. Using `condition()` seems a cleaner solution. – Sarah Vessels Oct 20 '11 at 15:47
  • Yeah changing the collation is definitely the right way to go then you won't need to mess about with case sensitivity every time you run a `LIKE` query – Clive Oct 20 '11 at 15:55
0

To search case insensitive do following in drupal 7

$query->where('UPPER(Department) LIKE :dept',  array('dept' => '%'.db_like($keyword).'%'));
$result = $query->execute();
Robson
  • 813
  • 5
  • 21
  • 40
Megz
  • 11
  • 1