MYSQL Syntax Error - SELECT statement

Question

I'm getting this error displayed on my screen I have been trying to debug.

"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 'to = 'testname'' at line 1"

my function im using for this is as follows:

function recentMessages() {
    $tbl_name="messages";
    $username = $_SESSION['username'];
    $result = mysql_query("SELECT * FROM $tbl_name WHERE to = '$username' ") or die(mysql_error());
    while ($row = mysql_fetch_row($result))
      {
        return $row['date']." ".$row['time']." ".$row['from']." ".$row['subject']. "<br />";    
      }
}

Basically what im trying to do is to get all the rows of data from the database messages where who its 'to' is the username of the session and its echo'd out. Any ideas on what im doing wrong? thanks

@BrendanLong it's a valid point (of course) to escape variables in sql but: if it's trivial to modify session data, sql injection would be the least of your application's worries. Andrew - consider having $username = "' OR 1=1 or x='" — AD7six, Oct 23 '12 at 21:34
You should really [escape string data](http://php.net/manual/en/function.mysql-real-escape-string.php) from external sources before using it in the database, even if you think it is probably safe. — Brendan Long, Oct 23 '12 at 21:37

score 10 · Accepted Answer · answered Oct 23 '12 at 21:29

10

to is a reserved word. Encase it in tick marks.

... WHERE `to` = '$username'

See the MySQL reserved words.

You should avoid using reserved words if possible.

answered Oct 23 '12 at 21:29

Kermit

33,827
13
85
121

Nice ty! Didn't even realize they have reserved words for mysql! – Nexus9988 Oct 23 '12 at 21:31
@AndrewL Most (if not all) DBMS's have reserved words. – Kermit Oct 23 '12 at 21:32

score 1 · Answer 2 · answered Oct 23 '12 at 21:32

The to is a reserved word. Try this:

$result = mysql_query("SELECT * FROM $tbl_name WHERE `to` = '$username' ") 
                or die(mysql_error());

In general try to avoid small words like to, between, from ... e.t.c. just to prevent this kind of issues. A better solution is to have a field name like : "receiver" or "message_to" or something similar

score 0 · Answer 3 · answered Oct 23 '12 at 21:32

0

TO is Reserved Words in MySQL. Use backticks to Separates that.

SELECT * FROM $tbl_name WHERE `to` = '$username'

answered Oct 23 '12 at 21:32

Rush

740
4
13

score -1 · Answer 4 · answered Oct 23 '12 at 21:30

-1

to is a reserved word I believe. Try changing to to [to] Edit: Wasn't sure entirely. I put it in SQL Server and saw that TO was a reserved word.

answered Oct 23 '12 at 21:30

sjramsay

555
1
5
12

I believe brackets only apply to MSSQL, my MySQL. – Kermit Oct 23 '12 at 21:31
1

Geez the voting on this was harsh. This answer is still correct about the problem (reserved word) and just has syntax for the wrong database.. – Brendan Long Oct 23 '12 at 21:34
@BrendanLong Original answer strictly said to encase in brackets. – Kermit Oct 23 '12 at 21:45

MYSQL Syntax Error - SELECT statement

4 Answers4