Preparing a SQL statement that contains paired single and double quotes in PHP-CodePudding

Here goes. I have a 200-line MySQL query that contains six different Excel formulas in one of the columns, like this:

SELECT '%%%=""Greg''s #""&INDIRECT(""G""&ROW()&""#"")' AS 'Location'

This code snippet will run correctly in MySQL, and because of the doubled-up punctuation, can be exported to a CSV without causing havoc in the CSV. However, now I have to put this query into a prepared statement in PHP as part of the process of automating my company's (you guessed it, Excel-based) reporting. How do I harden the prepared statement against all the doubled punctuation?

I've tried escaping with a \ before each quotation mark, but this somehow causes the MySQL query to return only the Excel formulas, and not the database data that has to go alongside it. Same with heredoc. PDO::quote() didn't even get me that far. I half suspect there's a way to use fputcsv to get around the problem, but I assume I have to prepare the statement before bringing fputcsv into the fray. (Not saying I've done any of these the correct way; I'm trying whatever Google says to try at this point.) So far, no queries have returned in PHP what they do in SQL.

The various data I care about exist in all three formats (and more besides...), and have to be collected as I go downstream, in this case from PHP to MySQL to an emailed CSV to a VBA-automated Excel report. My company thinks of data storage solutions like Pokémon. Gotta subscribe to them all. So this is a problem I can't just dodge.

Thank you, kind people. I'm doing my utmost to get our reporting under control, but this challenge has so far proved un-Googleable (probably because Google doesn't understand "double double quotes" or "paired double quotes").

CodePudding user response：

HEREDOC does the trick. The following code does not get mangled:

$csv_fields = array("Location","Location ID","Region","Area","Area #","Client","Store #","Signature","AM","AR","AA");

$TechExport = <<<Your_HEREDOC_Tag
    SELECT
        a.location AS 'Location'
        , '%%%=FILTER(''Store Admin''!A:A,(''Store Admin''!A:A<>''Store Admin''!A1)*(''Store Admin''!A:A<>""))' AS 'Location ID'
        , '%%%=INDEX(''Store Admin''!B:B,XMATCH(INDIRECT("B"&ROW()&"#"),''Store Admin''!G:G))' AS 'Region'
        , '%%%=INDEX(''Store Admin''!C:C,XMATCH(INDIRECT("B"&ROW()&"#"),''Store Admin''!G:G))' AS 'Area'
        , '%%%=INDEX(''Store Admin''!D:D,XMATCH(INDIRECT("B"&ROW()&"#"),''Store Admin''!G:G))' AS 'Area #'
        , '%%%=INDEX(''Store Admin''!E:E,XMATCH(INDIRECT("B"&ROW()&"#"),''Store Admin''!G:G))' AS 'Client'
        , '%%%=INDEX(''Store Admin''!F:F,XMATCH(INDIRECT("B"&ROW()&"#"),''Store Admin''!G:G))' AS 'Store #'
        , '%%%=FILTER(UNIQUE(FILTER(SORTBY(FILTER(''Requests''!I:M,(ISNUMBER(''Requests''!F:F))),FILTER(''Requests''!F:F,(ISNUMBER(''Requests''!F:F)))),{1,0,0,0,1})),{0,1})' AS 'Signature'
        , '%%%=INDEX(''Store Admin''!P:P,XMATCH(INDIRECT("B"&ROW()&"#"),''Store Admin''!G:G))' AS 'AM'
        , '%%%=SUMIFS(''Requests''!H:H,''Requests''!M:M,INDIRECT("B"&ROW()&"#"),''Requests''!I:I,INDIRECT("S"&ROW()&"#"))' AS 'AR'
        , '%%%=SUMIFS(''Assembly''!F:F,''Assembly''!A:A,INDIRECT("B"&ROW()&"#"),''Assembly''!E:E,INDIRECT("S"&ROW()&"#"))' AS 'AA'
    FROM 
        `full-database-name`.location a;
Your_HEREDOC_Tag;

$preparedSQL = $db->prepare($SQL);
$preparedSQL->execute();
$results = $preparedSQL->fetchAll(PDO::FETCH_ASSOC);

$f = fopen('./test/Results.csv', 'w ');

fputcsv($f,$csv_fields);

foreach ($results as $result)
{
    fputcsv($f,$result);
}

fclose($f);

I needed to do three things to convert my SQL code to a prepared statement: First, put the HEREDOC tag around the entire SQL code block. Second, fully qualify the SQL table references. Third, turn the paired double quotes into normal double quotes (""this"" into "this"). Single quotes stay paired (''like this''). I assume this is the fputcsv function preserving the double quotes during the CSV creation, where in MySQL I had to preserve them by doubling them up, but honestly this is just a guess.