Best way to error catch LOAD DATA LOCAL INFILE?

Question

I'm a MySQL newbie finishing up a chunk of code central to my webapp. The code imports a CSV file (locally), that is generated and cleaned up by FileMaker and leaves it be.

Given this is will go to production at some point soon (and probably be a database under 500mb ), I would love to know if there is any better error checking /catching that I could do to possibly prevent issues down the road or be alerted to my server setup. I've read things about temp logs, etc. and my MySQL administration isn't up to snuff yet.

The very basic code is:

$m = mysql_connect('localhost', 'mytable', 'mypassword');
$db = 'mydb';
mysql_select_db($db) or die("Import Error - Couldn't select database: " . mysql_error());

$sql = 'load data local infile "inventory.csv"
        into table ibl_account_details_temp fields terminated by ","
        optionally enclosed by "\""
        lines terminated by "\r"
        (store_id, SKU, account, item_number, brand, description, size, category, price, qty, fees)
        ';

echo mysql_query($sql) or die(myqsl_error());

PS EDIT: I would also love to know if this method of import is open to SQL injection?

I can't see how there would be any risk of first order SQL injection by executing `LOAD DATA INFILE`, since MySQL isn't executing the contents of the CSV file, it's just parsing it as CSV. Of course that says nothing about the risk of a second order attack, but as long as every query that takes input (including input from a query result) uses parameterization you should be safe. For that reason (amongst others) you should, use mysqli or PDO to access mysql instead of the mysql_* functions. — John Carter, May 18 '12 at 04:27
I was using a simple insert statement before and fgetcsv was going out of memory on my 18mb file. I couldn't solve it and am maxed by the host at 128mb of ram and need to get/keep this working. That said, every other place I'm using PDO ;-) — SWL, May 18 '12 at 11:52
Oh sure, I've no objection to using `LOAD DATA INFILE` to import CSV, that's what it's for. — John Carter, May 18 '12 at 21:48

score 4 · Accepted Answer · answered May 18 '12 at 02:42

4

Unfortunately, error handling with load data infile is very poor. I use it almost every day, and it's become simple routine to import into a temporary table of some kind, and use a combination of PHP and MySQL to validate what was imported. One can argue that this is extra work, but it does have the advantage of giving me full control of what an "error" is. Simply put, I use it to get the raw data in place as efficiently as possible, then build my rules of error checking and validation in a php script.

answered May 18 '12 at 02:42

GDP

8,109
6
45
82

Thanks for the quick response. My error checking is in FileMaker and it is pretty strict so I'm feeling safe there. Is there any possibility of a SQL injection this route btw? – SWL May 18 '12 at 02:51
I don't know where your data is coming from, so can't say for sure, but that's normally an issue with user input, so presumably not. It then comes back to the scrubbing you do in FileMaker as to how safe your finished product is. – GDP May 18 '12 at 03:01

Best way to error catch LOAD DATA LOCAL INFILE?

1 Answers1

Linked