Syntax error in update trouble

Question

I written that SQL command, but I'm troubling with it. I still receiving some errors, now its: "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 'HALF-LIFE sends a shock through the game industry with its combination of poundi' at line 1"

But i still don't know what is wrong. Processing command via phpmyadmin working properly, but i have to send it via php.

$sql = "UPDATE steam_games SET description = '1998. HALF-LIFE sends a shock through the game industry with its combination of pounding action and continuous, immersive storytelling. Valve's debut title wins more than 50 game-of-the-year awards on its way to being named \"Best PC Game Ever\" by PC Gamer, and launches a franchise with more than eight million retail units sold worldwide.<br><br>
            NOW. By taking the suspense, challenge and visceral charge of the original, and adding startling new realism and responsiveness, Half-Life 2 opens the door to a world where the player's presence affects everything around him, from the physical environment to the behaviors even the emotions of both friends and enemies.<br><br>
            The player again picks up the crowbar of research scientist Gordon Freeman, who finds himself on an alien-infested Earth being picked to the bone, its resources depleted, its populace dwindling. Freeman is thrust into the unenviable role of rescuing the world from the wrong he unleashed back at Black Mesa. And a lot of people he cares about are counting on him.<br><br>
            The intense, real-time gameplay of Half-Life 2 is made possible only by Source&reg;, Valve's new proprietary engine technology. Source provides major enhancements in:<br><br>
            <ul>
            <li><span class=\"copyHead\">Characters:</span> Advanced facial animation system delivers the most sophisticated in-game characters ever seen. With 40 distinct facial \"muscles,\" human characters convey the full array of human emotion, and respond to the player with fluidity and intelligence.</li>
            <li><span class=\"copyHead\">Physics:</span> From pebbles to water to 2-ton trucks respond as expected, as they obey the laws of mass, friction, gravity, and buoyancy.</li>
            <li><span class=\"copyHead\">Graphics:</span> Source's shader-based renderer, like the one used at Pixar to create movies such as Toy Story&reg; and Monster's, Inc.&reg;, creates the most beautiful and realistic environments ever seen in a video game.</li>
            <li><span class=\"copyHead\">AI:</span> Neither friends nor enemies charge blindly into the fray. They can assess threats, navigate tricky terrain, and fashion weapons from whatever is at hand.</li>
            </ul>', supported_languages = 'English<strong>*</strong>, French<strong>*</strong>, German<strong>*</strong>, Italian<strong>*</strong>, Korean<strong>*</strong>, Spanish<strong>*</strong>, Russian<strong>*</strong>, Simplified Chinese<strong>*</strong>, Traditional Chinese<strong>*</strong>, Dutch, Danish, Finnish, Japanese, Norwegian, Polish, Portuguese, Swedish, Thai<strong>*</strong><br><strong>*</strong>languages with full audio support', header_image = 'http://cdn2.steampowered.com/v/gfx/apps/220/header.jpg?t=1369255063', website = 'http://www.half-life2.com' WHERE appid = 220";

It may be the apostrophe in `Valve's` that's causing the error. Try escaping it like so `Valve\'s`, and see if that fixes it, pretty sure that's it. Also `player's` and `Valve's`, and `Source's`, and `Monster's` — Funk Forty Niner, Jun 29 '13 at 00:20
All the words with apostrophes, and you have many, escape them all with `\'`. Try that and let me know. — Funk Forty Niner, Jun 29 '13 at 00:23
I used: $myString = str_replace("'", "\'", $sql); , without results, still: 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 '\'1998. HALF-LIFE sends a shock through the game industry with its combination o' at line 1 . — Marian Kosowski, Jun 29 '13 at 00:36
Where is the rest of your code, related to `$myString`? You should've posted that. — Funk Forty Niner, Jun 29 '13 at 00:39
Why and where did you use `$myString = str_replace("'", "\'", $sql);`? — Funk Forty Niner, Jun 29 '13 at 00:43
In using `$myString = str_replace("'", "\'", $sql);`, **you are replacing ALL apostrophes**, where some of the apostrophes are needed, in both `description = '1998` and `'` as well as in the rest of that same line. Why are you doing a `str_replace` in the first place? The opening and closing apostrophes are not to be replaced by `\'`. **Don't do that**. — Funk Forty Niner, Jun 29 '13 at 00:50
Re-read my entire comments above, completely and you will see what I mean. — Funk Forty Niner, Jun 29 '13 at 00:51
@MarianKosowski I will undelete my answer, and it will only be right for you to accept it as the correct answer, given the help I've given you. Agreed? — Funk Forty Niner, Jun 29 '13 at 00:55

Dave Chen · Answer 1 · 2013-06-29T01:03:34.843

I would highly recommend preparing over replacing single quotes.

Example:

<?php

//assuming $mysqli is a connected database

$mysqli->prepare("UPDATE steam_games SET description = ?, supported_languages = ?, header_image = ?, website = ? WHERE appid = ?");

$desc = <<<EOT

1998. HALF-LIFE sends a shock through the game industry with its combination of pounding action and continuous, immersive storytelling. Valve's debut title wins more than 50 game-of-the-year awards on its way to being named \"Best PC Game Ever\" by PC Gamer, and launches a franchise with more than eight million retail units sold worldwide.<br><br>
            NOW. By taking the suspense, challenge and visceral charge of the original, and adding startling new realism and responsiveness, Half-Life 2 opens the door to a world where the player's presence affects everything around him, from the physical environment to the behaviors even the emotions of both friends and enemies.<br><br>
            The player again picks up the crowbar of research scientist Gordon Freeman, who finds himself on an alien-infested Earth being picked to the bone, its resources depleted, its populace dwindling. Freeman is thrust into the unenviable role of rescuing the world from the wrong he unleashed back at Black Mesa. And a lot of people he cares about are counting on him.<br><br>
            The intense, real-time gameplay of Half-Life 2 is made possible only by Source&reg;, Valve's new proprietary engine technology. Source provides major enhancements in:<br><br>
            <ul>
            <li><span class=\"copyHead\">Characters:</span> Advanced facial animation system delivers the most sophisticated in-game characters ever seen. With 40 distinct facial \"muscles,\" human characters convey the full array of human emotion, and respond to the player with fluidity and intelligence.</li>
            <li><span class=\"copyHead\">Physics:</span> From pebbles to water to 2-ton trucks respond as expected, as they obey the laws of mass, friction, gravity, and buoyancy.</li>
            <li><span class=\"copyHead\">Graphics:</span> Source's shader-based renderer, like the one used at Pixar to create movies such as Toy Story&reg; and Monster's, Inc.&reg;, creates the most beautiful and realistic environments ever seen in a video game.</li>
            <li><span class=\"copyHead\">AI:</span> Neither friends nor enemies charge blindly into the fray. They can assess threats, navigate tricky terrain, and fashion weapons from whatever is at hand.</li>
            </ul>

EOT;

$languages = <<<EOT

English<strong>*</strong>, French<strong>*</strong>, German<strong>*</strong>, Italian<strong>*</strong>, Korean<strong>*</strong>, Spanish<strong>*</strong>, Russian<strong>*</strong>, Simplified Chinese<strong>*</strong>, Traditional Chinese<strong>*</strong>, Dutch, Danish, Finnish, Japanese, Norwegian, Polish, Portuguese, Swedish, Thai<strong>*</strong><br><strong>*</strong>languages with full audio support

EOT;

$header_img = "http://cdn2.steampowered.com/v/gfx/apps/220/header.jpg?t=1369255063";
$website    = "http://www.half-life2.com";
$appid      = 220;

$mysqli->bind("ssssi", $desc, $languages, $header_img, $website, $appid);
$mysqli->execute();

?>

(**+1**) on my part as well. Cheers Dave – Funk Forty Niner Jun 29 '13 at 01:50 — Funk Forty Niner, Jun 29 '13 at 01:50

Funk Forty Niner · Accepted Answer · 2013-06-29T00:57:29.250

Actually, since I did give the first comments as to where the problem lay, this is worthy of an upvote, at the least.

You forgot to escape all the words that contain apostrophes.

In using $myString = str_replace("'", "\'", $sql);, as you stated in your comment and using that in order to "automate" things, you are replacing ALL apostrophes, where some of the apostrophes are needed, in both description = '1998 and </ul>' as well as in the rest of that same line.

The opening and closing apostrophes are NOT to be replaced by \', because this will throw you errors.

$sql = "UPDATE steam_games SET description = '1998. HALF-LIFE sends a shock through the game industry with its combination of pounding action and continuous, immersive storytelling. Valve\'s debut title wins more than 50 game-of-the-year awards on its way to being named \"Best PC Game Ever\" by PC Gamer, and launches a franchise with more than eight million retail units sold worldwide.<br><br>
    NOW. By taking the suspense, challenge and visceral charge of the original, and adding startling new realism and responsiveness, Half-Life 2 opens the door to a world where the player\'s presence affects everything around him, from the physical environment to the behaviors even the emotions of both friends and enemies.<br><br>
    The player again picks up the crowbar of research scientist Gordon Freeman, who finds himself on an alien-infested Earth being picked to the bone, its resources depleted, its populace dwindling. Freeman is thrust into the unenviable role of rescuing the world from the wrong he unleashed back at Black Mesa. And a lot of people he cares about are counting on him.<br><br>
    The intense, real-time gameplay of Half-Life 2 is made possible only by Source&reg;, Valve\'s new proprietary engine technology. Source provides major enhancements in:<br><br>
    <ul>
    <li><span class=\"copyHead\">Characters:</span> Advanced facial animation system delivers the most sophisticated in-game characters ever seen. With 40 distinct facial \"muscles,\" human characters convey the full array of human emotion, and respond to the player with fluidity and intelligence.</li>
    <li><span class=\"copyHead\">Physics:</span> From pebbles to water to 2-ton trucks respond as expected, as they obey the laws of mass, friction, gravity, and buoyancy.</li>
    <li><span class=\"copyHead\">Graphics:</span> Source\'s shader-based renderer, like the one used at Pixar to create movies such as Toy Story&reg; and Monster\'s, Inc.&reg;, creates the most beautiful and realistic environments ever seen in a video game.</li>
    <li><span class=\"copyHead\">AI:</span> Neither friends nor enemies charge blindly into the fray. They can assess threats, navigate tricky terrain, and fashion weapons from whatever is at hand.</li>
    </ul>', supported_languages = 'English<strong>*</strong>, French<strong>*</strong>, German<strong>*</strong>, Italian<strong>*</strong>, Korean<strong>*</strong>, Spanish<strong>*</strong>, Russian<strong>*</strong>, Simplified Chinese<strong>*</strong>, Traditional Chinese<strong>*</strong>, Dutch, Danish, Finnish, Japanese, Norwegian, Polish, Portuguese, Swedish, Thai<strong>*</strong><br><strong>*</strong>languages with full audio support', header_image = 'http://cdn2.steampowered.com/v/gfx/apps/220/header.jpg?t=1369255063', website = 'http://www.half-life2.com' WHERE appid = 220";

I recommend reading [this](http://stackoverflow.com/q/15537368/2344142). `description` is most likely user input. — Dave Chen, Jun 29 '13 at 01:15
Thanks Dave, I appreciate the info. However, I have come across that article and a good one at that. I merely fixed what the OP originally posted. — Funk Forty Niner, Jun 29 '13 at 01:38

Syntax error in update trouble

2 Answers2