Replace domain name in Wordpress database dump

USAGE.md

wp-domain-change

Simple script to replace domain name in a Wordpress MySQL dump file. In addition to replacing domain names in standard string values, the script is able to correctly replace strings in serialized PHP values. This means that it should be safe to operate on data from wp_options table where plugins store data as serialized PHP

Usage

Run the script from command line. First argument is the domain name to replace and the second is the replacement. SQL dump is read from stdin and written to stdout

node wp-domain-change.js "http://source.domain" "http://dest.domain" < input.sql > output.sql

wp-domain-change.js

#!/bin/env node

/* eslint-env es6 */

// USAGE:
//   node wp-domain-change.js "http://source.domain" "http://dest.domain" < input.sql > output.sql

'use strict';

const Transform = require('stream').Transform;
const updateSerialized = (str, replace, callback) => {
    let i = 0;
    let state = 'NONE';

    let key = '';
    let partlen = '';
    let partval = '';
    let expected = 0;
    let startpos = 0;
    let endpos = 0;
    let result = '';

    let replacements = [];

    let iterate = () => {
        for (; i < str.length; i++) {
            let c = str.charAt(i);
            switch (state) {
                case 'NONE':
                    key = c;
                    state = 'KEY';
                    startpos = i;
                    break;
                case 'KEY':
                    switch (c) {
                        case ':':
                            state = 'NUM';
                            partlen = '';
                            break;
                        default:
                            key += c;
                    }
                    break;
                case 'NUM':
                    if (/[0-9\.]/.test(c)) {
                        partlen += c;
                    } else if (c === ':') {
                        state = 'VALUE';
                    } else if (c === ';') {
                        state = 'NONE';
                    } else {
                        // seems to be something else than lenght
                        state = 'NONE';
                    }
                    break;
                case 'VALUE':
                    if (c === '"') {
                        // string value
                        state = 'STRING';
                        expected = Number(partlen);
                    } else if (c === '{') {
                        state = 'NONE';
                    } else if (c === '}') {
                        state = 'NONE';
                    } else if (c === ';') {
                        state = 'NONE';
                    } else {
                        // no idea?
                        state = 'NONE';
                    }
                    break;
                case 'STRING':
                    if (partval.length < expected) {
                        partval += c;
                    } else if (c !== '"') {
                        partval += c;
                    } else {
                        if (/^\\0/.test(partval) || key !== 's') {
                            // special string, ignore
                            state = 'VALUE';
                            partval = '';
                        } else {
                            endpos = ++i;
                            return replace(Buffer.from(partval, 'binary'), (err, replacement) => {
                                if (err) {
                                    return callback(err);
                                }

                                if (typeof replacement !== 'string') {
                                    replacement = replacement.toString('binary');
                                }

                                if (replacement !== partval) {
                                    // replace only strings that changed
                                    replacements.push({
                                        startpos,
                                        endpos,
                                        replacement
                                    });
                                }

                                state = 'VALUE';
                                partval = '';

                                iterate();
                            });
                        }

                    }
                    break;
            }
        }

        replacements.reverse().forEach(replacement => {
            let prefix = str.substr(0, replacement.startpos);
            let suffix = str.substr(replacement.endpos);
            str = prefix + 's:' + replacement.replacement.length + ':"' + replacement.replacement + '"' + suffix;
        });
        return callback(null, str);
    };
    iterate();
};

class SQLReplace extends Transform {
    constructor(replace) {
        super();
        this.replace = replace;

        this.state = 'NORMAL';
        this.terminator = false;
        this.terminatorMatch = 0;
        this.escape = false;
        this.lastChar = false;

        this.remainder = '';
        this.encoding = false;
    }

    _transform(chunk, encoding, callback) {
        let i = 0;
        let len = chunk.length
        let startpos = 0;

        let iterate = () => {
            for (; i < len; i++) {
                let c = chunk[i];

                switch (this.state) {
                    case 'NORMAL':
                        {
                            switch (c) {
                                case 0x23 /* # */ :
                                    this.state = 'COMMENT';
                                    this.terminator = Buffer.from('\n');
                                    break;
                                case 0x2D /* - */ :
                                    if (this.lastChar === 0x2D /* - */ ) {
                                        this.state = 'COMMENT';
                                        this.terminator = Buffer.from('\n');
                                    }
                                    break;
                                case 0x2A /* * */ :
                                    if (this.lastChar === 0x2F /* / */ ) {
                                        this.state = 'COMMENT';
                                        this.terminator = Buffer.from('*/');
                                    }
                                    break;
                                case 0x22 /* " */ :
                                case 0x27 /* ' */ :
                                    this.remainder = '';
                                    this.state = 'STRING';
                                    this.encoding = this.lastChar === 0x78 /* x */ || this.lastChar === 0x58 /* X */ ? 'hex' : 'binary';
                                    this.terminator = c;
                                    this.push(chunk.slice(startpos, i + 1));
                                    break;
                            }
                            break;
                        }
                    case 'COMMENT':
                        {
                            if (c === this.terminator[this.terminatorMatch]) {
                                this.terminatorMatch++;
                                if (this.terminatorMatch === this.terminator.length) {
                                    this.state = 'NORMAL';
                                }
                            } else if (this.terminatorMatch) {
                                this.terminatorMatch = 0;
                            }
                            break;
                        }
                    case 'STRING':
                        {
                            if (this.escape) {
                                // rewrite escaped char
                                switch (c) {
                                    case 0x30 /* 0 */ :
                                        c = 0x00;
                                        break;
                                    case 0x62 /* b */ :
                                        c = 0x08;
                                        break;
                                    case 0x6E /* n */ :
                                        c = 0x0A;
                                        break;
                                    case 0x72 /* r */ :
                                        c = 0x0D;
                                        break;
                                    case 0x74 /* t */ :
                                        c = 0x09;
                                        break;
                                    case 0x5A /* Z */ :
                                        c = 0x1A;
                                        break;
                                }

                                this.escape = false;
                                this.remainder += String.fromCharCode(c);
                                break;
                            }

                            switch (c) {
                                case 0x5C /* \ */ :
                                    this.escape = true;
                                    break;
                                case this.terminator:
                                    let buf = Buffer.from(this.remainder, this.encoding);
                                    this.state = 'NORMAL';
                                    startpos = i++;
                                    this.lastChar = c;

                                    return this.replaceBuffer(buf, (err, replacement) => {
                                        if (err) {
                                            return callback(err);
                                        }

                                        if (typeof replacement === 'string') {
                                            replacement = Buffer.from(replacement, 'binary');
                                        }

                                        if (this.encoding === 'hex') {
                                            this.push(Buffer.from(replacement.toString('hex')));
                                        } else {
                                            let regex = this.terminator === 0x27 ? /[\\']/g : /[\\"]/g;

                                            replacement = replacement.toString('binary').
                                            replace(/([\\'"])/g, '\\$1').
                                            replace(/\n/g, '\\n').
                                            replace(/\r/g, '\\r').
                                                //replace(/\t/g, '\\t').
                                            replace(/\x08/g, '\\b').
                                            replace(/\x1A/g, '\\Z').
                                            replace(/\x00/g, '\\0');

                                            this.push(Buffer.from(replacement, 'binary'));
                                        }
                                        iterate();
                                    });
                                default:
                                    this.remainder += String.fromCharCode(c);
                            }
                        }
                }

                this.lastChar = c;
            }

            if (this.state !== 'STRING') {
                this.push(chunk.slice(startpos));
            }
            callback();
        }

        iterate();
    }

    _flush(callback) {
        return callback();
    }

    replaceBuffer(buf, callback) {
        // does it look like a serialized php string?
        if (
            buf.length >= 3 &&
            (
                [0x62, 0x69, 0x64, 0x73, 0x61, 0x4f, 0x52, 0x72].includes(buf[0]) /* bidsaORr */ && buf[1] === 0x3A /* :*/ && buf[2] >= 0x30 /* 0 */ && buf[2] <= 0x39 /* 9 */
            ) ||
            (
                buf[0] === 0x4E /* N */ && buf[1] == 0x3B /* ; */
            )
        ) {
            return updateSerialized(buf.toString('binary'), this.replace, callback);
        } else {
            this.replace(buf, callback);
        }
    }
}

let sourceDomain = process.argv[2] || '';
let destDomain = process.argv[3] || '';

let replacer = new SQLReplace((buf, callback) => {
    let regex = new RegExp('\\b' + sourceDomain.replace(/[\-\[\]\/\{\}\(\)\*\+\?\.\\\^\$\|]/g, "\\$&") + '\\b', 'gi');
    return callback(null, buf.toString('binary').replace(regex, destDomain));
});

process.stdin.pipe(replacer).pipe(process.stdout);
process.stdin.resume();

No worries - I'm a little bit of a node 'noob' - the mysqldump was very large, so just added the --stack-size parameter to node