Errors with "LOAD DATA LOCAL INFILE" I am using \Drupal::database()-&gt;query() with the following SQL statement.  

LOAD DATA LOCAL INFILE '{my file name}' 
INTO TABLE {my table name} 
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"' 
LINES TERMINATED BY '\r\n' 
IGNORE 1 LINES 
({my column names})


I have also tried with db_query() with same result.  I can confirm the SQL statement works as expected in phpMyAdmin, so I don't think it's a problem with the SQL statement.

First error


  Warning: PDOStatement::execute(): LOAD DATA LOCAL INFILE forbidden in Drupal\Core\Database\Statement->execute() (line 59 of {my directoreis}\core\lib\Drupal\Core\Database\Statement.php)


Line 59 is $return = parent::execute($args); which I believe is referring to PDOStatement.

Second error


  Drupal\Core\Database\DatabaseExceptionWrapper: SQLSTATE[HY000]: General error: 2014 Cannot execute queries while other unbuffered queries are active. Consider using PDOStatement::fetchAll(). Alternatively, if your code is only ever going to run against mysql, you may enable query buffering by setting the PDO::MYSQL_ATTR_USE_BUFFERED_QUERY attribute.


Not sure if this can be set in a call to \Drupal::database()-&gt;query();

My research on this has shown me it should be possible to do in Drupal.

https://www.drupal.org/node/1034584

https://www.drupal.org/node/2110935

https://evolvingweb.ca/blog/import-hundreds-of-thousands-of-records-15-seconds-custom-entities

Database Abstraction Layer and &#39;LOAD DATA INFILE&#39; Statements

https://stackoverflow.com/questions/3994133/how-to-load-data-to-db-from-csv-using-load-data-infile-in-drupal

So, where am I going wrong and how can I fix it?

UPDATE

Based on some of the pages I linked to above, I was able to come up with a working solution.

// set up an extra db connection
$connection = \Drupal::database()-&gt;getConnectionOptions();
$connection['pdo'][\PDO::MYSQL_ATTR_LOCAL_INFILE] = 1;
\Drupal\Core\Database\Database::addConnectionInfo('extra', 'default', $connection);
\Drupal\Core\Database\Database::setActiveConnection('extra');

// instantiate the extra db connection and query against it
$db = \Drupal\Core\Database\Database::getConnection();
$db-&gt;query($sql_statement)-&gt;execute();

// set connection back to default;
\Drupal\Core\Database\Database::setActiveConnection();


But for some reason I don't think this would be the "Drupal" way.  And now, I am not sure what my question is.  Maybe, is this the appropriate Drupal way or is there a more elegant built in solution I am unaware of?
Maybe, is this the appropriate Drupal way or is there a more elegant built in solution I am unaware of?


Yes, that's an appropriate "Drupal" way.

The other way is to make it global, and add it to the database settings array in settings.php.

But you're essentially setting the same thing whichever way you do it. If you were to use your method, and then remove that option from the connection afterwards, it could arguably be considered more secure, since you're limiting the time and scope that LOCAL INFILE can be used in.