0

I am attempting a LOAD DATA INFILE and getting the above error.

LOAD DATA INFILE '$file'
REPLACE INTO TABLE $custom_parts
FIELDS TERMINATED BY ',' ESCAPED BY '\\\\'
LINES TERMINATED BY '\\r\\n'
IGNORE 1 LINES
 (`partsno`, `mfg`, `cond`, `price`, `is_deleted`, @date_added)
   SET `date_added` = STR_TO_DATE(@date_added, '%c/%e/%Y'),
  `prtky` = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(partsno, ' ', '' ) 
   , '\\\\', '' ) , '/', '' ) , '_', '' ) , '.', '' ) , '-', '' )

The columns of the file are so

Part Number,MFR,Condition,price,is_deleted,date_added

Whenever I run the PHP to load this, I get the error. I am entrigued as why this is occuring. I believe that it is an issue with the user variable not being assigned and I am just looking for verification.

Johan
  • 74,508
  • 24
  • 191
  • 319
Vern Burton
  • 3,215
  • 1
  • 18
  • 31

2 Answers2

0

You cannot use variables for dynamic table names.
You can only do this using prepared statements,

However
You cannot use load data infile inside a stored procedure and I'm not sure you can use it in a prepared statement either.

If you use MySQL from a higher level program (php, pascal, whatever) you can

  1. resolve the variable before constructing the query;
  2. Check the columnname against a whitelist to prevent SQL-injection
  3. Feed MySQL the expanded statement.

See this question for sample code: How to prevent SQL injection with dynamic tablenames?

Also, if you are using dynamic tablenames, escape them using ` backticks. This prevents MySQL from bombing if the tablenames contains funny chars or is a reserved word.

Community
  • 1
  • 1
Johan
  • 74,508
  • 24
  • 191
  • 319
  • Table isn't dynamic. This is a prepared statement in a PHP file. The statement works just fine if I claim the column away from the user variable, but I need the variable for the str_to_date portion, and per mysql is is possible http://dev.mysql.com/doc/refman/5.1/en/load-data.html – Vern Burton Sep 13 '11 at 16:12
  • I answered my own question. ---- User variables may be used in most contexts where expressions are permitted. This does not currently include contexts that explicitly require a literal value, such as in the LIMIT clause of a SELECT statement, or the IGNORE N LINES clause of a LOAD DATA statement. – Vern Burton Sep 15 '11 at 13:15
  • @Johan _You cannot use variables for dynamic table names. You can only do this using prepared statements,_ -- I'm not sure I agree with this. Using prepared statements, you cannot use a placeholder as a tablename nor bind a value to it. You can use a variable to represent a dynamic tablename. Is this what you mean to say? – mickmackusa Apr 27 '18 at 04:18
0

I answered my own question. ---- User variables may be used in most contexts where expressions are permitted. This does not currently include contexts that explicitly require a literal value, such as in the LIMIT clause of a SELECT statement, or the IGNORE N LINES clause of a LOAD DATA statement.

Vern Burton
  • 3,215
  • 1
  • 18
  • 31