[thelist] Avoiding SQL Injection

[Joshua Olson]

> -----Original Message-----
> From: Brooking, John
> Sent: Monday, March 21, 2005 1:58 PM

>    Following is the algorithm I've come up with so far. I'd be
> interested to hear if you notice any omissions in my thought process:
> 
>    * Allow alphanumeric and the following punctuation:   , . 
> @ " - ? ! :
> $ & + % * > ( ) [ ] { }
> 
>    * Substitute potentially dangerous characters and sequences as
> follows:
>          - Semi-colon to comma
>          - Two or more dashes (SQL comment) to single dash
>          - Single quote (') to \' (would have to be done for 
> syntactical
> correctness anyway)
>          - Less than (<) to HTML entity &lt; (I'm won't be 
> allowing HTML
> tags in the input)
>          - Newlines to HTML tag <br>

John,

You are trying to perform two actions with one set of rules.  Generally, I'd
suggest you avoid this.  If the goal is to combat SQL Injection, then
preparing the data for HTML presentation is not necessary.  Replacing <, >,
and Newlines is not going to protect you again SQL injection.

You may want to consider validating your data against the expected data
type.  If you are expecting a number, only allow for 0-9, negative, period,
and possibly comma.  Run a casting function to make sure they entered a
number.  Entering values for numbers such that the value contains
destructive SQL is the basis for many SQL Injection attacks.  By validating
the number, you circumvent this ENTIRE ARRAY of attacks.

If you are expecting a string, ensure that all characters are within the
database's character set.  There is no need to worry about replacing
semi-colons with commas, etc.. just make sure you escape the single quotes
(') so that the compiled SQL string stays in syntax and strings aren't ended
until you want them to be (breaking strings early is the basis for many SQL
Injection attacks).

<><><><><><><><><><>
Joshua Olson
Web Application Engineer
WAE Tech Inc.
http://www.waetech.com/service_areas/
706.210.0168