Use one bind_param() with variable number of input vars

Question

I try to use variable binding like this:

$stmt = $mysqli->prepare("UPDATE mytable SET myvar1=?, myvar2=... WHERE id = ?")) {
$stmt->bind_param("ss...", $_POST['myvar1'], $_POST['myvar2']...);

but some of the $_POST['...'] might be empty so I don't want to update them in the DB.

It's not practical to take into account all the different combination of empty $_POST['...'] and although I can build the string " UPDATE mytable SET..." to my needs, bind_param() is a different beast.

I could try building its call as a string and use eval() on it but it doesn't feel right :(

score 25 · Answer 1 · answered Apr 27 '09 at 13:41

25

You could use the call_user_func_array function to call the bind_param method with a variable number or arguments:

$paramNames = array('myvar1', 'myvar2', /* ... */);
$params = array();
foreach ($paramNames as $name) {
    if (isset($_POST[$name]) && $_POST[$name] != '') {
        $params[$name] = $_POST[$name];
    }
}
if (count($params)) {
    $query = 'UPDATE mytable SET ';
    foreach ($params as $name => $val) {
        $query .= $name.'=?,';
    }
    $query = substr($query, 0, -1);
    $query .= 'WHERE id = ?';
    $stmt = $mysqli->prepare($query);
    $params = array_merge(array(str_repeat('s', count($params))), array_values($params));
    call_user_func_array(array(&$stmt, 'bind_param'), $params);
}

answered Apr 27 '09 at 13:41

Gumbo

643,351
109
780
844

1

Just wanted to mention that in PHP 5.3+ the above code will not work, because it requires array values as reference. See: http://php.net/manual/de/mysqli-stmt.bind-param.php – Alex2php May 25 '15 at 15:01
Then use `bind_values` instead. – Gumbo May 25 '15 at 15:45
Where do I find this in MYSQLi? I only know it from PDO. – Alex2php May 25 '15 at 16:11
@Alex2php Oh, yeah, I meant PDO’s `bindValue`. – Gumbo May 25 '15 at 16:14
1

@Alex2php I had the same problem, but was able to get around the issue by adding everything to $params[] as a reference. For example `$params[$name] =& $_POST[$name];` – CragMonkey Jul 26 '15 at 17:13

Pim Jager · Answer 2 · 2009-04-27T13:43:28.107

This is what I use to do mysqli prepared statements with a variable amount of params. It's part of a class I wrote. It propably is overkill for what you need but it should show you the right direction.

public function __construct($con, $query){
    $this->con = $con;
    $this->query = $query;
    parent::__construct($con, $query);
    //We check for errors:
    if($this->con->error) throw new Exception($this->con->error);
}

protected static $allowed = array('d', 'i', 's', 'b'); //allowed types

protected static function mysqliContentType($value) {
    if(is_string($value)) $type = 's';
    elseif(is_float($value)) $type = 'd';
    elseif(is_int($value)) $type = 'i';
    else throw new Exception("type of '$value' is not string, int or float");
    return $type;
}

//This function checks if a given string is an allowed mysqli content type for prepared statement (s, d, b, or i)
protected static function mysqliAllowedContentType($s){
    return in_array($s, self::$allowed);
}

public function feed($params){
    //These should all be empty in case this gets used multiple times
    $this->paramArgs = array();
    $this->typestring = '';
    $this->params = $params;
    $this->paramArgs[0] = '';
    $i = 0;
    foreach($this->params as $value){
        //We check the type:
        if(is_array($value)){
            $temp = array_keys($value);
            $type = $temp[0];
            $this->params[$i] = $value[$type];
            if(!self::mysqliAllowedContentType($type)){
                $type = self::mysqliContentType($value[$type]);
            }
        }
        else{
            $type = self::mysqliContentType($value);
        }
        $this->typestring .= $type;
        //We build the array of values we pass to the bind_params function
        //We add a refrence to the value of the array to the array we will pass to the call_user_func_array function. Thus say we have the following
        //$this->params array:
            //$this->params[0] = 'foo';
            //$this->params[1] = 4;
        //$this->paramArgs will become: 
            //$this->paramArgs[0] = 'si'; //Typestring
            //$this->paramArgs[1] = &$this->params[0];
            //$this->paramArgs[2] = &$this->params[1].
        //Thus using call_user_func_array will call $this->bind_param() (which is inherented from the mysqli_stmt class) like this:
            //$this->bind_param( 'si', &$this->params[0], &$this->params[1] );
        $this->paramArgs[] = &$this->params[$i];
        $i++;
    }
    unset($i);
    $this->paramArgs[0] = $this->typestring;
    return call_user_func_array(array(&$this, 'bind_param'), $this->paramArgs);
}

You use it like this:

 $prep = new theClassAboveHere( $mysqli, $query );
 $prep->feed( array('string', 1, array('b', 'BLOB DATA') );

The class should extend the mysqli_stmt class.

I hope this helps you in the right direction.
If you wan't I could also post the whole class, it includes variable results binding.

score 1 · Answer 3 · answered Apr 27 '09 at 13:37

1

It is marginally more clear to build your statement using an array:

$params = array();
$fragments = array();
foreach($_POST as $col => $val)
{
  $fragments[] = "{$col} = ?";
  $params[] = $val;
}

$sql = sprintf("UPDATE sometable SET %s", implode(", ", $fragments));
$stmt = $mysqli->prepare($sql);
$stmt->bind_param($params);

answered Apr 27 '09 at 13:37

Jeff Ober

4,967
20
15

Is it safe to let the user specify the fields to be updated like that? I'm just thinking, if that were a user table, and the user somehow found out there was a column that stored their user privileges, they could easily escalate their user privileges just by submitting a form post with the correct key/value combination. – Calvin Apr 27 '09 at 13:47
2

Under no circumstance is good to let the user specify the fields. At a minimum he should have a $valid_fieldnames array and check if (isset($valid_fieldnames[$col])) as the body of the foreach. (I prefer Array('field' => 1, 'field2' => 1,...) over Array('field','field2',...) as isset() is faster than in_array().) – jmucchiello Apr 27 '09 at 14:00
2

By not checking the column names or at least validating them (e.g. only allowing limited characters etc.) you are also allowing SQL injection to happen – Tom Haigh Apr 27 '09 at 16:01
It is marginally prone to SQL injection, https://phpdelusions.net/pdo/sql_injection_example – Your Common Sense May 03 '17 at 10:18

score -1 · Answer 4 · answered Jun 19 '13 at 13:02

-1

array_insert does not exist, i'm guessing he refers to some home made function, but i'm not sure exactly what it does ... inserts the parameter types onto the array somewhere in the beginning i would guess since the value 0 is passed but hey it could be in the end too ;)

answered Jun 19 '13 at 13:02

Mr T

1

3

hmm ... this looks more like a comment than an answer? – kleopatra Jun 19 '13 at 13:23

score -2 · Answer 5 · edited May 03 '17 at 09:38

Build it as a string, but put your values into an array and pass that to bindd_param. (and substitute ?'s for values in your SQL string.

$stmt = $mysqli->prepare("UPDATE mytable SET myvar1=?, myvar2=... WHERE id = ?")) { $stmt->bind_param("ss...", $_POST['myvar1'], $_POST['myvar2']...);

For example:

$args = array();
$sql = "UPDATE sometable SET ";
$sep = "";
$paramtypes = "";
foreach($_POST as $key => $val) {
  $sql .= $sep.$key." = '?'";
  $paramtypes .= "s"; // you'll need to map these based on name
  array_push($args, $val);
  $sep = ",";
}
$sql .= " WHERE id = ?";
array_push($args, $id);
array_insert($args, $paramtypes, 0);

$stmt = $mysqli->prepare($sql);
call_user_func_array(array(&$stmt, 'bindparams'), $array_of_params);
$stmt->bind_param($args);

@altCongnito does `array_insert()` actually exist? – Andy Lobel Jul 06 '12 at 01:35 — Andy Lobel, Jul 06 '12 at 01:35
You wrote `$sql +=` instead of `$sql .=` – pmrotule Aug 22 '16 at 07:12 — pmrotule, Aug 22 '16 at 07:12

Use one bind_param() with variable number of input vars

5 Answers5

Linked

Related