Using MySQL INTO OUTFILE not writing to tmp

I’ve recently moved a project to a live VM instance, it worked perfectly during development and testing on my macOS box using XAMPP. Essentially the web app has 3 export options: XML, PDF and CSV. XML works fine and is created using a SELECT statement then looping through the results and pushing that into an XML file.

However for the CSV and PDF export (PDF uses TCPDF) I used:

CSV

INTO OUTFILE '/tmp/$department_name+feedback+$timestamp.csv' FIELDS TERMINATED BY ',' LINES TERMINATED BY 'n'";

PDF

INTO OUTFILE '/tmp/$department_name+feedback+$timestamp.txt' FIELDS TERMINATED BY ';' LINES TERMINATED BY 'n'";

However the file never gets written to /tmp. I assumed it was a permission issue however the directory is owned by root and has all the necessary permissions as expected.

The working XML code is as follows:

$fileLocation = '/tmp/' . $location_name . '+feedback+' . $timestamp . '+.xml';

$sql = "SELECT CONCAT (e.name,' ', e.surname) AS Fullname, f.score AS Score, d.department_name AS Department, f.feedback AS Feedback
            FROM employee AS e
            JOIN feedback AS f
            ON e.qr_id = f.qr_id
            JOIN departments as d
            ON e.department_id = d.department_id
            JOIN location AS l
            ON e.location_id = l.location_id
            AND e.location_id = '$location'
            AND e.company_id = '$company_id'
            AND f.`report_Time` BETWEEN SUBDATE(CURDATE(),
                                INTERVAL 1 MONTH) AND NOW()
                                ORDER BY f.score DESC";


$xml = new XMLWriter();

$xml->openURI($fileLocation);
$xml->startDocument();
$xml->setIndent(true);

$xml->startElement('feedback');

foreach ($mysqli->query($sql) as $row){
    $xml->startElement("Fullname");
    $xml->writeRaw($row['Fullname']);
    $xml->startElement("Score");
    $xml->writeRaw($row['Score']);
    $xml->startElement("Feedback");
    $xml->writeRaw($row['Feedback']);
    $xml->startElement("Department");
    $xml->writeRaw($row['Department']);

    $xml->endElement();
}

$xml->endElement();

header('Content-type: text/xml');
$xml->flush();


$file_url = $fileLocation;
header('Content-Type: application/xml');
header("Content-Transfer-Encoding: Binary");
header("Content-disposition: attachment; filename="" . basename($file_url) . """);
readfile($file_url);

So I’m unsure as to why the XML works when it’s writing to the /tmp directory and the PDF & CSV does not, this is running on a Google Cloud Platform VM instance. As I stated before CSV & PDF exports work fine on my macOS machine.

Answer

Show the result from this:

SHOW VARIABLES LIKE 'secure_file_priv';

if the Result not NULL you can only write in this directory.

Advertisement

Answer