0

I have this MySQL query:

$querynotis = "SELECT * FROM notifications WHERE pid = " . $_SESSION['sess_id'] . " AND read = 0";

And it gives me the 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 'read = 0' at line 1

If I remove the:

AND read = 0

part, it works perfectly fine. What could I have done wrong?

nickhar
  • 19,981
  • 12
  • 60
  • 73
Magnus
  • 391
  • 1
  • 7
  • 35
  • Print the value of `$querynotis` before trying to execute the query; you'll probably notice something. – mustaccio May 02 '14 at 20:36
  • possible duplicate of [Syntax error due to using a reserved word as a table or column name in MySQL](http://stackoverflow.com/questions/23446377/syntax-error-due-to-using-a-reserved-word-as-a-table-or-column-name-in-mysql) – Ian Ringrose May 06 '14 at 10:05

2 Answers2

5

READ is a Reserved Keyword and happens to be the name of your column. In order to avoid syntax error, the column name should be escaped using backticks. Ex,

$pid = $_SESSION['sess_id'];
$querynotis = "SELECT * FROM notifications WHERE pid = $pid AND `read` = 0";

Another way, rather than escaping it with backtick:

$pid = $_SESSION['sess_id'];
$querynotis = "SELECT * FROM notifications n WHERE pid = $pid AND n.read = 0";

If you have the privilege to alter the table, change the column name that is not on the Reserved Keyword List to prevent the same error from getting back again on the future.

As a sidenote, the query is vulnerable with SQL Injection if the value(s) of the variables came from the outside. Please take a look at the article below to learn how to prevent from it. By using PreparedStatements you can get rid of using single quotes around values.

Community
  • 1
  • 1
John Woo
  • 258,903
  • 69
  • 498
  • 492
  • Thank you, didn't know that. I'll edit the column-name right away. The variables does not come from the outside so I'll not be having troubles with SQL injections. Thanks for the heads up though. – Magnus May 11 '13 at 16:24
0

use this query, you are trying to use reserved keyword of mysql. READ is a reserve keyword. Use backtick operator

$querynotis = "SELECT * FROM notifications WHERE pid = " . $_SESSION['sess_id'] . " AND `read` = '0'";
chandresh_cool
  • 11,753
  • 3
  • 30
  • 45