Errcode 13 , SELECT INTO OUTFILE issue

Question

I'm trying to understand the reason why I keep experiencing problems while using INTO OUTFILE command.

I always get this erroro:

ERROR 1 (HY000): Can't create/write to file '/var/www/p1.txt' (Errcode: 13)


SELECT password FROM mysql.user WHERE user='root' INTO OUTFILE '/var/www/p1.txt';

Useful details:

• web application : DVWA (localhost) (for study purposes)

• Server: Apache/2.2.14 (Ubuntu) - PHP/5.3.2

• MySQL version 5.1.63

• Operating system Linux Backtrack 5r3.

I'm running the command as root. Also, I can freely create folders or files in /var/www/

Errcode 13 I know it means permission denied, but what should I do in order to fix the problem?

Any help will be highly appreciated.

Cageling

Does p1.txt already exist (it shouldn't)? Does the user under which the mysqld process have permission to write to the /var/www directory? – Miliary

Answer 1

chown /var/www to the user trying to write the file, or chmod 777 /var/www

this is probably not a secure way of doing it, you might like to consider putting the file elsewhere

Velamen

It works fine ...i know that for security reason I shouldn't do that..but I only wanted to test a MySQL data exfiltration example..thanks a lot.. – Cageling

This did not work for me, nor did changing AppArmor nor did running as root. Only creating /tmp/mysql-dump/ then chown -R 777 /tmp/mysql-dump/ and running mysqldump into that folder. – Elka

people downvoting this answer should note error 13 is "permission denied" and error 2 is "No such file or directory". I can't answer many different errors with one answer. PLBKAC as they say – Velamen

Answer 2

Even if you're logged in as root into MySQL, the file write will be performed as the user running the actual MySQL daemon.

In other words, you should check which user runs mysqld, and give write permission to the directory for that user.

Monocarpic

Very counter-intuitive, but this is so much better an answer than just blindly setting 777 everywhere. Thanks! – Violate

I stumbled across this and thought the user I connected (the linux user that ran mysql -uroot -p) as was the user running mysql. This is incorrect, in the my.cnf or wherever the servers configuration is controlled there is a user that is set to run. Useful article dev.mysql.com/doc/refman/8.0/en/changing-mysql-user.html grep 'user=' /etc/my.cnf Assuming mysql is your user the answer is chown mysql:mysql path_you_want_to_write_to – Surbase

Answer 3

you must alter the permissions for user mysqld. start by running the following command sudo aa-status to check your user status and authorized directories. if you want to change permissions, edit /etc/apparmor.d/usr.sbin.mysqld and insert the directories you want.

you must then restart apparmor sudo /etc/init.d/apparmor restart

Adame

This is legit. Thanks for posting! For the impatient, and/or those who want to do a quick command, mysqldump -T for instance, service apparmor teardown will turn it off. When done, you can service apparmor start and restore life back to normal. – Research

You save me a age! I am stuck here two days already. I was thinking of why mysqld don't have any permission to create a file instead of '/tmp' path and no matter how I grant user dir permission always failed. – Verbiage

Answer 4

Although this post is quite old, in 2018 this problem is still there. I spent a couple of hours banging my head in this maze.

Server version: 5.7.24 MySQL Community Server (GPL) running on Ubuntu 14.04

1. To allow MySql to SELECT INTO OUTFILE requires to set MySQL's secure-file-priv option in your configuration. Append the following 2 lines to /etc/mysql/mysql.conf:

   [mysqld]
   # allow INTO OUTFILE file and LOAD DATA INFILE to this directory
   secure_file_priv=/usr/share/mysql-files                
   

2. /usr/share/mysql-files is the directory where my files will be stored. I created it doing:

   sudo su
   cd /usr/share
   mkdir mysql-files
   chown mysql:mysql mysql-files
   chmod a+rw mysql-files
   

Change /usr/share/mysql-files for whatever you prefer, but avoid to use the /tmp directory!

Why? Because, at next time you'll be rebooting, the /tmp directory is happily erased including your precious mysql-files sub-directory. The mysql service then chokes and it won't start, leading to wierd errors with cryptics messages.

3. restart mysql and check:

   sudo su
   service mysql restart
   mysql
   mysql> SHOW VARIABLES LIKE "%secure%";
   +--------------------------+-------------------------+
   | Variable_name            | Value                   |
   +--------------------------+-------------------------+
   | require_secure_transport | OFF                     |
   | secure_auth              | ON                      |
   | secure_file_priv         | /usr/share/mysql-files/ |
   +--------------------------+-------------------------+
   3 rows in set (0.07 sec)
   mysql> quit
   Bye
   

4. You are not done, yet!

There is a troll by the name of apparmor who will ruines your project. Edit the file /etc/apparmor/local/usr/sbin/mysqld and append the following 2 lines -- don't forget the ending commas:

    /usr/share/mysql-files rw,
    /usr/share/mysql-files/** rw,

save it, and reparse:

sudo apparmor_parser -r /etc/apparmor.d/usr.sbin.mysqld

That should make it.

Gallon

Answer 5

On centos the selinux thing is playing not-nice.

$ getenforce
Enforcing

$ setenforce 0
Permissive

Now this is crude, but worked for me (until reboot, then it switches back on). If this temporary measure works, you need to google for how to configure selinux properly.

Prelusive

Answer 6

Check the user of mysqld with,

ps -aef | grep mysql
mysql     9355  9102  0 Aug24 ?        21:53:25 /usr/libexec/mysqld 

Check wiich group mysql belong to with,

groups mysql
mysql : mysql www

Then write the file under path which belong to mysql or have write permission for group www and mysql. For example, test under has write permission to group www.

ll /data/
drwxrwxr-x 2 www    www 4096 Dec  9 19:31 test

Then execute mysql mysql -u root -p -e 'use sc_test; select file_path from sc_files INTO OUTFILE "/data/test/paths.txt";'

Bevis

Answer 7

I was fighting with this enigmatic error for hours, too, tried everything I could find, to no avail, and finally remembered I already had this same problem years before without being able to find a solution back then no matter how long I searched for and tried all these smart counsels.

secure_file_priv was new to me but I didn't try this because I didn't want to rebuild my docker container just to make this work.

Solution

Looking at my docker-compose file I found the solution to this problem: I didn't have a mapping to the target directory, so for the mysql container this directory wasn't existent.

Workaround

Back then I developed a workaround for my cron jobs:

1. first dump to tmp (to which my container has a mapping)
2. mv to where it should be in the first place

Well, it works fine, so why bother.

Trotman