ChromeSword Posted October 1, 2013 Share Posted October 1, 2013 (edited) 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 Quote Link to comment Share on other sites More sharing options...
Solution jcbones Posted October 1, 2013 Solution Share Posted October 1, 2013 (edited) 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 Quote Link to comment Share on other sites More sharing options...
ChromeSword Posted October 1, 2013 Author Share Posted October 1, 2013 (edited) 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 Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.