MySQL - Advanced Search

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
batfastad
Forum Contributor
Posts: 433
Joined: Tue Mar 30, 2004 4:24 am
Location: London, UK

MySQL - Advanced Search

Post by batfastad »

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
User avatar
Ambush Commander
DevNet Master
Posts: 3698
Joined: Mon Oct 25, 2004 9:29 pm
Location: New Jersey, US

Re: MySQL - Advanced Search

Post by Ambush Commander »

No. 3, the readonly MySQL user-account, is crucial. Everything else is candy. Query builders especially, since they're much more complicated than they start off due to the complexity of SQL.
User avatar
batfastad
Forum Contributor
Posts: 433
Joined: Tue Mar 30, 2004 4:24 am
Location: London, UK

Re: MySQL - Advanced Search

Post by batfastad »

Yeah ok, thought that would be the best way.

I'll just have a large textarea (option 1) and a read-only MySQL account.
Also I guess I don't want to be running mysql_escape_string() on the query, as I actually want it to be executed correctly.

However I will be dealing with magic quotes on the variable, like all the variables, as that's handled globally within my included config.php file.

Thanks, B
Post Reply