A plea to PHP developers.

This is a plea to PHP developers. Perl and Python web developers should heed similar advice because this really applies to MySQL development, and not PHP.

Please use mysql_connect() with no arguments. This causes it to get the database settings from the php.ini file, or perhaps, the my.cnf file.

What you are doing by letting the user enter a username, a password, and a host is almost certainly wrong, and if you read on I’ll try and explain why.

  1. The default on UNIX is to use a UNIX-domain socket: That’s part of the filesystem. On Debian this is in /var/run/mysqld/mysqld.sock and while there’s a certain magic in mysql that has led many PHP developers into thinking their script is working, it quite obviously breaks down when there are multiple MySQL installs on the same machine.
  2. The default is to use the information in the my.cnf file (or the php.ini file): This is a smaller part; hosting providers and network administrators can preload these with useful configurations. Using the defaults means your application will “just work”.
  3. TCP networking is a security risk: If an attacker can break your filesystem security, they can also replace your files and get your database username and password anyway. Enabling TCP networking just means that an attacker doesn’t have to do that anymore and can attack your database without having to also break your site.
  4. Passwords are a security risk: If you use a password, your password can be stolen. If no password will allow access to the database (say, using ssh-keys) then an attacker simply cannot get in.

Many apps ask for a username, a password, a host, and sometimes a port number. Unfortunately, there’s no way to enter the path to a UNIX domain socket which is required for a default build of MySQL that is simply installed in a nonstandard location.

None of these questions are required, of course, they’re useful if php.ini can’t be edited (ours can), or if .my.cnf can’t be edited or is wrong (ours isn’t), but this is an edge case- where your user is on a hosting provider which ships bad configuration by default.

If you can’t be bothered to trust the defaults, at least let the user pick a UNIX-domain socket, and ideally, get the information from the .my.cnf file likely in the user’s home directory. Do not require a hostname, a port number, or a password. Our systems don’t have any of these things, and hosting providers shouldn’t require any of these things.

I said PHP isn’t the problem; Perl has a similar problem, but it’s compounded somewhat. DBD::mysql doesn’t read the .my.cnf file by default. While in PHP I recommend that you do less work to make your users happier, on Perl, you have to do more work. In perl do something like this:

$dbh = DBI->connect("DBI:mysql:$db;mysql_read_default_file=$ENV{HOME}/.my.cnf");

…or better still, let your users specify their own DSN. It’s designed to be human-editable, and you’d be surprised just how little sugar-coating your users actually require.

5 Comments

  1. Tim says:

    Any php developers using PDO instead of mysql_connect can use:

    $conn = new PDO(‘mysql’,null,null,array(PDO::MYSQL_ATTR_READ_DEFAULT_GROUP => true))

    or

    $conn = new PDO(‘mysql’,null,null,array(PDO::MYSQL_ATTR_READ_DEFAULT_FILE => ‘/path/filename.cnf’)

  2. Paul says:

    So…..if I were to listen to you would that mean I would have to stop using phpMyAdmin because I would need to have a password on the sql server.(unless I had a bunch of whitelisted ips of course, which would quickly become very cumbersome)

    Also wouldn’t this become problematic with scaling?

    I could be wrong about all of this, if so please correct me.

  3. geocar says:

    Paul: The choice is between managing usernames, passwords, an IP whitelist, and a TCP-enabled mysql, versus using a unix-domain socket, and mysql’s default configuration.

    Your phpMyAdmin is already a security risk if it knows your SQL server’s passwords, but more importantly, any bug in any PHP application you’ve got is already a security risk. If they can be coerced into reading files, your passwords and databases are leaked. Using unix-domain sockets means there are no passwords to leak- your other applications have to be coerced into running programs in order to be a security risk, and that hasn’t changed.

    Your php application only supports a single MySQL database. If you require that it be remote (or accessed via TCP) then the mysql query and response packets have to be put onto an ethernet wire- something that certainly runs much slower than just shuffling it around in memory (as unix-domain sockets do). Scaling just doesn’t enter into it.

  4. geocar says:

    Tim: Good tip. I’d like to see PDO-based applications do this by default, or offer me an easy flag to get this functionality.

  5. [...] “pre-populate it” with hostnames usernames and passwords, but the fact is you’re perpetuating a huge security risk, and there’s just no need for it. The DSN is designed to be edited by people, not you, not [...]

Leave a Reply