Does MySQL / MariaDB hash database passwords for PHP (7.x) mysqli_connect?

Question

Please excuse a beginner question. Suggestions, links, and further reading are all welcome.

I'm concerned about security on web pages and looking for best-practices. When using PHP to connect to MariaDB/MySQL databases, most recommendations put the database passwords right on the PHP page (or on an included page). Is this safe? Is there a better 'best-practices'?

I've searched docs, Stack Overflow, and the interwebs with keywords like "hash passwords MySQL MariaDB PHP7" etc., but the answers are all about clients logging in to a web page, not PHP interacting directly with MariaDB. The MySQL Docs says the passwords are hashed for storage, but that doesn't help my PHP file. The PHP docs don't provide much helpful info and no real-world examples.

So, my questions:

1. How high is the risk for an HTTP user to download my source files, and see those passwords? (I realize PHP parses pages, so a typical user won't see the raw code or be able to download PHP — and that security of ssh, PHP, MariaDB, etc., is a separate question.)

2. I get that I can hash the password, but what good does this do, if the password is right there on the same page? (Or am I missing something?)

3. Is it better/safer to put the database variables in the file, or to use include("super-sensitive-info.php") and put the variables there? Can I (should I) hash or encrypt either that file or the passwords, and still make it usable? Can I (should I) hide this file, e.g. .super-sensitive-info.php), and then use server security to restrict access?

4. And, using special characters has given me trouble, e.g. $password = "pa$$w@rd"; should look like $password = "pa\$\$w\@rd";, per typical code-in-quotes practice? Or did I miss the memo that I'm not supposed to use special characters for SQL?

To create a simple example, let's say I have two files, looking something like this. Is there a better way? Or is this it?

super-sensitive-info.php

$user = "username"
$password = "password" 
// $password = "pa\$\$w\@rd"; // (e.g. if database pw is "pa$$w@rd"?
$database_name = "database_name"

// I can hash it, but this seems only useful for client logins and such,
// unless I can hash this entire file....
// $hashed_password = password_hash($password, PASSWORD_DEFAULT);

index.php

include("super-sensitive-info.php")

$db = mysqli_connect('localhost',$user,$password,$database_name)
    or die('Error connecting to MySQL server: ' . mysqli_connect_error());

$query = "SELECT * FROM episodes";

Answer 1

For some of the best practices take a look at the most used framework's code.

In short, @chris85 suggested the most important points. And usually it has the folder structure:

config/config.local.php
public/index.php

Add config/*.local.php to git ignore:

$ echo "config/*.local.php">>.gitignore

In config.php:

return [
  'db' => [
    'username' => '...',
    ...
  ]
];

In index.php:

$config = require '../config.php';
$db = DbConnect($config['db']);
echo json_encode($db->getAllEpisodes());

Serve your page from public folder:

$ php -S localhost:8000 -t ./public/

Answer 2

You are confusing the way an authenticating entity (usually a server) validates a password presented to it with the way an entity which needs to prove its credentials (usually a client) stores its password. The server, in your example, the mysql database stores passwords as hashes - so that even if the data is leaked its is impossible to determine what the original password is. An important feature is that when a client wishes to authenticate, it must present the cleartext of the password so that the server can repeat the hashing process. If the password were hashed on the client and sent to the server, then the validation would be based on an exact match between what the client presented and what was stored on the server. The hash of the plaintext has become the password - this is very insecure.

In the case of a PHP/MySQL instance, yes, your PHP needs the cleartext of the password, and needs to present it the database to authenticate. There are other approaches, but these usually only apply where you control the code at both ends of the authentication. As a result, the cleartext of the password may be more exposed than the hashed password.

You can't store the password used by PHP to connect to the database hashed.

Before considering ways to protect the cleartext, you should endeavour to minimize the contexts in which it can be used. MySQL can authenticate using x509 certificates (and this is supported on the PHP side by mysqli) but that really just amounts to a very big password. Its a good idea to configure accounts in mysql in such a way that they can only be accessed from specific addresses (or localhost in the case of a single PHP/mysql node).

GRANT ALL ON yourdb.* TO 'phpuser'@'192.168.0.%';

..but not very convenient if you don't control the infrastructure.

How high is the risk for an HTTP user to download my source files

That's difficult to quantify when you know how and by whom the infrastructure is managed. But you seem to be ignoring the other routes by which the data might be exfiltrated.

As for protecting the password....

• As others have said, if you store the password in a file, then its a good idea to make sure that the file is outside the document root.

• Naming it the file containing the password with a .php extension should mean that if the webserver does get pointed directly at the file it will attempt to execute it, rather than returning the content to the http client. While trying to hide the file is security by obscurity and therefore not a good idea, sign-posting the presence of the password is not good practice.

• in the case of Apache (and some other webservers) prefixing the filename with '.ht' will normally prevent the webserver from accessing it directly

• you can store a mysql username and password as a PHP ini setting (and therefore also in .htaccess files). This is a fairly obvious place for an attacker to look for a password - but may be appropriate in some cases.

Encrypting the password just means you need to find somewhere secure to store the encryption key instead of someplace safe to store the password.

did I miss the memo that I'm not supposed to use special characters for SQL?

Your database password does not usually appear in SQL statements (except from GRANT...IDENTIFIED BY....). Regardless you can use special characters as long as they are appropriately escaped. But special characters don't matter for a password - the reason some systems insist on them is because human beings choose passwords that are easy to guess. A natural language word has around half the entropy per character compared to a randomly generated password using the printable characters from the ASCII charset - so a 10 character random password is as strong as a 20 character one based on words. And computers are very good at remembering large amounts of data. Its good practice to use random passwords in contexts like this.

If you need to remember a password for accessing the database manually, then use a seperate account for this.

Sometimes its desirable not to keep the password on the filesystem at all - this avoids risks of the password appearing in uploads to your version control system and backups. Other places you can put the password are in the environment, or shared memory. Linux has a nice virtual HSM capability with kernel key storage but running in a shell every time you want to retrieve the password to connect to the database is rather innefficient (AFAIK there is not a PHP extension bridging the API)

Answer 3

You are missing a very important security for PHP: "binding" or "escaping" parameters that come from the user (or a hacked url) and then put into a SQL query.

Read about "SQL Injection".