MySQL’s “SELECT INTO OUTFILE” – Should Joomla Administrators Be Afraid?

Note: This post is very advanced and is targeted at system administrators and advanced programmers.

Another note: The aim of this post is strictly to promote security on Joomla websites and to investigate potential threats. It is not aimed at teaching others how to exploit websites.

After reading a post this morning on the dangers of the “SELECT INTO OUTFILE” MySQL query statement, we initiated an investigation mission so that we can validate whether its threats are real or not.

Now, of course, you might be wondering, what is “SELECT INTO OUTFILE”?

“SELECT INTO OUTFILE” is a MySQL query statement that will write the selected rows into a file. For example, if you want to write the contents of the #__content table into a file, then you can run the following query:

SELECT * INTO OUTFILE '/tmp/content.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' FROM `#__content`;

Theoretically, the above query should dump the content of the #__content table into the content.csv file which is located under the /tmp folder, which is fine, but let’s look at another example:

SELECT "" INTO OUTFILE '/home/[cpanel-user]/public_html/outfile.php'

The above query will (theoretically – you’ll see later in this post why we’re using the word “theoretically” a lot) generate a PHP file called outfile.php under the main directory of the Joomla website. Since the file is typically created with rw-rw-rw permissions, then this means that it can be accessed from the browser by going to: http://www.yourjoomlawebsite.com/outfile.php.

The outfile.php that we created is harmless, but one doesn’t need to have the imaginative mind of Charles Lutwidge Dodgson (aka Lewis Carroll, the man who wrote Alice in Wonderland, who sometimes believed in as many as six impossible things before breakfast), to know what a malicious user can do with that powerful command.

In order to know how much of a threat that query was, we created a test environment and played the Exploit my test website using “SELECT INTO OUTFILE” game, which rules were the following:

  1. We had to upload a PHP file to the root directory of the website using the “SELECT INTO OUTFILE” query.

  2. There are no other rules, which means that we can do anything on the server and change anything to succeed, including using our root privileges to the test server.

Here’s how we started playing the game:

  • We opened the file index.php which is located under the main Joomla directory.

  • We added the following code to its very end:

    $db = JFactory::getDbo();
$sql = 'SELECT "<?php echo(\'Hello World from MySQL\'); ?>" INTO OUTFILE \'/home/[cpanel-user]/public_html/outfile.php\'';
$db->setQuery($sql);
print_r($db->query());

When we tried to load the website, we saw the following error:

1045 – Access denied for user ‘[mysql-database-user]‘@’localhost’ (using password: YES) SQL=SELECT “<?php echo(‘Hello World from MySQL’); ?>” INTO OUTFILE ‘/home/[cpanel-user]/public_html/outfile.php’

A quick investigation revealed that cPanel database users are never given the FILE privilege, which is required to execute “SELECT INTO OUTFILE” queries. In fact, even if you grant all privileges to the database user from within the cPanel interface, the FILE privilege will not be granted. It has to be granted manually by the root user (using the GRANT FILE ON [joomla-database].* TO [mysql-database-user] query)…

Speaking of the root user, it already has the FILE privilege, so why not use it since we are in a consequence-free environment? So, we changed the database user in the configuration.php file to root (we also changed the password to that of the root), and we tried again, and here’s what we saw this time:

1 – Can’t create/write to file ‘/home/[cpanel-user]/public_html/outfile.php’ (Errcode: 13 – Permission denied) SQL=SELECT “<?php echo(‘Hello World from MySQL’); ?>” INTO OUTFILE ‘/home/[cpanel-user]/public_html/outfile.php’

Aha! So, the root user was almost able to write the file, except that the file permissions prevented him from doing so. So, we changed the permissions of the public_html folder from 755 to 777 (775 didn’t work) and we tried again, and, unsurprisingly, it worked this time and it created the file. Wow!

Now, we wanted to verify that we were able to execute the file from the browser, so we pointed our browser to: http://www.testjoomlaenvironment.com/outfile.php, thinking that we will see the Hello World message, but instead, we saw this error:

Forbidden – You don’t have permission to access /outfile.php on this server.

Then we remembered that we had the following security rules in the .htaccess file to only allow the index.php file to be executed:

<Files *.php>
    Order Allow,Deny
    Deny from all
</Files>
<Files index.php>
    Order Allow,Deny
    Allow from all
</Files>

Removing those rules allowed the execution of the PHP file, and displayed the Hello World from MySQL message, which automatically made us win the game. Hooray!

But was it a fair game?

We admit, the game we played was not a fair game. First, there is a major assumption in this game, and it is that there is an exploit somewhere on the Joomla website that will allow us to inject the code, which, realistically, is only the case on a small percentage of Joomla websites. Second, even if there was an exploit, if it wasn’t for the second rule, we would have never been able to win; we would have been stuck trying to make the initial database user execute the “SELECT INTO OUTFILE” command.

But what really made the game impossible to win without “cheating” (e.g. without the second rule) was the fact that the WHM/cPanel environment is a very secure environment. For example, the privileges granted to database users do not include the FILE privilege and the permissions on the folders are correct (compare that to a Plesk environment where the whole thing looks like a mess [Note: We are not hinting here that Plesk, as an environment, is insecure or less secure than WHM but we have yet to see permissions done correctly on a Plesk hosted website]).

As you can see, the “SELECT INTO OUTFILE” query is a dangerous query, but you are more likely to win the lottery than get hacked using this query if you’re on a WHM/cPanel environment. If you’re on a less secure environment, then it is very important to make sure that the database user that you’re using does not have any FILE privilege. If in doubt, please contact us. We are always ready to help, our fees are super affordable, and we know how to ironclad a Joomla website.

No comments yet.

Leave a comment