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.

Answer 2

Don't do this, there will always be creative ways to make a dangerous query. Create an API that will manually construct your queries.

Answer 3

Since you said you would prefer not to use read only SQL accounts if there are alternatives. If you're running PHP 5.5.21+ or 5.6.5+: I'd suggest checking if the first statement of the query is a SELECT statement and disabling multiple queries in your PDO connection.

First disable multi statements on your PDO object...

$pdo = new PDO('mysql:host=hostname;dbname=database', 'user', 'password', [PDO::MYSQL_ATTR_MULTI_STATEMENTS => false]);

Then check that the first statement in the query uses SELECT and that there are no subqueries. The first regex ignores leading whitespace which is optional and the second detects parenthesis which would be used to create subqueries -- this does have the side effect of preventing users from using SQL functions but based on your example I don't think that's an issue.

if (preg_match('/^(\s+)?SELECT/i', $query) && preg_match('/[()]+/', $query) === 0) {
    // run query
}

If you're running an older version, you can disable emulated prepares to prevent multiple statements being executed but this relies on PDO::prepare() being used.

FWIW: It would be a lot better to use prepared statements/generate safe queries for your users or to use a read-only SQL account. If you're using MySQL and have admin rights/remote access, I'd suggest using the community edition of SQLyog (https://github.com/webyog/sqlyog-community/wiki/Downloads) to create read-only user accounts. It's extremely user friendly so you won't have to learn the GRANT syntax.