PDO arrays and using IN in select statement

PHP
1

we are trying to make our site safer from injection, and have hit a problem and I cant work out the answer.

if (!empty($_GET['regions']) ? $_GET['regions'] : null) {
$regionsArray = array();
foreach($_GET['regions'] as $regions) {	 
	 $regionsArray[] = ''.$regions.'';
}
$placeHoldersRegion = implode(',', array_fill(0, count($regionsArray), '?'));
$sqlregion =  ' AND IdRsrt_Hot IN ('.$placeHoldersRegion.')';	 
}

and using the above in the code below I get the error also below

if ($sqlregion!="" && $sqlstar=="" && $sqlcategory==""){
$f = $pdo->prepare("select DISTINCT(tbl_hotels.IdCat_Hot) from tbl_hotels WHERE (Act_Hot='1') AND (IdCat_Hot IN (1,2,3,4,5)) AND (IdCntry_Hot=:selectCountry)  AND (IdRsrt_Hot IN (".$placeHoldersRegion.")) order by IdCat_Hot ASC");
$f->bindParam(":selectCountry", $selectCountry);
//$f->bindParam(":regionData", $regionData);
$f->execute($regionsArray);

Fatal error: Uncaught exception ‘PDOException’ with message ‘SQLSTATE[HY093]: Invalid parameter number: mixed named and positional parameters’ in \CSFFILES11\WEBSITES\dlt.php:316 Stack trace: #0 \CSFFILES11\WEBSITES\dult.php(316): PDO->prepare(‘select DISTINCT…’) #1 {main} thrown in

2

you use ? and :selectCountry in the same statement, which is invalid (as the error message tells you).

3

Hi Dormilich,

I wasn’t sure what was going on in honesty, so didn’t see the error as you see it.

I have a lot of these to sort out, so could you advise the way around it…

$selectCountry=@$_GET[‘Country’];

I’m getting that value from the line above

4

either use only positional placeholders (?) or only named placeholders (:name) but not both.

5

I understand I needed to do that, so with $selectCountry, do I add the question mark using a similar way to below, just not sure as its not an array

$placeHoldersRegion = implode(‘,’, array_fill(0, count($regionsArray), ‘?’));

but

$countryRegion = implode(‘,’, array_fill(0, count($selectCountry), ‘?’));

6

If it’s a scalar value then you just use ? in the sql.

7

I tried this as it sort of made sense, but it didnt work

$f = $pdo->prepare("select DISTINCT(tbl_hotels.IdCat_Hot) from tbl_hotels WHERE (Act_Hot='1') AND (IdCat_Hot IN (1,2,3,4,5)) AND (IdCntry_Hot=:?)  AND (IdRsrt_Hot IN (".$placeHoldersRegion.")) order by IdCat_Hot ASC");
$f->bindParam(":?", $selectCountry);
$f->execute($regionsArray);

and without the :

8

Is “:?” a valid placeholder name?

9

No .

10

mmm

is this it?

$f = $pdo->prepare("select DISTINCT(tbl_hotels.IdCat_Hot) from tbl_hotels WHERE (Act_Hot='1') AND (IdCat_Hot IN (1,2,3,4,5)) AND (IdCntry_Hot=?)  AND (IdRsrt_Hot IN (".$placeHoldersRegion.")) order by IdCat_Hot ASC");
$f->execute($selectCountry, $regionsArray);

dont think so though

11

the sql looks correct so far, but the execute() must be called with a single array containing all the data.

12

Ye funny I was just thinking that and tried below

$f = $pdo->prepare("select DISTINCT(tbl_hotels.IdCat_Hot) from tbl_hotels WHERE (Act_Hot='1') AND (IdCat_Hot IN (1,2,3,4,5)) AND (IdCntry_Hot=?)  AND (IdRsrt_Hot IN (".$placeHoldersRegion.")) order by IdCat_Hot ASC");
$f->execute(array_merge($selectCountry, $regionsArray));

But the first value isnt an array, just a single string value

13

lol no, I’m not getting it

$regionsArrayb = $regionsArray;
$regionsArrayb = array_push($selectCountry, $regionsArrayb);
$f->execute($regionsArrayb);

wasn’t the answer, I think I can hear you chuckle from here.

Could you push me a step on Dormilich

15

http://php.net/unshift

16

Ahh frustrating, I did manage to get around to that (honestly), but I had it as below and again it didnt work so moved on with it.

$f = $pdo->prepare("select DISTINCT(tbl_hotels.IdCat_Hot) from tbl_hotels WHERE (Act_Hot='1') AND (IdCat_Hot IN (1,2,3,4,5)) AND (IdCntry_Hot=?)  AND (IdRsrt_Hot IN (".$placeHoldersRegion.")) order by IdCat_Hot ASC");
$selectCountryb = array($selectCountry);
array_unshift($regionsArray, $selectCountryb); 
$f->execute($regionsArray);

Error: Array to string conversion in

17

and then cut it down to this and there no error, but nothing showing.

$f = $pdo->prepare("select DISTINCT(tbl_hotels.IdCat_Hot) from tbl_hotels WHERE (Act_Hot='1') AND (IdCat_Hot IN (1,2,3,4,5)) AND (IdCntry_Hot=?)  AND (IdRsrt_Hot IN (".$placeHoldersRegion.")) order by IdCat_Hot ASC");
array_unshift($regionsArray, $selectCountry); 
$f->execute($regionsArray);
18

What is in “idcntryhot” that would make it equal to the regions array and the selected country? Surely you only want to pass in $selectCountry to the query, as you only have the single parameter?

19

idcntryhot is a numerical value

20

Sorry, read earlier posts. Ignore this and my previous question.

21

No worries droopsnoot, this is now the full chunk of code and the checkboxes arent appearing

I echo’s out the array values and they are working.

$f = $pdo->prepare("select DISTINCT(tbl_hotels.IdCat_Hot) from tbl_hotels WHERE (Act_Hot='1') AND (IdCat_Hot IN (1,2,3,4,5)) AND (IdCntry_Hot=?) AND (IdRsrt_Hot IN (".$placeHoldersRegion.")) order by IdCat_Hot ASC");
array_unshift($regionsArray, $selectCountry); 
$f->execute($regionsArray);
while ($t = $f->fetch()) { ?>
<input type="checkbox" name="star[]" value="<?php echo $t['IdCat_Hot']?>" onClick="javascript:checkRefresh()" <?php echo (isset($_REQUEST['star']) && in_array($t['IdCat_Hot'], $_REQUEST['star'])) ? 'checked="checked"' : '' ?> class="inline" />
<?php echo $t['IdCat_Hot']?> Star