3 ms·
Yes, something like this $user_ids = [1, 6, 46, 3, 17]; $count = count($user_ids); $in = '(' . implode(', ', array_fill(0, $count, '?')) . ')'; $sql =
by tored 5y ago
Yes, something like this
$user_ids = [1, 6, 46, 3, 17];
$count = count($user_ids);
$in = '(' . implode(', ', array_fill(0, $count, '?')) . ')';
$sql = "SELECT user_id, email FROM user WHERE user_id IN {$in}";
$stmt = $pdo->prepare($sql);
$stmt->execute($user_ids);
var_dump($stmt->fetchAll());
- throwawayboise 5y agoI realize this is just an example but if you're in control of all the values then there's nothing unsafe about concatenating them into a query. The problem comes with values that are submitted via a web form, API, or some other external, untrusted source.
- sellyme 5y agoYep. Fortunately a lot of the use cases of IN() are with controlled values - often IDs obtained from a previous query - so string concatenation is safe (and a lot less hassle than the alternatives). Unfortunately that gets people into the habit of using string concatenation, which is not a great habit to have.
- tored 5y agoNot sure if I follow you completely, but in my example $user_ids could come from an external source because I’m only concatenating question marks (?), then bind $user_ids with execute and that is safe. What I also always do before passing the $user_ids to execute if it comes from an external source is to validate that everyone of them is an integer with filter_var.
- wk_end 5y agoThis is true but it’s still better to default to the Safe Thing, as long as there isn’t a good reason not to. How long until those values you know you control carelessly get turned into values you don’t control somewhere along the way?
- sumtechguy 5y agoI used to mess with other devs by injecting bits of code from other sources (when blink in html worked it was one of my favorite ones). The correct way is to bind your params and do not trust that the data you got from some other system is 'OK'. What may be fine in one system could be an escape code in another. I speculate that the reason this is such an issue is because the interface at the ODBC level is basically security wise broken. It works 'OK' for getting/putting the data but it has 2 modes of execution. One of those paths is not great for security, the other has a usage issue. 'Binding' can be a real pain as it takes at least 1 call per variable parameter. Then managing the buffers correctly. So just building up the strings is an easy way to skip a lot of steps. So many take it. But that path leads to security vulins.