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?

Answer 1

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.