MYSQL PHP - You have an error in your SQL syntax

Question

why i always get error in hostgator when try to update this ga field in database, error happen when insert this code in textarea

_gaq.push(['_setCustomVar',1, 'Status', 'Logged In']);
_gaq.push(['_trackEvent', 'Custom', 'PageLoad', 'Setting Logged In State',0,true]);

<textarea name="ga"></textarea>

$name = $_POST['name'];
$ga = trim($_POST['ga']);

$req = "UPDATE `con` SET `name` = '".$name."', `ga` = '".$ga."'");
if (mysqli_query($con, $req)) {
  echo "success"
}

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 '_setCustomVar',1, 'Status', 'Logged In']); _gaq.push(['_trackEvent', 'Custom', ' at line 1

if i remove ga = '".$ga."' my database update but if add this code it give error i have hostgator and it work good in localhost

type of ga in database is text.

Can you echo out $req and let us know what the string value is? — Adam, Aug 01 '18 at 15:18
that isn't how UPDATE works. Please take the time to read the manual on this https://dev.mysql.com/doc/refman/8.0/en/update.html. It's a typographical error and voting to close as such. — Funk Forty Niner, Aug 01 '18 at 15:19
Your code is vulnerable to [**SQL injection**](https://en.wikipedia.org/wiki/SQL_injection) attacks. You should use prepared statements with bound parameters, via either [**mysqli**](https://secure.php.net/manual/en/mysqli.prepare.php) or [**PDO**](https://secure.php.net/manual/en/pdo.prepared-statements.php). [**This post**](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) has some good examples. — Alex Howansky, Aug 01 '18 at 15:20
The problem is that your $ga string contains ' characters, and they are not escaped properly. You should do as @AlexHowansky said and switch to prepared statements (where escaping will be handled automatically). — Naomi, Aug 01 '18 at 15:21
please forget about the sql injection i know, i will take care of it, i want just this simple update to work. — John Jack, Aug 01 '18 at 15:22
Again, [**RTM**](https://dev.mysql.com/doc/refman/8.0/en/update.html). — Funk Forty Niner, Aug 01 '18 at 15:22
you also forgot a bracket, yet there's one too many; another typographical error. — Funk Forty Niner, Aug 01 '18 at 15:23
@Dammeul UPDATE `con` SET `name` = 'Hello', `ga` = '_gaq.push(['_setCustomVar',1, 'Status', 'Logged In']); _gaq.push(['_trackEvent', 'Custom', 'PageLoad', 'Setting Logged In State',0,true]);' — John Jack, Aug 01 '18 at 15:24
_"please forget about the sql injection i know, i will take care of it"_ That's exactly what Doug used to say. See you later, New Doug! — Alex Howansky, Aug 01 '18 at 15:26
Also, many have said it, but your using an Update without specifying the row. — Adam, Aug 01 '18 at 15:35
You cannot "forget about the sql injection" and take care of it later... Your current bug is due to no data sanitization, which is also the way to prevent SQL injections. You have to fix the SQL injection for your bug to go away. (Also, like everyone else said, your UPDATE needs a WHERE clause, or it will update every row in the table) — Naomi, Aug 01 '18 at 15:46
You probably have the PHP setting "magic_quotes_gpc" activated on your localhost, and that's why it works locally. It's not a good practice to rely on this setting. https://stackoverflow.com/questions/2610524/why-is-turning-magic-quotes-gpc-on-considered-a-bad-practice — Naomi, Aug 01 '18 at 15:53
PDO is the way to go in my opinion, but if you use mysqli, use prepared statements to avoid SQL injection...https://stackoverflow.com/questions/18316501/php-update-prepared-statement — Adam, Aug 01 '18 at 15:54
@Naomi do i need to activate magic_quotes_gpc in my host? is it secure to enable it.? for other yes i have where clause i forgot to add it..because it is not the problem — John Jack, Aug 01 '18 at 16:11
Note: The object-oriented interface to `mysqli` is significantly less verbose, making code easier to read and audit, and is not easily confused with the obsolete `mysql_query` interface. Before you get too invested in the procedural style it’s worth switching over. Example: `$db = new mysqli(…)` and `$db->prepare("…")` The procedural interface is an artifact from the PHP 4 era when `mysqli` API was introduced and should not be used in new code. — tadman, Aug 01 '18 at 18:13
@Naomi [Magic quotes were removed in PHP 5.4](http://php.net/manual/en/security.magicquotes.php) so unless this is an ancient PHP 5.3 installation it's unlikely that's the case. If that is the case then this bubbling cauldron of trouble just got even more bubbly. It seems that the incoming data just has a lot of quotes in it because it's roughly a JSON array. — tadman, Aug 01 '18 at 18:14
@tadman I know, but he does say that it works on his localhost... and there is no way that his string will not ruin the SQL query syntax if the single quotes are not escaped... and he obviously does not escape them himself... so it's the only logical explanation I see. — Naomi, Aug 01 '18 at 18:35
@Naomi You're right that it's possible, but I'm suspicious. I'm not even sure where you'd get a PHP 5.3 installer. — tadman, Aug 01 '18 at 18:46

Saral · Answer 1 · 2018-08-01T18:43:00.740

Prepared statements are the one stop solution for this. Your code is supposed to be:

$name = trim($_POST['name']);
$ga = trim($_POST['ga']);
$some_value = 'some_value'; //This is just the imaginary value for demonstration

$req = "UPDATE `con` SET `name` = ?, `ga` = ? WHERE column_name = ?";
$stmt = $mysqli->prepare($req);
$stmt->bind_param('sss', $name, $ga, $some_value);
$stmt->execute();

You always want to use WHERE with UPDATE query. If you don't use WHERE clause, the changes will be applied to all records. I suppose you are aware of SQL injection, the notorious attack!

An alternative which can be used to fix the issue with your code is mysqli_real_escape_string(). For example,

$name = mysqli_real_escape_string($con, trim($_POST['name']));
$ga = mysqli_real_escape_string($con, trim($_POST['ga']));

But always use prepared statements as I've shown above.

MYSQL PHP - You have an error in your SQL syntax

1 Answers1