0

I am trying to insert a ' symbol into my database and have the below code.

$actionurl =$_POST['actionurl'];
$newtitle = $_POST['newtitle'];
$newtitle = mysql_real_escape_string($newtitle);
$result2 = mysql_query("UPDATE links SET title='$newtitle' WHERE url='$actionurl'") 
   or die(mysql_error());

And I get this 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 's to start up a sample library (forum thread)'' at line 1

Why am I getting an error if I'm using mysql_real_escape_string on the $newtitle variable?

desbest
  • 4,746
  • 11
  • 51
  • 84
  • 4
    Why use a deprecated API anyway. Use Prepared Statements and your problems go away. – juergen d May 01 '16 at 18:07
  • For the record, you are indeed inserting the `'` using the `newtitle` field and not using the `actionurl`? – Rick May 01 '16 at 18:11
  • 2
    Please [don't use `mysql_*`](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php?rq=1); the `mysql_*` functions are outdated, [deprecated](http://us3.php.net/manual/en/intro.mysql.php), and insecure. Use [`MySQLi`](http://us3.php.net/manual/en/book.mysqli.php) or [`PDO`](http://us3.php.net/manual/en/intro.pdo.php) instead, and use prepared statements. As @juergend said, this is the better approach and solves your problem. – elixenide May 01 '16 at 18:12
  • I'm using `mysql_` because it's easier and because I can use this class to get all the `mysql_` functions to work, once everyone's switched to php 7. http://www.phpclasses.org/package/9199-PHP-Replace-mysql-functions-using-the-mysqli-extension.html – desbest May 01 '16 at 18:18
  • @Rick Yes the apostophe I am inserting is part of the `$newtitle` variable. – desbest May 01 '16 at 18:20
  • Can I solve the problem without using mysqli or pdo? – desbest May 01 '16 at 18:24
  • 1
    Are you sure your connection is `mysql_`? That should work you know. You're not introducing any `mysqli_` functions anywhere, are you? Plus, what is the value of `$actionurl`? – Funk Forty Niner May 01 '16 at 18:35
  • Plus, you may have to pass db connection to `mysql_real_escape_string()` in some rare (but not so rare) cases and the same for your query. – Funk Forty Niner May 01 '16 at 18:38
  • My connection is `mysql_`. `$actionurl` is `http://dubstepforum.com/viewtopic.php?f=8&t=165922` – desbest May 01 '16 at 18:46
  • 1
    debugging: var_dump that query string and either: inspect it or give it to an SQL IDE. You will soon spot the issue? – Ryan Vincent May 01 '16 at 18:49
  • I've fixed the problem now. – desbest May 02 '16 at 08:02
  • So, what was the root cause? Why was mysql_real_escape_string not working? (The problem may be fixed. But this question on StackOverflow really deserves to have an answer.) What debugging step confirmed that the problem was mysql_real_escape_string? And what was the fix for the problem? – spencer7593 May 02 '16 at 18:45
  • It was my fault. There was a line above the code snippet with a variable I made which didn't have `mysql_real_escape_string`, just that because the php error mentioned the `$newtitle` variable and not the `$actionurl` variable, I thought it was the mentioned variable causing it in the 2nd sql query. So basically I had two sql queries, the first sql failed but the php error showed the sql of the second one. – desbest May 03 '16 at 11:37

2 Answers2

1

I suspect that it's actionurl that is causing the error, not $newtitle.

To debug this, echo or print the SQL statement to be executed.

You can do something like this:

$sql = "UPDATE links SET title='$newtitle' WHERE url='$actionurl'";
// for debugging, output contents of the $sql string
echo "SQL=" . $sql ;
mysql_query($sql) or die(mysql_error();

As others have already suggested, the mysql_ interface is deprecated. New development should use either mysqli or PDO. And use prepared statements with bind placeholders. It just seems nonsensical to be struggling with mysql_real_escape_string in 2016.

spencer7593
  • 106,611
  • 15
  • 112
  • 140
  • But I can use `mysql_` in php 7 with this class, that converts `mysql_` to `mysqli_` http://www.phpclasses.org/package/9199-PHP-Replace-mysql-functions-using-the-mysqli-extension.html – desbest May 01 '16 at 18:49
  • It seems the problem may be related to the package you are using to implement the mysql_ functions that were *removed* from PHP 7. There could be a problem with the implementation of `mysql_real_escape_string` from that package. – spencer7593 May 01 '16 at 19:01
-1

Are magic quotes on in your php.ini? If yes, disabling it should solve your issue. (It could be enabled by default)

Denis Leger
  • 213
  • 2
  • 7
  • http://php.net/manual/en/security.magicquotes.disabling.php - Please add the recommended lines to your php.ini - I suppose magic quotes are enabled by default in your system - and don't forget to restart Apache. – Denis Leger May 01 '16 at 18:33
  • Instead of down voting this post, did you really try my suggestion? I agree with others that it is now best to use PDO instead of mysql_ interface, but the real cause of the behaviour in this post should really be related to magic quotes being on. You can find other posts even on this website with exactly the same behaviour and same explanation. – Denis Leger May 01 '16 at 23:31