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]

The other method would be to supply your data as parameters to the SQL query, but I have never done this myself, so maybe another monk could step in here (hint, hint). The SQL queries look then like this :

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

and the statement to call it would be :

$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 :)

---

---

Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!

Titles consisting of a single word are discouraged, and in most cases are disallowed outright.

Read Where should I post X? if you're not absolutely sure you're posting in the right place.

Please read these before you post! —

• How do I compose an effective node title?
• How do I post a question effectively?
• Markup in the Monastery

Posts may use any of the Perl Monks Approved HTML tags:

a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr

You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)

	For:		Use:
	&		&
	<		&lt;
	>		&gt;
	[		&#91;
	]		&#93;

Link using PerlMonks shortcuts! What shortcuts can I use for linking?

See Writeup Formatting Tips and other pages linked from there for more info.