[thelist] Avoiding SQL Injection

[Brooking, John]

Hello,

   I understand what SQL Injection is, and have read some articles on
it. The one thing I'm not quite sure of is how restrictive I need to be
in a form input field which should allow free-form text. I will be
putting this into a database table for suggestions. Obviously, a balance
must be struck between restricting the characters the user can use too
much, versus guarding against the SQL attack. Obviously this must need
to be done all the time on blogs and discussion forums that store free
text in a database. I've done a few searches but haven't turned up any
articles discussing any "standard" acceptable algorithm for this.

   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>

   Comments appreciated.

- John
-- 


This message may contain information which is private, privileged or confidential and is intended solely for the use of the individual or entity named in the message. If you are not the intended recipient of this message, please notify the sender thereof and destroy / delete the message. Neither the sender nor Sappi Limited (including its subsidiaries and associated companies) shall incur any liability resulting directly or indirectly from accessing any of the attached files which may contain a virus or the like.