0

I created a PDO statement with when, then and else.

The only issue I have is that it doesn't want to execute from a PDO statement. If I print the entire SQL in a print_r it shows me a perfect query. Also when I execute it on the database it runs flawless and updates the field I want.

 public function sellNpcItems($items, $quantity, $npc_id)
 {
 try
 {
  $sql = "UPDATE npc_items SET available = CASE item_id";

  for($i=0;$i<count($items);$i++)
  {
      $sql .= " WHEN ".$items[$i]." THEN available - ".$quantity[$i];
  }
      $sql .= " ELSE available END WHERE npc_id = ".$npc_id;
      $sql = $this->dbh->prepare($sql);
      $sql->execute(array($npc_id));
      $this->dbh->commit();

      $result = $sql->fetchAll(PDO::FETCH_ASSOC);

      return $result;
  }
       catch(PDOException $e){echo 'Error ! '.$e->getMessage();die();}
  }

The output of above query is:

PDOStatement Object ( [queryString] => UPDATE npc_items SET available = CASE item_id WHEN 57 THEN available - 1 ELSE available END WHERE npc_id = 1 )

If I select the queryString and paste this directly in my SQL Workbench it directly updates. When I try to update it from my PHP script. It refuses to update (no error or anything) just continues like nothing happened.

Any of you have the solution?

KevinH
  • 39
  • 1
  • 7
  • 1
    [Little Bobby](http://bobby-tables.com/) says ***[your script is at risk for SQL Injection Attacks.](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php)***. Even [escaping the string](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) is not safe! – Jay Blanchard Feb 26 '18 at 20:54
  • 1
    It may be a race condition of sorts setup here: `$sql = $this->dbh->prepare($sql);` Instead of `$sql = ` use something else, like `$stmt = ` – Jay Blanchard Feb 26 '18 at 20:56
  • @JayBlanchard i know it is right now. It is just setting up a simple query so i can see where it goes wrong. Normally i just use bindParam() but i wanted to keep it simple right now.. I've changed the parameter of the prepare and execute commands. No changes. Value ain't getting updated.. – KevinH Feb 26 '18 at 21:00

1 Answers1

-1

Okay, I've solved the issue. Kinda stupid to oversee this one but when you use this->dbh->prepare() without any variables that you've pushed from the execute it fails.. and won't update..

KevinH
  • 39
  • 1
  • 7