Re:Ask HN: Creating a query builder for end users
by capkutay on Saturday April 19, 2014 @02:53PM
(#7614310)
'You must not convert this to SQL on the client side. You must validate the fields submitted are in your approved list on the backend (indexes/security).'
Interesting point. Can't you get around this by only exposing certain tables/fields in the UI based on the users permissions?
Re:Ask HN: Creating a query builder for end users
by shebson on Saturday April 19, 2014 @03:07PM
(#7614359)
You shouldn't trust it just because of your UI. With developer tools, regardless of your UI, it would be trivially easy for a malicious user to send any arbitrary SQL query. This would allow them to retrieve sensitive data or modify/delete records from your production database. You really should only build this query server-side, and, as the OP is using PHP, I should also add the caveat that they should use PDO.
The vast majority of your users won't use the complex filters.
Ask your customers what kinds of reports they want to build, usually it will be a series of AND requirements.
You can look at email filter builders for inspiration.
Usually it is something like building a series of :
Field (drop down) operator (drop down) value (text)
The tricky part in the ui is combining these (and/or) and if you really want to get fancy, supporting parena to change the precedence of the operators. A AND (b or c).
You must not convert this to SQL on the client side. You must validate the fields submitted are in your approved list on the backend (indexes/security).