By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,360 Members | 3,011 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,360 IT Pros & Developers. It's quick & easy.

PDO Prepared Statements and WHERE .. IN

P: n/a
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::execute() [function.PDOStatement-execute]:
SQLSTATE[22P02]: Invalid text representation: 7 ERROR: invalid input
syntax for integer:
"472,944,925,931,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?
Sep 11 '08 #1
Share this Question
Share on Google+
6 Replies


P: n/a
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::execute() [function.PDOStatement-execute]:
SQLSTATE[22P02]: Invalid text representation: 7 ERROR: invalid input
syntax for integer:
"472,944,925,931,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.
js*******@attglobal.net
==================

Sep 11 '08 #2

P: n/a
*** Gordon escribió/wrote (Thu, 11 Sep 2008 08:50:42 -0700 (PDT)):
Warning: PDOStatement::execute() [function.PDOStatement-execute]:
SQLSTATE[22P02]: Invalid text representation: 7 ERROR: invalid input
syntax for integer:
"472,944,925,931,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,931,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
--
Sep 11 '08 #3

P: n/a
On Sep 11, 7:09*pm, "Álvaro G. Vicario"
<webmasterNOSPAMTHA...@demogracia.comwrote:
*** Gordon escribió/wrote (Thu, 11 Sep 2008 08:50:42 -0700 (PDT)):
Warning: PDOStatement::execute() [function.PDOStatement-execute]:
SQLSTATE[22P02]: Invalid text representation: 7 ERROR: invalid input
syntax for integer:
"472,944,925,931,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,931,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.
Sep 11 '08 #4

P: n/a
Gordon wrote:
On Sep 11, 7:09 pm, "Álvaro G. Vicario"
<webmasterNOSPAMTHA...@demogracia.comwrote:
>*** Gordon escribió/wrote (Thu, 11 Sep 2008 08:50:42 -0700 (PDT)):
>>Warning: PDOStatement::execute() [function.PDOStatement-execute]:
SQLSTATE[22P02]: Invalid text representation: 7 ERROR: invalid input
syntax for integer:
"472,944,925,931,938,1692,1005,1036,1046,1051,10 42,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,931,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
Sep 12 '08 #5

P: n/a
AqD
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::execute() [function.PDOStatement-execute]:
SQLSTATE[22P02]: Invalid text representation: 7 ERROR: invalid input
syntax for integer:
"472,944,925,931,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.
Sep 16 '08 #6

P: n/a
AqD
On Sep 16, 9:17*am, AqD <aquila.d...@gmail.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::execute() [function.PDOStatement-execute]:
SQLSTATE[22P02]: Invalid text representation: 7 ERROR: invalid input
syntax for integer:
"472,944,925,931,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 ;)
Sep 16 '08 #7

This discussion thread is closed

Replies have been disabled for this discussion.