This is an archived copy of the forum for reference purposes

SQL IN and escaping data

posted by darren 7 years ago

Hi,

I am trying to pass an array of strings to IN function...

$data = array('a, 'b', 'c', 'd');
//or
$data = implode("', '", array('a, 'b', 'c', 'd'));

$result = $db->query("SELECT * FROM ... WHERE ... AND carriers IN (%s)", $data);

However I just cant get it to produce the correct bind data as it puts a around the lot.

Any help would be appreciated.

You are doing it right:

$result = $db->query("SELECT * FROM .. WHERE ... AND carriers IN (%s)", $data);

Note that you can pass values both as an array and as individual arguments:

$db->query("SELECT * FROM .. WHERE ... AND carriers IN (%s)", $arg1, $arg2, $arg3);

$db->query("SELECT * FROM .. WHERE ... AND carriers IN (%s)", array($arg1, $arg2, $arg3));

It might be that (due to the rest of your SQL that you've hidden) that flourish thinks of your $data as the list of arguements. Trying wrapping data in an array.

$db->query("SELECT * FROM .. WHERE ... AND carriers IN (%s)", array($data));

posted by mblarsen 7 years ago

Hi mblarsen,

$db->query("SELECT * FROM .. WHERE ... AND carriers IN (%s)", array($data));

The %s will only accept a string value otherwise you end up with...

Array to string conversion error

If i used %i and integer values it would be fine...

$data = implode(',', array('ab', 'cd'));

is translated to
IN ('ab,cd')

$data = implode("','", array('ab', 'cd'));
is translated to
IN ('ab\\',\\'cd')

I am thinking that its not possible doing it this way and will probably have to run...

$db->escape('string', $value);

over each of the values and then pass it directly to the query that way.

thanks

posted by darren 7 years ago

Hi Darren

You are giving Flourish lib to little credit :D, it will do all the escaping for you.

Here is a working example:

$sql = "SELECT * FROM products WHERE product_code IN (%s) AND stock > %i";
$result = $db->query($sql, array(array('T013', 'T014'), 0));

This is translated into:

SELECT * FROM products WHERE product_code IN ('T013', 'T014') AND stock > 0

If you want your model objects directly you can do like this:

$products = fRecordSet::buildFromSql('Product', array($sql, array(array('T013', 'T014'), 0)));

or without SQL

$products = fRecordSet::build('Product', array('product_code=' => array('T013', 'T014'), 'stock>' => 0));

I hope these examples are helpful. Otherwise just ask again.

Cheers

posted by mblarsen 7 years ago

Hi Darren

You are giving Flourish lib to little credit :D, it will do all the escaping for you.

Here is a working example:

$sql = "SELECT * FROM products WHERE product_code IN (%s) AND stock > %i";
$result = $db->query($sql, array(array('T013', 'T014'), 0));

This is translated into:

SELECT * FROM products WHERE product_code IN ('T013', 'T014') AND stock > 0

If you want your model objects directly you can do like this:

$products = fRecordSet::buildFromSql('Product', array($sql, array(array('T013', 'T014'), 0)));

or without SQL

$products = fRecordSet::build('Product', array('product_code=' => array('T013', 'T014'), 'stock>' => 0));

I hope these examples are helpful. Otherwise just ask again.

Cheers

posted by mblarsen 7 years ago

Hi mblarsen,

Still the same error :(

"Array to string conversion" .../flourish/fDatabase.php on line 2831

$results = $db->query("
	SELECT
		*
	FROM cube.complex_porting cp
	WHERE cp.cntldate BETWEEN '2012-06-01' AND '2012-06-30'
		AND cp.losing_carrier IN (%s)
	ORDER BY cp.port_ref_num", array(array('T013', 'T014'))
);

## OR

$results = $db->query("
	SELECT
		*
	FROM cube.complex_porting cp
	WHERE cp.cntldate BETWEEN '2012-06-01' AND '2012-06-30'
		AND cp.losing_carrier IN (%s)
	ORDER BY cp.port_ref_num", array('T013', 'T014')
);

Thanks

posted by darren 7 years ago

I believe that error message was added in 5.4, so it may need to be changed to not make noise on newer versions of PHP.

posted by wbond 7 years ago

Hi Will,

I believe that error message was added in 5.4, so it may need to be changed to not make noise on newer versions of PHP.

Well probably makes sense as I am running 5.4.4, so does mean it will not work as above and go back to my original idea of escaping each value then placing directly into the SQL?

Thanks.

posted by darren 7 years ago

Hi Will,

At line 2831, do you think it will cause any problems if i do the following as a temporary workaround?

} elseif ($piece == '%s' && $value !== NULL && ((string) $value) == '') {

// to

} elseif ($piece == '%s' && $value !== NULL && $value == '') {

Thanks

posted by darren 7 years ago