Re: Avoiding SQL insecurities

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>
[download]

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

Comment on Re: Avoiding SQL insecurities Download Code

Replies are listed 'Best First'.
Re^2: Avoiding SQL insecurities by DrHyde (Prior) on Jul 15, 2004 at 08:40 UTC
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]

Replies are listed 'Best First'.

Re^2: Avoiding SQL insecurities
by DrHyde (Prior) on Jul 15, 2004 at 08:40 UTC

$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]