PHP Prepared Statement not executing correctly

Question

I have been staring at a problem for far to long now.

I have a PHP file that is preparing an insert query, binding the params, and executing the query (Simple).

QUERY

$stmt = $db->prepare("SELECT insert_user(?, ?, ?, ?, ?, ?)");

For some reason the $stmt object returned by execute returns -1 for the affected rows. If I alter the code to do just an insert query with the values I wanted to bind, hard coded instead, the query works just fine.

HARD CODED QUERY

$db->query("SELECT insert_user('Test', 'Account', 'testAccount@testApp.io', 'testAccount9', '1980-01-01', 1)");

Something is going wrong in the bind_param section. I have errors turned on and am checking for mysqli errors too, but both are returning no errors.

PHP FILE

...

$postdata = file_get_contents("php://input");

if (isset($postdata) && !empty($postdata)) {
  $request = json_decode($postdata);
}

if (
  validate_string($request->fname) 
  && validate_string($request->lname) 
  && validate_integer(intval($request->gender))) {
      $stmt = $db->prepare("SELECT insert_user(?, ?, ?, ?, ?, ?)");
      if ($stmt) {
        $stmt->bind_param("ssssis", $first_name, $last_name, $email, $password, $gender, $dob);
        $first_name = $request->fname;
        $last_name = request->lname;
        $email = $request->email;
        $password = password_hash($request->password, PASSWORD_BCRYPT);
        $gender = intval($request->gender);
        $dob = $request->dob;

        $stmt->execute();
        if ($stmt->affected_rows > 0) {
          echo toJson('success');
        } else {
          echo toJson('fail');
        }
     } else {
         echo toJson("Prepare failed: (" . $db->errno . ") " . $db->error);
     }
} else { 
   echo toJson('fail - passed data not valid');
}
...

I feel the error must be simple at this point, but I have tried at least 23,432 different things to no success.

`SELECT` != `INSERT`. See https://dev.mysql.com/doc/refman/8.0/en/insert.html — aynber, Sep 05 '19 at 18:40
Check the syntax of an insert query - https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php - `INSERT INTO table (column) VALUES (:column)` — Nigel Ren, Sep 05 '19 at 18:42
Do you have _mysqli error reporting_ turned on? https://phpdelusions.net/mysqli/error_reporting — waterloomatt, Sep 05 '19 at 18:46
@aynber are you referring to the SELECT insert_user() portion? — Chris, Sep 05 '19 at 18:47

score 2 · Accepted Answer · answered Sep 05 '19 at 18:43

2

My guess the problem is here "ssssis" . The fifth should be string, the last integer. Maybe this should work?

answered Sep 05 '19 at 18:43

Shir Gans

1,976
3
23
40

1

If you look at the value put into the 5th bind (gender) the value is set using `$gender = intval($request->gender);` – Nigel Ren Sep 05 '19 at 18:47
@Shir Gans I am passing a integer value for gender in my DB. Date is a string converted to a DATE by the insert_user function. – Chris Sep 05 '19 at 18:48
1

Right, but OP's `HARD CODED QUERY`, they use gender `1` as the final argument. – waterloomatt Sep 05 '19 at 18:49
OMG, that is it! What a waste of time for you guys sorry! – Chris Sep 05 '19 at 18:50
1

The insert_function in my DB takes DOB as the 5th param and gender as the last. – Chris Sep 05 '19 at 18:50

PHP Prepared Statement not executing correctly

1 Answers1