PHP load csv into sql table only works when all permissions granted @localhost

Question

This question has been asked but no answers yet...

Have a csv upload form to load into an existing MySQL table for use on a Drupal page, hence everything being in one php. This is what I have (thanks coyotelab):

$mysqli = new mysqli($server, $user, $password, $database);

if ($mysqli->connect_error) {
     die("Failed to connect: " . $mysqli->connect_error);
}

if (isset($_POST['submit'])) {
  if (is_uploaded_file($_FILES['filename']['tmp_name'])) {
    echo "<h2>" . "File ". $_FILES['filename']['name'] ." uploaded successfully." . "</h2>";
  }

  $handle = fopen($_FILES['filename']['tmp_name'], "r");
  if ($handle !== FALSE) {
    $import="LOAD DATA LOCAL INFILE '" . $_FILES['filename']['tmp_name'] . "' INTO TABLE Demo2 CHARACTER SET 'utf8' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' IGNORE 1 LINES;";
    mysql_query($import) or die('<p style="color:red;">Database import unsuccessful: ' . mysql_error() . '</p>');
  }
  fclose($handle);
  print "<p style='color:blue;'>Import done</p>";
  echo "<p style='position: relative; top: 10px;'><strong>Displaying contents:</strong></p>";
  readfile($_FILES['filename']['tmp_name']);
  unset($_POST); 

} else {
print "<p>Import new csv</p>\n";
print "<form enctype='multipart/form-data' action='?q=drupal-relative-path-to/upload.php' method='post'>\n";
print "<input size='50' type='file' name='filename'><br />\n";
print "<input type='submit' name='submit' value='Import'></form>";

At first, the form was test locally and granting all permissions to localhost worked and data imported successfully. But on a Drupal server, there will be no change to localhost permissions. Other forms to insert SQL data have worked fine from Drupal pages. Only happens with the upload file function.

So far the form shows up on Drupal, but when uploading and submitting a CSV, I get the same error as testing on localhost without permissions. Error is "No database selected"

My thought was to change the location where uploaded temp files live, though haven't figured that out or might not be possible on the Drupal server. Tried adding a second db connect line, but that doesn't work. Must have something to do with the temp directory.

This form will be on a private network.

well unless its a typo youre not using the correct database function to run your query... it should be `mysqli_query($mysqli, $import)` as opposed to `mysql_query($import)`. that said you still need the proper permissions to do a `LOAD...INFILE`. — prodigitalson, Jun 19 '15 at 16:55
Is there a way to provide temporary permissions for the data set? Like a way to mask an anonymous user as a user with the proper permissions? — motorbaby, Jun 19 '15 at 17:12
The database connection is failing before `LOAD DATA INFILE...` It seems to fail when retrieving the contents. I've tried moving and renaming the uploaded CSV to localhost's root, but connection still failed. — motorbaby, Jun 19 '15 at 17:39
You just need to use a connection with a user that has the proper permission, whether this is the default user of your drupal, or an entirely different user. If you cannot adjust this then just parse the CSV and do your own inserts. It won't be as performant but its less of a security risk and requires no DB level configuration. — prodigitalson, Jun 19 '15 at 19:37
Tried allowing anonymous @localhost grant privilege. Added statement `GRANT ALL...` before `LOAD DATA`. Grant worked, but not load. Went back to an early revision with separate connect and db_connect statements. Tested the doc with mostly the above, and it worked. Had to use `REVOKE ALL` to reset permissions. As @prodigitalson says, `LOAD DATA` needs proper permissions. Will try creating an array from the csv and `INSERT` instead. — motorbaby, Jun 19 '15 at 20:00

score 0 · Answer 1 · answered Jun 19 '15 at 06:57

If error is "No database connection" then your code fails on very first code row displayed here. Something is wrong with your database user account information (or account it self). Maybe host isn't "localhost" but something else?

If Drupal is working well check out it's connection. Check file /sites/default/settings.php. There is stored information about database connection so you can copy your account data from there.

BTW, instead of old school fopen on PHP you have easier to use file_get_contents() function.

As I mentioned, when I grant all permissions for @localhost, it works. But I can't change this on the Drupal server. — motorbaby, Jun 19 '15 at 07:30

PHP load csv into sql table only works when all permissions granted @localhost

1 Answers1