How to properly skip apostrophe in db_select?

Question

In my mysql database I have texts full of apostropes which are used as stress marks. I need to implement keyword search ignoring these marks.

I tested it first by using direct sql in PhpMyAdmin:

SELECT * FROM `drupal_corpus_text` WHERE replace(text, "'", "") LIKE "%sinka%"

It perfectly finds the record containing sin'ka.

But I cannot figure how to do it using Drupal features.

Here is what I try to do:

  $apostrophe = "'";
  $query = db_select('corpus_text', 'n')
    ->fields('n', array('text_id', 'speaker', 'text'))
    ->condition('n.text', '%' . db_like($key_search) . '%', 'LIKE')
    ->orderBy('speaker', 'ASC');
  $query->addExpression('REPLACE(n.text, "'.$apostrophe.'", "")');
  $query->execute();

And here is the error I get:

PDOException: SQLSTATE[42S22]: 
Column not found: 1054 Unknown column ''' in 'field list': 
SELECT n.text_id AS text_id, n.speaker AS speaker, n.text AS text, 
REPLACE(n.text, "'", "") AS expression FROM {corpus_text} n 
WHERE (n.text LIKE :db_condition_placeholder_0 ESCAPE '\\') ORDER BY speaker ASC

I cannot figure out how I must properly use all the quotes and the apostrophe mark to make it work.

The symbol used in those texts is U+0027.

Answer 1

There are a couple of points here:

1. Your Drupal query is wrong insofar as it wouldn't produce your example SQL. You don't need to add an expression, rather a where clause

2. Quotes are fun in MySQL (see SQL modes). Your default mode would appear to be ANSI_QUOTES, which means:

   Treat “"” as an identifier quote character (like the “`” quote character) and not as a string quote character.

   Which explains why MySQL is trying to interpret the single quote as a column name. PMA probably sets a different mode per-query, so the same SQL works in that environment.

Taking those into account, this is more like what you're hoping for:

$query = db_select('corpus_text', 'n')
  ->fields('n', array('text_id', 'speaker', 'text'))
  ->where("REPLACE(n.text, '''', '') LIKE :search", array(':search' => '%' . db_like($key_search) . '%'))
  ->orderBy('speaker', 'ASC');

Which produces:

SELECT  n.text_id AS text_id, n.speaker AS speaker, n.text AS text
FROM 
{corpus_text} n
WHERE  (REPLACE(n.text, '''', '') LIKE :search) 
ORDER BY speaker ASC

Notice that the single quote is escaped with two single quotes, MySQL's way of handling this situation.

Answer 2

The Drupal database layer has support for query arguments. A good example of this is the db_query syntax:

$query = db_query('SELECT n.nid, n.title, n.created FROM {node} n WHERE n.uid = :uid', array(':uid' => $uid))

As you can see there are placeholders in the query, which are appended in the end. These placeholders are recognizable by the colon (:) and are sanitized in the end by Drupal to prevent SQL injections and syntax errors.

The SelectQuery::addExpression function also has support for these arguments SelectQuery::addExpression($expression, $alias = NULL, $arguments = array()). So you might want to try something like this:

$apostrophe = "'";
$query = db_select('corpus_text', 'n')
  ->fields('n', array('text_id', 'speaker', 'text'))
  ->condition('n.text', '%' . db_like($key_search) . '%', 'LIKE')
  ->orderBy('speaker', 'ASC');
$query->addExpression('REPLACE(n.text, ":apostrophe", "")', NULL, array(':apostrophe' => $apostrophe));
$query->execute();

If that fails, you can safely rewrite your query to the slightly faster db_query method. This is perfectly safe for SELECT queries (and for select queries only!) and faster because it involves less abstraction, so less processing to transform into a final query. It is however less flexible in cases where you want to add conditions based on variable values. So you cannot do this:

$query = db_select(...);
if ($foo) {
  $query->condition(...);
}

Just remember to use the placeholders for all user entered input. So I guess it will be something like this. Not sure if the apostrophe needs escaping and you can always place it in a placeholder.

$query = db_query('SELECT * FROM {drupal_corpus_text} WHERE REPLACE(text, "'", "") LIKE "%:search%"', array(':search' => $key_search));