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

Question

My colleague wrote something really terrible. Our boss wanted to be able to write any SELECT queries from the Back Office of our site, and then get the result in CSV format.

These queries will be executed by our PRODUCTTION MySQL cluster. This back office function should reject any request other than SELECT.

Therefore, he comes up with a truly naive solution for this. 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 who do not know PHP, this code removes the empty space from the beginning and end of the SQL query string, and then receives the first 6 characters, converts them to lowercase characters and, if they do not match (erf ...), select the request, the request 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 this.

However, I cannot prove to him that some hacks are possible.

It uses mysql_query () to run the query string, and this driver immediately rejects multiple queries. I cannot find any real exploit, but I think there is at least a 50% chance that something bad can happen.

Maybe some kind of NUL char, or some gloomy utf-8 characters can do the trick?

Answer 1

I see no way to get around your colleague checkQuery . It would be trivial if you did not use the mysql_* functions in PHP, but since they allow only one statement to be executed, they are at least not vulnerable to classics like Little Bobby Tables attack style. And in fact, this is one of the key reasons why standard query functions in both mysql_* and mysqli_* do not allow multiple statements (this is described in mysqli Multiple Statements .

I looked at other routes and I am very pleased that your peer code really works. (There is a way to generate false positives, for example, to initiate detection even with a valid SELECT query, but this is not very useful for you.) And while you do not have functions that modify data as a side effect, it shouldn’t do anything except SELECT , with statements starting with SELECT...

It is possible to write new files to the server using SELECT ... INTO OUTFILE . This is limited to new files, so you cannot clobber / etc / passwd or something else, but there are dangers.

The most dangerous scenario that I see will be if your MySQL server is the same computer as your PHP server, your MySQL user can write to your PHP directory, and the user you use has the FILE privilege. In this case, a carefully crafted SELECT ... INTO OUTFILE along these lines:

 SELECT '<?php echo \'DANGER WILL ROBINSON!\';' INTO OUTFILE '/Users/matt/Sites/dangerous.php' 

can allow someone to create a new PHP file that can be executed, and then go to it, and at that moment someone can execute arbitrary PHP code. I tested this potential exploit on the local computer and granted the user SELECT and FILE privileges and allowed MySQL write access to my wwwroot, I could successfully create a PHP file with the above code and execute it in my browser.

Besides attacks on this particular line, which may not be possible if the user you are currently using does not have FILE privileges, I do not see much danger. I know that this will not help you, but it seems to be the answer to your question.

However, I still highly recommend using MySQL authentication and security to create a new user for this application that has only SELECT permission. This is a built-in, database-based, absolutely standard, proven, proven way to solve the problem and limiting possible future problems.

For example, if someone hacks your PHP server in some other way, while you can read your MySQL user password, then they have access to an authenticated user with high privileges that they can use for other attacks (maybe by throwing your own scripts on your PHP server and running them.) If the MySQL user is SELECT-only, this limits the damage that your data / database can cause, even if your PHP server is completely compromised.

Answer 2

The correct approach to this function (if you cannot convince it that this is a bad idea) is to run queries as a limited MySQL user. Grant this user only SELECT permission. You can also limit the set of tables if you want.