0

I know there are hundreds of similar questions out there, If my answer is out there, I haven't been able to work it out yet...

I have some PHP, I'm using PDO to update a table in mySQL

when I do I get an uncaught PDO exception.

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'mouse, CatID=1 WHERE ID=1' at line 1' in C:\xampp\htdocs\classes\catalogue.php:93 Stack trace:

The method looks like this:

function editItem($item){
    $d = JSON_decode($item, true);
    var_dump($d);
    $name = $d['Name'];
    $price = $d['Price'];
    $desc = $d['Desc'];
    $catID = $d['CatID'];
    $ID = $d['ID'];

    $conn = self::connect();
    $sql = "UPDATE catalogue SET Name=$name, Price=$price, `Desc`=$desc, CatID=$catID WHERE ID=$ID";

    // Prepare statement
    $stmt = $conn->prepare($sql);

    // execute the query
    $stmt->execute();

}

the var_dump looks like this:

array(7) {
  ["ID"]=>
  string(1) "1"
  ["Name"]=>
  string(5) "Mouse"
  ["Price"]=>
  int(20)
  ["Desc"]=>
  string(7) "A mouse"
  ["CatID"]=>
  string(1) "1"
  ["$$hashKey"]=>
  string(9) "object:75"
  ["edit"]=>
  bool(true)
}

Clearly I've missed something obvious, I just can't work it out.... Can anyone see where I wen't wrong?

thanks

Jay Blanchard
  • 32,731
  • 15
  • 70
  • 112
Jamie McAllister
  • 699
  • 1
  • 9
  • 32
  • @AnkurTiwari No! OP should start to use `?` placeholders. – mario May 26 '16 at 12:37
  • will that solve my syntax error?.... because i've got 3 other update functions similar to this one that all work fine... – Jamie McAllister May 26 '16 at 12:42
  • i don't see how this question is answered in the linked question, I'm not asking about injection 0.o – Jamie McAllister May 26 '16 at 12:45
  • It answers your question whether you believe it or not. – mario May 26 '16 at 13:20
  • thank you so much for your helpful explanation after i made it clear that i did not understand how that question applied to me.... -_- – Jamie McAllister May 26 '16 at 13:23
  • Literal/unoquted strings in the query. Why not compare that one with the rest of your code? And why not bother at least trying out parameterization? You know, just once. – mario May 26 '16 at 13:25
  • as it happens, my dad was able to point out that i didn't have quotes around the strings in the query........ i did say in the question i was sure i'd missed something obvious :P – Jamie McAllister May 26 '16 at 13:26
  • So your conclusion is go back to retarded string quoting, instead of applying the proper solution to such issues? – mario May 26 '16 at 13:27
  • All i'm doing is making it work, and deciding if i like the features i'm adding.... I'll go back and refine it/secure it etc when i decide what i'm keeping on the site... okay, maybe not the most efficient development process... but i'm on no time schedule to complete it – Jamie McAllister May 26 '16 at 13:34
  • $sql = "UPDATE catalogue SET Name=:name, Price=:price, `Desc`=:desc, CatID=:catID WHERE ID=:ID"; // Prepare statement $stmt = $conn->prepare($sql); // execute the query $stmt->execute(['name'=>$name, 'price'=>$price, 'desc'=>$desc, 'catID'=> $catID, 'ID'=>$ID]); please escape your data, also the syntax error is because your data is not in qoutes, the escaping method of pdo takes care of that for you – Gauthier Feb 16 '17 at 21:33

0 Answers0