SQL Syntax Error Code 1064

Question

My code is here;

$m = 0.5;

$this->db->query("update chatusers set money = money - ".$m." where user = '".$this->input->post('member')."'");

and I'm getting this error;

A Database Error Occurred

Error Number: 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 'where user = ''' at line 1

update chatusers set money = money - where user = ''

Filename: /home/modenatu/public_html/beta/models/model/mupdate.php

Line Number: 87

I couldn't find how to fix it. Thanks in advance.

Answer 1

It looks like $m is evaluating to an empty string, based on the error message.

It looks like $this->input->post('member') is also evaluating to an empty string.

update chatusers set money = money - where user = ''
                                    ^              ^

Because if those two evaluated to non-empty strings (say for example 'foo' and 'bar' respectively) we'd expect the SQL to be like this:

update chatusers set money = money - foo where user = 'bar'
                                     ^^^               ^^^

---

Thankfully, $m didn't evaluate to a more nefarious string. Such as money WHERE 1=1 --

Which would have produced a statement like this:

update chatusers set money = money - money WHERE 1=1 -- foo where user = ''

The symptom of the problem is invalid syntax. The real problems here are 1) potential for SQL Injection (we don't see what values $m contains), and 2) code is producing invalid SQL statement.

For the sake of everything good and beautiful in this world... I can't emphasize this enough... use prepared statements with bind placeholders.

For example:

  $sql = 'UPDATE chatusers SET money = money - ? WHERE user = ?';
  $this->db->query($sql, array(0, 'me'));