I recently inherited a Perl script that takes delimited files and updates an SQL database. It recently started failing due to the descriptions containing apostrophes. I have read other posts on the site related to this and also several other pages on the net but couldn't determine the best method to fix my problem. The code for the section in question looks like this.
$lsth = $dbh2->do("INSERT INTO dbo.ICITEM(ITEMNO,AUDTDATE,AUDTTIME,AUD
+TUSER,AUDTORG,ALTSET,\"DESC\",DATELASTMN,INACTIVE,ITEMBRKID,FMTITEMNO
+,CATEGORY,CNTLACCT,STOCKITEM,STOCKUNIT,DEFPRICLST,UNITWGT,PICKINGSEQ,
+SERIALNO,COMMODIM,DATEINACTV,SEGMENT1,SEGMENT2,SEGMENT3,SEGMENT4,SEGM
+ENT5,SEGMENT6,SEGMENT7,SEGMENT8,SEGMENT9,SEGMENT10,COMMENT1,COMMENT2,
+COMMENT3,COMMENT4,ALLOWONWEB,KITTING,\"VALUES\",DEFKITNO,SELLABLE,WEI
+GHTUNIT,SERIALMASK,NEXTSERFMT,SUSEEXPDAY,SEXPDAYS,SDIFQTYOK,SVALUES,S
+WARYCODE,SCONTCODE,SCONTRECE,SWARYSOLD,SWARYREG,LOTITEM,LOTMASK,NEXTL
+OTFMT,LUSEEXPDAY,LEXPDAYS,LUSEQRNDAY,LQRNDAYS,LDIFQTYOK,LVALUES,LWARY
+CODE,LCONTCODE,LCONTRECE,LWARYSOLD) VALUES('$vendpartno', '$gmtyear$g
+mtmon$gmtmday', '$gmthour$gmtmin$gmtsec$gmtfsec', '$audtuser', '$audt
+org', '0', '$itemdesc', '$gmtyear$gmtmon$gmtmday', '0', '$itemtypeid'
+, '$vendpartno', '$categorycode', '$controlacct', '1', '$itemunit', '
+$pricelist', '$itemwgt', '1', '0', '', '0', '$vendpartno', '', '', ''
+, '', '', '', '', '', '', '$comment1', '$comment2', '$comment3', '$co
+mment4', '1', '0', '2', '', '1', '', '', '', '0', '0', '0', '0', '',
+'', '0', '0', '0', '0', '', '', '0', '0', '0', '0', '0', '0', '', '',
+ '0', '0')") or &bail($dbh2->errstr);
Based on what I read it sounds like I need to use placeholders instead of the literal values. I also read about the danger of inserting literal values in SQL but these files come from a well known distributor so there is little to no concern of anything malicious and I would prefer to make the least amount of changes to this script as possible at the moment. The problem value is $itemdesc which started to contain apostrophes. My question is whether or not I can just use a placeholder for this value only and then tag ,undef,($itemdesc) at the end. Will this successfully escape the apostrophes within the $itemdesc variable without making any huge changes to the overall script or is there a better way? As a side note, this SQL command gets called several hundred thousand times when run. Thanks.