您的SQL语法有错误; 检查与MySQL服务器对应的手册[关闭]

Question

I made code to try a users activity on my forum, and when I add in this line

$cat_id = $db->fetch("SELECT name FROM " . $prefix . "_categories WHERE id =" . mysql_real_escape_string($forum_data['cat_id']));
$page_title_pro = ' > ' . $system->present($cat_id['name']) . ' > ' . $system->present($forum_data['name']) . '';

I get

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 's Treasures > Contests' WHERE id = '2'' at line 1

I am assuming that the 2 is my user id, and in the footer, I have this :

$db->query("UPDATE accounts SET flocation = '$session_location', page = '$page_title_pro'  WHERE id = '$id';");

I can't seem to find the error, and every goes back to normal when i take the cat_id out, but then i can't use the current activity for the profiles. Any suggestions?

Answer 1

There is no problem with your update syntax. The problem is with the values you want to set on specific column that contains single quote which causes to break your update syntax. You need to escape the single quotes in the value before passing it on the query. One possible way is by using

mysql_real_escape_string

$val1 = mysql_real_escape_string($session_location);
$val2 = mysql_real_escape_string($page_title_pro);
$val3 = mysql_real_escape_string($id);
$db->query("UPDATE accounts SET flocation = '$val1', page = '$val2'  WHERE id = '$val3'");

Another (the PREFERRED one) is by using PreparedStatements (PDO or MySQLi extensions) you can get rid of using single quotes around values.

• How to prevent SQL injection in PHP?