Using INTO OUTFILE from a Remote Client

An often useful query in SQL is SELECT ... INTO. With SELECT ... INTO OUTFILE, results can be directly placed in a file as comma separated values. Once in the common CSV format, your results can be pulled into other scripts or used as backups (although the latter is not the ideal implementation for backing up). For example:

SELECT * FROM tableA INTO OUTFILE '/path/to/file.txt'

might result in a file.txt file like this:

Cash,Johnny,2-26-1932
Jagger,Michael,7-26-1943
Presley,Elvis,1-8-1935

This is straightforward, and useful for a majority of implementations. There are a few additional options, depending on your SQL implementation. For example, check out MySQL’s SELECT … INTO page

A major problem with SELECT ... INTO OUTFILE comes when your application exists on one server, while your database server is running on another. Many web hosting/application scenarios will run into this, as separating your database and application/web layers is a vital to improving performance. Since the outfile path is local to the database machine, and not the application machine, this SQL function becomes quite useless. Even if your database user had access to the path you supplied, it’s unlikely your application user has access to that path on a remote machine. Granting access in either case is unnecessary and sloppy security protocol. Instead, add a few lines to your code and program the identical functionality.

TL;DR – you can’t use SELECT ... INTO OUTFILE remotely. Sorry. Below is a PHP solution – look in comments for other language specific solutions.

Here’s a example in PHP that will accomplish the same thing:

$result = mysql_query("SELECT * FROM tableA");
$fh = fopen("/path/to/file.txt", "w+");
while ($row = mysql_fetch_row($result)) {
   fputs($fh, implode(',', $row)."\n");
}
fclose ($fh);

Be wary not to use PHP’s fputcsv(), as it will enclose the entire CSV row in quotes, and IS NOT identical to the output generated by SELECT ... INTO OUTFILE.

Post alternatives in your language of choice in the comments.