1

So a MySQLi statement fails to prepare with the error saying that it has incorrect syntax. However when I copy the prepared statement and replace use it directly on the dbhost it works fine.

Here's the code for the preparing.

function sanitiseStatement($sql, $params)
{
  global $mysqli;

  $stmt = mysqli_stmt_init($mysqli);
  if (mysqli_stmt_prepare($stmt, $sql))
  {
    call_user_func_array(array($stmt, 'bind_param'), refValues($params));

    return $stmt;
  }
  error_log("Failed to prepare statement: ".mysqli_stmt_error($stmt));
  die ("Failed to prepare statement: ".mysqli_stmt_error($stmt));
}

Here's the SQL statement that gets passed to it.

START TRANSACTION; INSERT INTO Recipe (UserID, RecipeName, Difficulty, Servings) VALUES (?, ?, ?, ?); SET @recipe_ID = LAST_INSERT_ID(); INSERT INTO Instructions (InstructionNumber, RecipeID, Instruction, Duration) VALUES (?, @recipe_ID, ?, ?);  INSERT IGNORE INTO Ingredients (IngredientName) VALUES (?);  INSERT INTO RecipeIngredients (RecipeID , IngredientID,Quantity, Required) SELECT @recipe_ID AS RecipeID, IngredientID, ? AS quantity, ? AS required FROM Ingredients WHERE IngredientName = ?;  COMMIT;

And here's the error log along with the parameters.

[Thu Mar 23 22:10:08 2017] 0: i => 2
[Thu Mar 23 22:10:08 2017] 1: s => Test
[Thu Mar 23 22:10:08 2017] 2: i => 1
[Thu Mar 23 22:10:08 2017] 3: i => 3
[Thu Mar 23 22:10:08 2017] 4: i => 1
[Thu Mar 23 22:10:08 2017] 5: s => Method
[Thu Mar 23 22:10:08 2017] 6: i => 2
[Thu Mar 23 22:10:08 2017] 7: s => Bacon
[Thu Mar 23 22:10:08 2017] 8: s => 1
[Thu Mar 23 22:10:08 2017] 9: i => 0
[Thu Mar 23 22:10:08 2017] 10: s => Bacon
[Thu Mar 23 22:10:08 2017] Failed to prepare statement: 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 'INSERT INTO Recipe (UserID, RecipeName, Difficulty, Servings) VALUES (?, ?, ?, ?' at line 1

I'm quite confused since I can't find an error in the SQL statement and it seems to work when using it directly with the database.

0 Answers0