mysqli_stmt_bind_param array loop

[ChromeSword]

I have been searching and searching over the internet, and I can't find any answer.

So what I need is a dynamic function that will automatically setup my columns and rows.

 

What I need to have done:

- use the mysqli_stmt_bind_param for securely using commands.

- Each column and value stored in a array

 

Example 1: (Not using mysqli_stmt_bind_param)

$columnsAndValues = Array("username"=>"userInputedUsername", "password"=>"userInputedPassword");

$cols = Array();
$vals = Array();

foreach($columnsAndValues as $key => $val){
	$cols[] = $key;
} foreach($columnsAndValues as $key => $val){
	$vals[] = $val;
}
$colString = "`". implode("`,`", $cols) ."`";
$valString = "'". implode("','", $cols) ."'";

$queryString = 'INSERT INTO someTable ('. $colString .') VALUES ('. $valString .');';

Which doing that would create:

<INSERT INTO someTable (`username`, `password`) VALUES ('userInputedUsername', 'userInputedPassword');>

 

Which I know that is unsecure, which is why I want to use mysqli_stmt_bind_param, so it will always be safe.

 

So if you see how it builds the columns and values using that 1 array, How would I do that with mysqli_stmt_bind_param?

So for the values part it puts 

 

<INSERT INTO someTable (`username`, `password`) VALUES (?,?);>

 

Then uses the mysqli_stmt_bind_param to fit with the amount of columns automatically.

mysqli_stmt_bind_param($stmt, $Array['username'], $Array['password'])

That would work, but what if there is 20 columns?

 

I hope you understand what I am trying to explain.

 

Another note: 

I need this to make a MySQL function that my entire site will use. So the function will be sending that array and other information, which I am stuck on this part. Is there another function that does the same thing as bind_param? just without needing the $stmt?

Edited October 1, 2013 by ChromeSword

[jcbones]

Un-Tested Edit: I missed something important.  There is a need to pass the value types also, which needs to be PUSHED onto the front of the $values array, before the call_user_func_array() function is used.

$columnsAndValues = Array("username"=>"userInputedUsername", "password"=>"userInputedPassword"); //array that holds value data.
 
$count = count($columnsAndValues); //count the number of placeholders we need.
$markers = array_fill(0,$count,'?'); //fill an array with the number of placeholders.

foreach($columnsAndValues as $key => $val){ //go through the array.
    $columns[] = $key; //assign the columns by keys.
    $values[] =& $columnsAndValues[$key]; //referencing the values into an array.
}

$queryString = 'INSERT INTO someTable ('. implode(',',$columns) .') VALUES ('. implode(',',$markers) .');'; //build the query with the columns and placeholders.
if(!stmt = $mysqli->prepare($queryString)) { //if the query fails to prepare, then dump an error.
  trigger_error($mysqli->error,E_USER_ERROR);
}
if(!empty($values)) { //if the values array isn't empty.
 call_user_func_array(array($stmt,'bind_param'),$values); //pass everything to the bind_param function.
}
if($stmt->execute()) { //then execute it.
 //get your data;
}
 

Edited October 1, 2013 by jcbones

[ChromeSword]

Ok, thanks for the help, Here is what my function looks like:

function mysql_secure_query($sqlCommand = null, $stringTable = null, $colsVals = Array(), $options=Array()){
	global $gweb; //gweb has the mysqli in it.
	
	$columnsAndValues = $colsVals;
	 
	$count = count($columnsAndValues); //count the number of placeholders we need.
	$markers = array_fill(0,$count,'?'); //fill an array with the number of placeholders.

	$values[] = str_repeat("s", $count); //Fixes what you were talking about.
	foreach($columnsAndValues as $key => $val){ //go through the array.
		$columns[] = $key; //assign the columns by keys.
		$values[] =& $columnsAndValues[$key]; //referencing the values into an array.
	}

	$queryString = 'INSERT INTO `'. $stringTable .'` (`'. implode('`,`',$columns) .'`) VALUES ('. implode(',',$markers) .');'; //build the query with the columns and placeholders.
	if(!$stmt = $gweb['sql']->prepare($queryString)) { //if the query fails to prepare, then dump an error.
	  trigger_error($gweb['sql']->error,E_USER_ERROR);
	}
	if(!empty($values)) { //if the values array isn't empty.
	 call_user_func_array(array($stmt,'bind_param'), $values); //pass everything to the bind_param function.
	}
	if($stmt->execute()) { //then execute it.
	 //get your data;
	}
}

Now to begin modifying it to work with certain commands, such as select. 

Edited October 1, 2013 by ChromeSword