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 stackoverflow.com/questions/2373562/pdo-with-where-in-queries for how to do this with prepared query instead of substituting strings. — Barmar
– Barmar, Commented Jul 22, 2020 at 23:55

Alberto · Accepted Answer · 2020-07-22 16:53:41Z

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, 2020 at 16:53

answered Jul 22, 2020 at 16:49

Alberto

13.1k3 gold badges31 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

11 Comments

Luuk Over a year ago

` 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.

user13977445 Over a year ago

Does something like SELECT id FROM user WHERE id IN (any number) exist?

Alberto Over a year ago

@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

user13977445 Over a year ago

@Luuk the values are from $_GET but I check if the array just contains numbers :).

Alberto Over a year ago

@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

|

Shubham Sakharkar · Accepted Answer · 2020-07-22 18:07:57Z

0

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

answered Jul 22, 2020 at 18:07

Shubham Sakharkar

1051 silver badge9 bronze badges

Collectives™ on Stack Overflow

sql pdo php where in variable

2 Answers 2

11 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

11 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related