Creating a prepared statement from an array

[KillGorack]

Trying to convert some old code with the use of the prepared statements, when I don't know the format of the table.

 

That format will be in an array, along with the values that will eventually be stuck in there.

 

example this first line can be built with this code

 

$stmt = "INSERT INTO ".$table_name." (";


foreach($fieldarray as $field){

  $stmtflds = $stmtflds.$field[2].", ";

  $stmtqs = $stmtqs."?, ";

}


$stmt = $stmt.trim($stmtflds, ", ").") VALUES (".trim($stmtqs, ", ").")";

$stmt = $conn->prepare($stmt);


// Outputs something like...

// $stmt = $conn->prepare("INSERT INTO MyGuests (firstname, lastname, email) VALUES (?, ?, ?)");

But for the life of me I can't figure out how to get this line below done with the contents of an array..

$stmt->bind_param("sss", $firstname, $lastname, $email);

Again, the values, and the field names are in the array, and roughly created like below.

 

 

Anyone have an idea?

Edited May 19, 2015 by KillGorack

[rwhite35]

I use a similar algorithm but only with authenticated users (like an admin) and never with public facing pages... NEVER TRUST un-authenticated input. That said, here is how I programmatically create the binder for the placeholders.

try {
		$stmt = $DB->prepare($query);
		
		if ($bind!=null) {
		  $cnt = count($bind);
		  if ($cnt>1) { //mulitple binders
		    $t=1;
		    for($i=0;$i<$cnt;$i++) {
			    $stmt->bindParam($t,$bind[$i]);
			    $t++;
		    }
		  } else { //single binder
			  $stmt->bindParam(1,$bind);
		  }
		}
		
		if($stmt->execute()) {
		  while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
	        $this->result[] = $row;
		  }
		  return $this->result;
		} else { 
			throw new Exception("L63: Error on dbmanage::query execution."); 
		}
	} catch ( Exception $e ) {
		error_log("Error on query method: ".$e->getMessage());
	}

This line here $stmt->bindParam($t,$bind[$i]); is taking the bind array and applying a number placeholder to the binding, in affect it would be the same as typing 

$stmt->bindParam(1,$bind[0]);
$stmt->bindParam(2,$bind[1]);
$stmt->bindParam(3,$bind[2]);

Where $bind is an array of your form input.  Also, make sure you sanitize everything. Good luck

[KillGorack]

Agree on your side comments completely. Getting from the form post to the array is a sanitizing function of course, not only administrators will evoke this code, but coworkers on an intranet environment.

 

I'll wrap my head around this and try tonight. Thanks!!!

[mac_gyver]

But for the life of me I can't figure out how to get this line below done with the contents of an array

 

 

to dynamically build and run the mysqli bind_param() statement requires that you use either call_user_func_array() or use reflection to call the method. there are examples of doing this in the php.net user contributed notes for the bind_param() statement.

 

however, this is a problem with using mysqli. it is much easier if you use PDO, since each bindParam()/bindValue() statement is called separately. you can just loop over the array of input values and call bindParam()/bindValue() inside the loop. note: if you are only using a query once, use bindValue() as it avoids the overhead of evaluating the bound variables when the ->execute() method is called.

 

for your insert query specific code, see the following -

$fieldarray[] = array('name'=>'firstname','type'=>PDO::PARAM_STR,'value'=>'john');
$fieldarray[] = array('name'=>'lastname','type'=>PDO::PARAM_STR,'value'=>'doe');
$fieldarray[] = array('name'=>'email','type'=>PDO::PARAM_STR,'value'=>'someone@somewhere.com');

$cols = array();
foreach($fieldarray as $field){
    $cols[] = $field['name'];
}

$holders = array_fill(0,count($cols),'?');
$query = "INSERT INTO $table_name (".implode(',',$cols).") VALUES (".implode(',',$holders).")";
$stmt = $conn->prepare($query);

// using PDO, the bind loop would look like -
$parm = 1;
foreach($fieldarra as $field){
    $stmt->bindValue($parm++, $field['value'],$field['type']);
}
$stmt->execute();

 

if you are at the database-layer in your code, your application would have already validated any external data so that it makes sense to the application, required fields are not empty, any specific constraints/formats have been checked. at the database-layer, you are protecting against things like sql special characters from breaking the sql syntax or in the case where the format permits content that could contain sql statements (a blog/forum post for example), to protect against sql injection. 

[rwhite35]

@mac_gyver, nice catch. I wasn't thinking mysqli!

 

I've had inconsistent results when using a foreach loop to build the bindValue for $stmt objects.  Specifically with OS X 10.10.  Using the incrementor rather than foreach has solved the issue.  Have you come across that situation before?  Thanks,

[KillGorack]

Tried mac_gyver's method, but getting errors..

  $stmt = "INSERT INTO ".$app_var." (";
  foreach($fieldarray as $field){
    $stmtflds = $stmtflds.$field[2].", ";
    $stmtqs = $stmtqs."?, ";
  }
  $query = $stmt.trim($stmtflds, ", ").") VALUES (".trim($stmtqs, ", ").")";
  $stmt = $db->prepare($query);

  $cntr = 1;
  foreach($fieldarray as $field){
    $stmt->bindValue($cntr++, $field[25], PDO::PARAM_STR);
  }
  $stmt->execute();

Results in error. Do I need to further set something up for PDO, I've not used that before. All the comments on scrubbing, validating users and the like is appreciated, trust me it's all in there.. Just need some syntax help. If i can get this above working I'll edit to my needs after. Just need to understand a simple example working. Test table has all fields strings (that it's updating)

 

Error on line 102 above

 

Fatal error: Call to undefined method mysqli_stmt::bindValue() in..

Edited May 19, 2015 by KillGorack

[mac_gyver]

my post states what you would need to do to do this using the mysqli database library. the code i posted uses the PDO database library, which was mentioned both in the text and in a comment in the code. you cannot mix calls to different database libraries.

[mac_gyver]

the following is what you would have to do if using mysqli (thanks php, just because you can make a function with a variable number of arguments for same meaning values, doesn't mean you should) - 

$fieldarray[] = array('name'=>'firstname','type'=>'s','value'=>'john');
$fieldarray[] = array('name'=>'lastname','type'=>'s','value'=>'doe');
$fieldarray[] = array('name'=>'email','type'=>'s','value'=>'someone@somewhere.com');

$cols = array();
$types = ''; // string holding the i,s,d,b types
$params = array(); // holds references to data values in the same order as the field names and types
foreach($fieldarray as $field){
    $cols[] = $field['name'];
    $types .= $field['type'];
    $params[] = &$field['value'];
}

$holders = array_fill(0,count($cols),'?');
$query = "INSERT INTO $table_name (".implode(',',$cols).") VALUES (".implode(',',$holders).")";
$stmt = $conn->prepare($query);

$refArr = array_merge(array($types),$params); // array of the bind_param parameters
$ref    = new ReflectionClass('mysqli_stmt'); // use class reflection to call mysqli->bind_param dynamically
$method = $ref->getMethod("bind_param");
$method->invokeArgs($stmt,$refArr); // 'call' the actual bind_param method
$stmt->execute();

Edited May 20, 2015 by mac_gyver

[KillGorack]

Understood! I'm risking becoming a little high maintenance on this one. All the help thus far is greatly appreciated! here is the latest iteration with your input below.

        $stmt = "INSERT INTO ".$app_var." (";

        foreach($fieldarray as $field){
          
            $stmtfld = $stmtfld.$field[2].", ";
            $stmtqst = $stmtqst."?, ";
            $stmtcon = $stmtcon.$field[25].", ";
            $stmttyp = $stmttyp.$field[26];
          
        }
        
        echo trim($stmtfld, ", ")."</br>"; // Checking the format
        echo trim($stmtqst, ", ")."</br>";
        echo trim($stmtcon, ", ")."</br>";
        echo trim($stmttyp, ", ")."</br>";

        $query = $stmt.trim($stmtfld, ", ").") VALUES (".trim($stmtqst, ", ").")";
        
        echo $query; // Again checking
        
        $stmt = $db->prepare($query);
        $params = explode(trim($stmtcon, ", "), ", ");

        $refArr = array_merge(array($stmttyp),$params); // array of the bind_param parameters
        $ref    = new ReflectionClass('mysqli_stmt'); // use class reflection to call mysqli->bind_param dynamically
        $method = $ref->getMethod("bind_param");
        $method->invokeArgs($stmt,$refArr); // 'call' the actual bind_param method
        $stmt->execute();

the echo's put the following out in case we think the array is at fault..

 

usr_login, usr_email, usr_f_name, usr_l_name, usr_pass

?, ?, ?, ?, ?

usr_name_0147, user@domain.com, John, Doe, password

sssss

INSERT INTO users (usr_login, usr_email, usr_f_name, usr_l_name, usr_pass) VALUES (?, ?, ?, ?, ?)

 

and finally the error

 

Fatal error: Uncaught exception 'ReflectionException' with message 'Invocation of method mysqli_stmt::bind_param() failed' in C:\xampp\htdocs\portal-x\all\fun\all\add.php:206 Stack trace: #0 C:\xampp\htdocs\portal-x\all\fun\all\add.php(206): ReflectionMethod->invokeArgs(Object(mysqli_stmt), Array) #1 C:\xampp\htdocs\portal-x\all\fun\switch.php(5): include('C:\xampp\htdocs...') #2 C:\xampp\htdocs\portal-x\index.php(20): include('C:\xampp\htdocs...') #3 {main} thrown in C:\xampp\htdocs\portal-x\all\fun\all\add.php on line 206

 

Of course the table is a just a test, all field names are purely consequential.

Edited May 20, 2015 by KillGorack

[mac_gyver]

every single character in the example code i posted was there for some reason. in particular there's one & that's required to make it work. also see the comment on the $params array - // holds references to data values in the same order as the field names and types

[KillGorack]

Thanks for the reply again. I tried that on and off and get the very same error. I've took the code out of the rest of the mess, and now am working with a single file. Code below, most of yours in in there including the characters I left out earlier.

<?php

  include('./inc/connection.php');
    
  $fieldarray[] = array("usr_login", "s", "user232");
  $fieldarray[] = array("usr_f_name", "s", "John");
  $fieldarray[] = array("usr_l_name", "s", "Doe");
  $fieldarray[] = array("usr_pass", "s", "password123");
  $fieldarray[] = array("usr_email", "s", "user@domain.com");

  $app_var = "users";

  $cols = array();
  $types = ''; // string holding the i,s,d,b types
  $params = array(); // holds references to data values in the same order as the field names and types

  foreach($fieldarray as $field){
    $cols[] = $field[0];
    $types .= $field[1];
    $params[] = &$field[2];
  }

  $holders = array_fill(0,count($cols),'?');
  $query = "INSERT INTO $app_var (".implode(',',$cols).") VALUES (".implode(',',$holders).")";
  $stmt = $db->prepare($query);

  echo implode(', ',$cols)."</br>";
  echo $types."</br>";
  echo implode(', ',$params)."</br>";
  echo $query."</br>";

  $refArr = array_merge(array($types),$params); // array of the bind_param parameters
  $ref    = new ReflectionClass('mysqli_stmt'); // use class reflection to call mysqli->bind_param dynamically
  $method = $ref->getMethod("bind_param");
  $method->invokeArgs($stmt,$refArr); // 'call' the actual bind_param method
  $stmt->execute();

?>

Echos output this;

usr_login, usr_f_name, usr_l_name, usr_pass, usr_email
sssss
user232, John, Doe, password123, user@domain.com
INSERT INTO users (usr_login,usr_f_name,usr_l_name,usr_pass,usr_email) VALUES (?,?,?,?,?)

 

 

Still getting an error, I'm fairly lost..

 

Fatal error: Uncaught exception 'ReflectionException' with message 'Invocation of method mysqli_stmt::bind_param() failed' in C:\xampp\htdocs\portal-x\test.php:35 Stack trace: #0 C:\xampp\htdocs\portal-x\test.php(35): ReflectionMethod->invokeArgs(Object(mysqli_stmt), Array) #1 {main} thrown in C:\xampp\htdocs\portal-x\test.php on line 35

[KillGorack]

Oh if it helps the structure of the table?

CREATE TABLE IF NOT EXISTS `users` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `usr_login` varchar(64) NOT NULL,
  `usr_f_name` varchar(64) NOT NULL,
  `usr_l_name` varchar(64) NOT NULL,
  `usr_pass` varchar(255) NOT NULL,
  `usr_email` varchar(255) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

[mac_gyver]

if you had php's error reporting set to E_ALL and display_errors set to ON, you would be getting a warning that would alert you to what's going on.

 

amazingly, i have used (past tense, i don't think anyone uses mysqli with prepared queries) this code, error free, with a value coming from the array being looped over. perhaps a php version change.

 

to get the current code to work, you need one more & in the foreach() - foreach($fieldarray as &$field){

 

are you sure you don't want to switch to PDO? this is a lot of extra code just to call one php built in method.

Edited May 21, 2015 by mac_gyver

[KillGorack]

Thanks that did the trick.

 

My issue with this is without being spoon fed the code at the end, I wouldn't be able to use the PDO version. Goes in hand with my mismatched code above. I really need to get my head into this version, and a comparable PDO version.

 

I just hope that modifying this to do an edit doesn't prove as difficult.

 

Thanks again for the help. 

Edited May 21, 2015 by KillGorack

[KillGorack]

Probably pushing things. I have an edit version that works. But I'm not sure if it's a great idea to prepare and execute for each field that needs updated in the table.

        foreach($fieldarray as $field){
            $stmt = $db->prepare("UPDATE ".$ap." SET ".$field[2]." = ? WHERE ID = ".$id);
            $stmt->bind_param($field[26], trim($field[25]));   
            $stmt->execute();
        }

As i said, it works, but would probably be quicker in the long run to prepare all fields in the table that needed it, and then execute. whaddia think?

[Barand]

There is no "probably" about it, it's a certainty.

[KillGorack]

There is no "probably" about it, it's a certainty.

 

There are two, to which are you referring too? Am I pushing things, or is this a bad idea?

Edited May 24, 2015 by KillGorack

[Barand]

this one

 

would probably be quicker in the long run to prepare all fields in the table that needed it, and then execute

[mac_gyver]

your code to build and run an UPDATE query would use the same method as for the insert query. you need to build the complete sql query statement in $query, a string of the input types in $types, and an array of input parameters in $params. after you have finished looping to build the set col = ? terms in an array, implode them and put them into the sql query statement. then add the final parts to $query, $types, $params for the WHERE id = ? part of the query.

 

you would reuse the common code from the ->prepare() statement through the ->execute() statement. in fact, in general, the code from the ->prepare() statement through the ->execute() statement would be part of a class method that would accept the $query, $types, and $params as inputs that you just call any time you need to run a prepared query.

[mac_gyver]

the code developed in the thread, but for an update query -

$cols = array();
$types = ''; // string holding the i,s,d,b types
$params = array(); // holds references to data values in the same order as the field names and types

foreach($fieldarray as &$field){
    $cols[] = "SET {$field[2]} = ?";
    $types .= $field[26];
    $params[] = &$field[25];
}
// add the types/params for the id
$types .= 'i';
$params[] = $id;

$query = "UPDATE $ap ".implode(',',$cols)." WHERE ID = ?";

$stmt = $db->prepare($query);
$refArr = array_merge(array($types),$params); // array of the bind_param parameters
$ref    = new ReflectionClass('mysqli_stmt'); // use class reflection to call mysqli->bind_param dynamically
$method = $ref->getMethod("bind_param");
$method->invokeArgs($stmt,$refArr); // 'call' the actual bind_param method
$stmt->execute();

[KillGorack]

 

the code developed in the thread, but for an update query -

 

swoopin in to save the day again I see. Thanks.. will incorporate that for sure..

[KillGorack]

PDO update loop not perfect yet. But thought i'd plop it in here.

$updts = array();
$bva = array();
foreach($fldarray as $fld){
  $upfld[] = $fld['fieldname']." = :".$fld['fieldname'];
  $bva[] = array(":".$fld['fieldname'], check_input($_POST[$fld['fieldname']]), $fld['PDODT']);
}
$sql = "UPDATE ".$ap." SET ".implode(", ", $upfld)." WHERE ID = :ID";
$stmt = $db->prepare($sql);
foreach($bva as $b){
  $stmt->bindParam($b[0], $b[1], $b[2]);
}
$stmt->bindParam(":ID", $id, PDO::PARAM_INT);
$stmt->execute();

Edited December 16, 2017 by KillGorack