Changing database connection details in a file using bash script

Question

I have the following database connection line in a setting file:

$database = 'mysql://custom_user:custom_password@localhost/custom_database';

and I would like to update the custom_user, custom_password and custom_database using bash script, which doesn't know their values as otherwise I would simply replace.

How can I update that line to:

$database = 'mysql://new_user:new_password@localhost/new_database';

using probably sed or awk?

EDIT: It would be nice if the answers addressed directly the question asked. Please do not assume anything about how input data is constructed as if it was not taken care of then I would definitely add that part of the problem into the question.

Ruslan's first answer in the comments was perfect and his detailed answer is just an overkill to my problem. Ed's answer also worked, but I am accepting Ruslan's answer as he was the first to suggest working code.

Again, this question was not at all about how input data like password is constructed.

could be done, but there would probably be conflicts in the shell with password specially if special characters are used. Would depend on the special chars of the password. — Jean-François Fabre, Dec 12 '16 at 13:53
can you suggest a solution version as i already know it could be done. — Nick, Dec 12 '16 at 13:58
`sed` could not predict the way the password is built. Use `/` in your password and in `sed` and you're toast. Same applies for `#`, etc. — Jean-François Fabre, Dec 12 '16 at 14:23
guys, i know it can be done, so instead of assuring and worrying about how password is built, please give me some examples. passwords are stored in variables and i am going to use them, so don't worry about how password is built. — Nick, Dec 12 '16 at 14:26
@Nick I thought you said your script doesn't know what the password is, how can you store it in a variable if you don't know what it is? — Jacek Trociński, Dec 12 '16 at 14:32
@dood my script doesn't know existing user, password and database, but of course it knows new ones. — Nick, Dec 12 '16 at 14:37
`database="'"'mysql://new_user:new_password@localhost/new_database'"'"; sed -e 's%^$\s*\$database\s*=$.*;%\1 '"$database"';%' file`? — Ruslan Osmanov, Dec 12 '16 at 15:38
Finally! Ruslan, can you add your version as an Answer and I'll accept it. Thank you! — Nick, Dec 12 '16 at 15:42
@RuslanOsmanov that would fail cryptically given various possible values of any of the "new_" replacement fields. Nick see http://stackoverflow.com/questions/29613304/is-it-possible-to-escape-regex-metacharacters-reliably-with-sed/29626460#29626460 if you really want to do this with sed but you're far better off just using a tool that can work with literal strings such as awk. — Ed Morton, Dec 12 '16 at 19:10

score 0 · Answer 1 · edited May 23 '17 at 12:30

sed is for simple substitutions on individual lines, that is all. In this case your substitution is on an individual line BUT it's not simple bacause a password, for example, can contain any characters and so then you're in sed escaping/quoting hell (see Is it possible to escape regex metacharacters reliably with sed)

Just use awk since it understands literal strings:

$ new='$database = '\''mysql://new_user:new_password@localhost/new_database'\'';' \
awk 'index($0,"$database ="){$0=sprintf("%s",ENVIRON["new"])} 1' file
$database = 'mysql://new_user:new_password@localhost/new_database';

That will work for any characters in your existing or replacements lines. Look at the difference vs the sed script suggested in the comments under your question when the new password contains a & for example:

$ new='$database = '\''mysql://new_user:foo&bar@localhost/new_database'\'';' \
awk 'index($0,"$database ="){$0=sprintf("%s",ENVIRON["new"])} 1' file
$database = 'mysql://new_user:foo&bar@localhost/new_database';

$ new='$database = '\''mysql://new_user:foo&bar@localhost/new_database'\'';' \
sed -e 's%^\(\s*\$database\s*=\).*;%\1 '"$new"';%' file
$database = 'mysql://new_user:foo$database = 'mysql://custom_user:custom_password@localhost/custom_database';bar@localhost/new_database';

Ruslan Osmanov · Accepted Answer · 2016-12-13T03:40:46.427

The expression in your configuration file looks like a PHP code, and the value for database is most likely an URI.

SED

According to RFC 3986 the special characters must be percent-encoded within URI parts. Let's use # character as a delimiter for SED substitution, as the character must be percent-encoded everywhere, except the fragment marker which will not be used in your URI, obviously.

Let's assume that the assignment is not distributed on multiple lines. Then you can run the following command to replace the old $database variable with new value:

# If you want to automate escaping, use rawurlencode() function, for example:
user=$(php -r 'echo rawurlencode($argv[1]);' 'user&#')
pass=$(php -r 'echo rawurlencode($argv[1]);' 'pass@ ')

# Make sure to escape sed-specific characters within the string, as it will be
# embedded into sed script below! Note, we use `#` as a delimiter for substitution.
database="'mysql://${user}:${pass}@localhost/new_database'"

sed -i.bak -e 's#^\([[:space:]]*\$database[[:space:]]*=\).*;#\1 '"$database"';#' \
  config.php

# With GNU sed
sed -i.bak -r 's#^(\s*\$database\s*=).*;#\1 '"$database"';#' config.php

Input

$database = 'mysql://custom_user:custom_password@localhost/custom_database';
    $database = "mysql://custom_user:Pass&word@localhost/custom_database" ;

     $database = "mysql://custom_user:Pass&word@localhost" . "/custom_" . "database" ;

Output

$database = 'mysql://user%26%23:pass%40%20@localhost/new_database';
    $database = 'mysql://user%26%23:pass%40%20@localhost/new_database';

     $database = 'mysql://user%26%23:pass%40%20@localhost/new_database';

Perl

script.pl

use strict;
use warnings;

my ($u, $p, $h, $d) = (shift, shift, shift, shift);
while (<>) {
  my $uri = sprintf("'mysql://%s:%s@%s/%s'",
    uri_escape($u),
    uri_escape($p),
    $h, $d);
  s/^(\s*\$database\s*=).*;/"$1 ${uri};"/eg;
  print;
}

Usage

perl -i.bak -MURI::Escape script.pl \
  'user&#' 'pass@ ' 'localhost' 'new_database' config.php

PHP

If my guess is right, and you are running PHP, then consider using two configuration files:

in the code repository, conf.php,
server-local, conf2.php:

The simplest implementation in PHP might look like the following.

conf.php

<?php
return [
  'db' => [
    'user' => 'default user',
    'host' => 'default host',
  ],
];

conf2.php

<?php
$default = require('conf.php');
return array_replace_recursive($default, [
  'db' => [
    'user' => 'local user',
    'host' => 'local host',
  ],
]);

test.php

<?php
$conf = require('conf2.php');
var_dump($conf);

Testing:

php test.php

Output

array(1) {
  ["db"]=>
  array(2) {
    ["user"]=>
    string(10) "local user"
    ["host"]=>
    string(10) "local host"
  }
}

Ruslan, I appreciate all the efforts you put in this detailed answers. However, I am afraid it is really an overkill fo something really simple that was asked. Please read edited part of the question. — Nick, Dec 13 '16 at 06:28

Changing database connection details in a file using bash script

2 Answers2

SED

Perl

PHP