user1274113
user1274113

Reputation: 446

PHP & PDO: placeholder results into wrong escaping with LOAD DATA INFILE

I spent the last few hours in a paradox. I use this query to import a CSV file into my database.

$query = $this->pdo->prepare('

    LOAD DATA LOCAL INFILE
        :file
    INTO TABLE
        product_feeds_raw
    FIELDS TERMINATED BY
        :fields_terminated_by
    OPTIONALLY ENCLOSED BY
        :fields_optionally_enclosed_by
    LINES TERMINATED BY
        :lines_terminated_by
    IGNORE 1 LINES
    (
        `aaa`, 
        `bbb`
    )
    SET
        task_id = :task_id
');

$params = [

    'file' => $this->path_to_file,
    'fields_terminated_by' => $fields_terminated_by,
    'fields_optionally_enclosed_by' => $fields_optionally_enclosed_by,
    'lines_terminated_by' => $lines_terminated_by,
    'task_id' => $this->task_id
];

$query->execute($params);

The entire file with its settings (separator, enclosure, line termination etc.) arrives from an OAuth-based API from end-users. I am telling you this to underline that obviously I cannot trust user input hence I must use placeholders.

The problem is that the way PDO escapes such parameters is odd. This is what the user sends via API:

'fields_terminated_by' => ';',
'fields_optionally_enclosed_by' => '"',
'lines_terminated_by' => '\n',

In essence we have ; " and \n which is pretty common for a CSV. I use these variables to feed my placeholders but PDO transforms them into this mess:

LOAD DATA LOCAL INFILE
    '../../myfile.csv'
INTO TABLE
    product_feeds_raw
FIELDS TERMINATED BY
    ';'
OPTIONALLY ENCLOSED BY
    '\"' <-------------- WRONG
LINES TERMINATED BY
    '\\n' <-------------- WRONG
IGNORE 1 LINES

As you can see PDO adds an unnecessary \ in front of my ". Same goes for \n that becomes \\n. As result mysql fails to import the CSV because delimiters are different. The query should have been this:

LOAD DATA LOCAL INFILE
    '../../myfile.csv'
INTO TABLE
    product_feeds_raw
FIELDS TERMINATED BY
    ';'
OPTIONALLY ENCLOSED BY
    '"' <-------------- CORRECT
LINES TERMINATED BY
    '\n' <-------------- CORRECT
IGNORE 1 LINES

I spent hours looking at other scripts but no one seems to use placeholders with LOAD DATA INFILE. Not for delimeters.

I know I could simply get rid of placeholders for delimiters and just use variables inside the query but I don't like this approach. Moreover I should rely on custom-made escapings to sanitize user-input.

Suggestions?

Upvotes: 0

Views: 69

Answers (0)

Related Questions