3

Well. Whenever i try to get more than one WHERE condition in a it just gives me a SQL syntax error.

The error:

Error Executing Database Query.

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 'read = '0'' at line 3

Resources:
Enable Robust Exception Information to provide greater detail about the source of errors. In the Administrator, click Debugging & Logging > Debug Output Settings, and select the Robust Exception Information option.
Check the ColdFusion documentation to verify that you are using the correct syntax.
Search the Knowledge Base to find a solution to your problem.
Browser     Mozilla/5.0 (Windows NT 6.3; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/38.0.2125.104 Safari/537.36
Remote Address      ::1
Referrer    
Date/Time   21-Oct-14 08:27 PM

The SQL query itself:

<cfquery name = "getUserAlerts" datasource = "#DSN#">
SELECT *
FROM user_alerts
WHERE client_id = '#clientUser.id#' 
AND read = '0'
</cfquery>

On my sight, there is nothing wrong with the SQL query. And it just should work, is there anyone who can see the mistake i written? Or possible give the fix?

Thanks in advance!

Edit:

Thanks for the quick answers you gave me. The backticks worked! Thanks guys!

Leigh
  • 28,765
  • 10
  • 55
  • 103
Donnyschr
  • 111
  • 1
  • 3
  • 1
    If at all possible, consider renaming the column instead. Using reserved words as object names is not a great idea. Plus, you won't have to remember to escape it every time you use it :) – Leigh Oct 21 '14 at 18:52
  • If the backticks worked, you should probably select it as the answer. Selecting it as the answer marks it as the likely solution for anyone else with the same problem. Additionally, it rewards the user some additional reputation. The QUestion Asker can mouse over the hollow question mark and click it to select the answer. You may only select one answer per question. http://imgur.com/OGpc8xV.png. – Regular Jo Oct 21 '14 at 19:29
  • Yep. Though new members are usually forced to wait a short period of time before they can accept an answer. (I am assuming that is why they have not done so already :). – Leigh Oct 21 '14 at 19:47
  • Also, add a CFQUERYPARAM around #clientUser.id#. – Adrian J. Moreno Oct 22 '14 at 15:45

2 Answers2

7

The error is probably due the fact that READ is a reserved word. Either rename the column entirely (preferable) or escape it using backticks.

As an aside, you should be using cfqueryparam on all variable query parameters. CFQueryparam offers a number of benefits (data type checking, improving query performance, etcetera). One of the most important is helping to prevent sql injection, which can be an issue even with quoted strings.

Community
  • 1
  • 1
Leigh
  • 28,765
  • 10
  • 55
  • 103
4

read is a reserved word: http://dev.mysql.com/doc/refman/5.5/en/reserved-words.html

You'll have to escape it:

WHERE ...
 AND `read` = '1'
     ^----^--

note that those are backticks, not single-quotes.

Marc B
  • 356,200
  • 43
  • 426
  • 500