MySQL dynamic where clause with security focus
MySQL dynamic where clause with security focus
I want to make a fully dynamic where clause as viewset. The where clause is fully implemented by the user and stored in a table. To get this working is easy but if have alook at security with this solution it's a big problem.
This should be implemented as a stored procedure
I have 2 tables:
Table: Viewset (where clause stored):
ID | Viewset<br>
1 | (a > 5) OR REGEX(...) etc.
And a datatable. In the stored procedure it's working like this:
This is ofc, fully vulnerable for SQL injections etc.
Now I want to get this secure. I had 2 ideas.
First: Just try to get any possible 'AND' and 'OR' connection and using a prepared statement at the end => Problem: I have around 100 possible combinations.
Second idea: where clause through a whitelist of words and the values through prepared statements. Problem again: MySQL doesn't allow arrays and string splits etc. that makes this very hard to implement.
Maybe there is a better solution.
Well if the query is prepared using something like PDO then the whitelist isn't neccessary? The prepared statement would stop any form of injection, just results in a query that returns 0 rows
– P James
Dec 15 '16 at 16:06
For field names it should protect, but tables and column names cannot be prepared with PDO. prepare('SELECT * FROM :table') will not work. So you would need to write it as prepare('SELECT * FROM $table'). If one could modify $table it could become a problem. Therefore you use an array of allowed $tables even tho you use PDO.
– user3606329
Dec 15 '16 at 16:43
By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.
Why you don't implement the whitelist of words in your code before you peform the SQL query? (for example in PHP). This is usually the normal way, even if you use PDO (for ORDER BY commands, etc.)
– user3606329
Dec 15 '16 at 15:57