-1

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

Mose
  • 541
  • 1
  • 11
  • 27
  • Can you include the code you use to talk to the database handle (ie. the DBI call). – Ether Jun 30 '10 at 23:11
  • Right now I am trying the following: $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. – Mose Jul 01 '10 at 13:38
  • param is method from CGI package. You have to 'use' it and instantiate. – Toto Jul 01 '10 at 15:41
  • possible duplicate of [How can I protect against SQL injection attacks using Perl's DBI?](http://stackoverflow.com/questions/2300765/how-can-i-protect-against-sql-injection-attacks-using-perls-dbi) – Ether Jul 01 '10 at 21:27
  • @mose: please edit that code into your question, so you can use proper formatting etc. – Ether Jul 01 '10 at 21:27
  • I'll update soon, working remotely and copy/paste via RDP isn't playing nice. – Mose Jul 02 '10 at 00:09

2 Answers2

2

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.

psmears
  • 26,070
  • 4
  • 40
  • 48
  • 3
    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 Jun 30 '10 at 23:13
  • 1
    @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... – psmears Jul 01 '10 at 07:17
  • Its not only single quotes I am looking at passing, the $note field serves the same purpose as the Add Comment field in this forum. Im simply looking for a way to pass all characters in $note to qq {execute procedure test(123,45,date,'$note');}; and insert them to DB. – Mose Jul 01 '10 at 10:46
  • @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. – psmears Jul 01 '10 at 13:33
  • its not so much a single character I need to escape. My form is much like the "Add Comment" here, where people can type anything (to include special characters) and it posts to a database. I need to be able to bind a variable to the notes in a textarea, and insert that into SQL. – Mose Jul 01 '10 at 16:23
  • @mose - yes, I know - and the suggested solutions will both do exactly what you want :) I'll try to clarify my answer! – psmears Jul 01 '10 at 19:10
  • In testing these solutions if someone enters a $, perl interprets the adjacent work as a variable and the output is 0 instead of the adjacent word, so while yes, it deals with the single qoute issue, it doesn't seem to rectify any other special characters such as @${} and |. Additionally, users aren't going to add \ to break special characters. They are simply going to type messages and expect what they see in textarea to appear in the database. – Mose Jul 01 '10 at 19:33
  • 1
    @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 :-) – psmears Jul 01 '10 at 20:09
  • Right, but thats what Im driving at. There is no way to verify that. I think everyone is focusing to hard on the Database insert aspect of it, rather than the variable part of it. While the methods you are talking about will address an errant single quote, it doesn't address other special characters such as $,}, etc. This note field will usually have screen dumps of Linux & Cisco devices. Therefore Characters such as $, # and parenthesis are going to be very common place. I really dont know how else to describe what I am trying to achieve other to refer to how "Add Comment" works here. – Mose Jul 02 '10 at 01:44
  • I was trying to use tr/\$/\\$ or something along those lines, but regex is not my strong suit. Also I've found alot of libraries via CPAN that have helped greatly in conversion of dates and other such common tasks. I've hit a stumbling point when it comes to this subject though. – Mose Jul 02 '10 at 01:46
  • @mose: People are focussing on the database part because that's the only part that is described in the question (and because the code that was originally posted would have had serious problems with some characters). If the data is being corrupted elsewhere, we need to know where/how, otherwise we can't really help (because the quoting will need to be done there, and will depend on how the corruption is happening). I'm not sure what you mean by 'there is no way to verify that' - can't you add some printfs to check the value and/or connect to the database directly? – psmears Jul 02 '10 at 06:03
  • @psmears: $note = qq{testing special characters!@#$%^&*()_+}{;:'"<>.,;}; If someone enters that into a textfield it fails. $note will not have consistent data. Its a memo field that regardless of what is entered into it, needs to be inserted into the DB. So my first challenge is to figure out how to tell Perl to not recognise anything from the textbox as anything other than text. At that point the database insert should be easy. I have this db code working for other parts of the page that only accept numbers, so I would assume once perl ignores special characters, that line too will work. – Mose Jul 02 '10 at 23:38
  • @mose: if you're setting `$note` with `qq{}` then that tells perl that it *should* interpret special characters! I thought you said the input was coming from a CGI form? If so, then perl won't interpret any special characters at all! Did you really expect us to spot an issue in a part of your code that wasn't even mentioned in the question? – psmears Jul 03 '10 at 13:32
-3

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

Mose
  • 541
  • 1
  • 11
  • 27
  • 1
    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 – psmears Jul 03 '10 at 13:32
  • dbh->qoute() isn't escaping the qoutes once inside the stored procedure. If I remove $note =~ s/'/\\'/g; it breaks. – Mose Jul 04 '10 at 04:19