Beefy Boxes and Bandwidth Generously Provided by pair Networks
Pathologically Eclectic Rubbish Lister

Avoiding SQL insecurities

by FubarPA (Monk)
on Jul 14, 2004 at 20:49 UTC ( #374438=perlquestion: print w/replies, xml ) Need Help??

FubarPA has asked for the wisdom of the Perl Monks concerning the following question:

I'm pretty new to perl, and as such I'm prone to writing bad code. I tend to learn from my mistakes, but now I'm looking to start writing something I want to use on the web.

I want to query a database and perhaps perform some updates. I've read about other scripts and packages (PHP-Nuke, etc) which have been found to have SQL injection vulnerabilities. I want to avoid that in my perl scripts. Can someone recommend a good module or an example of a decent query script from which I can learn? Thanks.

Replies are listed 'Best First'.
Re: Avoiding SQL insecurities
by gmax (Abbot) on Jul 14, 2004 at 21:23 UTC
Re: Avoiding SQL insecurities
by samtregar (Abbot) on Jul 14, 2004 at 20:57 UTC
    There's only one module you should use for database work in Perl: DBI. I suggest you pick up a copy of Programming the Perl DBI. That should give you everything you need to know, including information about avoiding SQL injection attacks (use placeholders).


Re: Avoiding SQL insecurities
by freddo411 (Chaplain) on Jul 14, 2004 at 22:49 UTC

    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>
    and then have a lookup table where you translated the value to the actual SQL.

    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!

    Nothing is too wonderful to be true
    -- Michael Faraday

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

Re: Avoiding SQL insecurities
by shemp (Deacon) on Jul 14, 2004 at 22:03 UTC
    If you're doing updates from the script this may not be able to happen, but its always a good idea to severly limit the privileges of the SQL user account that you are connecting to the DB with.

    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.

Re: Avoiding SQL insecurities
by ercparker (Hermit) on Jul 15, 2004 at 06:27 UTC
Re: Avoiding SQL insecurities
by ccn (Vicar) on Jul 14, 2004 at 21:16 UTC

    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.


Re: Avoiding SQL insecurities
by Belgarion (Chaplain) on Jul 15, 2004 at 18:30 UTC

    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);
Re: Avoiding SQL insecurities
by FubarPA (Monk) on Jul 15, 2004 at 15:10 UTC
    Thanks everyone for the input. I'll check out the recommended books and do a bit more research here in the monastery. SQL is a bit scary at first, but I think I'll get the hang of it pretty quickly. Thanks again!

Log In?

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://374438]
Approved by samtregar
Front-paged by grinder
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others about the Monastery: (4)
As of 2022-11-29 17:37 GMT
Find Nodes?
    Voting Booth?