[thelist] Avoiding SQL Injection

[Matt Warden]

On Mon, 21 Mar 2005 13:58:25 -0500, Brooking, John
<John.Brooking at sappi.com> wrote:
>    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.

Most people forget the most important one:

1. Grant only the permissions to the database user that are necessary.

For example, if the database user will only be doing INSERT, SELECT,
and UPDATE, then you really don't have much to worry about (except
maybe extra bogus inserts and updates) if you restrict the database
user to these operations. A database user for a production app should
99.999999% of the time not have permissions to alter the schema (DROP
TABLE, ALTER TABLE, CREATE TABLE, etc.). The exception is, possibly,
CREATE TEMPORARY TABLE, but this it is usually possible to allow temp
table creation while disallowing the rest of the schema definition
language.


-- 
Matt Warden
Miami University
Oxford, OH, USA
http://mattwarden.com


This email proudly and graciously contributes to entropy.