Re: Avoiding SQL insecurities
by gmax (Abbot) on Jul 14, 2004 at 21:23 UTC
|
| [reply] |
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).
-sam
| [reply] |
Re: Avoiding SQL insecurities
by freddo411 (Chaplain) on Jul 14, 2004 at 22:49 UTC
|
Hi,
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
| [reply] [d/l] |
|
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".
| [reply] [d/l] [select] |
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. | [reply] |
Re: Avoiding SQL insecurities
by ercparker (Hermit) on Jul 15, 2004 at 06:27 UTC
|
| [reply] |
Re: Avoiding SQL insecurities
by ccn (Vicar) on Jul 14, 2004 at 21:16 UTC
|
| [reply] |
|
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
| [reply] |
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.
| [reply] |
|
$dbh->do('UPDATE table SET col = ? WHERE id = ?', {}, $value, $id);
my $sth = $dbh->prepare('SELECT foo, bar FROM table WHERE baz = ?');
$sth->execute($baz);
| [reply] [d/l] |
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! | [reply] |