The SET option in mysqlimport / LOAD DATA INFILE : transforming values before insert

 

Let’s say you want to load a text file into a mysql table, but some mandatory columns are missing in the file or you have to modify some data first.
Well, the LOAD DATA INFILE statement has the handful SET clause for transforming values before assigning them to columns.
Take this example:

LOAD DATA INFILE 'my_users.txt'
INTO TABLE MY_TABLE
(@id, @ignore_col_1, @ignore_col_2, user_name, user_group)
SET loaded_from = 'manual import', id = (@id + 1000)

which loads a file like

3,blabla,dummy,Daniel,admin
4,hahaha,hehehe,John,newbie

The 1st column of the file becomes the @id variable (later transformed by SET), the 2nd and 3rd columns are ignored (they become variables not used by SET), the “user_name” and “user_group” columns are loaded directly. An extra column “loaded_from” (that was not in the file) is also loaded by SET.

Here’s the result:

id user_name user_group loaded_from
1003 Daniel admin manual import
1004 John newbie manual import

 

So.. it would be nice to have this SET feature in mysqlimport too.
But it’s still not officially supported in the current versions (5.x)! (I’ve filled a BUG, let’s hope it gets validated).

When you call mysqlimport, it ends up creating a LOAD DATA INFILE statement, but there’s no trace of the SET clause. Here’s the src code:

#client/mysqlimport.c
...
end= add_load_option(end, escaped, " ESCAPED BY");
end= add_load_option(end, lines_terminated, " LINES TERMINATED BY");
if (opt_ignore_lines >= 0) end= strmov(longlong10_to_str(opt_ignore_lines,
strmov(end, " IGNORE "),10), " LINES");
// COLUMN definition is the last thing before executing the statement:
if (opt_columns) end= strmov(strmov(strmov(end, " ("), opt_columns),")");
*end= '\0';
if (mysql_query(mysql, sql_statement)){
...

 

Ugly solution: SQL-injecting the SET clause in the --columns option! LOL
You must leave unbalanced parenthesis because the program concats "(" + "--columns" + ")"

#!/bin/sh

filename=my_users.txt

cols="@id, @ignore_col_1, @ignore_col_2, user_name, user_group) \
SET loaded_from = 'manual import', id = (@id + 1000"
# ATTENTION, you must leave an unbalanced parenthesis!!!!

mysqlimport5 --columns="$cols" "$filename"

mysqlimport will then run:


LOAD DATA INFILE 'my_users.txt'
...
INTO TABLE MY_USERS
(
@id, @ignore_col_1, @ignore_col_2, user_name, user_group) SET loaded_from = 'manual import', id = (@id + 1000
) 

If you have found a better workaround, please share! 🙂

Leave a Reply