Keep getting error 'mysqli.allow_local_infile'

General PHP Help
#1

Just reading Kevin Yank’s book PHP & MySQL: Novice to Ninja, so I thought I would try setting up a db for my students. I’m just a beginner. I’m using Ubuntu 18.04.3

As an exercise, I made createtable.html.php, it creates a table in the db allstudentsdb. It works and creates a new table according to the data I put in a form. No problem.

Next I want to populate the table from a csv file, so I made a form.html.php When I click the submit button I get this error:

Error importing /home/pedro/mycsvfiles/19BE1.csv’ This problem: SQLSTATE[HY000]: General error: 2000 LOAD DATA LOCAL INFILE is forbidden, check mysqli.allow_local_infile

my php.ini file has this:

; Allow accessing, from PHP’s perspective, local files with LOAD DATA statements
; http://php.net/mysqli.allow_local_infile
;changed by me to open
mysqli.allow_local_infile = On

However, when I go to a bash terminal and log in to mysql, I can do this:

LOAD DATA LOCAL INFILE ‘/home/pedro/mycsvfiles/17BE3.csv’ INTO TABLE 17bizeng CHARACTER SET utf8
FIELDS TERMINATED BY ‘,’ OPTIONALLY ENCLOSED BY ‘"’
LINES TERMINATED BY ‘\n’ IGNORE 1 LINES;

The above works perfectly. No error, the table is populated.

So I am a bit perplexed. Any tips please??

This is the code that is supposed to do the work (don’t laugh, I’m just learning this stuff!):

if (isset($_POST["tablename"])) {
	
	include $_SERVER['DOCUMENT_ROOT'] . '/includes/studentdb.inc.php';
try
{
	$pre = 'LOAD DATA LOCAL INFILE \'';
	$fileName = '/home/pedro/mycsvfiles/' . $_POST['tablename'] . '.csv\'';
	$tablename = $_POST['tablename'];
	$intotable = ' INTO TABLE ';
	$charset = ' CHARACTER SET utf8 ';
	$rest = ' FIELDS TERMINATED BY \',\' OPTIONALLY ENCLOSED BY \'"\' LINES TERMINATED BY \'\n\' IGNORE 1 LINES;';
	$sql = $pre . $fileName . $intotable . $tablename . $charset . $rest ;
	echo $sql;
	$s = $pdo->prepare($sql);
	$pdo->exec($sql);
	$output = 'The table "' . $tablename . '" was successfully created.';
	include 'output.html.php';
	exit();
}
catch (PDOException $e)
{
	$output = 'Error importing ' . $fileName . 'table:  ' . $e->getMessage();
	include 'output.html.php';
	exit();
 }
}
#2

The php.ini setting you found is for the mysqli extension. You are apparently using the PDO extension. See the PDO::MYSQL_ATTR_LOCAL_INFILE attribute/setting at this link - https://www.php.net/manual/en/ref.pdo-mysql.php

#3

Thanks for the link!

If I echo $sql into my output page, I see this:

LOAD DATA LOCAL INFILE ‘/home/pedro/mycsvfiles/19BE3.csv’ INTO TABLE 19BE3 CHARACTER SET utf8 FIELDS TERMINATED BY ‘,’ OPTIONALLY ENCLOSED BY ‘"’ LINES TERMINATED BY ‘\n’ IGNORE 1 LINES;

I can paste this in mysql in bash, and it does exactly what I want.

But if I send it from PHP I get:

Error importing /home/pedro/mycsvfiles/19BE3.csv into 19BE3 This problem: SQLSTATE[HY000]: General error: 2000 LOAD DATA LOCAL INFILE is forbidden, check mysqli.allow_local_infile

I tried setting mysqli.allow_local_infile = 1 and mysqli.allow_local_infile = On

My createtable,html.php also needs this setting On and works, so I assume it is installed and On

I rebooted several times, but I can’t get it to work. Maybe this is some other kind of injection protection?

sudo apt install php-mysql

gets the modules needed for PDO if they are not present. I did that too.

Funny thing is, almost the same code creates a table from the browser, and it works great!

Sleep on it!

#4

What? You have code that uses a LOAD DATA LOCAL INFILE … query that works? How about posting that code?

#5

No, I don’t have that code, I have code which makes a table, very similar to the code I would like to use the populate a table:

<?php

include_once $_SERVER['DOCUMENT_ROOT'] . '/includes/studentdb.inc.php';

try
{
	$pre = 'CREATE TABLE IF NOT EXISTS ';
	$tablename = $arrayoutput[0]; 
	$id = ' (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, ';
	$end = ' DEFAULT CHARACTER SET utf8 ENGINE=InnoDB;';

	$sql = $pre . $tablename . $id . $allcols . $end;
	echo '$sql is ' . $sql;
	//$s = $pdo->prepare($sql);
	$pdo->exec($sql);
	$output = 'The table "' . $tablename . '" was successfully created.';
	include 'output.html.php';
	exit();
}
catch (PDOException $e)
{
$output = 'Error creating ' . $tablename . 'table: ' . $e->getMessage();
include 'output.html.php';
exit();
}
?>

This code requires mysqli.allow_local_infile = On

So I’m thinking, my problem is not actually mysqli.allow_local_infile = On, but some other sql injection safety measure within mysql. Any ideas, because the code is almost identical.

Of course, I can login to mysql and do this, but I wanted to try to do it from php.

#6

No, it doesn’t. You are not even reading the error message, nor did you read the information in my reply and at the link I posted.

The error is because the LOAD DATA LOCAL INFILE … sql query is not allowed, by default, in some versions of php. That the error message mentions mysqli.allow_local_infile (note the i in the mysqli) is either a mistake on php’s part (the php change log and bug reports read like the clown act at the circus at times) or your actual code is using the mysqli extension.

When I run a LOAD DATA LOCAL INFILE … query with php, using the PDO extension, without the PDO::MYSQL_ATTR_LOCAL_INFILE setting being set to a true value, I get a php warning -

Warning : PDO::exec(): LOAD DATA LOCAL INFILE forbidden in myfile.php…

#7

Sorry, but this is all a bit above my head. I went to the link, saw this:

For example, on Ubuntu the php5-mysql package installs the ext/mysql, ext/mysqli, and PDO_MYSQL PHP extensions. On CentOS, the php-mysql package also installs these three PHP extensions.

So that’s what I did:

sudo apt install php-mysql

Now, what I think you mean is add this in my studentdb.inc.php :

PDO::MYSQL_ATTR_LOCAL_INFILE

I tried that, please see below, but the error .log still says: LOAD DATA LOCAL INFILE forbidden in /var/www/html/importcsv/index.php on line 25

This connects me to mysql, specifically allstudentsdb:

<?php
try
{
$pdo = new PDO('mysql:host=localhost;dbname=allstudentsdb', 'myusername', 'mypassword');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION, PDO::MYSQL_ATTR_LOCAL_INFILE);
$pdo->exec('SET NAMES "utf8"');
}
catch (PDOException $e)
{
$error = 'Unable to connect to the database server.';
include 'error.html.php';
exit();
}
?>

I hope that’s what you meant!

Well, whatever, that does not work: error.log shows:

[Sun Jan 26 07:50:08.322150 2020] [php7:warn] [pid 22752] [client 127.0.0.1:43824] PHP Warning: PDO::setAttribute() expects exactly 2 parameters, 3 given in /var/www/html/connect/index.php on line 5

and if I just put:

PDO::MYSQL_ATTR_LOCAL_INFILE

in setAttribute( ) I get:

[Sun Jan 26 07:51:50.770826 2020] [php7:warn] [pid 22751] [client 127.0.0.1:43826] PHP Warning: PDO::setAttribute() expects exactly 2 parameters, 1 given in /var/www/html/connect/index.php on line 6

Maybe there is some other attribute which belongs with

PDO::MYSQL_ATTR_LOCAL_INFILE

#8

If anyone else has this problem, try this:

  1. make the db connection like this, I added this: array(PDO::MYSQL_ATTR_LOCAL_INFILE => true,) (don’t know why there is a , after true but it works!!)

    <?php try { $pdo = new PDO('mysql:host=localhost;dbname=allstudentsdb', 'myuser', 'mypassword', array(PDO::MYSQL_ATTR_LOCAL_INFILE => true,)); $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); $pdo->exec('SET NAMES "utf8"'); } catch (PDOException $e) { $error = 'Unable to connect to the database server' . $e ; include 'error.html.php'; exit(); } ?>

  2. I moved the directory mycsvfiles to /var/www because php or apache couldn’t find them after the above change.

Then it works, straight from the webbrowser!!

Sponsor our Newsletter | Privacy Policy | Terms of Service