special Characters in Perl

Question

I'm creating a CGI form to update a Sybase stored procedure.

qq {execute procedure test(123,45,date,'$note');}; the $note variable is information obtained from a textbox that contains trouble ticket log information. As such people who enter such information can, and most likely will use special characters such as '|"{} etc. Im curious to know if there is a way to get this information into the database via the variable $note.

Most of my research has produced DBI->quote() functions, but they dont seem to work, and Im not sure how relevant those are since this is a stored procedure.

Right now I am trying the following:

use DBI;
$livedb = DBI->connect("dbi:Sybase:server=test","admin","pass") || die "could not connect to live DB, $DBI::errstr";
$note = "testing special characters:";
$livedb->do(qq {execute procedure jinsert(5304905,65,getdate,?);},undef,(param('$note')));

I get the following error:

Undefined subroutine &main::param called at test.cgi line 11.

when I use the below methods, the code fails if a ' exists in $note:

$qy = $livedb->prepare($note);
$qy->execute($test) || die "could not journal: $DBI::errstr";
$qy->finish();

Answer 1

Firstly, to answer your question directly: DBI->quote() is indeed your friend here :-) It puts quotes round the string in the correct way for the language of the database you're using (which is invariably the same for SELECT/UPDATE/INSERT/DELETE queries as for stored procedures, since the latter usually consist of combinations of the former!).

For example, assuming $dbh has been set up as your DBI connection to your database, then

my $string = "Here's a string that needs \"quoting\"!";

print $dbh->quote($string);

prints something like:

'Here''s a string that needs "quoting"!'

Notice how:

• It's doubled the ' in Here's
• It's put '' quotes around the whole string.

The exact string that it prints will depend on your database - some use slightly different conventions.

However...

Looking at what you're doing, you shouldn't actually need to do any quoting: let DBI do all the work for you, like this:

$livedb->do(qq {execute procedure jinsert(5304905,65,getdate,?);}, undef, $note);

The DBI code knows what quoting to do in order to replace the ? with $note.

Answer 2

I finally figured out what the problem was. I am able to escape all of the special characters from perl using q {$note};. dbh->quote is not applicable here since its calling a stored procedure, and the issue isn't simply matching qoutes. Single qoutes have to be passed escaped to the stored procedure. A combination of q and sed fixed it:

use DBI;
$livedb = DBI->connect("dbi:Sybase:server=test","admin","pass") || die "could not connect to live DB, $DBI::errstr";
$note = q {testing special characters:''''''''''''''!@#$%^%^&*()};
$note =~ s/'/\\'/g;
$livedb->do(qq {execute procedure jinsert(5304905,65,getdate,?);},undef,(param('$note')))

So in a nutshell, when calling a stored procedure such as this one, single qoutes need to be escaped twice (once in perl, a second type for the stored procedure).