Beefy Boxes and Bandwidth Generously Provided by pair Networks
Clear questions and runnable code
get the best and fastest answer
 
PerlMonks  

PerlDBI and quoting

by jerryhone (Sexton)
on Feb 05, 2021 at 12:19 UTC ( #11127924=perlquestion: print w/replies, xml ) Need Help??

jerryhone has asked for the wisdom of the Perl Monks concerning the following question:

Brothers, Background - I'm using PerlDBI to interface an Oracle database with a ConnectDirect remote file transfer. It's all basically working, but here's the problem... We receive a file via connect direct of potentially large size and with embedded quotes/apostrophes etc. I'm therefore inserting the file content into a CLOB field in the database after using the PerlDBI 'quote' function on it - again, all working.
my $RawContent = dbh->quote( $filecontent, VARCHAR ); my $insert_sql = qq( INSERT INTO Received ( RawContent, Filename ) VALUES ( ?, ? ) ); my $insert_sth = $dbh->prepare($insert_sql); my $rv = $insert_sth->execute( $RawContent, $filename );
However, when I look at the data content in SQL Developer, or when I then perform a 'select' on the table to extract the data later, it has enclosing quotes i.e. I don't get " CONTENT " - I get " 'CONTENT' ", which then interferes with string matching downstream. What have I missed?

Replies are listed 'Best First'.
Re: PerlDBI and quoting
by haukex (Bishop) on Feb 05, 2021 at 12:21 UTC
    However, when I look at the data content in SQL Developer, or when I then perform a 'select' on the table to extract the data later, it has enclosing quotes i.e. I don't get " CONTENT " - I get " 'CONTENT' ", which then interferes with string matching downstream. What have I missed?

    You don't need both ->quote and placeholders (?, ?). Placeholders are The Right Way, so just use only those.

      Thanks haukex - that works nicely! I was clearly being a a bit overprotective!
        I was clearly being a a bit overprotective!

        Better than not knowing Little Bobby Tables at all! :-)

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://11127924]
Approved by haukex
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others pondering the Monastery: (3)
As of 2021-04-10 19:19 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found

    Notices?