How to allow SELECT queries and prevent others?

Question

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:

• Use an SQL account, which is only allowed to SELECT. (I don't want to do this, if there are alternatives.)
• Use a magic regex, that checks whether the query is dangerous or not. (Would this be good? Is there already such a regex?)

We are using PHP PDO.

Answer 1

As I see it, there are three options to choose from:

Option 1

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.

Option 2

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.

Option 3

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

Conclusion

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.