mysqli prepared statements. Wrong parameter count for mysqli_stmt::bind_param()

[yamikowebs]

There's a number of questions on this that are answered here but I cant find what is wrong with mine.

DB class that uses mysqli which is stored as a property. The prepare, bind and execute are triggered with the magic call methods:

public static function __callStatic($name, $arg)
{
    echo '<pre>';
    var_dump($arg);
    echo '</pre>';
    if($name == 'Prepare'){
        self::$Stmt = self::$mysqli->prepare(implode(', ', $arg));
        $return = self::$Stmt;
    }elseif($name == 'Bind')
        $return = self::$Stmt->bind_param(implode(', ', $arg));
    elseif($name == 'Execute')
        $return = self::$Stmt->execute();
    else
        $return = self::$mysqli->$name(implode(', ', $arg));
    if(!self::GetErr())
        return $return;
}

Also I have a class that generates SQL statements. This is the ouput used in the prepare statement:

INSERT INTO account (UName, FName, LName, Email, Password, RecQuestion, RecAnswer, Admin) VALUES (?, ?, ?, ?, ?, ?, ?, ?) 

Here is the code:

DB::Prepare(SQL::Table('account')->Insert([
    'UName' => '?',
    'FName' => '?',
    'LName' => '?',
    'Email' => '?',
    'Password' => '?',
    'RecQuestion' => '?',
    'RecAnswer' => '?',
    'Admin' => '?'
]));
DB::Bind('sssssssi', $userName, $fName, $lName, $email, $password, $recQ, $recA, $admin);
DB::Execute();

As I can see there are 8 types and 8 values to go with it...

also here is the output of var_dump($arg) for when DB::Bind is called

array(9) {
  [0]=>
  string( "sssssssi"
  [1]=>
  string(4) "user"
  [2]=>
  string(5) "first"
  [3]=>
  string(4) "last"
  [4]=>
  string(15) "email@email.com"
  [5]=>
  string(64) "$2a$10$Tw4eOkUYA6SX8WP8XJfKZeFfOM9htVRJyP0d1iYlka0jNCV/qPGzazakT"
  [6]=>
  string(7) "recover"
  [7]=>
  string(64) "$2a$10$LrfK2EdkRi6pPdx1tUtPWe8p24T8ISdQHYhW0N06RjbvCrU4Flqiie4jU"
  [8]=>
  int(1)
}

[Psycho]

Look closer. The error state, the problem is with the "bind_param()" method/function - not "Bind". Here is where you use bind_param()

 

$return = self::$Stmt->bind_param(implode(', ', $arg));

 

You have a single parameter as an argument (the result of the implode). Per the manual there are two required arguments (or three if you are using procedural style):

 

bool mysqli_stmt::bind_param ( string $types , mixed &$var1 [, mixed &$... ] )

 

Also, I am sure your error message gave you a line number. Did you not use that to verify where the error occurred?

[yamikowebs]

Thanks for the reply.

 

Oddly I was able to do this just fine with other methods before. I have since switched to 5.4...Is there a way I can have DB::Bind run the prepared statement when I don't know the number of arguments? If not I can make a getter to get the mysqli stmt.

 

My understanding with prepared statements is they are better because they secure your data automagicaly. I can use the normal query and secure the data automatically like I have been doing. Or perhaps there's other reasons I should switch to using prepared statements? What are your opinions on this?

[Psycho]

Change this

    }elseif($name == 'Bind')
        $return = self::$Stmt->bind_param(implode(', ', $arg));

 

To this

    }elseif($name == 'Bind')
    echo implode(', ', $arg);
        //$return = self::$Stmt->bind_param(implode(', ', $arg));

 

And you should see the problem. When you implode the values you are providing a SINGLE string to the function and none of the values are even encapsulated in quotes! So it would be something like this

$return = self::$Stmt->bind_param(format, value1, value2, value3);

 

instead of

$return = self::$Stmt->bind_param('format', 'value1', 'value2', 'value3');

 

But, I don't think just adding the quotes will work, but I think there's an easier way. Just remove the formats parameter from the array for the first argument and pass the remaining for the values. Thy this:

$return = self::$Stmt->bind_param(array_shift($arg), $arg));

[yamikowebs]

Again thank you for your reply. I normally did the very same thing to interact with the other mySQLi methods fine. I looked over my old code and discovered in all cases I only used methods that used only 1 parameter. your new solution doesnt do it because the format for bind_param has an unknown number of parameters before hand. a few examples:

$stmt = $mysqli->prepare("INSERT INTO CountryLanguage VALUES (?, ?, ?, ?)");
$stmt->bind_param('sssd', $code, $language, $official, $percent);

$stmt = $mysqli->prepare("INSERT INTO Account VALUES (?, ?, ?,)");
$stmt->bind_param('iss', $ID, $email, $name);

So I believe this is where I would use

call_user_func_array();

This gets the right number of parameters but another rule on the parameters passed is that they need to be passed by reference.

 

I looked into how to do this and here is the final solution:

foreach($arg as &$v)
$Arg[] = &$v;
$return = call_user_func_array(array(self::$Stmt, 'bind_param'), $Arg);