MySQL - Advanced Search
Posted: Fri Mar 07, 2008 4:08 am
Hi everyone
I'm currently developing a small intranet application using PHP and MySQL, we're a small business so there'll only be 10 users max.
As well as a regular search screen that all users will use, I also plan to have an advanced search where one other user will be able to enter full SQL queries to return results.
For more complicated searches of our data that require more complicated fields and joins.
I only want that user to be able to perform SELECT queries. And basically they'll just copy & paste the SQL code I give them, into a textarea and run the query.
I've got it almost working so far returning results both in an HTML table, but also in CSV format.
There are 3 ways I have thought of doing this:
1) Run str_replace() on the query to remove the following words, UPDATE, TRUNCATE, DROP, DELETE etc.
And ensure that the first word entered in the Query is SELECT.
2) Have the query broken up into separate boxes.
One textarea for the user to enter the fields they want as part of the select.
Another for the bit after the FROM, where they list the tables.
One combined textarea after that for joins and WHERE clause
Another box for ORDER BY
A final box for LIMIT
Then my script combines all those user inputs into a query like this:
SELECT $input_fieldlist
FROM $input_tablelist
$input_wherejoins
ORDER BY $input_orderby
[LIMIT $input_limit]
Square brackets round the final LIMIT clause as it will be optional.
Sections in red are bits my script will add in to concatenate the user input into a query.
Green is the user input.
If they enter an invalid MySQL query, obviously my error handling will kick in.
3) Use a limited localhost MySQL user account for this query, that only permits SELECT queries. So the user can't possibly do anything wrong.
Clearly option 3 is the most secure, and I'll definitely implement that.
But also combine it with option 2 as well?
Or just have one large textarea as in option 1?
Comments / suggestions?
Thanks, B
I'm currently developing a small intranet application using PHP and MySQL, we're a small business so there'll only be 10 users max.
As well as a regular search screen that all users will use, I also plan to have an advanced search where one other user will be able to enter full SQL queries to return results.
For more complicated searches of our data that require more complicated fields and joins.
I only want that user to be able to perform SELECT queries. And basically they'll just copy & paste the SQL code I give them, into a textarea and run the query.
I've got it almost working so far returning results both in an HTML table, but also in CSV format.
There are 3 ways I have thought of doing this:
1) Run str_replace() on the query to remove the following words, UPDATE, TRUNCATE, DROP, DELETE etc.
And ensure that the first word entered in the Query is SELECT.
2) Have the query broken up into separate boxes.
One textarea for the user to enter the fields they want as part of the select.
Another for the bit after the FROM, where they list the tables.
One combined textarea after that for joins and WHERE clause
Another box for ORDER BY
A final box for LIMIT
Then my script combines all those user inputs into a query like this:
SELECT $input_fieldlist
FROM $input_tablelist
$input_wherejoins
ORDER BY $input_orderby
[LIMIT $input_limit]
Square brackets round the final LIMIT clause as it will be optional.
Sections in red are bits my script will add in to concatenate the user input into a query.
Green is the user input.
If they enter an invalid MySQL query, obviously my error handling will kick in.
3) Use a limited localhost MySQL user account for this query, that only permits SELECT queries. So the user can't possibly do anything wrong.
Clearly option 3 is the most secure, and I'll definitely implement that.
But also combine it with option 2 as well?
Or just have one large textarea as in option 1?
Comments / suggestions?
Thanks, B