db_query(): Problems with variable substitution and LIKE

I was working on porting a module to D6 this morning. In the process of doing the port I wanted to clean up the UI a little bit so I converted some of the larger select elements to autocomplete. Here's a look at the autocomplete function:

function mymodule_data_autocomplete($string='') {
  $matches = array();
  if(strlen($string > 2) {
    $results = db_query('SELECT data FROM {mymodule_data} WHERE data LIKE "%%s%"',
$string);
    while($result = db_result($results)) {
      $matches[$result] = $result;
    } 
  }
  drupal_json($matches);
}

After setting up the menu callback I quickly noticed my autocomplete function wasn't filtering results. Regardless of what input it was given it cheerfully returned the entire list of available values from the database. I figured my query must be off so I dropped to a command line and tested it with mysql. Nope, works as advertised.

I spent a few minutes scratching my head then replaced the "%%s%" with "%' . $string . '%". This got the function to start filtering based on input...sort of. I was getting on average half the expected results back. After more head scratching and tinkering with the input field I realized that LIKE was doing case sensitive search without the BINARY keyword! WTF?

According to google this kind of behavior is a known issue.

Attempted fix #1: doubling up the %'s

   $results = db_query('SELECT data FROM {mymodule_data} WHERE data LIKE "%%s%%"',
$string);

No dice. Still getting every record in the database.

Attempted fix #2:mod the string

  $string = '%' . $string . '%';
  $results = db_query('SELECT data FROM {mymodule_data} WHERE data LIKE "%s"',
$string);

Bingo! For whatever reason bracketing a variable substitution (eg. %s, %d) with %'s breaks the variable substitution, but adding the %'s to the string works like a charm. Go figure.