"1064 - You have an error in your SQL syntax" when LOAD DATA INFILE with fields specification

Question

I've spend several hours reading probably over 10 different Q&As on this website and reviewing my following query many times and still couldn't find what the problem is. This is my query definition line in my PHP codes:

$q="LOAD DATA INFILE '$filename' 
    IGNORE INTO TABLE `temp_data_1` 
    FIELDS TERMINATED BY ',' ENCLOSED BY '\"' ESCAPED BY '\\' 
    LINES TERMINATED BY '\r\n' IGNORE 1 
    LINES (`field_1`, `field_2`, `field_3`, `field_4`, `field_5`, 
            `field_6`, `field_8`, `field_9`) 
        SET `field_7` = SUBSTRING_INDEX(`field_6`, '.',-1)";

$filename is set after uploading the CSV file. Pretty similar code works in phpMyAdmin but I cannot make it work in my PHP codes. I keep getting this error:

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' IGNORE 1 LINES (field_1, field_2, field_3, field_4, field_5, field_6, ' at line 2

I suspect it's related to LINES TERMINATED BY '\r\n' but I don't see any problem with it, as that exact same line termination definition is working in some other part of the code.

Thanks for the help.

Answer 1

Simple test, just write a little script and run it in the CLI to see what gets output from your string.

$filename = 'dsafsdf';

$q="LOAD DATA INFILE '$filename' 
    IGNORE INTO TABLE `temp_data_1` 
    FIELDS TERMINATED BY ',' ENCLOSED BY '\\\"' ESCAPED BY '\\\' 
    LINES TERMINATED BY '\\r\\n' IGNORE 1 
    LINES (`field_1`, `field_2`, `field_3`, `field_4`, `field_5`, 
            `field_6`, `field_8`, `field_9`) 
        SET `field_7` = SUBSTRING_INDEX(`field_6`, '.',-1)";
echo $q;

The output is

LOAD DATA INFILE 'dsafsdf' 
    IGNORE INTO TABLE `temp_data_1` 
    FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\' 
    LINES TERMINATED BY '
' IGNORE 1 
    LINES (`field_1`, `field_2`, `field_3`, `field_4`, `field_5`, 
            `field_6`, `field_8`, `field_9`) 
        SET `field_7` = SUBSTRING_INDEX(`field_6`, '.',-1)

Note the missing \ which have been interpreted as excape chars because you are in a double quoted string. So all you need to do now is add the relevant number of escapes to get the correct output like this

$q="LOAD DATA INFILE '$filename' 
    IGNORE INTO TABLE `temp_data_1` 
    FIELDS TERMINATED BY ',' ENCLOSED BY '\\\"' ESCAPED BY '\\\' 
    LINES TERMINATED BY '\\r\\n' IGNORE 1 
    LINES (`field_1`, `field_2`, `field_3`, `field_4`, `field_5`, 
            `field_6`, `field_8`, `field_9`) 
        SET `field_7` = SUBSTRING_INDEX(`field_6`, '.',-1)";
echo $q;

Generates what was expected

LOAD DATA INFILE 'dsafsdf' 
    IGNORE INTO TABLE `temp_data_1` 
    FIELDS TERMINATED BY ',' ENCLOSED BY '\"' ESCAPED BY '\\' 
    LINES TERMINATED BY '\r\n' IGNORE 1 
    LINES (`field_1`, `field_2`, `field_3`, `field_4`, `field_5`, 
            `field_6`, `field_8`, `field_9`) 
        SET `field_7` = SUBSTRING_INDEX(`field_6`, '.',-1)