Jump to content

Creating a prepared statement from an array


KillGorack

Recommended Posts

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.

 

Capturetable.PNG

 

Anyone have an idea?

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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!!!

Link to comment
Share on other sites

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. 

Link to comment
Share on other sites

@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,

Link to comment
Share on other sites

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..
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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();
Link to comment
Share on other sites

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.
Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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 ;
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.  :happy-04:

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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();
Link to comment
Share on other sites

  • 2 years later...

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();
Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.