2

I have a CakePHP database.php config file that I'd like to leverage to open a command line mysql client instance.

The goal would be to be able to run a shell script like ./db-login.sh from the project root and have it pull the database details (host, port, database name, username, password) from the PHP config file and pass them to the mysql command line as arguments. This is to avoid having to enter the details every time.

I understand it's possible to create a shell alias that has the values hard-coded: I would like a portable script that could be included with any of my Cake projects. I also would like to keep the task of getting the DB credentials into bash variables separate from launching the mysql client. This would open up the ability to re-use the DB credentials in other shell scripts easily (such as a mysqldump backup script.)

Here's what I have so far:

database.php

Consider this file immutable for the purposes of this question. It must exist exactly as you see it.

<?php
class DATABASE_CONFIG {
    public $default = array(
        'host'        => 'localhost',
        'login'       => 'cakephpuser',
        'password'    => 'c4k3 roxx!',
        'database'    => 'my_cakephp_project',
    );
}

db-cred.sh

Acts as middleware to convert the PHP variables into bash (friendly) variables.

#!/usr/bin/env php
<?php
include 'database.php';
$db = new DATABASE_CONFIG(); 

// Selectively wrap quotes. Has no real effect(?)
$p = (!empty($db->default['password']) ? "\"$db->default['password']\"" : '');

echo <<<EOD
DB_HOST="{$db->default['host']}"
DB_NAME="{$db->default['database']}"
DB_USER="{$db->default['login']}"
DB_PASS={$p}
EOD;

db-login.sh

#!/usr/bin/env bash
# Uses Cake's database.php file to log into mysql on the 
# command line with the correct arguments.

# Holy yuck, but nothing else works!
eval $( db-cred.sh )

# Try to set an appropriate password clause.
PATTERN=" |'"
if [ -n "$DB_PASS" ]; then
    if [[ $DB_PASS =~ $PATTERN ]]; then
        PASS_CLAUSE=" -p'${DB_PASS}'"
    else
        PASS_CLAUSE=" -p${DB_PASS}"
    fi
else
    PASS_CLAUSE=""
fi

# Get a rough look at what we're about to run.
echo mysql --host=${DB_HOST} --database=${DB_NAME} --user=${DB_USER}${PASS_CLAUSE}

# Call MySQL.
mysql --host=${DB_HOST} --database=${DB_NAME} --user=${DB_USER}${PASS_CLAUSE}

Things that need help:

  • The db-login.sh script uses eval to suck in the variables. Yuck.
  • The MySQL password is leaked on the command line. This is not a dealbreaker, but if there's a clean/portable way to avoid it, I'm open to it.
  • Spaces and quotes in the mysql password don't get passed to bash properly.
    • A password like my Pass will cause DB_PASS to be set to my in db-login.sh.
    • Quoting the string in db-cred.sh produces "my instead.
  • Likewise, my attempts at quoting PASS_CLAUSE are not effective: Only passwords without spaces/quotes will successfully log into the mysql client.
beporter
  • 3,740
  • 3
  • 37
  • 45
  • Could you make a file that could be evaluated by both bash and PHP? Lines like `db_config('host', 'hostname')` could be made to work in both languages. – tadman May 01 '13 at 15:16
  • have your database.php echo out the variables in a comma separated list if passed a flag like ?echosh or something then you won't need db-cred.sh you can include your database.php directly into the db-login.sh then use http://stackoverflow.com/questions/10586153/bash-split-string-into-array to split the returned string into an array and you can access your array elements directly then. – Dave May 01 '13 at 15:21
  • The format of Cake's database.php config file is fixed, and that's kind of the linchpin to the whole thing. It's true that I *could* set the variables you suggest in another file and pull them into Cake's config file, but I'd have to do that for all of my Cake projects. That negates the portability and isn't any better than a shell alias with the values hardcoded. – beporter May 01 '13 at 15:23
  • @Dave - As I mentioned, database.php isn't up for change in this scenario. There needs to be another way to bridge its contents into bash vars for this to work cleanly. As far as the shebangs, feel free to make a case for a better alternative. – beporter May 01 '13 at 15:24
  • you can edit your cake database.php easily enough though and just remember to use your new one rather than their application one in future? or as you say the better idea for portability is to store your creds else where and include them into the cake config. – Dave May 01 '13 at 15:25
  • @Dave - As I've stated, neither of those choices are in fact portable. A portable solution is to be able to read the existing config without requiring modification. The goal is a script that does the work for you, not you doing work for the script. – beporter May 01 '13 at 15:26
  • The idea of portable to me is you write the script once and then can reuse it many times. By modifying your database.php directly and calling that directly you are writing once reusing many and it has no impact on the framework as a whole. otherwise as you've noted you have to have your go between layer which is quite franky nasty. Still my other line about returning a comma separated string resolves the bulk of your other issues even if you stick with your bridge bit. since shell has a split to array and splitting to array on a , will resolve teh space issue. – Dave May 01 '13 at 15:29
  • @Dave - Again, you're neglecting the fact that I'd have to modify dozens of Cake config files in dozens of repos and on dozens of servers in order for that script to be useful. This was all in my original question. There is nothing to be gained by pursing this idea further in the comments. – beporter May 01 '13 at 15:37
  • @beporter You've missed the 2nd part of my suggestion using YOUR middleware if you like your "db-cred.sh" to return a csv list rather than direct access variables which you can then just source and split into an array which you can then direct access in the bash script. Read the full last comment before argueing. – Dave May 01 '13 at 15:43
  • Ah true, apologies. Still, as @nickb has suggested, I think the crux of the issue is probably in properly quoting the password. – beporter May 01 '13 at 16:03

2 Answers2

2

  1. The db-login.sh script uses eval to suck in the variables. Yuck.

    Instead of eval, you can use the source command:

    source db-cred.sh

  2. The MySQL password is leaked on the command line. This is not a dealbreaker, but if there's a clean/portable way to avoid it, I'm open to it.

    You can just unset the MySQL password after it is used:

    unset DB_PASS

    However, you will always have this problem if you are supplied the password to MySQL via the command line. You could remove the -pPassword clause and force the user to enter the password when MySQL prompts for it, but from your use-case this does not seem feasible.

  3. Spaces and quotes in the mysql password don't get passed to bash properly.

  4. Likewise, my attempts at quoting PASS_CLAUSE are not effective: Only passwords without spaces/quotes will successfully log into the mysql client.

For 3 and 4, I believe all you need to do is properly quote your variable values.

In PHP:

echo <<<EOD
DB_HOST="{$db->default['host']}"
DB_NAME="{$db->default['database']}"
DB_USER="{$db->default['login']}"
DB_PASS="{$p}"
EOD;

Note the added quotes in DB_PASS="{$p}".

As a reminder, you can also escape the quotes in PHP with addslashes().

nickb
  • 59,313
  • 13
  • 108
  • 143
  • 1. I've tried `source`. It doesn't work because the script is actually PHP code, not bash statements (`source` ignores the #!). 2. The [mysql docs](http://dev.mysql.com/doc/refman/5.0/en/password-security-user.html) explain why `-p` is unsafe in general. It's not a matter of unsetting vars, it's exposing to logs, `ps`, etc. 4. I think you may be on the right track with `addslashes` although `escapeshellarg` may be more appropriate. I will experiment further. – beporter May 01 '13 at 16:01
  • Why don't you just call out to PHP then to execute the script? Then you can pull in the result of that to your `db-login.sh`script – nickb May 01 '13 at 16:05
  • I just tested that, but it seems source can't take I/O redirection. I think since it's a shell builtin, it flat out requires a filename as an argument. – beporter May 01 '13 at 16:08
0

Okay, so @nickb's suggestions led me down the right track with enough modifications.

Explanation

Issues arise if you try to pass the MySQL password through multiple bash variables. Here's a stripped down example:

#!/bin/bash
set -x
DB_PASS="pass with space and 'quote"
PASS_CLAUSE=" -p'$DB_PASS'"

# Doesn't work.
mysql $PASS_CLAUSE

# Works.
mysql -p"$DB_PASS"

Note that with the set -x we're able to see the commands that are actually being run. This is what helped me identify the double-escaping. The string in $DB_PASS gets re-escaped when it gets saved into $PASS_CLAUSE. Since it gets passed to MySQL with one level of escaping still in place the first connection fails, but using the original variable succeeds.

Final Scripts

db-cred.sh

#!/usr/bin/php
<?php
include 'database.php';
$db = new DATABASE_CONFIG(); 
echo <<<EOD
DB_HOST="{$db->default['host']}"
DB_NAME="{$db->default['database']}"
DB_USER="{$db->default['login']}"
DB_PASS="{$db->default['password']}"
EOD;

db-login.sh

#!/bin/bash

# Holy yuck, but nothing else works!
eval $( db-cred.sh )

CMD="mysql --host=${DB_HOST} --database=${DB_NAME} --user=${DB_USER}"
PATTERN=" |'"

if [[ ${DB_PASS} =~ ${PATTERN} ]]; then
    ${CMD} -p"${DB_PASS}"
elif [ -n "${DB_PASS}" ]; then
    ${CMD} -p${DB_PASS}
else
    ${CMD}
fi

Final Thoughts

  • I haven't specifically tested for it, but there may still be issues with passwords that contain double-quotes.
  • This could still be improved by somehow removing the need for eval. @Dave's idea about using CSV as a go between has potential, but in my tests it further complicated things by adding another level of escaping/unescaping necessary to get the variables into bash cleanly.
  • The MySQL password is still used on the command line. Writing a mysql config file, using it with --defaults-extra-file=filename and afterwards deleting it might be a way around that.
  • db-login.sh now represents a pattern for executing command lines with the database credentials and can be easily modified/extended for other tools like mysqldump.
beporter
  • 3,740
  • 3
  • 37
  • 45