[thelist] sql injection problem

[Chris at globet.com]

Brian

[..]

> > I would personally URL-decode the
> > input value, then check that it contained only alpha/numeric 
> > characters and spaces (or whatever your criteria are). If 
> any further 
> > system vulnerabilities are discovered, you're unlikely to 
> be compromised. 
> 
> So, once i url-decode, I still need to strip out the SCRIPT, 
> INSERT, etc etc, no?  I don't see how this helps much.  It 
> saves a little time over replacing all the single/double 
> quotes, but in the end, i still need to replace out the commands.
> Or am I not following?

You don't replace anything at all in the model I suggested. Taking the
example of a "name" field in a form, with only alphanumeric characters
and spaces allowed, you would validate the input against the following
regular expression pattern:

"^[A-Za-z0-9\s]+$"

If the input fulfills the requirements of this pattern, you are
protected from all SQL injections attacks that I'm aware of; so you
process it. If it does not, you alert the user that they have entered
invalid information and invite them to re-enter it. If it does *not*
fulfill the requirements of the pattern then you should *not* process
it. Any way you choose to view it, invalid data is invalid. If you try
to sanitise it, you still have invalid data. Also, by "sanitising" the
data you may also be corrupting genuine data - "Johnny Postscript" would
have his name changed to "Johnny Post". All of the above is following
the criteria that you posted, whereby you're simply disallowing strings
like "<script>" and "'".

At the risk of labouring the point, if input is invalid, you do *not*
want it in your database in any form. There is *no* upside to entering
data that you have created from invalid input, unless you're logging it.

Let me know if you would like any further clarification. As an aside,
"Innocent Code" by Sverre Huseby is a great book on security for web
developers. It's not too pricey or heavy going.

HTH

-- 
Chris Marsh
Software Developer
t: +44 20 8246 4804 x828
f: +44 20 8246 4808
e: chris at globet.com
w: http://www.globet.com/

Any opinions expressed in this email are those of the individual and not
necessarily the Company. This message is intended for the use of the
individual or entity to which it is addressed and may contain
information that is confidential and privileged and exempt from
disclosure under applicable law. If the reader of this message is not
the intended recipient, you are hereby notified that any dissemination,
distribution, or copying of this communication is strictly prohibited.
If you have received this communication in error, please contact the
sender immediately and delete it from your system.