sql server error revealed login details!

[ohno]

Our hosts had an SQL server error which required the service to be restarted. However, while the site was down the error message gave the DB name, login & password!

Fatal error: Uncaught mysqli_sql_exception: Connection refused in /home/domain/public_html/include/database.php:9 Stack trace: #0 /home/domain/public_html/include/database.php(9): mysqli->__construct('127.0.0.1', 'domain_DB, 'PASSWORD!!!', 'DB_USER_NAME!!!') #1 /home/domain/public_html/include/app_top.php(12): DbManager->__construct() #2 /home/domain/public_html/index.php(4): require_once('/home/domain/...') #3 {main} thrown in /home/domain/public_html/include/database.php on line 9

database.php :-

 

function __construct() {
			mysqli_report(MYSQLI_REPORT_ERROR|MYSQLI_REPORT_STRICT);
			$this->db = new mysqli(DB_SERVER, DB_USERNAME, DB_PASSWORD, DB_DATABASE);
			if ($this->db->connect_errno) {
				die("Failed to connect to MySQL: (" . $this->db->connect_errno . ") " . $this->db->connect_error);
			}

Line 9 is $this->db = new mysqli(DB_SERVER, DB_USERNAME, DB_PASSWORD, DB_DATABASE);

Looking at this guide :-

https://www.w3schools.com/php/func_mysqli_error.asp

 

Should the code look like this??

 

function __construct() {
			mysqli_report(MYSQLI_REPORT_ERROR|MYSQLI_REPORT_STRICT);
			$this->db = new mysqli(DB_SERVER, DB_USERNAME, DB_PASSWORD, DB_DATABASE);
			if ($this->db->connect_errno) {
				echo "Failed to connect to MySQL: " . mysqli_connect_error();
  exit();
			}

Thanks

[mac_gyver]

on a live/public server, you should log all php errors, which since you are using exceptions for the mysqli statement errors, will  include the database statement errors.

also, since you are using exceptions for mysqli statement errors, there's no point in having discrete logic to test for errors. that logic won't ever get executed upon an error, since execution transfers to the nearest correct type of exception handling, which will be php in the case of the code you posted. remove any such discrete error handling logic, simplifying your code.

you should only display all php errors, when learning, developing, and debugging code/query(ies).

put any php error related settings in the php.ini on your system, so that they can be changed at a single point.

[ohno]

Thanks, currently PHP is set to display errors (E_ALL & ~E_NOTICE), are you saying that should be disabled?

 

I'm not a coder (!) but a pro coder did write the code above.

[mac_gyver]

1 minute ago, ohno said:

currently PHP is set to display errors (E_ALL & ~E_NOTICE), are you saying that should be disabled?

php's error_reporting setting should always be set to E_ALL (or even better a -1 since php has been confused about what the word all means.)

on a development system, the display_errors setting should be set to ON. on a live/public server, display_errors should be set to OFF and log_errors should be set to ON.

 

[ohno]

OK, that's done. Would that now avoid the issue that the SQL server problem caused?

[mac_gyver]

3 minutes ago, ohno said:

Would that now avoid the issue that the SQL server problem caused?

yes. if you have access to the database server, you can temporarily stop it to test what the result will be. if you don't have access to the database server, temporarily introduce a typo mistake in the DB_SERVER value to test what the result will be.

[ohno]

Ok, I got a white screen which I guess was expected? If I comment out this line : -

mysqli_report(MYSQLI_REPORT_ERROR|MYSQLI_REPORT_STRICT);

I get this error : -

Failed to connect to MySQL: (2002) php_network_getaddresses: getaddrinfo failed: Name or service not known

Which seems more helpful? (ie, if we have another SQL server error at least we know what the issue is without checking error logging??).

 

 

[mac_gyver]

4 minutes ago, ohno said:

I got a white screen which I guess was expected?

if you successfully set the php error related settings to the stated values, you should have gotten a http 500 error page. either they didn't get set to those values or you have settings in your code that are changing the values.

6 minutes ago, ohno said:

Which seems more helpful?

a visitor to your site doesn't need to know anything about why a web page is not working and if you let a hacker know anything about what type of error occurred, they will just do more of the same to trigger more errors. you are logging the raw php/database error information on a live/public site so that you, the programmer/developer, will know what type of errors are occurring, so that you can find and fix what's causing them, or in the case of a database server not running, why the site was temporarily not working.

[ohno]

Hmmm, the settings are deffo correct so I guess there is more to look into as I didn't get a 500 error. Good point on the other message.

[mac_gyver]

you can use a phpinfo() statement in a .php script to check what the master and local values are for those settings.

[ohno]

I've sorted this now so if there is an SQL error a nice error page is displayed informing the user the site is experiencing a technical issue and will be online again soon.

This got me thinking, is there an easy way to display a site maintenance page?

So if a DB entry is set to true all requests get redirected to one page?

I found this :-https://rimuhosting.com/knowledgebase/creating-a-maintenance-page-for-your-site

But I'm not sure how to make a PHP script create or delete the maintenance.enable file??

Or is there a better way to achieve this?

Thanks.

 

Edited August 16, 2021 by ohno

[ohno]

OK, so I made a test script that creates a file and deletes it for the above htaccess code :-

RewriteEngine On
RewriteCond %{DOCUMENT_ROOT}/site-error.php -f
RewriteCond %{DOCUMENT_ROOT}/maintenance.enable -f
RewriteCond %{SCRIPT_FILENAME} !site-error.php
RewriteRule ^.*$ /site-error.php [R=503,L]
ErrorDocument 503 /site-error.php

The script that makes/deletes the file works fine, what I need to work out now is how to NOT include my admin directory in the above code, otherwise I can't re-enable the site as the whole site shows the error page!

 

ie, I need to be able to still access /admin and all sub directories in it.

 

[ohno]

Sorted it, I think!

RewriteEngine On
RewriteCond %{REQUEST_URI} !^/admin/
RewriteCond %{DOCUMENT_ROOT}/site-error.php -f
RewriteCond %{DOCUMENT_ROOT}/admin/maintenance.enable -f
RewriteCond %{SCRIPT_FILENAME} !site-error.php
RewriteRule .*$ /site-error.php [R=503,L]
ErrorDocument 503 /site-error.php