Menu
Community /Pin to ProfileBookmark
—-
@NogDogNov 01.2021 — #Yeah, PDO (or probably any other DB abstraction layer of the same sort) is not going to automagically handle that. You'll probably have to just take that array and turn it into a comma-separated list, doing your own validation/escaping of the user-supplied data. If it's just integer values, maybe something like
@NogDogNov 02.2021 — #@kiwis80#1638942
That's the purpose of the foreach loop in my example: every value will be explicitly set to an integer, so if someone tries to include something like
Test output:
PDO SELECT WHERE IN (ARRAY)
Final question / issue with this.
I don’t believe this is possible but I’ll throw it out there.
I have an array of ID’s being submitted by a user. Is it possible to do sometihng like
[code]
WHERE `studentId` IN(:IDS)
Where IDS = implode(“,”, $_POST[students’]);
From testing it binds it into the query as a string which only matches against the first value.
—-
If nothing is possible. Given I need to do this against several fields. It is better to retrieve ALL records from my database and do PHP filtration. However I may have over 7,000 rows in my database shortly.
Sign in
to post a comment17 Comments(s) ↴
0
@NogDogNov 01.2021 — #Yeah, PDO (or probably any other DB abstraction layer of the same sort) is not going to automagically handle that. You'll probably have to just take that array and turn it into a comma-separated list, doing your own validation/escaping of the user-supplied data. If it's just integer values, maybe something like[code=php]
$ids = [];
foreach($_POST['students'] as $id) {
$ids[] = (int) $id;
}
$where_clause = "WHERE studentId IN(".implode(',', $ids).")";
[/code]0
@NogDogNov 02.2021 — #That's the purpose of the foreach loop in my example: every value will be explicitly set to an integer, so if someone tries to include something like
"1 or (truncate table students)"10
@NogDogNov 02.2021 — #Okay, this _might_ work, but I have not actually tested it against a database/connection, so.... 🤷 (Note use of &[code=php]
$_POST['students'] = [11, 13, 12, 15, 14];
$_POST['foo'] = 'bar';
$sql = "
SELECT *
FROM some_table
WHERE foo = :foo
";
$bind_values = [':foo' => &$_POST['foo']];
if(!empty($_POST['students']) and is_array($_POST['students'])) {
$counter = 1;
$student_ids = [];
foreach ($_POST['students'] as $student_id) {
${"student_id_$counter"} = $student_id;
$student_ids[":student_id_$counter"] = &${"student_id_$counter"};
$counter++;
}
$sql .= "AND id IN(".implode(',', array_keys($student_ids)).")n";
$bind_values = array_merge($bind_values, $student_ids);
}
$sql .= "ORDER BY some_column";
/* USAGE:
$stmt = $pdo->prepare($sql);
$stmt->execute($bind_values);
*/
// debug output:
echo "$sqln";
var_dump($bind_values);
[/code]Test output:
[code=text]
SELECT *
FROM some_table
WHERE foo = :foo
AND id IN(:student_id_1,:student_id_2,:student_id_3,:student_id_4,:student_id_5)
ORDER BY some_column
array(6) {
[":foo"]=>
&string(3) "bar"
[":student_id_1"]=>
&int(11)
[":student_id_2"]=>
&int(13)
[":student_id_3"]=>
&int(12)
[":student_id_4"]=>
&int(15)
[":student_id_5"]=>
&int(14)
}
[/code]