sql pdo php where in variable

Question

I'd like to select some data from my database using variables.

Here is a short version of my code:

// $a is either null or something like [1, 2]
if ($a) {
    $debug = implode(',', $a);
} else {
    $debug = [0-9];
}

$sql = "SELECT id FROM user WHERE id IN ($debug)"

How can I achieve that I get only user 1 and 2 (= value in $a) if $a is set and all user if $a is not set?

See https://stackoverflow.com/questions/2373562/pdo-with-where-in-queries for how to do this with prepared query instead of substituting strings. — Barmar, Jul 22 '20 at 23:55

Alberto Sinigaglia · Answer 1 · 2020-07-22T16:53:41.927

1

First:
be aware when you directly inject string inside queries, because you can be target of a SQL Injection

Second:
change directly the query might be the easiest solution

// $a is either null or something like [1, 2]
$sql = "SELECT id FROM user";
if ($a) {
    $debug = implode(',', $a);
    $sql = "SELECT id FROM user WHERE id IN ($debug)";
}

edited Jul 22 '20 at 16:53

answered Jul 22 '20 at 16:49

Alberto Sinigaglia

12,097
2
20
48

` target of a SQL Injection` does this not need input from a user? If `$debug` is only set with known values (not from `$_POST`, or so) then this should not be a problem. – Luuk Jul 22 '20 at 16:52
Does something like `SELECT id FROM user WHERE id IN (any number)` exist? – user13977445 Jul 22 '20 at 16:52
@Luuk infact in my code i've injected directly the string since idk where that array comes from, but i'm warning him of that possibility – Alberto Sinigaglia Jul 22 '20 at 16:54
@Luuk the values are from $_GET but I check if the array just contains numbers :). – user13977445 Jul 22 '20 at 16:54
@user13977445 i'm unable to find any way to achieve what you are asking using numbers, or if you really really need this kind of things, you can use something like `where ... id IN (select id from user)` and so injecting `select id from user` as string, but i mean, it's not that great as a suggestion ahah – Alberto Sinigaglia Jul 22 '20 at 16:57
@Berto99 hmm, I think I have no other option then selecting the id. is this a security risk? Can I store the select statement in a variable and then use this varible like ... in ($var)? I don't get it to work :/ – user13977445 Jul 22 '20 at 17:14
@Berto99 otherwise I can select all ids in a different statement an store them in a variable? Is this a "better" option? – user13977445 Jul 22 '20 at 17:16
@user13977445 sorry for the question but, why the solution shown in the answer does not fit your requirements? – Alberto Sinigaglia Jul 22 '20 at 17:18
@Berto99 just because the sql statement is very very long (more than 10 joins, multiple where and grouping). If there is another option I'd be glad :)! – user13977445 Jul 22 '20 at 17:22
@user13977445 can you please post the real cases? so that i can maybe manipulate the string in a better way – Alberto Sinigaglia Jul 22 '20 at 17:25
@Berto99 I figured out a solution yesterday: I store part of the sql query (tbl_user in (1,2)) or just the value true in a variable so I can use it in the actual sql statment like SELECT id FROM user where [...] and $var. Thanks for your help! – user13977445 Jul 23 '20 at 06:59

score 0 · Answer 2 · answered Jul 22 '20 at 18:07

you can set flag on $a to check if its null $debug must contain a comma separated string with ids i.e '2,32,12,54,56,76'

$sql = "SELECT id FROM user ";
if(!empty($a)){
$debug = implode(',', $a); //here you must get string like '1,2,3,4,5'
$sql .= " WHERE id IN ($debug)";
}

//here if $a is empty you have all rows and if $a have values you get selected rows

//final query will be
// if a is empty 'SELECT id FROM user '
// else 'SELECT id FROM user WHERE id IN (1,2,3)' // where 1,2,3 got from $debug

sql pdo php where in variable

2 Answers2

Linked