Temporary string substitution

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

Dear Monks,

I recently encountered the following sort of problem: a SQL statement is assigned to a scalar variable as,

<code style="font-size:100%"> $SQLstring = qq{ select rtrim('$lastname',' ')LAST from SYS.DUAL }; </code>

which works okay unless the last name is something like O'Reilly. I believe that the SQL statement is okay if the lone apostrophe is doubled (but not a double quote). My solution then is this,

<code style="font-size:100%"> ($tmp = $lastname) =~ s/'/''/;
$SQLstring = qq{ select rtrim('$tmp',' ')LAST from SYS.DUAL }; </code>

It doesn't permanently change <code style="font-size:100%">$lastname</code>, and it leaves the original code relatively intact. It just doesn't seem like the best way to solve the problem though. Are there other ways to temporarily alter <code style="font-size:100%">$lastname</code>?

PCS

Comment on Temporary string substitution

Replies are listed 'Best First'.
(crazyinsomniac) Re: Temporary string substitution by crazyinsomniac (Prior) on Nov 10, 2001 at 10:52 UTC
You're using DBI right? you need to use placeholders or the quote method. if you do a search on DBI Placeholders here on perlmonks you can find out how. or you can just escape the quotes like $var =~ s/'/\\'/g; ___crazyinsomniac_______________________________________ `Disclaimer: Don't blame. It came from inside the void` `perl -e "$q=$_;map({chr unpack qq;H;,$_}split(q;;,qH*));print;$q/$q;"`	[reply]
Re: Temporary string substitution by blakem (Monsignor) on Nov 10, 2001 at 10:57 UTC
From `% perldoc DBI` `$sql = sprintf "SELECT foo FROM bar WHERE baz = %s", $dbh->quote("Don' +t");` [download] I usually use a slight variation on this.... `$sql = "SELECT foo FROM bar WHERE baz = " . $dbh->quote("Don't");` [download] -Blake	[reply] [d/l] [select]
Re: Temporary string substitution by webengr (Pilgrim) on Nov 11, 2001 at 02:27 UTC
Good pointers on the DBI module... that is probably the best approach within a SQL context. I wasn't originally thinking along those lines because I am looking for a more generic solution, beyond just SQL statements. So, given a scalar, is there a simple way to access a regex substitution on it without changing its value and without requiring the use of a temporary variable? PCS	[reply]