[ic] Form Input and Database Insert Safety, Interchange

Kevin Walsh kevin at cursor.biz
Mon May 22 11:45:56 EDT 2006


Mark Johnson <mark at endpoint.com> wrote:
> Andrew N wrote:
> > I am going to take a form input from users to our web site and write a
> > database record based on it.  Pretty common I know.  I've done it
> > before, but now I am more concerned about security because the form
> > field that is being logged will go verbatim into the database.
> > 
> > I am running 4.9.5.  Is there a native tag or does someone have a
> > useful perl statement(s) that would strip out harmful characters and
> > avoid things like SQL bombs.
> >
> Interchange has a [filter] tag that, given the type of filter as an
> argument, can do what you want. In your case, you're interested in the
> 'sql' filter:
> 
> 	[filter op=sql interpolate=1][cgi my_raw_form_data][/filter]
> 
> There are numerous other useful filters for all manner of data
> transformation in addition to this one.
> 
> A word of caution on the sql filter in 4.9. Your version escapes by the
> SQL-standard of doubling up single quotes. Unfortunately, databases such
> as MySQL and Postgres also allow backslash escaping, which means it's
> still possible to produce SQL injections using a combination of single
> quotes and backslashes. The current IC uses the native DBI quote method
> so that escapes for a particular database are handled properly according
> to its particular DBD.
> 
Later versions of the [query] tag have a [sql-quote] "subtag" that
will do a proper $db->quote() on the value:

    [query
        list=1
        sql=|
            SELECT  code
            FROM    products
            WHERE   sku = [sql-quote][cgi sku][/sql-quote]
        |
    ]
    ...
    [/quote]

That will work for INSERT and DELETE (etc.) too, of course, and will
avoid the need for [filter].  It also neatly side-steps any potential
security issues.

I suggest an upgrade from 4.9 (development) to 5.4 (stable), which will
provide you with this facility, and lots of other goodies.  On the
other hand, any competent Interchange consultant will be able to
back-port this facility into 4.9.

-- 
   _/   _/  _/_/_/_/  _/    _/  _/_/_/  _/    _/
  _/_/_/   _/_/      _/    _/    _/    _/_/  _/   K e v i n   W a l s h
 _/ _/    _/          _/ _/     _/    _/  _/_/    kevin at cursor.biz
_/   _/  _/_/_/_/      _/    _/_/_/  _/    _/


More information about the interchange-users mailing list