pdo query bindparam array as string (or integer)?

Question

I'd like to make a pdo pgsql query with an IN operator where the value - witch come from a variable - also contains single id-s, and array from id-s like that:

UPDATE:

  if ($_POST['select'] == '1') {
    $id= 109;
  } elseif ($_POST['select'] == '2') {
    $id= 111;
  } elseif ($_POST['select'] == '3') {
    $id= 117;
  } elseif ($_POST['select'] == '4') {
    $id= 114;
  } elseif ($_POST['select'] == '5') {
    $id= [108, 107, 101, 103];
  } else {
    $id=NULL;
  }

The query looks like that:

$result = "SELECT * FROM table
WHERE table.id IN (:id)";

$query = $pgConn->prepare($result);
$query->bindparam(':id', $id);
$query->execute();

I’d try to convert the array to sting in multiple ways, but the query returns always the same error: “invalid input syntax for integer”

One attempt like:

$ids_string = "'".implode("','", $id)."'";

returns: invalid input syntax for integer: "'108','107','101','103'"'

The other - subsidiary - problem is to using single and array id-s from the same variable but I think is manageable later with an if-else statement.

Thanks for the help!

Take a look at [this post](https://stackoverflow.com/questions/920353/can-i-bind-an-array-to-an-in-condition) — wayneOS, Apr 24 '18 at 09:47
Yes, I've check this post. I've also try some solution from these. I can convert the array to sting, and when I place the converted variable directly into the query it works. But if I place it into the "$query->bindparam(':id', $id);" code, the query see it as an integer value and returns an error :/ — Attila Deák, Apr 24 '18 at 13:20

wayneOS · Accepted Answer · 2018-04-25T08:53:09.157

0

According to PHP.net about bindParam() and Predefined PDO Constants. you can specifiy the type of your varibale you want to bind. So to avoid that PDO sees your string as a number, do something like:

$query -> bindValue (':id', $id, PDO::PARAM_STR);

EDIT

To solve the problem with binding an array to IN() I guess you will have to use two loops. One to create the placeholders and one to bind your parameters. Here is an example for your case:

//create placeholders for IN()
foreach ($id_array as $i => $id) {
    if ($i == 0)
        $in_query = ':val_'.$i;
    else
        $in_query .= ', :val_'.$i;
}

//prepare statement with placeholders
$query = $pgConn -> prepare ("SELECT * FROM table WHERE table.id IN ($in_query)");

//bind values to placeholders
foreach ($id_array as $i => $id) {
    $query -> bindParam (":val_".$i, $id_array[$i]);
}

edited Apr 25 '18 at 08:53

answered Apr 24 '18 at 13:45

wayneOS

1,427
1
14
20

It doesnt works. I've also try: $query->bindparam(':id', $id, PDO::PARAM_STR, settype($id, "string")); – Attila Deák Apr 24 '18 at 14:00
@AttilaDeák ok...weird. Then try `bindValue()` instead of `bindParam()`. I updated my answer – wayneOS Apr 24 '18 at 14:07
Now: ERROR: bind message supplies 2 parameters, but prepared statement "pdo_stmt_00000001" requires 3' – Attila Deák Apr 24 '18 at 14:40
sorry, the `strlen ($id)` had to be removed. – wayneOS Apr 24 '18 at 14:46
No solution yet. Something wrong with the binding. If I take the $id directly to the query, everything OK regardless of the type of data. But if I know that this is a unsafe solution... – Attila Deák Apr 25 '18 at 05:52
@AttilaDeák I updated my answer again. Try this solution. It worked for me – wayneOS Apr 25 '18 at 08:54
Thanks, I will play with it :-) Otherwise is this whole procedure necessary if I specify the possible values of the $id variable like in the updated post? Nevertheless the query remain vulnerable if I place the $id directly to the IN() operator? – Attila Deák Apr 25 '18 at 11:16
yes. if you place a variable directly in the query-string it is considered unsecure. however, if you can be certain about that it will not causing any damage (maybe because you dont get any value from outside your code) it is irrelevant. but pdo also boosts your query-speed if you follow the standards. – wayneOS Apr 25 '18 at 12:59
I try your solution: It works but I had to change the syntax like that: foreach ($id as $i => $id_array) Thanks! – Attila Deák Apr 25 '18 at 13:56
Do you help me, how can I use with this solution together the single (integer) and array id-s from the same variable? For example: $id= 109; and $id= [108, 107, 101, 103]; – Attila Deák Apr 25 '18 at 20:54
@AttilaDeák it should also work with single integers. the first loop will only add a commata if there a more then one integer. and IN() should also work with a single parameter. what is your exact error? – wayneOS Apr 26 '18 at 06:36
Invalid argument supplied for foreach() – Attila Deák Apr 26 '18 at 07:44
@AttilaDeák then you have to do something like this before: `if (!is_array($id)) $id = array ($id);` – wayneOS Apr 26 '18 at 07:53
Now everything is OK ;-) Thanks for all! – Attila Deák Apr 26 '18 at 08:47

pdo query bindparam array as string (or integer)?

1 Answers1