PDO Prepared Statements and WHERE .. IN

[Gordon]

I want to search a folder structure represented in a database. I'm
using the ID and Parent columns model, and would like to be able to
search a folder's subfolders as well as teh folder itself.

To do this I'm getting a list of all the subfolders with a recursive
function and building them into a CSV list of IDs. Example output:
472,944,925,931 ,938,1692,1005, 1036,1046,1051, 1042,1816,1819, 921

I then want to do a select against the list using an IN clause. I'm
using PDO and prepared statements. The query would look something
like:

SELECT * FROM items where someValue = ? AND parent IN (?);

I execute () this statement with some search value as the first item
in the array I feed into the execute method, and my CSV list of IDs as
the second value. However, this doesn't work. It seems that the list
I'm feeding in is getting wrapped in quotes, and that's causing
Postgres to evaluate my list as a string instead of a list of
integers.

Warning: PDOStatement::e xecute() [function.PDOSta tement-execute]:
SQLSTATE[22P02]: Invalid text representation: 7 ERROR: invalid input
syntax for integer:
"472,944,925,93 1,938,1692,1005 ,1036,1046,1051 ,1042,1816,1819 ,921"

If i hard-code these values into the query I get the expected results,
but not if I try to put them in via the prepared statement
mechanism.

Can anybody help with a solution to this?

[Jerry Stuckle]

Re: PDO Prepared Statements and WHERE .. IN

Gordon wrote:

I want to search a folder structure represented in a database. I'm
using the ID and Parent columns model, and would like to be able to
search a folder's subfolders as well as teh folder itself.
>
To do this I'm getting a list of all the subfolders with a recursive
function and building them into a CSV list of IDs. Example output:
472,944,925,931 ,938,1692,1005, 1036,1046,1051, 1042,1816,1819, 921
>
I then want to do a select against the list using an IN clause. I'm
using PDO and prepared statements. The query would look something
like:
>
SELECT * FROM items where someValue = ? AND parent IN (?);
>
I execute () this statement with some search value as the first item
in the array I feed into the execute method, and my CSV list of IDs as
the second value. However, this doesn't work. It seems that the list
I'm feeding in is getting wrapped in quotes, and that's causing
Postgres to evaluate my list as a string instead of a list of
integers.
>
Warning: PDOStatement::e xecute() [function.PDOSta tement-execute]:
SQLSTATE[22P02]: Invalid text representation: 7 ERROR: invalid input
syntax for integer:
"472,944,925,93 1,938,1692,1005 ,1036,1046,1051 ,1042,1816,1819 ,921"
>
If i hard-code these values into the query I get the expected results,
but not if I try to put them in via the prepared statement
mechanism.
>
Can anybody help with a solution to this?
>

Sorry, my crystal ball is still in the shop, and you didn't provide
enough code to determine your problem.

--
=============== ===
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attgl obal.net
=============== ===

[=?iso-8859-1?Q?=C1lvaro?= G. Vicario]

Re: PDO Prepared Statements and WHERE .. IN

*** Gordon escribió/wrote (Thu, 11 Sep 2008 08:50:42 -0700 (PDT)):

Warning: PDOStatement::e xecute() [function.PDOSta tement-execute]:
SQLSTATE[22P02]: Invalid text representation: 7 ERROR: invalid input
syntax for integer:
"472,944,925,93 1,938,1692,1005 ,1036,1046,1051 ,1042,1816,1819 ,921"
>
If i hard-code these values into the query I get the expected results,
but not if I try to put them in via the prepared statement
mechanism.

I suppose you define only one parameter and try to feed it with the
integers list:

$somevalue = 31416;
$nodes = '472,944,925,93 1,938,1692,1005 ,1036,1046,1051 ,1042,1816,1819 ,921';
$sql = 'SELECT * FROM items where someValue = ? AND parent IN (?)';
....
$sth = $dbh->prepare($sql );
$sth->execute(array( $somevalue, $nodes));

You don't want one big string, you want many integers. Your query will need
to look like:

SELECT * FROM items where someValue = ? AND parent IN (?, ?, ?, ?, ?, ?, ?)



--
-- http://alvaro.es - Álvaro G. Vicario - Burgos, Spain
-- Mi sitio sobre programación web: http://bits.demogracia.com
-- Mi web de humor en cubitos: http://www.demogracia.com
--

[Gordon]

Re: PDO Prepared Statements and WHERE .. IN

On Sep 11, 7:09 pm, "Álvaro G. Vicario"
<webmasterNOSPA MTHA...@demogra cia.comwrote:

*** Gordon escribió/wrote (Thu, 11 Sep 2008 08:50:42 -0700 (PDT)):
>

Warning: PDOStatement::e xecute() [function.PDOSta tement-execute]:
SQLSTATE[22P02]: Invalid text representation: 7 ERROR: invalid input
syntax for integer:
"472,944,925,93 1,938,1692,1005 ,1036,1046,1051 ,1042,1816,1819 ,921"

>

If i hard-code these values into the query I get the expected results,
but not if I try to put them in via the prepared statement
mechanism.

>
I suppose you define only one parameter and try to feed it with the
integers list:
>
$somevalue = 31416;
$nodes = '472,944,925,93 1,938,1692,1005 ,1036,1046,1051 ,1042,1816,1819 ,921';
$sql = 'SELECT * FROM items where someValue = ? AND parent IN (?)';
...
$sth = $dbh->prepare($sql );
$sth->execute(array( $somevalue, $nodes));
>
You don't want one big string, you want many integers. Your query will need
to look like:
>
SELECT * FROM items where someValue = ? AND parent IN (?, ?, ?, ?, ?, ?, ?)
>
--
--http://alvaro.es- Álvaro G. Vicario - Burgos, Spain
-- Mi sitio sobre programación web:http://bits.demogracia.com
-- Mi web de humor en cubitos:http://www.demogracia.com
--

Experimentation seems to suggest so much, but I've got to be able to
feed the query a variable amount of parameters. I could inject the
string of ints directly into the query, but that's the old way of
doing things and brings back the spectre of potential SQL injections
that prepared queries are meant to mitigate.

It does give me another idea, maybe I could just get the length of the
array my list gets returned in and insert the correct number of
question marks into the query before preparing it. That should allow
an arbitrary length of parameters while still keeping raw strings out
of the query (only question marks get injected, the number of question
marks dependant on the length of the list). Thanks for the help,
unlike Jerry your suggestion was very helpful. I'll give it a try
when I get back to work tomorrow.

[Curtis]

Re: PDO Prepared Statements and WHERE .. IN

Gordon wrote:

On Sep 11, 7:09 pm, "Álvaro G. Vicario"
<webmasterNOSPA MTHA...@demogra cia.comwrote:

>*** Gordon escribió/wrote (Thu, 11 Sep 2008 08:50:42 -0700 (PDT)):
>>

>>Warning: PDOStatement::e xecute() [function.PDOSta tement-execute]:
>>SQLSTATE[22P02]: Invalid text representation: 7 ERROR: invalid input
>>syntax for integer:
>>"472,944,925, 931,938,1692,10 05,1036,1046,10 51,1042,1816,18 19,921"
>>If i hard-code these values into the query I get the expected results,
>>but not if I try to put them in via the prepared statement
>>mechanism.

>I suppose you define only one parameter and try to feed it with the
>integers list:
>>
>$somevalue = 31416;
>$nodes = '472,944,925,93 1,938,1692,1005 ,1036,1046,1051 ,1042,1816,1819 ,921';
>$sql = 'SELECT * FROM items where someValue = ? AND parent IN (?)';
>...
>$sth = $dbh->prepare($sql );
>$sth->execute(array( $somevalue, $nodes));
>>
>You don't want one big string, you want many integers. Your query will need
>to look like:
>>
>SELECT * FROM items where someValue = ? AND parent IN (?, ?, ?, ?, ?, ?, ?)
>>
>--
>--http://alvaro.es- Álvaro G. Vicario - Burgos, Spain
>-- Mi sitio sobre programación web:http://bits.demogracia.com
>-- Mi web de humor en cubitos:http://www.demogracia.com
>--

>
Experimentation seems to suggest so much, but I've got to be able to
feed the query a variable amount of parameters. I could inject the
string of ints directly into the query, but that's the old way of
doing things and brings back the spectre of potential SQL injections
that prepared queries are meant to mitigate.
>
It does give me another idea, maybe I could just get the length of the
array my list gets returned in and insert the correct number of
question marks into the query before preparing it. That should allow
an arbitrary length of parameters while still keeping raw strings out
of the query (only question marks get injected, the number of question
marks dependant on the length of the list). Thanks for the help,
unlike Jerry your suggestion was very helpful. I'll give it a try
when I get back to work tomorrow.

Have a look at the str_repeat() function, if you decide to go through
with your idea.

Also, you could loop through your list of data, and intval() each
value, in which case, the data would be safe to use in the query.

--
Curtis

[AqD]

Re: PDO Prepared Statements and WHERE .. IN

Gordon wrote:

I want to search a folder structure represented in a database. I'm
using the ID and Parent columns model, and would like to be able to
search a folder's subfolders as well as teh folder itself.
>
To do this I'm getting a list of all the subfolders with a recursive
function and building them into a CSV list of IDs. Example output:
472,944,925,931 ,938,1692,1005, 1036,1046,1051, 1042,1816,1819, 921
>
I then want to do a select against the list using an IN clause. I'm
using PDO and prepared statements. The query would look something
like:
>
SELECT * FROM items where someValue = ? AND parent IN (?);
>
I execute () this statement with some search value as the first item
in the array I feed into the execute method, and my CSV list of IDs as
the second value. However, this doesn't work. It seems that the list
I'm feeding in is getting wrapped in quotes, and that's causing
Postgres to evaluate my list as a string instead of a list of
integers.
>
Warning: PDOStatement::e xecute() [function.PDOSta tement-execute]:
SQLSTATE[22P02]: Invalid text representation: 7 ERROR: invalid input
syntax for integer:
"472,944,925,93 1,938,1692,1005 ,1036,1046,1051 ,1042,1816,1819 ,921"
>
If i hard-code these values into the query I get the expected results,
but not if I try to put them in via the prepared statement
mechanism.
>
Can anybody help with a solution to this?

PDO doesn't seem to support parameter value in array type. You have to
make the conversion by yourself.

[AqD]

Re: PDO Prepared Statements and WHERE .. IN

On Sep 16, 9:17 am, AqD <aquila.d...@gm ail.comwrote:

Gordon wrote:

I want to search a folder structure represented in a database.  I'm
using the ID and Parent columns model, and would like to be able to
search a folder's subfolders as well as teh folder itself.

>

To do this I'm getting a list of all the subfolders with a recursive
function and building them into a CSV list of IDs.  Example output:
472,944,925,931 ,938,1692,1005, 1036,1046,1051, 1042,1816,1819, 921

>

I then want to do a select against the list using an IN clause.  I'm
using PDO and prepared statements.  The query would look something
like:

>

SELECT * FROM items where someValue = ? AND parent IN (?);

>

I execute () this statement with some search value as the first item
in the array I feed into the execute method, and my CSV list of IDs as
the second value.  However, this doesn't work.  It seems that the list
I'm feeding in is getting wrapped in quotes, and that's causing
Postgres to evaluate my list as a string instead of a list of
integers.

>

Warning: PDOStatement::e xecute() [function.PDOSta tement-execute]:
SQLSTATE[22P02]: Invalid text representation: 7 ERROR: invalid input
syntax for integer:
"472,944,925,93 1,938,1692,1005 ,1036,1046,1051 ,1042,1816,1819 ,921"

>

If i hard-code these values into the query I get the expected results,
but not if I try to put them in via the prepared statement
mechanism.

>

Can anybody help with a solution to this?

>
PDO doesn't seem to support parameter value in array type. You have to
make the conversion by yourself.

The result should be set as PDO::PARAM_STMT type rather than
PDO:PARAM_STR, but I'm not sure if this is implemented ;)