in reply to Re: untainting or encoding for shelled sqlplus update
in thread untainting or encoding for shelled sqlplus update

Using DBI makes good sense. I don't use Oracle so can't test however according to this and other Google hits something like this may work. You may need to send a "set escape \" command to SQLPLUS too. 

my @res_word = qw( ABOUT ACCUM AND BT BTG BTI BTP FUZZY HASPATH 
                   INPATH MINUS NEAR NOT NT NTG NTI NTP OR PT RT 
                   SQE SYN TR TRSYN TT WITHIN );
my @res_char = qw( , & ? { } \ ( ) [ ] - ; ~ | $ ! > * % _ );
my $rw = join '|', @res_word;
$rw = qr/$rw/;
my $rc = join '', map{"\\$_"}@res_char;
$rc = qr/[$rc]/;

sub escape {
    my $str = shift;
    $str =~ s/($rc)/\\$1/g;  # reserved char escapes
    $str =~ s/($rw)/{$1}/g;  # reserved word escapes
    $str =~ s/(['"])/$1$1/g; # quote escapes
  return $str;
}

[download]

Replies are listed 'Best First'.
Re^3: untainting or encoding for shelled sqlplus update
by goibhniu (Hermit) on May 15, 2008 at 21:15 UTC

    ++ I obviously don't work directly with the DB enough either, but your example and reference give me a more exhaustive list of chars, etc. to untaint. That may do just the trick for now (though ikegami's comment convinces me that using the DBI quote function is the right thing).

    #my sig used to say 'I humbly seek wisdom. '. Now it says:
    use strict;
    use warnings;
    I humbly seek wisdom.
[reply]

      While a quick kludge may fix your problem with minimal effort using the bulk loader functionality that comes bundled with oracle makes better sense (speed and correct quoting) than using DBI to me.

[reply]

        There's no bulk in this case; it's one record at a time. Does that change your answer or is there some clever design you're thinking of (I love cleverness)?

        #my sig used to say 'I humbly seek wisdom. '. Now it says:
        use strict;
        use warnings;
        I humbly seek wisdom.
[reply]

In Section Seekers of Perl Wisdom