Select into outfile php

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

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 "" 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 “” 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 “” 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:

 Order Allow,Deny Deny from all Order Allow,Deny Allow from all 

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!

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.

Leave a comment

• At itoctopus, we’re here to help you with your business! That’s our promise to you. Whether you’re looking for urgent programming help in the middle of the night, or you need help expanding your website, or you need to claim your projects for R&D, then you’re at the right place. We have served a lot of customers since 2001, and we’ll be happy to serve you!

Источник

Создание csv файла средствами mysql

Есть задача — получить данные из таблицы или результат запроса и сохранить в виде CSV файла. Т.е. получить Файл CSV MySQL средствами.

mysql> SELECT* FROM user; +----+-------+----------------------------------+------------+ | id | login | pass | date | +----+-------+----------------------------------+------------+ | 1 | maxx | 8ffc4674e44d1dffafe02b58603a9621 | 2009-01-09 | | 5 | admin | 651e6a3959b7c09ed603071db34b238a | 2009-01-11 | | 6 | tzmk | 651e6a3959b7c09ed603071db34b238a | 2009-01-19 | +----+-------+----------------------------------+------------+ 3 rows in set (0.00 sec)
 
Оператор SELECT позволяет сохранить результат в файл. Скажу сразу, файл не должен существовать, БД его не переписывает, дабы случайно не переписать нужные системные файлы.
 
Вот полный запрос для данных действий:
 
SELECT * INTO OUTFILE 'd:\\test.csv' FIELDS TERMINATED BY ';' ENCLOSED BY '"' ESCAPED BY '\\' LINES STARTING BY '' TERMINATED BY '\r\n' FROM user
 
Конструкция INTO OUTFILE ‘d:\\test.csv’ говорит о том, результат запроса будет помещен в файл по указанному пути, путь абсолютный.
 
Конструкция FIELDS TERMINATED BY ‘;’ ENCLOSED BY ‘»‘ ESCAPED BY ‘\\’ говорит о том, что каждое поля в файле будут разделены знаком ;, также что каждое поле будет обернуто в знак «, если внутри поле будет соот знак, то БД позаботится об экранирования (ESCAPED BY) таких знаков (правила построения CSV файлов).
 
Конструкция LINES STARTING BY » TERMINATED BY ‘\r\n’ говорит о том, что каждая строка начинается с указаного занчения в LINES STARTING BY, а заканчивается строка значением в TERMINATED BY, в данном примере, я ничего не присваиваю началу строки, просто написал чтобы был виден общий синтаксис. Т.к. я юзаю Windows, которому в качестве переноса строки необходимо указать \r\n, поэтому я это значение и поставил в TERMINATED BY, если Вы юзаете *nix подобные системы, то там значение конца строки \n
 
В итоге получится файл с таким содержимым:
 
"1";"maxx";"8ffc4674e44d1dffafe02b58603a9621";"2009-01-09" "5";"admin";"651e6a3959b7c09ed603071db34b238a";"2009-01-11" "6";"tzmk";"651e6a3959b7c09ed603071db34b238a";"2009-01-19"
 
Но этого для полноценного CSV файла мало. Необходимо указать название столбцов, это можно сделать так:
 
SELECT 'id', 'login', 'pass','date' UNION ( SELECT * INTO OUTFILE 'd:\\test1.csv' FIELDS TERMINATED BY ';' ENCLOSED BY '"' ESCAPED BY '\\' LINES STARTING BY '' TERMINATED BY '\r\n' FROM user ORDER BY id ASC )
 
тогда результирующий файл будет такого содержания:
 
"id";"login";"pass";"date" "1";"maxx";"8ffc4674e44d1dffafe02b58603a9621";"2009-01-09" "5";"admin";"651e6a3959b7c09ed603071db34b238a";"2009-01-11" "6";"tzmk";"651e6a3959b7c09ed603071db34b238a";"2009-01-19"
 
Обратите внимание. Если Вам необходимо результирующие данные как-то отсортировать, то сортируемый запрос оберните в скобки, иначе MySQL сначала совместит 2 запроса, а потом сделает общую сортировку, вот цитата из документации:
 
To apply ORDER BY or LIMIT to an individual SELECT, place the clause inside the parentheses that enclose the: (SELECT a FROM t1 WHERE a=10 AND B=1 ORDER BY a LIMIT 10) UNION (SELECT a FROM t2 WHERE a=11 AND B=2 ORDER BY a LIMIT 10);
 
Не забываем поддерживать проект кликая на рекламу гугля))
 
Статья просмотренна 72757 раз, зашло посетителей 24806
 
242 7 комментариев http%3A%2F%2Fblog.nagaychenko.com%2F2010%2F02%2F13%2F%25d1%2581%25d0%25be%25d0%25b7%25d0%25b4%25d0%25b0%25d0%25bd%25d0%25b8%25d0%25b5-%25d1%2584%25d0%25b0%25d0%25b9%25d0%25bb%25d0%25b0-csv-mysql-%25d1%2581%25d1%2580%25d0%25b5%25d0%25b4%25d1%2581%25d1%2582%25d0%25b2%25d0%25b0%25d0%25bc%25d0%25b8%2F %D0%A1%D0%BE%D0%B7%D0%B4%D0%B0%D0%BD%D0%B8%D0%B5+csv+%D1%84%D0%B0%D0%B9%D0%BB%D0%B0+%D1%81%D1%80%D0%B5%D0%B4%D1%81%D1%82%D0%B2%D0%B0%D0%BC%D0%B8+mysql 2010-02-13+17%3A17%3A57 maxnag http%3A%2F%2Fblog.nagaychenko.com%2F%3Fp%3D242 в “Создание csv файла средствами mysql”
 
Never Lex пишет: Честно говоря, если бы делал, то просто генерил бы текстовый файлик с помощью PHP и в цикле закидывал бы туда данные. И кучу проверок и обработок делал бы. А оно однако намного проще всё. Огромное спасибо.
 
maxnag пишет: Рад, что помог. Я и сам не знал, пока не столкнулся с проблемой, зато сейчас один запрос и все данные которые нужны!
 
Иван пишет: Спасибо за статью. Я тоже как-то все по старинке делал через PHP. О таком способе знал, но почему-то не использовал. Надо будет попробывать.
 
maxnag пишет: Спасибо за каммент. 
Единственное, что тут не очень мне нравится, но это все безопасность, что нельзя переписывать файл созданный SELECT * INTO OUTFILE '/tmp/test1.csv' и если нет консоли, то не всегда можно сделать требуемое, а так конечно эта фича меня очень часто выручала.
 
Источник