In our application, users can create custom export functions in form of SQL statements. Something like this:
SELECT name, age, date_birth FROM users WHERE group_id = 2
I don't want them to clear the whole database by inserting a DELETE statement. My ideas would be:
We are using PHP PDO.
As I see it, there are three options to choose from:
Create a tool that will create the query for the user on the background. Simply by clicking buttons and entering table names. This way you can catch all weird behavior in the background bringing you out of danger for queries you don't want executed.
Create a MySQL user that is only allowed to do SELECT
queries. I believe you can even decide what tables that user is allowed to select from. Use that user to execute the queries the user enters. Create a seperate user that has the permissions you want it to to do your UPDATE
, INSERT
and DELETE
queries.
Before the query is executed, make sure there is nothing harmfull in it. Scan the query for bad syntax.
Example:
// Check if SELECT is in the query
if (preg_match('/SELECT/', strtoupper($query)) != 0) {
// Array with forbidden query parts
$disAllow = array(
'INSERT',
'UPDATE',
'DELETE',
'RENAME',
'DROP',
'CREATE',
'TRUNCATE',
'ALTER',
'COMMIT',
'ROLLBACK',
'MERGE',
'CALL',
'EXPLAIN',
'LOCK',
'GRANT',
'REVOKE',
'SAVEPOINT',
'TRANSACTION',
'SET',
);
// Convert array to pipe-seperated string
// strings are appended and prepended with \b
$disAllow = implode('|',
array_map(function ($value) {
return '\b' . $value . '\b';
}
), $disAllow);
// Check if no other harmfull statements exist
if (preg_match('/('.$disAllow.')/gai', $query) == 0) {
// Execute query
}
}
Note: You could add some PHP code to filter out comments before doing this check
What you are looking to do is quite possible however you'll never have a 100 percent guarantee that it's safe. Instead of letting the users make the queries it's better to use an API to provide data to your users.