Shay Mualem Shay Mualem - 4 months ago 12
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

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


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.