0

I'm performing a query in laravel that selects all products and puts the result in a csv file. This all works fine on windows, but on linux, file-permissions are preventing the action.

My query:

$query = 
<<<END
select * from products
INTO OUTFILE '/var/www/html/storage/feeds/products.csv'
fields TERMINATED BY '~' optionally ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
END

DB::statement($query);

My error:

SQLSTATE[HY000]: General error: 1 Can't create/write to file '/var/www/html/storage/feeds/products.csv'

I added the www-data user and group to the feeds folder, the folder is currently empty.

Note:

  1. I've got a copy of this project running on the same server as the db is hosted, here I could just add the permissions of the mysql user to the feeds folder to fix this.
  2. I'm reaching the db trough an ssh tunnel, so I do not have a mysql user on this system to assign access to.

A complete list of users on my system:

root:x:0:0:root:/root:/bin/ash
bin:x:1:1:bin:/bin:/sbin/nologin
daemon:x:2:2:daemon:/sbin:/sbin/nologin
adm:x:3:4:adm:/var/adm:/sbin/nologin
lp:x:4:7:lp:/var/spool/lpd:/sbin/nologin
sync:x:5:0:sync:/sbin:/bin/sync
shutdown:x:6:0:shutdown:/sbin:/sbin/shutdown
halt:x:7:0:halt:/sbin:/sbin/halt
mail:x:8:12:mail:/var/mail:/sbin/nologin
news:x:9:13:news:/usr/lib/news:/sbin/nologin
uucp:x:10:14:uucp:/var/spool/uucppublic:/sbin/nologin
operator:x:11:0:operator:/root:/sbin/nologin
man:x:13:15:man:/usr/man:/sbin/nologin
postmaster:x:14:12:postmaster:/var/mail:/sbin/nologin
cron:x:16:16:cron:/var/spool/cron:/sbin/nologin
ftp:x:21:21::/var/lib/ftp:/sbin/nologin
sshd:x:22:22:sshd:/dev/null:/sbin/nologin
at:x:25:25:at:/var/spool/cron/atjobs:/sbin/nologin
squid:x:31:31:Squid:/var/cache/squid:/sbin/nologin
xfs:x:33:33:X Font Server:/etc/X11/fs:/sbin/nologin
games:x:35:35:games:/usr/games:/sbin/nologin
cyrus:x:85:12::/usr/cyrus:/sbin/nologin
vpopmail:x:89:89::/var/vpopmail:/sbin/nologin
ntp:x:123:123:NTP:/var/empty:/sbin/nologin
smmsp:x:209:209:smmsp:/var/spool/mqueue:/sbin/nologin
guest:x:405:100:guest:/dev/null:/sbin/nologin
nobody:x:65534:65534:nobody:/:/sbin/nologin
www-data:x:82:82:Linux User,,,:/home/www-data:/sbin/nologin
application:x:1000:1000:Linux User,,,:/home/application:/bin/bash
nginx:x:100:101:nginx:/var/lib/nginx:/sbin/nologin

EDIT: I've tried installing mysql and mysql-client on this machine so I have a mysql user and group to assign to the desitnation folder, but that did not work.

EDIT2: As suggested by @LawrenceCherone, I tried running the query by putting the file into a tmp location. This time the query did not crash, but I could not locate the file. After retrying the query, it crashed, saying the file already exists. I started looking on the mysql server and found it. Tho not a solution, it shines some light on what's happening: the file is not created on the server where I need it, but on the mysql server.

Techno
  • 1,668
  • 1
  • 9
  • 19
  • Is this really a PHP problem? – Nico Haase Dec 07 '22 at 13:38
  • alt, save it in /tmp, then move/copy it to where you want it after DB::statement line – Lawrence Cherone Dec 07 '22 at 13:49
  • @NicoHaase Not sure. The query is ran by php, and there is no mysql service(nor client) on the machine, so I'm hesitant to rule it out so far. – Techno Dec 07 '22 at 13:49
  • @LawrenceCherone Thanks for the tip! I will try it directly – Techno Dec 07 '22 at 13:51
  • Have you tried `chmod 0666 /var/www/html/storage/feeds`? Or even `0777`? – Justinas Dec 07 '22 at 13:58
  • Is your PHP and MySql on same server? – Justinas Dec 07 '22 at 13:59
  • @Justinas Yeah I tried opening up the permissions as much as possible(777). Php and mysql are on different servers. – Techno Dec 07 '22 at 14:13
  • @LawrenceCherone I tried your suggestion, but I notice the file is being created on the mysql server, not the php server. Tho not a solution, it gave me a bit more insight on the context that the query is ran in. My original original setup probably tries to put the file in `/var/www/html/storage/feeds` location on the mysql server, which does not exist. – Techno Dec 07 '22 at 14:16
  • plot thickens, yeah 2 separate servers your need to do more to move it to a diff server, like scp over the file – Lawrence Cherone Dec 07 '22 at 14:19
  • @Techno Then that means there is no such path on MySql server. – Justinas Dec 08 '22 at 07:23

1 Answers1

0

Thanks for the commenters for guiding me into the direction of a solution.

Since it became clear to me into outfile is not going to put the file on the client side, but on the server side, and that getting the file from server to client would be the next objective, I decided to use the solution as described here:

First, I need to make sure mysql-client is installed on the php server, then add this code:

$host = config('database.connections.mysql.host');
$port = config('database.connections.mysql.port');
$user = config('database.connections.mysql.username');
$password = config('database.connections.mysql.password');
$database = config('database.connections.mysql.database');
$query = "select * from products";
$pattern = 's/\'/\\\'/;s/\t/\",\"/g;s/^/\"/;s/$/\"/;s/\n//g';
$filepath = '/var/www/html/storage/feeds/products.csv';
$command = "mysql -h $host -P $port -u $user --password=$password $database -e \"$query\" | sed \"$pattern\" > $filepath";
exec($command);

The permissions on the directory need to be of the user who runs php. I did exec('whoami', $output);var_dump($output);die; in php to find this out.

Techno
  • 1,668
  • 1
  • 9
  • 19