LOAD DATA INFILE + Ошибка MySQL 28000

Ответ 1

Вы можете попробовать LOAD DATA LOCAL INFILE и использовать файл, хранящийся на клиентском диске. Таким образом, вам не нужно иметь разрешение FILE. Он будет медленнее, так как файл должен быть загружен на сервер.

Надеемся на эту помощь

Ответ 2

Для начала покажите нам свой файл конфигурации (my.cnf) и проверку базы данных. Также полезен вывод кода, в котором соединение установлено. Это должно быть что-то вроде:

/*** mysql hostname ***/
$hostname = 'localhost';
/*** mysql username ***/
$username = 'user';
/*** mysql password ***/
$password = 'pass';

function testdb_connect ($hostname, $username, $password){
    $dbh = new PDO("mysql:host=$hostname;dbname=database", $username, $password);
    return $dbh;
}

try {
    $dbh = testdb_connect ($hostname, $username, $password);
    echo 'Connected to database';
} catch(PDOException $e) {
    echo $e->getMessage();
}

Что касается ошибок:

ERROR 1045 (28000) at line 1: Access denied for user 'user'@'localhost'
and
ERROR 1148 (42000) at line 1: The used command is not allowed with this MySQL version

Вы получите сообщение об ошибке, если вы выполните:

/usr/bin/mysql --host=localhost --user=theuser --password=password --database=db_database<<EOFMYSQL
show tables;
EOFMYSQL

Одна вещь, которую вы можете проверить (которая требует от вас входа в консоль MySQL) - убедитесь, что у вас есть разрешения на вход в MySQL через localhost:

mysql -h 127.0.0.1 -u user -p

mysql> select user,host from mysql.user;
+------+--------------------------------+
| user | host                           |
+------+--------------------------------+
| user | 127.0.0.1                      | 
| user | ::1                            |
| user | localhost                      | <-- Make sure you have a localhost entry for root
+------+--------------------------------+
3 rows in set (0.00 sec)

Кроме того, вы можете проверить GRANTS для своего пользователя. Чтобы перечислить привилегии, предоставленные учетной записи, которую вы используете для подключения к серверу, вы можете использовать любое из следующих утверждений:

SHOW GRANTS;
SHOW GRANTS FOR CURRENT_USER;
SHOW GRANTS FOR CURRENT_USER();

Чтобы быть уверенным, что есть проблема с привилегиями, попробуйте следующее:

sudo service mysql stop
sudo mysqld --skip-grant-tables

И выполните команду LOAD.

UPDATE

Что касается вашего файла конфигурации, по соображениям безопасности у вас есть:

[mysqld]
local-infile=0

Если LOAD DATA LOCAL отключено, либо на сервере, либо на клиенте, клиент, который пытается выдать такой оператор, получает следующее сообщение об ошибке:

ERROR 1148: The used command is not allowed with this MySQL version

В чем вы можете установить всех пользователей, кроме "admin" и "root" (например), чтобы не иметь эту привилегию на уровне mysql, затем войдите в MySQL и запустите запрос:

UPDATE mysql.user SET File_priv='N' WHERE user!='da_admin' AND user!='root';
FLUSH PRIVILEGES;

Для командной строки попытайтесь использовать только "--local-infile" insted из "--local-infile = 1" и дайте нам знать результат.

Ответ 3

ИНДИКАТОР ЗАГРУЗКИ ДАННЫХ

Эта директива используется для загрузки файлов, расположенных на сервере.

Чтобы использовать LOAD DATA INFILE, убедитесь, что:

• у подключаемого пользователя есть привилегия FILE. См. 6.2.1 Привилегии, предоставляемые MySQL.

• сервер имеет достаточные (файловые системы) разрешения для чтения файла.

• что если системная переменная secure_file_priv установлена, файл находится в этом каталоге. Сервер не будет загружать файлы за пределами этого каталога.

LOCAL INFILE LOAD DATA

Эта директива используется для загрузки файлов, расположенных на клиенте.

Чтобы использовать LOAD DATA INFILE, убедитесь, что:

• клиент MySQL скомпилирован с помощью -DENABLED_LOCAL_INFILE=1. LOAD DATA LOCAL INFILE не может работать без него.

  Большинство дистрибутивов скомпилированы с этим флагом, но некоторые из них не являются. Это в основном проблема, когда MySQL скомпилирован вручную.

• сервер MySQL запускается с опцией --local-infile=1.

  Вы также можете установить local-infile=1 под группу [mysqld], поэтому он включается автоматически при запуске сервера.

  LOAD DATA LOCAL INFILE не может работать без него.

• параметр loose-local-infile=1 устанавливается под группой [client]. Это не всегда необходимо, но лучше убедиться, что это не проблема.

• Чтобы использовать LOAD DATA LOCAL INFILE в PHP, вам нужно установить параметр-драйвер PDO::MYSQL_ATTR_LOCAL_INFILE в true при создании нового дескриптора базы данных. См. Функции MySQL (PDO_MYSQL)

  Это необходимо сделать при создании нового экземпляра PDO. После этого установка PDO::setAttribute() не будет работать.

Подробнее см. 6.1.6 Проблемы безопасности с LOAD DATA LOCAL.

Ответ 4

Когда я связался с хостингом, они сказали, что мне нужно разрешение FILE для этого. Но это плохая практика и не рекомендуется.

Хорошо, там у вас есть. Вам нужны разрешения FILE, чтобы делать то, что вы пытаетесь сделать. Это действительно так, независимо от того, используете ли вы PHP или bash, потому что привилегия проходит через MySQL.

В то время как у меня есть некоторые оговорки в отношении того, что плохая практика использует привилегию FILE, я думаю, что есть серьезные проблемы с безопасностью. Однако вам не нужно предоставлять разрешение FILE для импорта таблицы Excel.

Я собираюсь предоставить вам два решения: один, который использует DATA INFILE, и тот, который этого не делает.

Во-первых, примечание:

Ключ к безопасным и безопасным данным с включенной привилегией FILE или без нее обеспечивает правильное экранирование для ВСЕХ ваших запросов, особенно тех, которые принимают вход пользователя (пример ниже).

Опция DATA INFILE

Если вы собираетесь назначить привилегии FILE, большая проблема, с которой я столкнулся, будет заключаться в том, что хакер сможет генерировать всевозможные сомнительные данные или извлекать данные, которые вы не хотите, чтобы кто-то имел (статья здесь объясняет, как READ LOCAL INFILE может быть очень опасным). Если ваш хост разрешает это, вы можете создать отдельного пользователя с разрешениями FILE, доступными только через localhost. Хотя это отнюдь не серебряная пуля, она значительно снижает каналы, через которые хакер может обращаться к файлам, если у вас есть уязвимости в области инъекций.

CREATE USER 'filewriter'@'localhost' IDENTIFIED_BY 'some_really_long_and_complex_password';
GRANT FILE ON *.* TO 'filewriter'@'localhost';

Опция DATA INFILE

Рассмотрим этот пример.

Поскольку вы используете PHP, я рекомендую вам создать файл PHP bash или просто поместить его в ваш script и вынуть hashbang (#!/usr/bin/php).

Вы должны сделать что-то вроде этого:

#!/usr/bin/php

$mysqli = new mysqli("host_name", "username", "password", "database") or die("Could not connect.");

$file_handle = fopen("myfile.csv", "r");


$query="INSERT into my_table_name(user_name,user_address,user_class) values(?, ?, ?)";
//practice safe escaping and store records faster.
$stmt = $mysqli->prepare($query) or die(mysql_error());
$stmt->bind_param("sss", $col1, $col2, $col3);
while (($line_of_data = fgetcsv($file_handle, 1000, ",")) !== FALSE) {
    $col1 = $line_of_data[0];
    $col2 = $line_of_data[1];
    $col3 = $line_of_data[2];

    $stmt->execute();
}

$stmt->close();

Используя PHP, язык, на котором я полагаю, вам комфортно, вы сможете воспользоваться любыми ограничениями безопасности, которые вам нужны (я уже включил подготовленные заявления) и хранить ваши данные. Это может быть не так быстро, как LOAD DATA INFILE, но это безопасный способ загрузить ваши данные в PHP. Если ваша таблица не массивная (GB), это должно быть просто отлично.

Ответ 5

строка

Uncaught exception 'PDOException' with message 'SQLSTATE[28000]: Invalid authorization specification: 1045 Access denied for user 'myuser'@'localhost' (using password: YES)'

очевидно, говорит, что у вас возникла проблема с вашим именем входа, а затем посмотрите на это:

+------------------------------------------------------------------------------------------------------------------------+
| Grants for [email protected]                                                                                           |
+------------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'theuser'@'localhost' IDENTIFIED BY PASSWORD '*password' |
| GRANT ALL PRIVILEGES ON `mydomain_live`.* TO 'theuser'@'localhost'                                                    |
| GRANT ALL PRIVILEGES ON `mydomain_staging`.* TO 'theuser'@'localhost' WITH GRANT OPTION                               |
+------------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.01 sec)

сообщает, что у вас есть доступ к базе данных mydomain_live и mydomain_staging, поэтому вы импортируете script:

#!/bin/bash
/usr/bin/mysql --host=localhost --user=theuser --password=password --database=mydomain_staging<<EOFMYSQL
LOAD DATA INFILE 'locationofmyfile.csv'
INTO TABLE test_import 
FIELDS TERMINATED BY ';'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES ( ArtID, ArtNamePharmLang, ArtNameFr, ArtNameNl, PubPrice, PercentageRebate, RebateAmount, SellingPrice, Localisation, CnkNr, EanNr, SoldQty, MinThd, MaxThd, QtyInStock, DateLastSale, VatRate, SupplierManufName, BuyPrice, InvCatCode, ArtType, ApbCatCode, ApbLegCode, PharmApbNr );
EOFMYSQL

обратите внимание на - database = mydomain_staging?

Ответ 6

Вы пытались использовать mysqlimport из командной строки? Это должно быть равнозначно загружаемым данным infile:

Вот пример с опцией -local для использования на стороне клиента:

mysqlimport -uTHEUSER -pPASSWORD -h ServerIPorDomainGoesHere --local --compress db_name /somepath/TableName.csv

Важной деталью является то, что имя файла csv должно быть именем таблицы здесь, попробуйте его и сообщите нам, если вы все еще получаете ту же ошибку.

Также попробуйте проверить db для анонимных пользователей, если mysqlimport не работает:

mysql> select user,host from mysql.user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| root             | localhost |
| root             | 127.0.0.1 |
| root             | ::1       |
|                  | localhost |
|                  | %         |
| myuser           | localhost |
+------------------+-----------+ 

Попробуйте удалить эти пустые имена пользователей в таблице, так как ваш логин будет соответствовать одному из них в этом примере перед сопоставлением "myuser". Так что даже имея разрешения на "myuser", анонимный, вероятно, не делает.

Надеюсь, что это поможет!

Ответ 7

Хостинговые компании обычно отключают LOAD DATA INFILE по соображениям безопасности и предлагают вместо этого использовать LOAD DATA LOCAL.

Вот один из Hostmonster: https://my.hostmonster.com/cgi/help/317 Или тот же ответ от Bluehost: https://my.bluehost.com/cgi/help/317

В качестве альтернативы вы можете просто читать данные из файла на PHP, используя обычные функции fopen(), fgets()/fgetcsv(), fclose() и вставлять их в таблицу MySQL.