How to use mysql_real_escape_string with UPDATE x SET y WHERE z

PHP
1

I’m attempting to update a mysql field with a value that contains an apostrophe.
Althoug the command I’m using is deprecated, I would still like to use this command. For this particular case, SQL injections do not apply.

Most online syntax examples provide something like

 $topic = mysql_real_escape_string($_POST['topic']);

as an example, but my data is not coming from an HTML form $_Post. It’s coming from a previously called variable that contains the apostrophe.

After reviewing lots of posts, I still can’t figure out the syntax.

$bookQuery = 
"

UPDATE book    
SET title = mysql_real_escape_string($alternateTitle)
WHERE id = $newTitle_id"
;

// Execute the Query
$result = $db->query($bookQuery);

Any help in fixing this would be greatly appreciated. Thanks.

2

$bookQuery =
"
UPDATE book
SET title = " . mysql_real_escape_string($alternateTitle) . "
WHERE id = $newTitle_id"
;
// Execute the Query
$result = $db->query($bookQuery);

3

You escape data when it is being jumbled with code so as to ensure that the data doesn’t get misinterpreted as code. Where it came from doesn’t matter. Data that comes from the user should be validated. Validation is an input process while escaping is an output process.

With mySQL you can avoid this issue using prepare statements that keep the code in one statement and the data in a completely separate statement.

The $db->query statement indicates that you must be using either the mySQLi interface or PDO as the mySQL interface didn’t support object oriented calls. mysql_real_escape_string() is a part of the antiquated mysql interface that is in the process of being removed from PHP. You can’t mix the three interfaces together, you must use one of the three for all of your calls - preferably one of the two that will still be supported next year.

4

Thanks Guido! Although this syntax will display $alternateTitle properly on screen via echo, it will not update $alternateTitle to the database.

5

If you echoed the query you might have noticed that the quotes around the title are missing.

$bookQuery = “UPDATE book SET title = '” . mysql_real_escape_string($alternateTitle) . “’ WHERE id = $newTitle_id”;
// Execute the Query
$result = $db->query($bookQuery);

6

Sorry, I did echo it, which is how I noticed it displayed correctly, but I failed to see the missing quotes. :frowning: At any rate, this one echoes just fine as well, but the entry in the database is blank. No NULL or anything. Just a blank space. Sorry for not being able to figure this out on my own.

7

Do you have error reporting on? You can also look in your error log.

Add in this code at the top of your file. You should get errors.

error_reporting(E_ALL);

8

Gads, you guys are quick … I had hoped to answer before someone answered … I’m a dope. Guido was right. When I cut and pasted, there were two $$before the variable. I fixed this and the entry and display are perfect. THANKS!

9

Many people don’t come to Sitepoint on the weekends. No fear however! I have no life. Always here to help :slight_smile: .

Glad it’s sorted.

10

Ha – 9:30 pm on a Friday. Where am I?! :smile:

1 Like
11

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.