PHP MySQLi permission denied but working from MySQL CLI
When connecting from PHP with MySQL, MySQLi and PDO to a remote MySQL host.
However, if I connect to the same host, with the same credentials through MySQL from the same host on the command line it works perfectly.
So I’m making the assumption its a PHP config problem?
PHP 5.6.40 (Client API version => mysqlnd 5.0.11-dev) MySQL 5.5.59 PHP 5.6.23 (Client API version => mysqlnd 5.0.11-dev) MySQL 5.5.52 Anyone got any ideas why I can connect from the command line to MySQL but not from PHP to the same host with the exact same credentials?
Answers
I guess that you have SELinux enabled on your server. By default it doesn’t allow apache process to initialize outgoing network connections.
Copy of an answer from https://serverfault.com/a/456875/442205
To see what flags are set on httpd processes
To allow Apache to connect to remote database through SELinux
setsebool httpd_can_network_connect_db 1 Use -P option makes the change permanent. Without this option, the boolean would be reset to 0 at reboot.
setsebool -P httpd_can_network_connect_db 1 The server encoding must be either not set, or set to UTF-8. This is done via the apache AddDefaultCharset directive. This can go to the virtualhost or the general file (see documentation).
- Set the collation of the database to be UTF-8
- Set the connection encoding. It can be done as someone said with mysqli_set_charset, or by sending this just after connecting:
SET NAMES 'utf8' COLLATE 'utf8_unicode_ci'
1- You should set the HTML charset of the page to be UTF-8, via a meta tag on the page, or via a PHP header:
-or- header('Content-type: text/html; charset=utf-8'); 2- You should always use the mb* version of string-related functions, for example, mbstrlen instead of strlen to get the string length of a string.
This should allow you to have UTF-8 everywhere, from the pages to the data. A test you can do: right-click anywhere on the page using firefox, and select Show page information. The effective encoding is listed in that page.
I would check that the permissions on:
/Users/alvincrespo/Sites/apollo/storage/views/26bdebca7505781c753aa21663170a1b Allow your application to write to this directory.
Assuming you are on a linux box you could run ls -l to see what the permissions are, and if it is set to read only, change the permissions with chmod.
solved the problem by replacing $this->path with storage_path()
SO, for everyone that is wondering and will find this thread in the future: I DID NOT FIND AN ANSWER WITH THE SOLUTION I WANTED TO USE, HOWEVER I CAME UP WITH SOMETHING ELSE, AND HERE IS A DESCRIPTION:
Instead of making Node.js server send the AJAX request, i left it as i had before, the jQuery $.post() request from the client, to a PHP function.
What i did next was to implement a MySQL listener, that checked the MySQL binlog for changes. I used mysql-events module. It retrieves the newly added row with all data and then uses socket.io emit function to send it to connected clients. I also had to drop SSL because it apparently hates me. It’s a small hobby project, so i don’t really have to bother that much with SSL.
Best solution would be obviously to program the whole webserver in Node.js and just drop Apache completely. Node.js is awesome for real time applications, and it’s a very easy language to learn and use.
My setup of Node.js + Socket.io + mysql-events: (ignore the unused requires)
// NODE var socket = require( 'socket.io' ); var express = require( 'express' ); var https = require( 'https' ); var http = require( 'http'); var fs = require( 'fs' ); var request = require( 'request' ); var qs = require( 'qs' ); var MySQLEvents = require('mysql-events'); var app = express(); /*Correct way of supplying certificates. var server = https.createServer(< key: fs.readFileSync('/etc/letsencrypt/live/x/privkey.pem'), cert: fs.readFileSync('/etc/letsencrypt/live/x/cert.pem'), ca: fs.readFileSync('/etc/letsencrypt/live/x/chain.pem') >,app); */ var server = http.createServer( app ); // Won't work without cert. var io = socket.listen( server ); console.log("Server Started"); //DB credentials var dsn = < host: 'x', user: 'x', password: 'x', >; var mysqlEventWatcher = MySQLEvents(dsn); //Watcher magic, waits for mysql events. var watcher = mysqlEventWatcher.add( 'newage_db.chat', function (oldRow, newRow, event) < //row inserted if (oldRow === null) < //insert code goes here var res = JSON.stringify(newRow.fields); //Gets only the newly inserted row data res.charset = 'utf-8'; //Not sure if needed but i had some charset trouble so i'm leaving this. console.log("Row has updated " + res); io.sockets.emit('message', "[" + res + "]"); //Emits to all clients. Square brackets because it's not a complete JSON array w/o them, and that's what i need. >//row deleted if (newRow === null) < //delete code goes here >//row updated if (oldRow !== null && newRow !== null) < //update code goes here >//detailed event information //console.log(event) >); io.sockets.on( 'connection', function( client ) < console.log( "New client !" ); client.on( 'message', function( data ) < //PHP Handles DB insertion with POST requests as it used to. >); >); server.listen(8080, function() < console.log('Listening'); >); Client JavaScript SEND MESSAGE:
$('#txtArea').keypress(function (e) < if (e.which == 13 && ! e.shiftKey) < var emptyValue = $('#txtArea').val(); if (!emptyValue.replace(/s/g, '').length) < /*Do nothing, only spaces*/ >else < $.post("/shana/?p=execPOST", $("#msgTextarea").serialize(), function(data) < >); > $('#txtArea').val(''); e.preventDefault(); > >); Cliend JavaScript RECIEVE MESSAGE:
socket.on( 'message', function( data ) < var obj = JSON.parse(data); obj.forEach(function(ob) < //Execute appends var timestamp = ob.timestamp.replace('T', ' ').replace('.000Z', ''); $('#messages').append(""+ob.username+""+timestamp+""+ob.message+""); $('#messages').append("- - - - - - - - - - - - - - - - - - - - - - - - - - -"); //ADD SCROLL TO BOTTOM $("#messages").animate(< scrollTop: $('#messages').prop("scrollHeight")>, 1000); >); >); Somehow, the binlog magic destroys the timestamp string, so to clean it up i had to replace a bit of the string itself.
PHP DB INSERT FUNCTION:
function sendMessage($msg, $col) < GLOBAL $db; $un = ""; if (!isset($_SESSION['username'])) < $un = self::generateRandomUsername(); >else < $un = $_SESSION['username']; >try < $stmt = $db->prepare('INSERT INTO chat (id, username, timestamp, message, color) VALUES (null, :un, NOW(), :msg, :col)'); $stmt->bindParam(':un', $un, PDO::PARAM_STR); $stmt->bindValue(':msg', strip_tags(stripslashes($msg)), PDO::PARAM_LOB); //Stripslashes cuz it saved \ to the DB before quotes, strip_tags to prevent malicious scripts. TODO: Whitelist some tags. $stmt->bindParam(':col', $col, PDO::PARAM_STR); > catch (Exception $e) < var_dump($e->getMessage()); > $stmt->execute(); >
I hope this helps someone at least a bit. Feel free to use this code, as i probably copied most of it from the internet already anyway 🙂 I will be checking this thread from time to time, so if you have any questions leave a comment.
Uncaught mysqli_sql_exception: Permission denied
Had problem initially because mysqli had not been installed on the web server but I dept have done this now.
I added extension = mysqli into the php.ini file (etc/php.ini) and restarted the httpd service via webmin interface Bootup and Shutdown
My connection returns an error message:
Fatal error : Uncaught mysqli_sql_exception: Permission denied in /var/www/html/NCR/Hello.php:10 Stack trace: #0 /var/www/html/NCR/Hello.php(10): mysqli_connect() #1 thrown in /var/www/html/NCR/Hello.php on line 10
ini_set("display_errors", 1); error_reporting(E_ALL); echo 'Hello There! - It is ' . date('H:i, jS F Y') . '
'; $conn = mysqli_connect('remoteip', 'user', 'password', 'dbname'); if (mysqli_connect_errno()) echo "Couldn't make the connection: " . mysqli_connect_error(); exit; > else echo "Hurrah!"; > ?>
the connection criteria I use in the mysqli_connect() are correct and the remote server is configured to accept remote connections and the user has the correct privileges to access tables on the remote server.
I don’t see the «Couldn’t make connection» message so I’m not sure I get as far as connection being attempted let alone failing.
Please, is that assessment correct? How do I resolve this?
yes you do reach the server. at least if remoteip is not ‘localhost’ this is for sure.
i think your credentials are ok as well but you can confirm this by sticking a wrong password voluntarily.
most likely, this user has no privileges on dbname from the ip of the web server
@skullnobrains
As you suggested I used a deliberately wrong password and got no error message at all — will look at the user privileges on the remote server.
i would expect a different error rather than no error at all. the point of the wrong password is to check the error message is different.
. i guess you did not see hurrah either
likewise, you may try the correct password without specifying the db so you differentiate between connection errors and using a db you have no privileges on.
you can also run «show grants» after a successful connection to check live credentials.
can you confirm remoteip is actually an ip or a host name that is not localhost in your code ?
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
@skullnobrains is likely on the right track regarding permissions or passwords.
As of PHP 8.1, the mechanism for handling errors changed for MySQLi. Previously, errors were just silenced, and you’d then check the mysqli_connect_errno() to see what went wrong. You could override this behaviour and turn errors into Exceptions if you wanted.
Since PHP 8.1, Exceptions are the default way of error handling — this is why your error message shows «Uncaught mysqli_sql_exception», and dies without ever reaching your «Couldn’t make the connection» line. An Exception is thrown (instead of an error) and you never catch it (uncaught!)
Exceptions are now the recommended way of handling errors:
try $conn = mysqli_connect('remoteip', 'user', 'password', 'dbname'); echo "Hurrah!"; > catch (mysqli_sql_exception $e) die("Couldn't make the connection: " . $e->getMessage()); >
i do not really get why an error would be thrown in one case and not the other. error handling is supposed to be consistent whatever the error. i believe something else might have changed between tests, or maybe the authors second test actually ended up in a parse error and the web server config is set to hide said errors and display a blank page.
it might be a good thing to display_startup_errors as well or call the file from the command line which defaults to print such errors.
Since (A) you’re using Apache and (B) you’ve got an IT team that’s handling / enabling pieces of the architecture at a time, and (C) you haven’t had a successful connection at all yet.
I would suggest you ask your IT team to check if SELinux is enabled on the server (my guess is it is). Think of SELinux as a tiny security guard working inside the kernel / brain of your Linux installation. It can allow or block things to help toughen up your server security.
If SELinux is enabled and you’re using mod_php (a common way for PHP to be coupled with Apache) then you’ll need to ask the team to make sure the httpd_can_network_connect flag is enabled in SELinux.
Otherwise, any socket activity from PHP (such as your DB connection attempt) is going to originate from Apache and will get blocked with a generic «Permission denied» error.
They can also just temporarily disable SELinux altogether to see if it’s playing any role in the problem (you might try this if you’re using PHP-FPM instead of mod_php). However, don’t just permanently disable it — if it is a root cause then the right thing to do is simply configure it correctly.
Oh, and I presume your IT team should know the commands already, but just in case, to enable the flag (prepend sudo as necessary):
setsebool -P httpd_can_network_connect 1
Or to disable SELinux altogether:
So I used Chris Stanyon’s suggestion to try and catch any exception(s), this approach returns the message:
Couldn’t make the connection: Permission denied
The message is returned when the password is deliberately incorrect and when the password is correct and no database is specified as per skullnobrains suggestion.
I started with Experts Exchange in 2004 and it’s been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
GET A PERSONALIZED SOLUTION
> why they didn’t do that in the first place I don’t know
Because that’s not the right solution to the problem. It’s like saying, «I can’t get my car to start to go to work, so I’m just going to move into the office and then I won’t have to drive.»
I mean, yeah, that kind of eliminates the need to solve that particular problem, but it didn’t actually solve the problem and creates other impacts that you might not want.
It’s one thing if all you have is one server, so the database and the web server HAVE to live on the same hardware and share resources. But clearly you had separate servers before, which is the better architecture. You don’t WANT both services to have to share RAM and disk space and disk I/O and security permissions and so on. The way they had it on separate servers allows that separation of resources and security, which is a very good thing.
This problem has a better solution if you stick with it for a bit longer. Bear in mind that at some point, you’ll likely need to connect out to another server, and if you haven’t solved the underlying root cause, then this problem will just come up again in another situation. It might be a cURL call that fails instead of the database connection. Or if the server gets too busy, you might have to separate out the servers anyway and then you’re back to square one.
What they should have done:
1. First check out SELinux.
2. If SELinux wasn’t the issue, the next step would be to enable debug logging on MySQL and verify the connection attempt was actually reaching the server.
3. If it was reaching the server, then the final step would be to check to ensure proper permissions (sometimes the user permissions in MySQL are not as straightforward as they seem).
