SQL Error: SQL Syntax Error

Question

Error Message: SQL Error: 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 'AND domain = 'ard.qc' AND snapshot_id = 2010 AND locale = 'en_US'' at line 1

SQL Query:

SELECT 
  entity_id, 
  content_id
FROM collateral_cms_mapping 
WHERE entity_id IN ({$entity_ids}) 
  AND domain = '{$this->getSite()->getInternalId()}' 
  AND snapshot_id = {$this->getSnapshotDao()->getCurrentSnapshot()} 
  AND locale = '{$locale}'

Here is the actual SQL after value replace and string concatenation:

    SELECT 
      entity_id, 
      content_id 
    FROM
      collateral_cms_mapping 
   WHERE 
     entity_id 
     IN 
      () 
     AND 
      domain = 'ard.qc' 
     AND 
      snapshot_id = 2009 
     AND 
      locale = 'en_US'

Any Suggestions ?

Answer 1

What's the value of $entity_ids? Probably it's empty, so your query contains IN () which is invalid.

Answer 2

You do not to concatenate your strings.

It looks like the prepared query is faulty somewhere in the IN ({$entity_ids}). echo the query string to check your IN statement.

Answer 3

Definitely look at the IN statement. While I'm not familiar with MySql, I don't believe an empty IN () clause is permitted in DB2 or Oracle (although I won't swear to it).