Jump to content

mysqli_stmt_bind_param array loop


ChromeSword
Go to solution Solved by jcbones,

Recommended Posts

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

  • Solution

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

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

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • 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.