Shay Mualem Shay Mualem - 6 months ago 24
MySQL Question

SQL query syntax error near ''

I'm trying to use Magento theme and when I import the theme data I get an sql error that says

SQLSTATE[42000]: Syntax error or access violation: 1064 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 '' at line 2, query was:

SELECT `#__cms_page`.*
FROM `#__cms_page`
INNER JOIN `#__cms_page_store` AS `cms_page_store`
ON #__cms_page.page_id = cms_page_store.page_id
WHERE (`#__cms_page`.`identifier`='home-demo-01')
AND (is_active = 1)
AND (cms_page_store.store_id IN (0, 1))
ORDER BY `cms_page_store`.`store_id` DESC
LIMIT 1


I can't seems to find the problem as it says the syntax error is near nothing..

Answer

You've not escaped the identifier reference in the ON clause.

i.e. you're missing backticks here:

ON  #__cms_page .page_id  = cms_page_store.page_id
   ^           ^

If we're going to escape identifiers, we prefer to do it consistently. My preference would be to assign a short alias to the table. Also, the reference to is_active should be qualified... which table is that column coming from?

My tendency would be to write the query like this, and only escape the identifiers that actually need to be escaped:

  SELECT p.*
    FROM `#__cms_page` p
    JOIN `#__cms_page_store` s
      ON s.page_id = p.page_id
   WHERE ( p.identifier = 'home-demo-01' ) 
     AND ( p.is_active = 1 ) 
     AND ( s.store_id IN (0, 1) ) 
   ORDER BY s.store_id DESC
   LIMIT 1

If I was going to escape identifiers that don't need to be escaped, I would enclose all of them in backticks, everywhere.