Check our Tutorials. There are several posts about how to use the DBI.

Also, most enlightening is the discussion under Use placeholders. For SECURITY!

 _  _ _  _  
(_|| | |(_|><
 _|   

-sam

The most basic rule it that the front end UI elements must have no SQL in them, just references back to your scripts that contain the SQL. The reason for this is that these elements can be freely hacked.

So instead of an HTML menu like:

<select name="report_menu">
<option value="Select * from myTable">My cool SQL report</option>
<option value="Select * from myTable where foo=bar">my other cool repo
+rt</option>
</select>

You would do:

<select name="report_menu">
<option value="1">My cool SQL report</option>
<option value="2">my other cool report</option>
</select>
[download]

You can accept user input for some of the values in the SQL, but you have to carefully check/filter the values (ALWAYS!!) to make sure that they don't contain nasty SQL injections. One quick and dirty way to do this is to limit inputs to alphanums only, (or something more complex if needed).

Good Luck!

Perhaps more important - because it's a bit more subtle - is that you should NEVER directly interpolate user input into SQL. Don't do things like:

$dbh->do("UPDATE mytable SET foo='$wossname' WHERE bar='$otherwossname'");

Where $wossname comes straight from a HTML form, because at some point, a nasty fellow like me will provide a value like:

you are screwed';DELETE FROM mytable;

which is the classic SQL injection attack. *That's* why placeholders are so good, but even with placeholders, you need to validate user input so that you don't try to update a user's date of birth with "grapefruit".

Basically if your script doesnt need a particular SQL privilege, dont give that privilege to the SQL user account that you're using.

I'm thinking in terms of MySQL, which has excellent user management, but your DB may be different, or if you dont get to admin that db, you may be SOL.

Also, NEVER, NEVER imbed queries in web pages, if this is a web interface, you're really asking for it there.

try to use taint mode in your cgi scripts (-T flag)
perldoc perlsec

That won't help avoid SQL injection attacks unless you also turn DBI's TaintIn option on. Otherwise DBI will happily accept a tainted string as an SQL query.

-sam

You may also want to look into something like Class::DBI as a means of interfacing with your database. Class::DBI handles the SQL translation correctly without any interaction by you.

Even if you don't end up using the module it is a good piece of code to study to see how to work with a database from within Perl.

Always use placeholders. Examples below.

$dbh->do('UPDATE table SET col = ? WHERE id = ?', {}, $value, $id);

my $sth = $dbh->prepare('SELECT foo, bar FROM table WHERE baz = ?');

$sth->execute($baz);
[download]