Re: Masking data in a MySQL dump file. Ideas?

[choroba]

Just write a parser for the input lines. For example, the following works for your sample input mentioned in a reply, and uses Marpa::R2 to parse the SQL:

#!/usr/bin/perl
use warnings;
use strict;
use feature qw{ say };

use Marpa::R2;


my $line = q{INSERT INTO sample_table (first_name,last_name) VALUES ('
+John','Doe'),('John','Smith'),('Foo','O\'Bar'),('Baz','D\'Qux (II)'),
+('(and so on, ','and so forth);');};

my $dsl = << '__DSL__';
:default ::= action => [name,values]
lexeme default = latm => 1

Statement       ::= (insert_into space) table_name (MaybeSpace)
                    Columns (MaybeSpace values MaybeSpace)
                    Values (semicolon)             action => [values]
Columns         ::= (l_par) ColumnList (r_par)     action => ::first
ColumnList      ::= column_name+
                    separator => comma             action => [values]
MaybeSpace      ::= (space) | (empty)
Values          ::= ValueList                      action => ::first
ValueList       ::= Value+                         action => [values]
                    separator => comma
Value           ::= (l_par) Strings (r_par)        action => ::first
Strings         ::= String+
                    separator => comma             action => [values]
String          ::= (quote) Chars (quote)          action => ::first
Chars           ::= Char+                          action => concat
Char            ::= literal                        action => ::first
                  | backslashed_quote              action => bsq

table_name        ~ [\w]+
column_name       ~ [\w]+
insert_into       ~ 'INSERT INTO'
values            ~ 'VALUES'
semicolon         ~ ';'
r_par             ~ ')'
l_par             ~ '('
comma             ~ ','
space             ~ [\s]+
empty             ~ [^\s\S]
quote             ~ [']
literal           ~ [^'\x5c]
backslashed_quote ~ [\x5c][']

__DSL__
sub concat { shift; join q(), @_ }
sub bsq { q(') }

my $grammar = 'Marpa::R2::Scanless::G'->new({ source => \$dsl });
my $value = $grammar->parse(\$line, { semantics_package => 'main' });

my ($table, $columns, $values) = @$$value;

sub anonymise {
    my @names = @_;
    $names[0] =~ s/John/Sebastian/;
    return @names
}

sub quote {
    my @names = @_;
    s/'/\\'/g, s/^/'/, s/$/'/ for @names;
    substr $names[0], 0, 0, '(';
    $names[-1] .= ')';
    return @names
}

say join ' ', 'INSERT INTO', $table, '(', join(',', @$columns), ')',
              'VALUES', join(',', map quote(anonymise(@$_)), @$values)
+, ';';