2

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?

Chris Laplante
  • 29,338
  • 17
  • 103
  • 134
Malik
  • 123
  • 4
  • 10
  • What is `$prefix` content? – Iswanto San Mar 16 '13 at 03:17
  • The value you are retrieving has a ' in it. 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. It's the data of `name` that is messing it up. – Mario Wenig Mar 16 '13 at 03:19
  • $prefix is the table prefix, which is forum @MarioWenig: I can't find that in any lines of my codes It's the footer code, but how do i change it? – Malik Mar 16 '13 at 03:20
  • @Malik it's in the value of `$page_title_pro`, you need to escape it. if you're using `PDO` use prepared statements. – tradyblix Mar 16 '13 at 03:22
  • It's not in your code, it's the value that gets returned. Example: Lets say your name returns as **Giligan's Island** the single quote breaks your query. – Mario Wenig Mar 16 '13 at 03:27

2 Answers2

5

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.

Community
  • 1
  • 1
John Woo
  • 258,903
  • 69
  • 498
  • 492
3

mysql_real_escape_string your input value for the Update query, its $page_title_pro from the previous query that has a ' in it.

Mario Wenig
  • 122
  • 4