CODEWITHSUNDEEP
phpmysqlsqlpdo
shqnks
shqnks

Reputation: 246

LOAD DATA (LOCAL) INFILE - Invalid authorization specification

I uploaded a project by FTP to a 1and1 server. I got an error when the website goes to the function that imports a csv file into a table of the database.

Here's my code:

    $file = "/Contenu/BD/file.csv";

    $sql = "LOAD DATA INFILE '$file' INTO TABLE table CHARACTER SET UTF8 FIELDS TERMINATED BY \";\" LINES TERMINATED BY \"#\"";

When I use "LOAD DATA INFILE", I have a SQL error saying :

" SQLSTATE[28000]: Invalid authorization specification: 
1045 Access denied for user xxxxxx (using password: YES) "

And when I use LOAD DATA LOCAL INFILE, I have no SQL errors, but the integration doesn't work.

So I came to you to understand what is happening with my file importation. This file imports correctly in my development environment, in localhost, but not on the production server.

(the whole database works on the project so I don't think that I have errors on my SQL logins)

Thank you guys for your answers.

Upvotes: 2

Views: 3809

Answers (1)

Drew
Drew

Reputation: 24959

Look into the GRANT FILE privileges which may vary by user from server to server. Especially if wildcards were used during initial user setup. Or should I say, a GRANT ALL ... was done on one server but not on another.

See also the Manual page on security, entitled Security Issues with LOAD DATA LOCAL.

And check your server settings with:

show variables where variable_name='local_infile';

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| local_infile  | ON    |
+---------------+-------+

For those in hosted environments where changing server-level variables is not an option, then the following are typically performed:

  1. Find the full path to your file. This is not easy and may result in the file being something cryptic on the prefix side of the path leading up to your virtualized directory. Meaning /some/path/to/you/home/shqnks
  2. Having the FILE permissions part of the GRANT
  3. Finding out what user context is actually running the script. This may be a longshot but select user() can help.
  4. ssh access and a chmod for the file. Example here.
  5. Some have luck with LOCAL while others find it silly because the file is already on the server, and local puts it in a temp directory. The reason that may work for some is that additional file-level access rights may be opened up in doing that.

Upvotes: 2

Related Questions