Reputation: 446
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