1

I am using \Drupal::database()->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()->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 'LOAD DATA INFILE' 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()->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->query($sql_statement)->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?

  • I have done this in Drupal 7. You might need more PDO flags. gist.github.com/kevinquillen/45fb33206559449e9cb2 – Kevin Jun 12 '17 at 14:21
  • Thanks, I will look into adding those other flags. And for those without a github account, they are: PDO::ATTR_EMULATE_PREPARES => TRUE, PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => TRUE, PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_PERSISTENT – rjl Jun 12 '17 at 15:08
  • None of throse flags are needed for this question specifically, they're all unrelated to this problem (except buffering, but I doubt you'll need it after you fix the first problem) - and if you have a modern version of MySQL (5.5+ if memory serves), you shouldn't use ATTR_EMULATE_PREPARES. That should be used only when the target engine has no native support for prepared statements. Using it on a modern version of MySQL will slow PDO down – Clive Jun 13 '17 at 6:45
1

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.

  • Thanks, I think the PDO part is the "Drupal" way, but your "in settings.php is the perfect, and logical, place" makes me wonder if my solution isn't a bit of a hack. My solution is inline with no changes to settings.php. Since it is the same database, I am thinking that maybe I should just add the PDO changes to the settings. I will look into this more. – rjl Jun 12 '17 at 15:18
  • Oh sorry, I just glanced through your code without paying enough attention to it, I parsed it as being part of the connection settings in settings.php. Now that I've read it properly (!) there's nothing wrong with the solution in your question. It's arguably more secure, actually, as you're only allowing LOCAL INFILE in very specific circumstances and not globally (well, it will be if you remove it from the connection options again once you're done). I updated the answer with some slightly more sensible thoughts – Clive Jun 12 '17 at 15:30
  • I like the "arguably more secure" thought - I am more inclined now to keep it inline. – rjl Jun 12 '17 at 15:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.