2

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.

Rich Benner
  • 7,873
  • 9
  • 33
  • 39
shqnks
  • 236
  • 7
  • 17

1 Answers1

2

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.
Community
  • 1
  • 1
Drew
  • 24,851
  • 10
  • 43
  • 78
  • Thanks for your answer, I try to do it and I come back to you – shqnks Jun 23 '16 at 10:57
  • I did "GRANT ALL PRIVILEGES ON mydb.* TO 'myuser'@'%' WITH GRANT OPTION;" and nothing happened (I replaces mydb and myuser by the actual good values) I dont know where to show these variables. It's a 1and1 server and I dont know where to go to write your line "show variables where variable_name='local_infile';" – shqnks Jun 27 '16 at 12:29
  • I tried to grant all privileges from the Phpmyadmin panel and : MySQL said: Documentation #1044 - Access denied for user 'xxx'@'%' to database 'xxx' – shqnks Jun 27 '16 at 12:42
  • I tried to ping you into [Campaigns](http://chat.stackoverflow.com/rooms/95290/campaigns) to try to figure this out concerning 1and1. I cannot contact them for a cheatsheet as I have no acct with them: https://contact.1and1.com/area – Drew Jun 27 '16 at 13:01
  • 1
    I read the question a few times now. Now via comments it is obvious about this situation with a hoster than changing a server variable is not going to be the solution (it would affect security for all your peer accounts on the server and it is locked down). There is no immediate obvious solution other than tinker with ssh connections for determining the `full path` to your file and `chmod` file permissions and `select user()` to determine the auth/context of the user attempting the import. I am happy to do this for you, but you would have to allow it. Join the chat above if interested. – Drew Jun 27 '16 at 13:17
  • I would be glad to join your chat, how can I do it? – shqnks Jun 27 '16 at 13:25
  • Just click on the word up there that says Campaigns – Drew Jun 27 '16 at 13:30
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/115734/discussion-between-shqnks-and-drew). – shqnks Jun 27 '16 at 13:42