Thanks for everyone's input. I know "what's right" is to refactor to DBI. I'm hoping to get away with less. Here's an attempt at code examples to give you all a better idea at how much refactoring might be involved in various designs.

I call it here: 

        $result = update_string_to_form($reportoutput, $thisformobjid,
+ 'REPORTBODY')    ;
        if (not defined $result) {
            die 'could not update report body on form';
        } else {
            if ($result != 0) {
                die 'error updating report body on form.';
            }
        }

[download]

This is update_string_to_form: 

sub update_string_to_form {
    my ($string, $formobjid, $column) = @_;
    return undef unless defined $string;      #string is required to b
+e defined, but can be ''
    return undef unless $formobjid;           #formobjid is required
    return undef unless $column;              #column is required
    unless ( grep($_ eq $column, formcolumns_from_formobjid($formobjid
+)) ){
        warn ("invalid column for form");
        return undef;
    }
    my $formtypeobjid = formtypeobjid_from_formobjid($formobjid);
    return undef unless $formtypeobjid;
    my $formtablename = formtablename_from_formtypeobjid($formtypeobji
+d);
    return undef unless $formtablename;
    my $sql = "update $formtablename set $column = '$string' where FOR
+MOBJID = '$formobjid'";
    #if ( harcommon::debug() ) {
    if ( 1 ) {
        warn '$formobjid      : ',$formobjid;
        warn '$formtypeobjid  : ',$formtypeobjid;
        warn '$formtablename  : ',$formtablename;
        warn '$string         : ',$string;
        warn '$sql            : ',$sql;
    }
    my @results = 
       harcommon::runsql( $sql, $$DBSettings{'tnsname'}, $$DBSettings{
+'dbuser'}, $$DBSettings{'dbpass'} );
    #if ( harcommon::debug() ) {
    if ( 1 ) {
       warn '@results: ',join(",",@results);
       warn 'number of results: ', scalar(@results);
    }
    if (scalar(@results) != 0) {
       my @trimmedresults = map {harcommon::trim($_)} @results;
       return @trimmedresults;
    } else {
       return 0;
    }
}

[download]
and somewhere down in harcommon::runsql, they encapsulate looking up credentials and wrapping my stuff in a sqlplus command and executing / catching results from sqlplus.

My first naive solution was to add: 

 . . .
    $string =~ s/'/quot/g;
    $string =~ s/;/semi/g;
    my $sql = "update $formtablename set $column = '$string' where FOR
+MOBJID = '$formobjid'";

 . . .

[download]
before setting $sql. FWIW, this approach is still failing (though I'm debugging and looking for dumb mistakes).

One design (moritz's suggestion, which I like) would be to use the minimum of DBI to get access to ->quote. 

 . . .
    my $dbh = DBI->connect($data_source, $username, $auth, \%attr);
    $string = $dbh->quote($string);
    my $sql = "update $formtablename set $column = '$string' where FOR
+MOBJID = '$formobjid'";
 . . .

[download]
but now I would have to un-encapsulate the looking up of $data_source, $username, $auth, etc. from the harcommon::runsql sub just to pass $sql in and let it do that again.

I suppose the next thing would be to refactor harcommon::runsqlto use DBI and abandon sqlplus entirely. That's getting to be orders of magnitude larger a problem than I thought I was working on.

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

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

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.