Error: (1064) in php.

Need new set of eyes!

[jay7981]

ok i am making a page to update a table in my db, i am not a noob at things of this nature but for some reason i cannot find the error in this code ... can someone take a look and see if they can find it? Thanks in advance!

as stated i am getting this error:

A MySQL error has occurred.
Your Query:
Error: (1064) 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 'int = '51', attk = '61', wgt = '71', sell = '81', bonus = 'test1', loc = '901' W' at line 1

Update.php

<?php
include("connect.php");
$id = $_GET['id'];
$qProfile = "SELECT * FROM `mytable` WHERE `id` = $id";
$rsProfile = mysql_query($qProfile);
$row = mysql_fetch_array($rsProfile);
extract($row);
$name = stripslashes($name);
$str = stripslashes($str);
$end = stripslashes($end);
$agi = stripslashes($agi);
$wis = stripslashes($wis);
$int = stripslashes($int);
$attk = stripslashes($attk);
$wgt = stripslashes($wgt);
$sell = stripslashes($sell);
$bonus = stripslashes($bonus);
$loc = stripslashes($loc);
?>
<div align="center"><h1 align="center">Update <?php echo $name ?></h1></div>
<form id="update" action="updated.php" method="post" name="update">
<table width="448" border="0" align="center" cellpadding="0" cellspacing="2">
<tr><td width="150"><div align="right">
<label for="name">Armor Name</label></div>
</td>
<td>
<input id="name" name="name" type="text" size="25" value="<?php echo $name ?>" maxlength="255"></td>
</tr>
<tr><td width="150"><div align="right">Strength</div>
</td>
<td>
<input id="str" name="str" type="text" size="25" value="<?php echo $str ?>" maxlength="255"></td>
</tr>
<tr><td width="150"><div align="right">Endurance</div>
</td>
<td>
<input id="end" name="end" type="text" size="25" value="<?php echo $end ?>" maxlength="255"></td>
</tr>
<tr><td width="150"><div align="right">Agility</div>
</td>
<td>
<input id="agi" name="agi" type="text" size="25" value="<?php echo $agi ?>" maxlength="255"></td>
</tr>
<tr><td width="150"><div align="right">Wisdom</div>
</td>
<td>
<input id="wis" name="wis" type="text" size="25" value="<?php echo $wis ?>" maxlength="255"></td>
</tr>
<tr><td width="150"><div align="right">
<label for="int">Intellect</label></div>
</td>
<td>
<input id="int" name="int" type="text" size="25" value="<?php echo $int ?>" maxlength="255"></td>
</tr>
<tr><td width="150"><div align="right">Attack</div>
</td>
<td>
<input id="attk" name="attk" type="text" size="25" value="<?php echo $attk ?>" maxlength="255"></td>
</tr>
<tr><td width="150"><div align="right">Weight</div>
</td>
<td>
<input id="wgt" name="wgt" type="text" size="25" value="<?php echo $wgt ?>" maxlength="255"></td>
</tr>
<tr><td width="150"><div align="right">
<label for="sell">Sell Price</label></div>
</td>
<td>
<input id="sell" name="sell" type="text" size="25" value="<?php echo $sell ?>" maxlength="255"></td>
</tr>
<tr><td width="150"><div align="right">Bonus</div>
</td>
<td><textarea id="bonus" name="bonus" rows="4" cols="40"><?php echo $bonus ?></textarea></td>
</tr>
<tr><td width="150"><div align="right">
<label for="loc">Location</label></div>
</td>
<td>
<input id="loc" name="loc" type="text" size="25" value="<?php echo $loc ?>" maxlength="255"></td>
</tr>
<tr>
 <td width="150"></td>
 <td><input type="submit" name="submit" value="Update"><input type="hidden" name="id" value="<?php echo $id ?>"></td>
</tr>
</table>
</form>
<?php mysql_close();?>

updated.php

<?php
include("connect.php");
$id = $_POST['id'];
$name = $_POST['name'];
$str = $_POST['str'];
$end = $_POST['end'];
$agi = $_POST['agi'];
$wis = $_POST['wis'];
$int = $_POST['int'];
$attk = $_POST['attk'];
$wgt = $_POST['wgt'];
$sell = $_POST['sell'];
$bonus = $_POST['bonus'];
$loc = $_POST['loc'];
$update = "UPDATE `mytable` SET name = '$name', str = '$str', end = '$end', agi = '$agi', wis = '$wis', int = '$int', attk = '$attk', wgt = '$wgt', sell = '$sell', bonus = '$bonus', loc = '$loc' WHERE `id` = $id ";
$rsUpdate = mysql_query($update) or die("A MySQL error has occurred.<br />Your Query: " . $your_query . "<br /> Error: (" . mysql_errno() . ") " . mysql_error());
if ($rsUpdate)
{
echo ("<p align='center'><font face='Arial' size='5' color='#000000'>Armor '$name' Updated/font></p>");
} mysql_close();
?>

[whoisgregg]

Welcome to WebmasterWorld, jay7981!

That error is a "reserved word" error. There are certain words you can't use in queries because they have special meaning. In your case, you can simply put back ticks around each field name and it should work fine.

$update = "UPDATE `mytable` SET `name` = '$name', `str` = '$str', `end` = '$end', `agi` = '$agi', `wis` = '$wis', `int` = '$int', `attk` = '$attk', `wgt` = '$wgt', `sell` = '$sell', `bonus` = '$bonus', `loc` = '$loc' WHERE `id` = $id "; 

However, your script is currently susceptible to sql injection attacks. Take a few moments to read about sql injection attacks [php.net], then start using mysql_real_escape_string() [php.net] every time you pass user provided data to a query.

[jay7981]

thanks a ton that worked like a charm ... and as for the sql injections, these scripts will only be accessable locally on an intranet so i am not to concerned about data security at this point but thanks for that link and info i will definatly read up on it.

[idfer]

Even if you trust your visitors you still need to escape special SQL characters like single-quotes inside string values, and calling mysql_real_escape_string() is the easiest and most standard way of doing it. In older versions of PHP, it was common to turn on the magic_quotes option (this automatically escapes all single-quotes inside submitted values) but that's not as useable and is being deprecated.

Try entering something like "Gorgon's Shield" for Armor Name in your form. If it works, then it means you have magic_quotes on, so you're sort of ok, otherwise you should pass all your string values through mysql_real_escape_string() inside your SQL statement.

[Baruch Menachem]

What does mysql_real_escape_string() do to the input?

I put a bit of mysql (select * from users where clue>0;)code into an unprotected insertion and into a protected insertion. I looked at the database both times, and the same string was in the database. But when I did a search for the term select, the php code returned something that made firefox close all the tabs, and the second time the database couldn't find the term select, even though it was in the database.

Cool if the guy is trying to stick code into your database, and all he manages to do is get no joy out of it.

[jay7981]

so if i am correct in my understanding all i would have to do is change

$id = $_POST['id'];
$name = $_POST['name'];
$str = $_POST['str'];
$end = $_POST['end'];
$agi = $_POST['agi'];
$wis = $_POST['wis'];
$int = $_POST['int'];
$attk = $_POST['attk'];
$wgt = $_POST['wgt'];
$sell = $_POST['sell'];
$bonus = $_POST['bonus'];
$loc = $_POST['loc']; 

to this

$id = mysql_real_escape_string($_POST['id']);
$name = mysql_real_escape_string($_POST['name']);
$str = mysql_real_escape_string($_POST['str']);
$end = mysql_real_escape_string($_POST['end']);
$agi = mysql_real_escape_string($_POST['agi']);
$wis = mysql_real_escape_string($_POST['wis']);
$int = mysql_real_escape_string($_POST['int']);
$attk = mysql_real_escape_string($_POST['attk']);
$wgt = mysql_real_escape_string($_POST['wgt']);
$sell = mysql_real_escape_string($_POST['sell']);
$bonus = mysql_real_escape_string($_POST['bonus']);
$loc = mysql_real_escape_string($_POST['loc']); 

[whoisgregg]

Yup, that should do the trick jay7981. :)