homepage Welcome to WebmasterWorld Guest from 54.227.146.68
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor 2014
Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
Forum Library, Charter, Moderators: coopster & jatar k

PHP Server Side Scripting Forum

    
Error: (1064) in php.
Need new set of eyes!
jay7981




msg:3914576
 5:58 pm on May 15, 2009 (gmt 0)

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




msg:3914616
 7:04 pm on May 15, 2009 (gmt 0)

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




msg:3914803
 2:19 am on May 16, 2009 (gmt 0)

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




msg:3914965
 3:30 pm on May 16, 2009 (gmt 0)

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




msg:3915339
 4:12 pm on May 17, 2009 (gmt 0)

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




msg:3915346
 4:36 pm on May 17, 2009 (gmt 0)

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




msg:3915504
 10:45 pm on May 17, 2009 (gmt 0)

Yup, that should do the trick jay7981. :)

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved