Attempting to catch malicious input is misguided in most cases. You should either only allow known good - verifiable - input or make sure the content of the input doesn't matter.

update:

I think there is room for a Perl module that can screen against all attacks through stored procedures of any database app.

Agreed. However, if for some reason your app actually requires the ability to input actual queries (like a SQL tutorial, maybe), you should probably be relying much more on the database's built-in security model. If you give an untrusted user real access to a database that you don't want them altering/destroying, a fragile regex is not going to save you.

As an aside for similar efforts, no regex of that length should be a one-liner. At the very least, you should be using the /x switch and commenting what you want to do. Even better would be building the regex in several chunks, so that a syntax error might have a prayer of being found.

Many people think that, rather than checking for particular things that you don't want, it's better to check that the input is what you do want, and only accept it if it is.

For example, instead of saying 'don't accept input with dollar symbols in it', you could say 'only accept input consisting solely of alphanumeric characters and whitespace'.

The logic behind this is that, if you try to check for all the possible things that could cause trouble, you're bound to miss some out, perhaps because a particular security problem is only discovered after you write your code - in that situation, there's no way you could have known about it.

There are a couple of tools in Perl that can help with this:

• For data coming in from web forms, there is something called Taint mode. What this does, is it forces you to 'clean' data which could have potential security problems, before you can use it.

  You do this 'cleaning' by using a regular expression to say what characters you're willing to accept, like this:
  

  if ($data =~ /^([-\@\w.]+)$/) {
      $data = $1;             # $data now untainted
      } else {
      die "Bad data in '$data'";     # log this somewhere
      }
  [download]

  
  (This example is from the docs linked above)

• For sending statements out to databases, you can use something called Placeholders. What these do, is they let you separate out the data in a SQL statement, from the parts of the SQL statement that actually do things. So, if someone replaces your data with something unexpected, they can't send instructions to the database itself.

  Placeholders work like this:

  SELECT description FROM products WHERE product_code = ?

  (Example taken from the docs linked above)

I can see you've just asked in the Chatterbox, "Why didn't people like my last post?", and the answer, as far as I can tell, is that some Monks are getting impatient with you because, when people answer your questions by telling you about online documentation, you're not going away and reading that documentation for yourself.

(Personally I'm a 'no such thing as a stupid question' person, but not everyone is).

You could also take a look at the tips in this article, The Help Vampire, a Spotter's Guide, and if you think that you might be doing some of the things they talk about (such as asking the same question multiple times, and not using your own initiative to understand the documentation), then there are some very useful tips for you to follow, on that page.

This is all meant in the very friendliest spirit, and I hope that it reads in that way. After all, Perlmonks is here to answer questions, so everyone is absolutely entitled to ask them! Which is lucky for me, otherwise I'd have been stuck many times. :)

Do get back to me with any queries about the 'taint mode' and 'placeholders' stuff above.

Hope that helps, and best wishes,
andye

(Other relevant nodes: Database access problem, Perl DBI issue)

The tips from the 'help vampire' page are:

If You're a Help Vampire...

#assuming you're connected, with a $dbh
#my $sql = "select ? from ?"; #incorrect, thanks wfsp
my $sql = "select ? from myTable";
my $sth=$dbh->prepare( $sql ) ;
$sth->bind_param(1, $col_to_select );
#$sth->bind_param(2, $table ) ; #also incorrect
$sth->execute ;
[download]

"relax even harder"++)

That regexp won't even compile. You have an unescaped slash and an unescaped opening parenthesis in it. But when it does compile, I think it will match too much valid data. Like, what is the branch \s+\s supposed to catch?

One good thing that has come out of this discussion is that I understand web security better now. I've developed an internal web app for my department and now that I've got it up and running with just dbh->quote()ing everything possible I think I'll start to bind up my db queries. I have to let my users input backslashes and other potentially dangerous stuff because of the nature of the data.