3

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?

dreftymac
  • 31,404
  • 26
  • 119
  • 182
baao
  • 71,625
  • 17
  • 143
  • 203

1 Answers1

5

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.

spencer7593
  • 106,611
  • 15
  • 112
  • 140
  • Thank you for your answer! That means I don't need to be concerned about the load data operation, but what about the update statement thereafter? If I think a little further, I have a not escaped query string after the load data op in my temp table and then run a update query without this being prepared (and I don't find a way to do so with $stmt5), don't I have the vulnerability within this query then? – baao Jan 09 '15 at 20:59
  • If there's a vulnerability in `$stmt5`, it's in the contents of `${prefix}` variable. The values from the columns processed by the `UPDATE` statement are handled just data. (The only other potential I see there is the corner case of an a vulnerable UPDATE trigger on target table of the UPDATE... the trigger would have to coded to prepare and execute dynamic SQL (is that even allowed in a trigger?).. the UPDATE statement itself isn't vulnerable to "unsafe" values stored in the table. – spencer7593 Jan 09 '15 at 21:42
  • Alright, thanks again! The $prefix is made with a switch / whitelist, to avoid the vulnerability here. – baao Jan 09 '15 at 21:45