mysql load_file from client

Question

I want to insert a HTML file, which has lots of SQL meta characters (e.g. single quotes) that I do not want to escape manually, into a MySQL table.

I though I could make the mysql client load file contents into a variable and simply insert that variable, something along these lines:

set @result_text = load_file('/tmp/results.html');
INSERT INTO election_results (election_id, result) VALUES (17, @result_text);

However, the documentation of load_file says, that it loads the file on the server, and not on the client. And that doesn't work for me.

Hence my question: How to make the mysql client load a file into a variable?

NB: These do not apply here, as they mention that load_file works on the server only. That I know.

eggyal · Accepted Answer · 2013-06-10 09:53:35Z

1

Use MySQL's LOAD DATA command, with the LOCAL keyword:

LOAD DATA LOCAL INFILE '/tmp/results.html'
    INTO TABLE election_results
    FIELDS
        TERMINATED BY ''
        ESCAPED BY ''
    LINES
        TERMINATED BY ''
    (result)
    SET election_id = 17

However, take note of the Security Issues with LOAD DATA LOCAL.

answered Jun 10, 2013 at 9:53

eggyal

127k18 gold badges220 silver badges244 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Frederick Nord Over a year ago

That gets me `ERROR 1084 (42000): You can't use fixed rowlength with BLOBs; please use 'fields terminated by'

neu-rah · Accepted Answer · 2013-08-20 03:56:45Z

0

i've passed over that Error 1084 by using:

LOAD DATA LOCAL INFILE '/tmp/results.html'
INTO TABLE election_results
FIELDS
    TERMINATED BY '\Z'
    ESCAPED BY ''
LINES
    TERMINATED BY ''
(result)
SET election_id = 17

please notice the '\Z' as field terminator.

answered Aug 20, 2013 at 3:56

neu-rah

1,69321 silver badges34 bronze badges

Comments

user151851 · Accepted Answer · 2013-12-26 19:39:15Z

0

Not exactly load_file but works fine in my case.

Consider this one-liner (note, I'm on Ubuntu):

printf "$(cat update_xml.sql)" "$(cat my.xml | sed s/"'"/"\\\'"/g)" | mysql -h myRemoteHost -u me -p***

In update_xml.sql there is:

UPDATE
   myTable
SET
   myXmlColumn = '%s'
WHERE
   ...

edited Dec 26, 2013 at 19:39

answered Dec 26, 2013 at 19:33

user151851

1981 gold badge2 silver badges8 bronze badges

Collectives™ on Stack Overflow

mysql load_file from client

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related