0

I'd like to safely use an array of strings within a WHERE IN SQL statement. For example:

$myArray = array('a','b','c');

Would be converted to something like

SELECT * FROM myTable WHERE name IN ('a','b','c');

We could do this in an unsafe manner by doing something like

$in = "WHERE IN " . implode(",",$myArray);

But that is completely insecure from SQL injection.

We could use preg_replace to strip out unwanted characters, but that might be limiting.

We could use addslashes but I'm not confident that would be injection safe.

We could use PDO::quote. This might work like this:

function pdoQuote($val) { global $conn; return $conn->quote($val); }
$in = "WHERE IN " . implode(",", array_map('pdoQuote',$myArray));

But the docs clearly say:

If you are using this function to build SQL statements, you are strongly recommended to use PDO::prepare() to prepare SQL statements with bound parameters instead of using PDO::quote() to interpolate user input into an SQL statement. Prepared statements with bound parameters are not only more portable, more convenient, immune to SQL injection, but are often much faster to execute than interpolated queries, as both the server and client side can cache a compiled form of the query.

Plus, it looks a bit messy, right?

We could also do something like a for loop to add the values to the query, and another for loop to bind them, but that does lead to lengthy and/or messy code.

My question: Is there a simple, safe way, that is only a single line (or so), of converting a PHP array into an SQL WHERE IN statement, that is has no security issues?

Many thanks in advance!

Ben
  • 4,707
  • 5
  • 34
  • 55
  • Where does this array come from? Is it user submitted? Surely you have a list of safe options? – GrumpyCrouton Nov 22 '17 at 14:14
  • The answer to your question is `no`. It will take more than 1 line. – chris85 Nov 22 '17 at 14:16
  • there is no simple a one-line way with vanilla PDO. Thought you can always write such a helper function yourself – Your Common Sense Nov 22 '17 at 14:16
  • @GrumpyCrouton it's essentially wrong question to ask. it should be nobody's business whether the data is "user submitted" or not – Your Common Sense Nov 22 '17 at 14:18
  • @YourCommonSense What? I was asking because if it's not user submitted, then OP doesn't need to do anything special to it. It is injection proof if users cannot alter it. – GrumpyCrouton Nov 22 '17 at 14:20
  • @GrumpyCrouton you cannot be more wrong. Besides, a programmer could busy himself with more important things than investigating the origin of the data they are dealing with – Your Common Sense Nov 22 '17 at 14:24
  • @YourCommonSense Thank you - if I were to write a helper function (which seems sensible) what would it look like? Would it use PDO->quote? Perhaps you could post an answer with this as a potential solution? – Ben Nov 22 '17 at 14:28
  • @Ben by no means it should be quote but prepare like in the linked question. I already posted, if you didn't notice, check the top of your question – Your Common Sense Nov 22 '17 at 14:29
  • @GrumpyCrouton the data is unfortunately based on user input (more specifically, returned from an AJAX call), otherwise as you say I wouldn't need to check for SQL injection. There may be a way to do what you suggest and check a look up of some kind - that's probably not going to answer the question above, but is a sensible way forward if possible. – Ben Nov 22 '17 at 14:30
  • @Ben If you had an array of _possibilities_, `$possible = ["col1", "col2", "col3"];`, then you could just loop the array through and check if the value is contained in the possible array. `foreach($user_input as $value) { if(!in_array($value, $possible)) die("INVALID PARAMETER"); }` If the user input is not a `$possible` value, then stop processing the page and return an error or whatever. – GrumpyCrouton Nov 22 '17 at 14:32
  • @GrumpyCrouton your ideas are impractical and dangerous. JFYI. Following your habit, you are chewing on some *imaginary* cases. Get more experience with real world issues. – Your Common Sense Nov 22 '17 at 14:35
  • @GrumpCrouton, makes sense, however in this example there would be 100,000s of possible values so this isn't really an option. I'd really like to find a way to safely prepare a string along the lines of `mysql_real_escape_string` if that's possible. – Ben Nov 22 '17 at 14:36
  • @YourCommonSense Instead of just saying it's "impractical and dangerous", how about you explain to me _how_ or _why_? Because I don't see how that solution is either of those things. If there were only a handful of possible valid inputs, then that solution is _very_ practical, and would work just fine safety wise. – GrumpyCrouton Nov 22 '17 at 14:49
  • @GrumpyCrouton it's impractical because for the values stored in the database, it's *SQL query* which is used to validate. Which makes your extra validation an overkill – Your Common Sense Nov 22 '17 at 15:05
  • @GrumpyCrouton why add another level to your database interaction? Why can't you use *the same generic approach for* all queries? Your suggestion makes sense for the field names, and for them it's ok. But for the data stored in the database it's a rather pointless overkill – Your Common Sense Nov 22 '17 at 15:17
  • @YourCommonSense I believe the best solution here is indeed to use bound parameters and have managed to do this with minimal changes to the code. Happy to post the solution if you're able to re-open the question. – Ben Nov 22 '17 at 15:40
  • @Ben I believe that existing solution is optimal enough – Your Common Sense Nov 22 '17 at 16:00

0 Answers0