array in prepared statement

[dweb]

Hi

 

I wonder if someone may help, I have already had a good look online, but not found a solution that works with what i'm trying to do

 

At the moment I have the following code and it works great

$stmt = $mysqli->prepare("SELECT d.id,d.val FROM datalog pd LEFT JOIN datafeeds d ON d.id = pd.did WHERE pd.pid = ? AND pd.aa = ? AND pd.bb = ?");
$stmt->bind_param('sii', $id,$aa=1,$bb=1);
$stmt->execute();
$stmt->bind_result($id,$val);
mysqli_stmt_store_result($stmt);
if(mysqli_stmt_num_rows($stmt) != 0)
{
while ($stmt->fetch())
{
$array[] = array('numA'=>$id, 'numB'=>$val);
}
}
return $array;

but what I would like to do is use an array, which could be any length
 

$array = array(1,2,4,7,9);

and change my query to include

d.type IN ($array)

could someone give me some help?

 

thanks

 

[requinix]

If you want to do a prepared statement using an IN then you need placeholder for every value.

 

You know what's easier than that? Make sure all the numbers are integers, like with an int cast or intval(), then implode() it and put it directly into the query. As long as you're definitely, definitely making things integers here then you'll be alright.

Edited August 20, 2014 by requinix

[mac_gyver]

using a prepared query with the IN() comparison, will require that you dynamically supply a place-holder in the query for each value in the array - it must look like IN (?,?,?,?,?), you must dynamically build the bind_parm() statement with the correct first parameter with a type character for each place holder, and the hard part, you must dynamically supply a 2nd through nth parameter consisting of a variable (which can be an array element) for each parameter.

 

if you look at the user contributed notes in the php.net documentation, either in the msyqli prepare or mysqli bind_parm section, you can probably find a script/class that helps do this for you.

 

to build the place-holders and the type character string, you can just get a count of the elements in the array and use string functions to build those. to actually supply a dynamic number of parameters to a function, you must use call_user_func_array().

 

or if you can switch to using the PDO database library/class, all of this becomes very simple, because you can bind each parameter separately.

[dweb]

Hi

 

I tried dynamically creating the bind_parm, but I couldnt get it working. Is it possible you could show an example of building up the bind_parm?

 

I've been told that PDO is not possible with the server setup i've got

 

thanks

[cyberRobot]

I tried dynamically creating the bind_parm, but I couldnt get it working. Is it possible you could show an example of building up the bind_parm?

 

What does your updated code look like?

[dweb]

What does your updated code look like?

 

The top part of the code looks like

$arrays = array(1,2,4,7,9);

//for the array values as comma separated
$build_array = implode(',', $arrays);

//for the SQL query
$build_in = implode(',', array_fill(0, count($arrays), '?'));

//for the bind_param string
$build_bind_param .= 's';
foreach($arrays as $array)
{
$build_bind_param .= 's';
}
$build_bind_param .= 'ii';

$stmt = $mysqli->prepare("SELECT d.id,d.val FROM datalog pd LEFT JOIN datafeeds d ON d.id = pd.did WHERE pd.pid = ? AND pd.aa = ? AND pd.bb = ? AND d.type IN ($build_in)");
$stmt->bind_param($build_bind_param, $id,$aa=1,$bb=1,$build_array);
$stmt->execute();

and I get;

Warning: mysqli_stmt::bind_param(): Number of elements in type definition string doesn't match number of bind variables

is this not the correct way to do it?

 

thanks

[mac_gyver]

the bind_parm() statement must have -

the correct first parameter with a type character for each place holder, and the hard part, you must dynamically supply a 2nd through nth parameter consisting of a variable (which can be an array element) for each parameter.

 

 

i'm not making this up, this comes directly from the php.net documentation for that function -

 

bool mysqli_stmt::bind_param ( string $types , mixed &$var1 [, mixed &$... ] )

 

 

the statement you end up producing must be in the form -

 

$stmt->bind_param ('iissssss',$var1,$var2,$var3,$var4,$var5,$var6,$var7,$var8);

 

the only way one of the ways you can dynamically do this is to use call_user_func_array() and as stated, there are examples in the user contributed notes in the php.net documentation. edit: the user contributed notes that i have mentioned contains a method using class Reflection to do this in a more direct way.

 

also, your $build_bind_param string isn't correct for the query you are showing. you would build the part that corresponds to the set of $arrays values and concatenate that in the correct place in the string relative to the other parameters.

[Jacques1]

Several things.

 

First of all, the bind_param() call makes no sense. You have 8 parameters, but you only bind 4 variables to them: $id, $aa, $bb and $build_array. That last one is some strange comma-separated string. Not sure what this is supposed to do. Like mc_gyver already said, you need to use call_user_func_array() so that you can pass a variable number of arguments to bind_param(). You cannot call bind_param() directly.

 

I also think the order of the parameter types isn't quite right. You have "ssss...ii", but shouldn't that be "siisss..."? The parameters for the IN operator come after the fixed parameters.

 

It might be a good idea to approach this more systematically: Make one string for the types, one array for the parameter values, and fill them simultaneously:

$id = 15;
$aa = 1;
$bb = 1;

$datafeed_types = array(1, 2, 4, 7, 9);

$query = '
    SELECT
        datafeeds.id
        , datafeeds.val
    FROM
        datalog
        LEFT JOIN datafeeds ON datafeeds.id = datalog.did
    WHERE
        datalog.pid = ?
        AND datalog.aa = ?
        AND datalog.bb = ?
        AND datafeeds.type IN (' . implode(',', array_fill(0, count($datafeed_types), '?')) . ')
';

$parameter_types = '';
$parameter_values = array();

// the fixed parameters first
$parameter_types .= 'sii';
$parameter_values[] = $id;
$parameter_values[] = $aa;
$parameter_values[] = $bb;

// the dynamic parameters for the datafeed types
foreach ($datafeed_types as $datafeed_type)
{
    $parameter_types .= 's';
    $parameter_values[] = $datafeed_type;
}

// we need to pass references to bind_param
$bind_param_args = array();
$bind_param_args[] = $parameter_types;
foreach ($parameter_values as $i => $_)
{
    $bind_param_args[] = &$parameter_values[$i];
}

$stmt = $mysqli->prepare($query);
call_user_func_array(array($stmt, 'bind_param'), $bind_param_args);
$stmt->execute();

Too complex? Use PDO. Nobody said that MySQLi was a good idea.

[dweb]

thanks man, i'll give that a go

[dweb]

Just out of curiosity, would you suggest that  PDO would be better all round? Or just for specific parts of a site?

 

What's the benefit of PDO over MySQLi

[Jacques1]

PDO is much easier to use than MySQLi. You don't need tons of low-level code and strange workarounds to get stuff done. I mean, the script above isn't even finished. Now comes the bind_result() and fetch() fun. In PDO, all you need is prepare(), execute() and a foreach loop to go through the result set.

 

But more importantly, PDO is a universal interface for all mainstream database systems. Being limited to MySQL may be OK as long as that's all you know. But there are many other systems out there which you may want or have to use at some point. PDO let's you do that without forcing you to rewrite your entire code.

[dweb]

PDO is much easier to use than MySQLi. You don't need tons of low-level code and strange workarounds to get stuff done. I mean, the script above isn't even finished. Now comes the bind_result() and fetch() fun. In PDO, all you need is prepare(), execute() and a foreach loop to go through the result set.

 

But more importantly, PDO is a universal interface for all mainstream database systems. Being limited to MySQL may be OK as long as that's all you know. But there are many other systems out there which you may want or have to use at some point. PDO let's you do that without forcing you to rewrite your entire code.

 

 

Great thanks.

 

I am going to start using PDO.

 

I managed to get this working

$type=1;
$sth = $dbh->prepare('SELECT `name`,`id` FROM `people` WHERE `type` = :type');
$sth->bindParam(':type', $type);

but if I use

$type=1;
$ids=array(24,25,26,27,28,29,30);

$sth = $dbh->prepare('SELECT `name`,`id` FROM `people` WHERE `type` = :type AND `id` IN (:ids)');
$sth->bindParam(':type', $type);
$sth->bindParam(':ids', $ids);

then I get "Notice: Array to string conversion".

 

I've looked around to see how best to convert

$ids=array(24,25,26,27,28,29,30);

but cannot seem to find the answer anywhere

 

what am i doing wrong?

 

thanks

[Jacques1]

You still cannot pass an array to a parameter. This simply isn't possible with prepared statements, regardless of which database interface you happen to use.

 

So you still need to create a separate parameter for each array element. However, PDO lets you skip the call_user_func_array() stuff, because you can simply call bind_param() for each parameter or pass an array to the execute() method.

[dweb]

You still cannot pass an array to a parameter. This simply isn't possible with prepared statements, regardless of which database interface you happen to use.

 

So you still need to create a separate parameter for each array element. However, PDO lets you skip the call_user_func_array() stuff, because you can simply call bind_param() for each parameter or pass an array to the execute() method.

 

Ok, so is this a logical way of doing it

$array=array(24,27);
$i=0;

foreach($array as $val)
{
    if($i > 0) { $query_in .= ','; }
    $query_in .= ':'.$i;
    $i++;
}

and in the query use

`id` IN ('.$query_in.')

and finish off with

$i=0;
foreach($array as $val)
{
$sth->bindParam(':'.$i, $array[$i]);
$i++;
}

which seems to output a result, but i'm not sure if this is the best way to do it

 

thanks

 

[Jacques1]

You don't need two loops. Either create the IN expression with array_fill() again or merge both loops into one. Numbered parameters also don't require a colon. Just use question marks and reference them by their number in the bindParam() call. Or even simpler, collect all values in an array and pass that array to execute().

 

Last but not least, be careful with the PDO configuration: By default, PDO replaces the prepared statements with escaped queries (which is much less secure). To use actual prepared statements, you have to explicitly disable PDO::ATTR_EMULATE_PREPARES. 

<?php

$database = new PDO('mysql:host=localhost;dbname=test;charset=utf8mb4', 'YOURUSER', 'YOURPASSWORD', array(
	PDO::ATTR_EMULATE_PREPARES => false,
	PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
	PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
));

$type = 1;
$ids = array(24, 25, 26, 27, 28, 29, 30);

$people_stmt = $database->prepare('
	SELECT
		name,
		id
	FROM
		people
	WHERE
		type = ?
		AND id IN ('. implode(',', array_fill(0, count($ids), '?')) .')
');

// collect values
$parameters = array();
$parameters[] = $type;
foreach ($ids as $id)
{
	$parameters[] = $id;
}

$people_stmt->execute($parameters);

foreach ($people_stmt as $person)
{
	var_dump($person);
}

[dweb]

 

You don't need two loops. Either create the IN expression with array_fill() again or merge both loops into one. Numbered parameters also don't require a colon. Just use question marks and reference them by their number in the bindParam() call. Or even simpler, collect all values in an array and pass that array to execute().

 

Last but not least, be careful with the PDO configuration: By default, PDO replaces the prepared statements with escaped queries (which is much less secure). To use actual prepared statements, you have to explicitly disable PDO::ATTR_EMULATE_PREPARES. 

<?php

$database = new PDO('mysql:host=localhost;dbname=test;charset=utf8mb4', 'YOURUSER', 'YOURPASSWORD', array(
	PDO::ATTR_EMULATE_PREPARES => false,
	PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
	PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
));

$type = 1;
$ids = array(24, 25, 26, 27, 28, 29, 30);

$people_stmt = $database->prepare('
	SELECT
		name,
		id
	FROM
		people
	WHERE
		type = ?
		AND id IN ('. implode(',', array_fill(0, count($ids), '?')) .')
');

// collect values
$parameters = array();
$parameters[] = $type;
foreach ($ids as $id)
{
	$parameters[] = $id;
}

$people_stmt->execute($parameters);

foreach ($people_stmt as $person)
{
	var_dump($person);
}

 

thanks, i'll give that a try

 

So are you disabling ATTR_EMULATE_PREPARES by using

PDO::ATTR_EMULATE_PREPARES => false

and what's the reason for doing so?

[ginerjm]

Just the opposite.  He sets that to false so that PDO doesn't emulate, ie, use un-prepared queries, and in fact DOES use prepare queries.

[Jacques1]

The reason why prepared statements are secure is because they separate the query from the input data: You first send the query template to the database system. And then you send the input and execute the prepared statement. This two-stage scheme prevents the input from interfering with the query and causing an SQL injection.

 

Unfortunately, the PHP core developers in all their wisdom decided to replace this scheme with so-called “emulated prepared statements”: They take the data, escape it, literally insert it into the placeholders and then send the whole thing as a plain query to the database system. In other words, this is not a prepared statement at all. It's just a kind of auto-escaping. This auto-escaping is much less secure than a prepared statement, because the input may very well interfere with the query. For example, the escape function may not work correctly due to encoding issues.

 

So unless you have a very good reason for using this “feature” and fully understand the security implications, turn it off at all times. It should never have been the default.

[jazzman1]

I've created literally hundreds apps using PDO / MySQL / FireBird and never thought at this point that by default PDO does not use pure prepared statements rather than emulated prepared queries

+1 Jacques, learn something new everyday. 

Edited August 21, 2014 by jazzman1

[mac_gyver]

you can test if your pdo driver is emulating prepared queries (for some, that's the only choice, regardless of the PDO::ATTR_EMULATE_PREPARES setting), by checking if a deliberate syntax error in your sql statement is throwing an error at the ->prepare() statement (temporarily add a die; after the prepare() to prevent the ->execute() from running) or at the ->execute() statement; if the query is actually being sent to the database server to be prepared, the syntax error will be thrown at the ->prepare() statement.

[Jacques1]

(for some, [using “emulation” is] the only choice, regardless of the PDO::ATTR_EMULATE_PREPARES setting)

 

Is it? So far, I've only found three cases where prepared statements in PDO are not supported:

• Ancient MySQL versions before the year 2004.
• Ancient PostgreSQL versions before the year 2003.
• The experimental DBLIB driver.

Have you encountered any of those in real life? I haven't. And if there actually are people trying to use PDO with a 10-year-old database system or an experimental driver, I think that's the problem.

Edited August 22, 2014 by Jacques1