Finding a good routine

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

Ok monks, got a style question here.

I have several scalars I want to write to the database using Update. However, I need to format the scalars so that the special characters are not an issue. Below is an example.

my $SD = $dbh->quote($SupportDetails);
$SupportDetails = ($SD =~ s/\"/\\"/g);
[download]

This works well, however, I want to perform the same function on several variables. Let's say I have three variables and I want to change them as follows:

original scalar -> changed scalar
$SupportDetails -> $SD
$Issue -> $Is
$Cause -> $Ca
[download]

I know I need a subroutine, but cannot come up with the proper one. I think I need to add some counters and lists to the routine, but I keep messing it up.

Any ideas or samples any of you have done

Please advise,
Simon

Edit by myocom (added code tags)

Comment on Finding a good routine Select or Download Code

Replies are listed 'Best First'.
Re: Finding a good routine by Juerd (Abbot) on Mar 15, 2002 at 15:09 UTC
I have several scalars I want to write to the database using Update. However, I need to format the scalars so that the special characters are not an issue. Let DBI take care of it. You're double-escaping your data, which is probably not good. I like to avoid `quote()` and use `?` placeholders instead: `my $sth = $dbh->prepare('UPDATE foo SET bar = ?'); $sth->execute($variable);` [download] This is like saying `UPDATE foo SET bar='$variable'`, but with automatic proper escaping. U28geW91IGNhbiBhbGwgcm90MTMgY W5kIHBhY2soKS4gQnV0IGRvIHlvdS ByZWNvZ25pc2UgQmFzZTY0IHdoZW4 geW91IHNlZSBpdD8gIC0tIEp1ZXJk	[reply] [d/l]
Re: Finding a good routine by bmcatt (Friar) on Mar 15, 2002 at 15:12 UTC
Use placeholders or die... Then you don't need to worry about quoting at all. Update: Also, take a look at George_Sherston's code in this discussion to see a more generic way to do this sort of thing.	[reply]
Re: Re: Finding a good routine by sdyates (Scribe) on Mar 15, 2002 at 18:22 UTC
Thanks to all of you. It is too bad I can only give you one vote each! Each suggestion helped explain the issue further. Now that I know the better way, I will do this always. Thanks guys. Perl always seems to have an easy way of doing things! Simon	[reply]
Re: Finding a good routine by rdfield (Priest) on Mar 15, 2002 at 15:12 UTC
Use placeholders, e.g. `update mytable set sd = ? where key = ?` [download] and put your scalars as parameters to the execute. See 'perldoc DBI' for details. rdfield	[reply] [d/l]