2

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.

Community
  • 1
  • 1
Frederick Nord
  • 1,246
  • 1
  • 14
  • 31

3 Answers3

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.

eggyal
  • 122,705
  • 18
  • 212
  • 237
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.

neu-rah
  • 1,662
  • 19
  • 32
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
   ...
user151851
  • 198
  • 1
  • 2
  • 8