special Characters in Perl

Tag: perl , cgi , special-characters , quotes Author: a820909 Date: 2010-06-14

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();

Can you include the code you use to talk to the database handle (ie. the DBI call).
Right now I am trying the following:<code> $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')));</code> I get the following error: <code>Undefined subroutine &main::param called at test.cgi line 11.</code>
param is method from CGI package. You have to 'use' it and instantiate.
possible duplicate of How can I protect against SQL injection attacks using Perl's DBI?
@mose: please edit that code into your question, so you can use proper formatting etc.

Other Answer1

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.

comments:

DBI->quote is not really the preferred approach. Instead of the classic SQL injection vulnerability in the original sample code, the better way is to use placeholders ('?') and pass the values as extra arguments to $sth->execute().
@Grant McLean: DBI->quote and "?" placeholders are equivalent, and both fix the SQL injection issue - indeed, one is implemented in terms of the other! Granted, the "?" placeholders are neater in many circumstances but sometimes (e.g. when constructing a complex query programatically) it makes more sense to quote explicitly upfront, rather than having to track the sequence of "?" parameters throughout the construction of the query. It's not clear from the question whether the string being generated forms part of a larger sequence to execute, so explicit quoting is not totally unreasonable...
Its not only single quotes I am looking at passing, the <code>$note</code> field serves the same purpose as the Add Comment field in this forum. Im simply looking for a way to pass all characters in <code>$note</code> to <code>qq {execute procedure test(123,45,date,'$note');};</code> and insert them to DB.
@mose: Yes, DBI->quote() will do this for you, as will the "?" method that Grant has mentioned. If I get time later I'll update my answer to describe both methods and the reasons for using each.
@mose: If you're seeing that, then the problem must be elsewhere in the code... are you sure that (1) the variable $note has exactly the value you're expecting (i.e. is it possible that the $ has got interpreted beforehand), and (2) the value isn't getting corrupted after it's read out of the database (i.e. the above is working, but the code that retrieves it from the database is broken)? From what you're saying, it sounds like there's an extra call to eval somewhere (or the result is being passed unquoted to a shell script, or similar)... but the code above is correct, I promise :-)

Other Answer2

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).

comments:

dbh->quote() is applicable here, because you're using it!! As I said in my answer, that's how the "?" mechanism that you're using works. It isn't about matching quotes
dbh->qoute() isn't escaping the qoutes once inside the stored procedure. If I remove $note =~ s/'/\\'/g; it breaks.