Quote in DB String – Perl DBI

Today I fixed a bug in one of our legacy perl tool.

The tool worked well until I got the complaint from user this morning that it is not able to update data. After debugging I found it is due to quote string in the text.

E.g

$sth = $dbh->prepare(“UPDATE ABC_REQUEST set COMMENTS=$paramComm‘ where REQUEST_SID=$paramID”);

if the $paramComm contains ‘ (quote) string then it fails. For example, below update statement will fail.

UPDATE ABC_REQUEST set COMMENTS=’This is required for bug xxx in ‘I am a quote’ here ‘ where REQUEST_SID=xxx

Solution:

Change

From-

$sth = $dbh->prepare("UPDATE ABC_REQUEST set COMMENTS='$paramComm' where REQUEST_SID=$paramID");

To-


$paramComm = $dbh->quote($paramComm);
$sth = $dbh->prepare("UPDATE ABC_REQUEST set COMMENTS=$paramComm where REQUEST_SID=$paramID");

Note: by using quote(), you don’t need to quote your comments with ” any more.

More details, http://search.cpan.org/~timb/DBI-1.631/DBI.pm#quote