[thelist] Avoiding SQL Injection

Ken Schaefer Ken at adOpenStatic.com
Mon Mar 21 16:04:00 CST 2005



: -----Original Message-----
: From: thelist-bounces at lists.evolt.org [mailto:thelist-
: bounces at lists.evolt.org] On Behalf Of Joshua Olson
: Subject: RE: [thelist] Avoiding SQL Injection
: 
: > -----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).

I am surprised that the use of prepared statements and parametised queries
has not been brought up.

There are numerous ways to either (a) inject SQL or (b) finger print a
database via error codes. Attempting to sanitise bad input is, in the final
analysis, a losing battle. Instead, the use of parametised queries is the way
to avoid SQL injection. You are putting the burden of avoiding SQL Injection
onto your data access technology, rather than attempting to come up with your
own algorithms.

Cheers
Ken


More information about the thelist mailing list