douchen9855
douchen9855
2013-12-14 23:25
浏览 40
已采纳

为什么这个mysql更新脚本不起作用?

I have been beating my head against a wall for a few hours now trying to get this to update my DB.

<?
//edit_item_data.php
$con=mysqli_connect("localhost","root","","Inventory");
// Check connection
if (mysqli_connect_errno())
  {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
  }
  $sql= "UPDATE Item 
          SET Catagory = '$_POST[Catagory]',
          Cost = '$_POST[Cost]',
          Condition = '$_POST[Condition]',
          PurchaseLot_PurchaseLotID = '$_POST[PurchaseLot]',
          Location = '$_POST[Location]',
          Desc = '$_POST[Desc]',
          Notes = '$_POST[Notes]'
          WHERE 
          ItemID = '$_POST[id]'";

if (!mysqli_query($con,$sql))
  {
  die('Error: ' . mysqli_error($con));
  }
echo "1 record added";

mysqli_close($con);
?>
<script type='text/javascript'>
 settimeout('self.close()',5000);
</script>

this is the error I'm getting

Error: 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 'Condition = New,     
PurchaseLot_PurchaseLotID = 1, Location = e' at line 4

I'm running mysql 5.6 and php 5.5. I'm sure its something dumb but I can't for the life of me see what it is.

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

3条回答 默认 最新

  • duanliang2017
    duanliang2017 2014-01-08 14:51
    已采纳

    The real issue was lack of grave accents

            SET `Catagory` = '$_POST[Catagory]',
    
    点赞 评论
  • doucang2871
    doucang2871 2013-12-14 23:29

    Well you are hilariously vulnerable to SQL injection doing what you are doing, but the problem is that you aren't enclosing your variables in quotes, e.g:

    SET Catagory = '$_POST[Catagory]',
    -- etc
    

    Use mysqli_real_escape_string to escape your variables before you put them into your SQL, like this:

    SET Catagory = '" . mysqli_real_escape_string($_POST['Catagory'], $con) . "',
    
    点赞 评论
  • douewei1665
    douewei1665 2013-12-14 23:30

    xkcd

    You want something like this:

    $sql = "UPDATE `Item` SET
       `Catagory` = '".mysqli_real_escape_string($_POST['Catagory'],$con)."',
       `Cost` = '".mysqli_real_escape_string($_POST['Cost'],$con)."',
       ........
       WHERE `ItemID` = ".intval($_POST['id']);
    

    Side-note, it's spelled "category".


    EDIT: If you, like me, can't be arsed to type out such a long function name...

    $e = function($str) use ($con) {
        return mysqli_real_escape_string($str,$con);
    };
    

    Then:

    ... `Catagory` = '".$e($_POST['Catagory'])."' ...
    
    点赞 评论

相关推荐