how to secure load data local infile update query against sql injection

Question

In my app, I need to offer the possibility of uploading csv and excel files which are then used to update the database. The files contain +/- a few million rows, so I need to use load local data infile:

$stmt1 = $dbh->prepare("CREATE TEMPORARY TABLE ${prefix}tempskuEAN LIKE ${prefix}skuEAN");

$stmt4 = $dbh->prepare("LOAD DATA LOCAL INFILE '/ama/$aa[0]/CustomerUpload/$a.csv' INTO TABLE ${prefix}tempskuEAN FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '\"' (seller_sku, EAN, fallback)");

$stmt5 = $dbh->prepare("UPDATE ${prefix}skuEAN a LEFT JOIN ${prefix}tempskuEAN b ON a.seller_sku = b.seller_sku SET a.EAN = b.EAN, a.fallback = b.fallback WHERE a.seller_sku = b.seller_sku");

$stmt6 = $dbh->prepare("DROP TEMPORARY TABLE ${prefix}tempskuEAN");

The variables in $stmt4 are set by my program, so they won't be a problem, but I'm quite concerned about the security of the updated/inserted values. Is there any way to escape the values together with load data local infile without performance loss?

Answer 1

If I understand what you're asking... there's no need to "escape" the values in the file read in by the LOAD DATA statement. The values are interpreted as data, not as part of the SQL text.

That is, if a value read in from the file is something like NOW(), that's going to be read as a string. If that gets stored to a VARCHAR column, that string value will be stored in the column; the contents of that string won't be interpreted to mean "call a SQL function".

The LOAD DATA is akin to prepared statement with bind placeholders, e.g.:

INSERT INTO mytable (a,b,c) VALUES (?,?,?), (?,?,?), (?,?,?)

The values supplied for the placeholders are handled as just data, they aren't part of the SQL text.

NOTE: This doesn't guarantee that the value stored in the table is "safe". The LOAD DATA statement is safe. It's entirely possible for that value to be extracted by some other statement, and then some other statement not properly handling that value as potentially unsafe, and causing havoc.

---

EDIT

My answer said that the LOAD DATA statement wasn't vulnerable to SQL Injection, as far as values read from the file.

Just to clarify, this part of the code:

$stmt4 = $dbh->prepare("LOAD DATA LOCAL INFILE '/ama/$aa[0]/CustomerUpload/$a.csv' INTO

is (potentially) vulnerable to injection, from $aa and $a being included in SQL text.