After update mysql2, LOAD LOCAL file asks to stream

[calebeaires]

When using mysql2 1.6.5 with TypeORM and running the query bellow, everything goes as expected, but after upgrade to 2.02 version, I get this error.

// CODE
this.connection.query(`
LOAD DATA INFILE '/home/data.csv' INTO TABLE studio_data
  FIELDS TERMINATED BY ',';
`)

// ERROR
As a result of LOCAL INFILE command server wants to read /home/data.csv file, 
but as of v2.0 you must provide 
streamFactory option returning ReadStream.

[manavkothari3797]

Yes i am having the same problem

[sidorares]

@calebeaires @manavkothari3797 mysql2 no longer allows full direct access to fs as a result to LOCAL INFILE server response. You can still emulate that using something similar to following code:

const conn = mysql.createConnection({
  //... other options
  infileStreamFactory: path => fs.createReadStream(path)
})

Ideally you should add check that file name is in your white list

[sidorares]

See article about potential attack that is possible when arbitrary file access is enabled: http://www.abclinuxu.cz/blog/jenda/2019/2/exploiting-mysql-arbitrary-file-read-a-honeypot-that-kicks

[scriptsure]

@sidorares man this is a super bummer... we are using sequelize which uses mysql2. Our app relies heavily on config files for connection creation. How would suggest we implement sending this to a connection with sequelize?

var config = require('config').db;
config.infileStreamFactory= path => fs.createReadStream('/home/ubuntu/sites/load');
var sequelize = new Sequelize(config.database, config.username, config.password, config);

Tried to do this but no go.... any ideas?

[scriptsure]

@calebeaires @manavkothari3797 did you ever get this to work?

[calebeaires]

I have no update on this. Still using the version that works

[sidorares]

@scriptsure @calebeaires can you give a bit more detailed example how you are using configs with sequelize? I don't expect reverting LOAD LOCAL behaviour due du security risks but we could work together with sequelize to make it easier to pass down filenames whitelist or something like "virtual fs" to driver settings

[calebeaires]

It is not Sequelize, but TypeORM. Both has the same logic when we are talking about drive connection. Here is an overview of how the code goes on:

import { createConnection } from 'typeorm';

const connection = createConnection({
                    name,
                    type: credentials.client,
                    host: credentials.host,
                    port: Number(credentials.port),
                    username: credentials.user,
                    password: credentials.password,
                    database: credentials.database,
                    logging: true,
                    dateStrings: true,
                    multipleStatements: true,
                    supportBigNumbers: true,
                    bigNumberStrings: false,
                    extra: {
                        // here we can use the FS module
                        // I have tried to use your suggestion here, but I had no success
                        // infileStreamFactory: path => fs.createReadStream(path)
                    },
                })

// then
// more code and:
connection.query(`LOAD DATA LOCAL INFILE
            "${destination}"
            INTO TABLE ${resultTable}
            CHARACTER SET ${charset}
            FIELDS TERMINATED BY ","
            OPTIONALLY ENCLOSED BY '\"'
            LINES TERMINATED BY "\r\n"
            IGNORE 1 LINES`
).then(res=> {
// more code and ....
})

[sidorares]

@calebeaires same advice as in #919 (comment) - try to add flags: 'LOCAL_FILES' to extra: {} object ( might have to be "+LOCAL_FILES", haven't checked myself, see mergeFlags function in the linked comment )

[scriptsure]

@sidorares i did not try +LOCAL_FILES I will give that a shot on the mysql2 connection and bypass sequelize.

[calebeaires]

Finally
ORM: TypeORM - https://typeorm.io/
node-mysql2 VERSION: 2.1.0

Connection

const connection = createConnection({
                    name,
                    type: credentials.client,
                    host: credentials.host,
                    port: Number(credentials.port),
                    username: credentials.user,
                    password: credentials.password,
                    database: credentials.database,
                    logging: true,
                    dateStrings: true,
                    multipleStatements: true,
                    supportBigNumbers: true,
                    bigNumberStrings: false,
                    flags: ['+LOCAL_FILES'] // <======
                });

Query

return connection.query({
            sql: ` SET SESSION sql_mode = '';
                      LOAD DATA LOCAL INFILE "${localFileToImport}"
                      INTO TABLE ${taskData.resultTable}
                      ${characterSet}
                      ${fieldsTerminatedBy}
                      ${optionallyEnclosedBy}
                      ${linesTerminatedBy}
                      ${ignoreFistRow}
                     ${transform.columns}
                     ${transform.replace}`,
           values: [],
           infileStreamFactory: () => fs.createReadStream(localFileToImport)  // <======
        })

I have follow this example:

node-mysql2/test/integration/connection/test-load-infile.js

Lines 26 to 38 in dbf4887

	connection.query(
	{
	sql,
	values: [path, ','],
	infileStreamFactory: () => fs.createReadStream(path)
	},
	(err, _ok) => {
	if (err) {
	throw err;
	}
	ok = _ok;
	}
	);

@scriptsure, maybe my example can help you

[calebeaires]

Sorry to reopen this issue, but my app got another problem since the last change. Here is the problem:

After the changes above, everything goes ok until the same code is executed by an schedule made by the node-cron module (implemented by NestJS).

It seems that the infileStreamFactory: () => fs.createReadStream(path) thing works when it is started from a rest api, but when the same code is schedule the creatReadStream does not work. The problem still persists:

As a result of LOCAL INFILE command server wants to read example.csv file, but as of v2.0 you must provide streamFactory option returning ReadStream

[sidorares]

when the same code is schedule the creatReadStream does not work

can you give a bit more details about what's not working?

[calebeaires]

On NestJS we create a instance. Some tasks is called by a cron job service that I obtain from a reference of a connection instance registered within the Nest application. Here is how I do:

    await app
        .select(CronModule)
        .get(CronService,  { strict: true })
        .uploadFileToMysql();

This method uploadFileToMysql that has the connection with the infileStreamFactory: () => fs.createReadStream(path) works very well when called from an API/Rest controller, but when schedule it does not.

I mean, why the connection can work with DATA LOAD when called by a controller endpoint but it does not work when the same peace of code is called by a schedule module?

[scriptsure]

@sidorares thanks very much for helping me with this issue. Everything works great. Really appreciate it!!!! 👍

[calebeaires]

Same here. Thanks!