MySQL - is it safe to check the 6 first characters of a query to be sure it is a SELECT?

Question

A coworker of mine has written something really awful. Our boss wanted to be able to write any SELECT queries from the Back Office of our website, then get the result in CSV format.

Those queries will be executed by our PRODUCTION MySQL cluster. This back-office feature should reject any non-SELECT queries.

So he comes up with a really naive solution for that. This is the PHP code:

function checkQuery()
{
        $sQuery = trim($_POST['query']);
        if (empty($sQuery))
                return false;
        $sCmd = substr($sQuery, 0, 6);
        if (strtolower($sCmd) != 'select')
                return errorDiv('Only SELECT queries are authorized');
        return $sQuery;
}

For people not knowing PHP, this code removes white space from the begining and the end of an SQL query string, then get the 6 first characters, transforms them to lowercase characters, and if it doesn't match (erf... loosely match) 'select', the query is rejected.

It looks awful and disgusting to me. I tried to convince him to create at least another MySQL user, with limited privileges, but he is too lazy to do that.

However I cannot prove him that some kind of hacks are possible.

He uses mysql_query() to run the query string, and this driver rejects multiple queries at once. I can't find any real exploit, but I think there are at least 50% of chance that something bad can happen.

Maybe some NUL char, or some obscur utf-8 chars can do the trick?

Answer 1

The correct approach to this feature (if you can't convince him it is a bad idea) is to run the queries as a limited MySQL user. Grant that user only the SELECT permission. You can also limit the table set if desired.