MySQL won't read password from configuration file

Question

I have an html form on my site and I am trying to process the data and then store it in a mysql database on my server. In order to connect securely to mysql via php, I have, as suggested here, placed the login information in a configuration file outside of the main webroot.

The code executes seamlessly when it is written like this:

<?php
$mysqli = mysqli_init( );
$mysqli->options(MYSQLI_READ_DEFAULT_FILE, "/this/is/the/filepath/to/my.cnf");
$mysqli->real_connect(NULL, NULL,'*********',NULL );  

if (mysqli_connect_errno()) {
    printf("Connect failed: ", mysqli_connect_error());
    exit();
}

$email = $mysqli->real_escape_string($_POST['email']);

$mysqli->query("INSERT INTO subscribetest (email) VALUES ('$email')");  
?>

But when I take the password out, like this:

<?php
$mysqli = mysqli_init( );
$mysqli->options(MYSQLI_READ_DEFAULT_FILE, "/this/is/the/filepath/to/my.cnf");
$mysqli->real_connect(NULL,NULL,NULL,NULL ); 

if (mysqli_connect_errno()) {
    printf("Connect failed: ", mysqli_connect_error());
    exit();
}

$email = $mysqli->real_escape_string($_POST['email']);

$mysqli->query("INSERT INTO subscribetest (email) VALUES ('$email')");
?>

The code crashes and I am given the error message: 'Warning: mysqli::real_connect(). Access denied for 'user'@'webaddress.net' (using password:NO)'

Why can my php script read everything EXCEPT the password from the config file? It is able to read the username, host, and database from the config file; the password is the only one which is left out. I've looked everywhere, spent hours on this, but I haven't been able to find any solutions. Has anyone else had this problem? What can I do?

As the error states you need to check the connection. You are passing `NULL` for everything do you have a valid user with no password ? — Deepak, Sep 03 '12 at 02:34

score 1 · Answer 1 · answered Sep 03 '12 at 07:52

1

The documentation says that setting the password parameter in real_connect() to NULL means "user does not need a password". Does it work if you call it without any parameters (they're all optional)?

answered Sep 03 '12 at 07:52

Simon

12,018
4
34
39

I've tried calling it without any parameters, and I get the same error that I get when all parameters are set to NULL (aka read from the config file) except for the password. You referenced the php documentation for real_connect(); the mysql documentation for real_connect (http://dev.mysql.com/doc/refman/5.0/en/mysql-real-connect.html) says that you can instruct real_connect() to read the parameters from an option file by setting those parameters to NULL: "it is possible to have the value taken from an option file rather than from an explicit value in the mysql_real_connect() call." – Justin Manley Sep 03 '12 at 13:54
Alright, so there's probabyly something wrong with your options file. Can you post it? – Simon Sep 03 '12 at 15:04
The configuration file reads as follows: '[client] host = host.com user = newsletter password = ******** database = ed_newsletter' I know that the password and hostname are correct because when I place them in the real_connect() call explicitly (that is, write them in the php code), the MySQL statement executes correctly. – Justin Manley Sep 03 '12 at 15:28
Odd. If you have a shell on the server, does running `mysql` w/o any arguments log you in? Can you change the password to ASCII only characters to test whether PHP has a problem parsing the options file (maybe quoting the password helps, like `password="blahblah"`)? – Simon Sep 03 '12 at 20:30
Better yet, can you create an empty options file and add line by line to see if PHP is offended by one? – Simon Sep 03 '12 at 20:37
Simon, I don't have a shell on the server, but I did do as you suggested and added line by line, moving the values from the php file to the option file one by one. As expected, it worked for all of them except for the password. As you suggested earlier, I changed the password to a simpler one and tried it both in the php file and the config file. As usual, it worked in the php file, but not the option file, so no progress there. I also noticed that when I listed the password BOTH in the option file and the php file, the query was successful. This suggests that the presence of the pa – Justin Manley Sep 05 '12 at 03:06
assword in the option file does not inhibit the authentification, it simply has no effect. It is still curious to me that when the password is only listed in the option file, the error code reads "using password:NO." I've tried "pass = ***" and "password = ***" and "passwd = ***". I don't know how to get it to recognize that there is a password present in the option file. – Justin Manley Sep 05 '12 at 03:08

score 0 · Answer 2 · answered Nov 17 '16 at 15:33

This bug still seems to exist: https://bugs.php.net/bug.php?id=43812

Workaround:

$iniData = file_get_contents('/etc/mysql/debian.cnf');
$iniData = preg_replace('/#.*$/m', '', $iniData);
$mysqlConfig = parse_ini_string($iniData, true);
$mysqli = new mysqli( $mysqlConfig['client']['host'], $mysqlConfig['client']['user'], $mysqlConfig['client']['password'] );
if ( $mysqli->connect_error ) {
    die(sprintf("MySQL Connect Error: %s", $mysqli->connect_error));
}

MySQL won't read password from configuration file

2 Answers2