I'm developing a project using CodeIgniter where the pages displayed have an option list with available queries (SELECT statements) for the displayed data on the page. These queries are stored in a MySQL table and include User Defined queries.
My questions is, what's the best method of handling execution of the selected query:
Obtain SQL Statement from Table (view_id key) using query command. Replace parameters in returned SQL statement. Execute SQL statement using query command.
When loading the page, store the SQL statements in an array (could be large). When user changes the selection on the page, grab the statement from the array, replace parameters in statement, run query.
Use MySQL prepared statement. (foresee an issue with the parameters, as all views would need the same parameter sequence)
Other options?
I'm looking for an efficient as well as secure method.
Thanks
The first one - Don't expose any of your sql - you are only asking for trouble.
You want to separate the user input from the actual code being run. Set ti up just like it was a list of links to normal things a user would have - example, profile, account settings, etc.
Parse your input for sql injection, permission to actually run that query, etc.
in other words, set it up like a simple list of names of pages in your site - completely ignore he is selecting sql statements