1

What is the best practice a scenario where you have complex, user-specified WHERE clauses and you want to use PDO and MySQL?

My users need to be able to specify one or more selection criteria for one or more columns of the table they're querying. There are way too many possible combinations to anticipate in a case statement at the PHP end. On the other hand, allowing them to type in their criteria directly would defeat the protection parametrized queries are supposed to offer against SQL injection attacks.

I'm trying to do exactly what this guy did:

How can execute a MySQL query with multiple WHERE-clauses?

...except I'm trying to be a good little codemonkey and use PDO instead of php_mysql, and to not rely on string-sanitizing unless that really is what people do for such scenarios.

Oh, one more thing:

I guess I can pass each user-supplied criterion as a [column, operator, value] array (e.g. [ "age", ">", "30" ]). I could verify that the first two elements of that array exactly match valid column names and operator names respectively, coerce the last element to a number, and then append them to a string containing the WHERE clause in the PHP script. But what about a criterion like ["fullname","like","M_rty M%cFly"]? Now I'm back to sanitizing user-created character strings, aren't I?

Community
  • 1
  • 1
bokov
  • 3,444
  • 2
  • 31
  • 49
  • 1
    Are you still looking for an answer to this question? Look at my answer here: http://stackoverflow.com/questions/13333006/insert-large-amount-of-variables-into-table-using-pdo/13333344#13333344, including the links I added at the bottom. If you want help after that, let me know and I'll assist. – Jonathan Spiller Dec 04 '12 at 17:50

1 Answers1

2

But what about a criterion like ["fullname","like","M_rty M%cFly"]?

For column names and operators (including LIKE) use whitelisting to validate them. If the user input doesn't match a known valid choice, don't use that input.

For any literal string value, such as "M_rty M%cFly", use query parameters. Same for numeric or date literals. Query parameters are best for any dynamic content that should be treated as a literal value in the SQL expression.

Use query parameters for values, and use whitelisting for other cases.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • +1 so the correct answer! In a sea of wrong answers and meme responses that don't understand that parameterised queries wont sanitise a field list for you. – Gavin Jul 03 '13 at 10:42