MS SQL Database CSV Export – Part 2

By | June 26, 2017
0 Comment

In a previous post, we described a method to query a MS SQL database and export the results to a CSV file using a batch script.

This post will expand on the previous solution to include a way to upload the CSV file to a PHP web server using cURL. Also, the PHP page can generate an email to notify users there is a new file and direct them to site. Note: With a small customization you can provide a direct link to the file via email ($site_url + $uploadname).

  1. First modify the RunSQL-CSV.bat file to enable the curl.exe line
  2. You will need to download curl.exe from curl.haxx.se. Download the Windows Binary (Generic) since chances are we are working on a windows machine. Extract to the same working directory as the batch file for ease of use, but you can place it anywhere as long as you update the script accordingly.
  3. Now create a uploader.php file on your PHP web server and add the code below.

RunSQL-CSV.bat

@echo off
c:
cd c:\utils
"c:\Program Files\Microsoft SQL Server\100\Tools\Binn\SQLCMD.EXE" -S SQLSERVER\DBINST -d dbname -i c:\utils\dbquery.sql -o c:\utils\query_result.csv -W -s"," -w 700 >> runlog.txt
rem LINE BELOW IS NOW ACTIVE
curl.exe -F "file_contents=@query_result.csv" http://phpserver/sql-reports/uploader.php >> runlog.txt
echo %date% %time% >> runlog.txt
del /Q query_result.csv >> runlog.txt

uploader.php

<?php
// This is a standalone public page so have to set timezone
date_default_timezone_set("America/Chicago");

$site_url = 'http' . (isset($_SERVER['HTTPS']) ? 's' : '') . '://' . $_SERVER['HTTP_HOST'].dirname($_SERVER['REQUEST_URI']).'/';

// SET THE DEFAULT SEND EMAIL SETTINGS
$ALERT_EMAIL = 'report-alerts@candisa.org';
$headers = 'From: no-reply.reports@candisa.org' . "\r\n" .
'Reply-To: report-admin@candisa.org' . "\r\n" .
'Bcc: report-admin@candisa.org' . "\r\n" .
'Content-type:text/html;charset=UTF-8\r\n' .
'X-Mailer: PHP/' . phpversion();

$uploaddt = date("YmdHis");
$uploaddir = realpath('./') . '/reports/';
$uploadsuccess = 0;
if (empty($_FILES['file_contents']['name'])) {
} else {
$uploadname = $uploaddt . "_" . basename($_FILES['file_contents']['name']);
$uploadfile = $uploaddir . $uploadname;
echo '<pre>';
if (move_uploaded_file($_FILES['file_contents']['tmp_name'], $uploadfile)) {
$uploadsuccess = 1;
echo "OK";
} else {
//echo "Possible file upload attack!\n";
}
//echo 'Here is some more debugging info:';
//print_r($_FILES);
//echo "\n<hr />\n";
//print_r($_POST);
print "</pre>\n";
}

if ($uploadsuccess == 1) {
/* SEND EMAIL Notification */
$to = $ALERT_EMAIL;
$subject = '[SQL Reports] New Report File Available';
$message = '<h2>Application Custom Report</h2>' .
'<p>A new report file is available, click the link below to access.</p>' .
'<p><a href="'.$site_url.'">'.$site_url.'</a></p>' .
'<hr>&nbsp;<hr></br>' . $uploadname;

mail($to, $subject, $message, $headers);
} else {
/* SEND EMAIL Alert */
$to = 'report-admin@candisa.org';
$subject = '[SQL Reports] Alert - Custom Report Upload Issue';
$message = '<h2>Application Custom Reports</h2>' .
'<p>There was a problem uploading a file to the report site.</p>' .
'<p>Check server at: <a target="_top" href="'.$site_url.'">'.$site_url.'</a></p>';

mail($to, $subject, $message, $headers);
}
?>