First of all, the Perl Monks FAQ has information on how to format and post Perl code. Use <CODE> tags wrapped around your code like this :
<CODE>
# Some Perl code
</CODE>

There are two ways prevent users from stuffing bad data into the parameters and execute arbitrary SQL code. One way is to quote the data, that is, placing all (string) data in '', and replacing all "'" within strings with "\'". This would be done like the following, but mind that there are already invented wheels for this around somewhere (although I don't know where exactly, maybe in the DBI module series) :

Update: Jouke knows DBI better than I do, and the function you want is $dbh->quote() - that is, if you switch from Win32::ODBC to the DBI/DBD combo.

sub SQLQuote {
  my ( $data ) = @_;
  $data =~ s/'/\\'/g;
  return $data;
};
[download]

SELECT * FROM table WHERE name=? AND id=?
[download]

$dsn->Sql($SQL, $name, $id );
[download]

Update: kudra told me that using parameters would automagically provide correct quoting for the database in question. It goes more like this :

# Code courtesy of kudra++, errors courtesy of me
$SQL = < SQL;
  SELECT * FROM table WHERE name=? and id=?
SQL

$statement = $dbh->prepare( $SQL );
$statement->execute($name, $id);
[download]

Update : Thinking further about this, you might want to make sure before using all that code that the strings somewhat resemble what you expect, that is, data that should be a number should match /^\d+$/ and usernames should maybe match /^[a-z0-9_]+$/ and strings in general should maybe not contain characters below 32 - this will protect you a bit more against bad data supplied by the users.

Update: And I've even learned some more about SQL - what I called "parameter syntax" is instead called "placeholders". Another day at Perlmonks, another thing learned :)

Corion is right in using the questionmarks. In DBI this would look like this:

my $sth = $dbh->prepare('SELECT * FROM table WHERE name=? AND id=?');
$sth->execute($name, $id);
[download]

but I'm not sure wether or not this provides the quoting...(sure, some other Monk will know ;-) ) Jouke Visser, Perl 'Adept'

Unfortunately prepared queries really requires DBI.

What you do is "prepare" a query, get back a statement handle, then pass it the parameters when you "execute" it. If latency is low for inserting multiple rows this can can come close to the maximum theoretical throughput. But there is a lot of overhead to preparing a query with parameters.

"unfortunately" you say...I'd say: why use anything else?? Why should anyone use Win32::ODBC when DBI is available? Jouke Visser, Perl 'Adept'

$SQL = "SELECT * FROM st WHERE st_name=".$dbh->quote($name);
[download]

This is one of my coding rules for secure programs - always use parameters to pass in user input when doing SQL queries. This is the ? in the prepare statement below. You then fill in the blanks when you execute the statement.

my $find_name = $dbh->prepare("select * from table where name = ?");
$find_name->execute($unsafe_name_from _user_via_cgi);
my $row = $find_name->fetchrow_hashref; # or whatever
$find_name->finish;
[download]

You can also re-use the prepared SQL which can make it quicker if you do the same type of query over and over.

I wrote a similar CGI program at a recent client's site using Win32::ODBC. But the driver you use is not really relevent in my mind.

First off, if you are concerned about a user inputing complete SQL statements that will be destructive, either don't let them ( however this might involve creating an interface that allows column selection then crtieria, which can be a very complex solution) or REGEX the SQL for damaging code ( check for delete, and quanantine that user if they send such SQL ) and don't allow it to execute.

You can alway revoke permissions in the database to disallow a web user from writing to the database, by giving them select rights only. If the user isn't supposed to be able to write to the database this SHOULD be in place already. Check with your DBA.

If your system uses a login, then you can disallow this AND identify someone who attmepts to submit destructive code.