#!/usr/bin/perl
##########
# Script Name: SRDbs(Search/Replace within databases)
# Description: Script to properly search/replace content within databases while accounting for serialized data.
# URL to Wiki: https://gatorwiki.hostgator.com/Migrations/Srdbs
# URL to GIT: http://git.toolbox.hostgator.com/serializer/srdbs
# Please submit all bug reports at bugs.hostgator.com
#
# (C) 2013 - HostGator.com, LLC
##########
#
# Changelog:
#
# 1.0 - initial push
# 1.1 - Added --workdir option.
# 1.2 - bug fixes
# 1.3 - Modified regex to account for drupal's serialized strings structure.
# 1.4 - Fixed the previous regex change to address issues in #14588
# 2.0 - Refactored to use the config parsers from xfermodules.pm
# 2.1 - Updated the SQL generation to escape the key and table names
#

use strict;
use Getopt::Long qw (:config pass_through);
use Term::ANSIColor;
use Cwd;
use File::Find ();
use Cwd 'abs_path';
use DBI;
use DBD::mysql;
use File::Path;

my $config;
my $search;
my $replace;
my $nobackup;
my $workdir;
my $help;
my @dbinfo;
my $within;

GetOptions ( 'ww'          => \$within,
			 'config=s'    => \$config,
			 'search=s'    => \$search,
			 'replace=s'   => \$replace,
			 'nobackup'    => \$nobackup,
			 'workdir=s'   => \$workdir,
			 'help'        => \$help,
			 'dbinfo=s{3}' => \@dbinfo
		   );

print "[*] ".colorify("S", "green").colorify("R","red").colorify("Dbs","green").colorify(" 2.1", "yellow")."\n";
print "[+] By Rish - please report bugs at https://projects.hostgator.com/projects/hg_dbserial\n\n";

if (@ARGV) {
	print "Unknown option passed. Please see --help\n";
	exit 1;
}

if ($help) {

	print "Usage: srdbs --search='string-to-search-for' --replace='string-to-replace-with'\n\n";
	print "Specify the database information with one of the following:\n";
	print "\t--config=configuration-file (if you want the script to parse the information from a configuration file) or\n";
	print "\t--dbinfo <database_name> <database_username> <database_password> (if you want to specify the database information yourself)\n";
	print "\nYou can also use the following optional switches:\n";
	print "\t--nobackup             - Bypasses the backup process.\n";
	print "\t--workdir              - Specify a directory where you wish to store the backup file.\n\n";
	if (dependency_check()) {
		my $dbc = dbconnect->new();
		$dbc->show_supported_configs();
	}
	exit 1;
}

# If a dependency is out of date or missing, then exit.
dependency_check() or exit 1;

# If the dbconnect module instantiation fails, exit now.
my $dbc = dbconnect->new() or do {
	print "[!] Failed to initialize DBConnect object - please consult an upper tier administrator or put in a bug report with all of the server details.\n";
	exit 1;
};

if (!$config && !(scalar(@dbinfo) == 3)) {
	print "[!] Missing necessary database information. Please specify:\n";
	print "\tThe config file to parse with '--config' or\n";
	print "\tThe database credentials with '--dbinfo'\n";
	print "[!] See --help for more details.\n";
	exit 1;
}

if (!$search && !$replace) {
	print "[!] The search ('$search') and replace ('$replace') strings can not be empty!\n";
	exit 1;
}

if (lc($search) eq lc($replace)) {
	print "[!] You are trying search for $search, and then replace it with itself... \n";
	exit 1;
}

main();

sub main {

	my $dbname;
	my $dbuser;
	my $dbpass;
	my $appname;

	if ($config) {
		my $filename = $config;
		$filename =~ s/.*\///;

		if ( (-s "$config") && 
		($filename eq "wp-config.php"     || $filename eq "bb-config.php" ||
		 $filename eq "configuration.php" || $filename eq "config.php"    ||
		 $filename eq "local.xml"         || $filename eq "settings.php"  ||
		 $filename eq "Settings.php"      || $filename eq "configure.php" ||
		 $filename eq "site.php"          || $filename eq "settings.inc.php"
		)) {
			my @info = $dbc->extract_db($config);
			$dbname  = trimspace($info[1]);
			$dbuser  = trimspace($info[2]);
			$dbpass  = trimspace($info[3]);
			$appname = trimspace($info[4]);
		} else {
			print "[!] Unsupported application from the looks of it, as I was unable to parse the proper information out. Sorry.\n";
			exit 1;
		}
	} elsif (scalar(@dbinfo) == 3) {

		$dbname = $dbinfo[0];
		$dbuser = $dbinfo[1];
		$dbpass = $dbinfo[2];
		$appname = "Unknown";
	}

	if (length($appname) > 0) {
		my $dsn = check_db($dbname, $dbuser, $dbpass);
		if ($dsn){
			if (not $nobackup) {
				print "[+] Successfully connected to the database for the $appname application. Backing up the '$dbname' database before making any changes to it\n";
				my $check = backup_db($dbname, $dbuser, $dbpass, $dsn);
				if (!$check) {
					print "[!] Backup process failed. Please manually backup this file or initiate the script with --nobackup if a backup has already been made\n";
					exit 1;
				}
			}
			print "[+] Starting search/replace process for ".colorify("'$search'","red")."/".colorify("'$replace'","green")." in the '$dbname' database now...\n";
			start_process($dsn);
			$dsn->disconnect();
		} else {
			print "[!] The database information does not appear to be valid. Unable to establish a connection... not proceeding.\n";
			print "Please check the following information:\n";
			print "Database name: $dbname\n";
			print "Database username: $dbuser\n";
			print "Database password: $dbpass\n";
			exit 1;
		}
	} else {
		print "[!] Unable to parse the necessary information. Bailing out....\n";
		exit 1;
	}
}

sub start_process {

	my $dsn = shift;
	$dsn->{'mysql_enable_utf8'} = 1;
	$dsn->do('SET NAMES utf8');

	my $counter = 0;

	my $sql = "SHOW TABLES";
	my $sth = $dsn->prepare($sql);
	$sth->execute;
	while (my @row = $sth->fetchrow) {
		my $table = $row[0];
		my $sqlin = "SELECT * FROM `$table`";
		my $sthin = $dsn->prepare($sqlin);
		$sthin->execute;

		while ( my $rows = $sthin->fetchrow_hashref() ){
			my $needupdate = 0;

			my $SQL1 = "UPDATE IGNORE `$table` SET ";
			my $SQL2;

			my $first = 0;
			my $second = 0;
			foreach my $key (keys %{$rows}) {

				my $value = $rows->{$key};
				if ($value =~ m/(?:(?<=http:\/\/))$search/ or $value =~ m/(?:(?<=https:\/\/))$search/ or $value =~ m/\b*$search\b*/ or ($within and $value =~ m/$search/)) {
					$counter++;
					$value = safe_replace($value);
					$needupdate = 1;
					if (!$second) {
						$SQL1 .= " `$key` =".$dsn->quote($value);
						$second = 1;
					} else {
						$SQL1 .= ", `$key` =".$dsn->quote($value);
					}
				} elsif (defined $value) {
					if (!$first) {
						$first = 1;
						$SQL2 .= " WHERE `$key` = ".$dsn->quote($value);
					} else {
						$SQL2 .= " AND `$key` = ".$dsn->quote($value);
					}
				}
			}

			if ($needupdate) {
				updatesql($SQL1.$SQL2, $dsn);
			}
		}
	}
	print "[+] ".colorify("$search","green")." was replaced in ".colorify("$counter", "green")." record(s).\n";
}

sub safe_replace {

	my $value = shift;
	$value =~ s/$search/$replace/gi;
	if (isserialized($value)){
		$value = checksdata($value);
	}
	
	return $value;
}

sub checksdata {

	my $value = shift;
	while ($value =~ m/s:(\d+):"(.*?)"(?:(?=;(?:[sabiNO}{]{1}|$)))/msg) {
		my $curlength = $1;
		my $string = $2;
		$string = $string;

		my $curstring = "s:$curlength:\"$string\";";
		if (sqllength($string) != $curlength) {
			my $newsdata = "s:".sqllength($string).":\"$string\";";
			$value =~ s/\Q$curstring\E/$newsdata/;
		}
	}

	return $value;
}

sub sqllength {

	use bytes;
	my $string = shift;
	$string =~ s/^"(.*)"$/$1/;
	return length($string);
}

sub isserialized {

	my $value = shift;
	if ($value =~ m/s:[0-9]+:.*;/){
		return 1;
	}

	return 0;
}

sub updatesql {

	my ($sql, $dsn) = @_;
	my $sth = $dsn->prepare($sql);
	$sth->execute;
}

sub check_db {

	my ($dbname, $dbuser, $dbpass) = @_;

	my $dsn = "DBI:mysql:$dbname:localhost:3306";
	my $connect = DBI->connect($dsn, $dbuser, $dbpass, { PrintError => 0 });
	if (!$connect) {
		return 0;
	} else {
		return $connect;
	}
}

sub backup_db {
	
	my $dbname = shift;
	my $dbuser = shift;
	my $dbpass = shift;
	my $dsn    = shift;

	my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime(time);
	my $date = sprintf("%4d%02d%02d",$year+1900,$mon+1,$mday);

	my $backupfile;
	if (defined $workdir and ($workdir !~ m/\/$/)) {
		$workdir = "$workdir/";
	}

	if (!$workdir) {
                $backupfile = "/home/hgtransfer/myimport-backups-$date/$dbname.sql";
        } else {
                $backupfile = $workdir."$dbname.sql";
	}

	my $dbtbls = $dsn->do('show tables;');
	chomp $dbtbls;

	if ($dbtbls == 0){
		print "[+] $dbname is empty, so skipping backup process\n";
		return 1;
	}

	my @dirs = split(/\//, $backupfile);
	pop (@dirs);
	my $backdir = join("/", @dirs);

	if (!-d "$backdir"){
		mkpath($backdir, 0, 0755) or die ("[!] Failed to create $backdir - Unable to backup Databases properly. Aborting!\n");
		if (!-d "$backdir"){
			print "[!] Failed to create $backdir - Unable to backup Databases properly. Aborting!\n";
			return 0;
		}
	}

	while (-e "$backupfile"){
                my $x = sprintf("%4d-%02d-%02d-%02d-%02d-%02d",$year+1900,$mon+1,$mday, $hour, $min, $sec);
                $backupfile =~ s|$dbname|$dbname.$x|i;
        }

	my $backup = `mysqldump -u\'$dbuser\' -p\'$dbpass\' $dbname > $backupfile 2>&1`;
	chomp $backup;

	if ($backup){
		print "[!] Backing up of $dbname failed. Errors:\n";
		print "$backup\n";
		return 0;
	} else {
		print "[+] Backup successful: $dbname to $backupfile\n";
		return 1;
	}
}

sub randoms {

	my $limit = shift;
	my $possible = 'abcdefghijkmnpqrstuvwxyz';
	my $string = "";

	while (length($string) < $limit){
		$string .= substr( $possible, ( int( rand( length($possible) ) ) ), 1 );
	}

	return $string;
}

sub trimspace {

	my $string = shift;
	chomp $string;
	$string =~ s/^\s+//;
	$string =~ s/\s+$//;
	return $string;
}

# Check for dependencies and load the dependencies.
# Input:  $plesk global variable
# Output: 1=Success, undef=Fail
sub dependency_check {

	my $no_print = shift;

	#************** Check for xfermodules ****************
	# Is xfermodules available?
	if (eval {require "/root/bin/xfermodules.pm";} ) {
		if ($xfermodules::VERSION < 1.05) {
			unless ($no_print) {
				print_warning(1) and return; 
			}
		}
	} else {
		unless ($no_print) {
			print_warning() and return;
		}
	}
	return 1;
}

sub print_warning {

	my $warn = shift;
	if ($warn) {
		print "[!] This server appears to have an outdated version of the transfers modules.\n";
	} else {
		print "[!] The transfers modules do not appear to be on this server.\n";
	}

	print "[*] Please install the latest version. If this is a shared/reseller server, have a level 2 admin install them for you.\n";
	print "[+] Install with: ".colorify("GET http://git.hgfix.net/migrationsmodule/xfermodules.pm > /root/bin/xfermodules.pm\n", "YELLOW");
}

sub colorify {

	my $string = shift;
	my $color = shift;
	return color("$color").$string.color("reset");
}
