Sql statement routines [Was: Re: Security measures]

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

Sql statement routines [Was: Re: Security measures]

Nikolaj de Fine Licht
Greg,

Thank you for explaining in depth about automatic sql statement
routines in context with form submission and the risk of undesired
fields submitted!

I have always manually coded sql statements, and everytime I was coding
a statement with, say, 30 fields to update I thought "there must be an
easier way to do this". But this was having form field names equal to
db field names, and when I left that I stopped thinking of automation.

Your post intrigued to think in automation again. I don't want to ask
you or anybody else how to do, because that would be going to close to
"business secrets", or whatever that would be called in English, but
maybe I can post some thoughts on this and hope for a little feedback
like "getting closer", "nope, not that way", "pitfalls ahead" or so...
:)

'Cause my first thought after your post was "but how can you build
automatic sql statement routines without having form field names and db
field names equal"?. And I'm still quite stuck here:

1. I'm thinking the trick could be to have _some_ part of the form
field names being equal to _some_ part of the db field names
respectively, and then have a routine for soughting that out, and thus
be able to determine which form field input belongs to which db field
name. But then again: wouldn't that compromise security: once you
figure the principle used for this naming you have all the db field
names...?

2. Then I thought in direction of some sort order thing: you build an
array of form field names-values submitted and you have an array of db
field names, and then you have array index numbers determine which form
input name-value pair belongs to which db  field name. But I haven't
tried yet to build this.

3. What I think I _would_ be able to is fiddle together is an sql
statement building tag which takes a map of db field names and
corresponding form field name values as parameters. While this would
certainly be better it still needs some manual coding for each
statement...

Your point of making sure that such an automatic routine doesn't allow
for not desired form field inputs to get into the routine made me think
of page specific ids, or transaction ids: when a page containing a form
is called a random transaction id is created and passed invisibly (via
the session...?) on subsequent form submissions and deleted after
completion of the process - successful or unsuccesful.

So, I'm really just thinking aloud inspired by Greg's post. I hope I'm
not annoying the list too much...

/nikolaj

On 4. jun 2005, at 20:18, Greg Willits wrote:

>> Could this happens under other circumstances than having form field
>> names equal to database field names and running an automatic routine
>> which updates the database with whatever action_params that are
>> available? I couldn't imagine what other kind of programming that
>> would allow for other fields than the ones desired to get into the
>> db...
>
> It has less to do with form field names equal to database field names,
> and more to do with the automatic routines.
>
> If you hand code every table action and specify literally what fields
> get added/updated, then you're right, that's not going to let
> extraneous fields in the form get added. You have essentially hand
> filtered the data coming in.
>
> While that's spiffy for getting started, and spiffy for routines/pages
> where every tick of performance is critical, it's a total pain for
> everything in between--especially if you're writing lots of apps or
> writing an app that constantly being expanded / changed. In those
> cases even if speed is critical, it is usually still cheaper to
> abstract the query process and pay for a faster or additional server
> than it is to hand code everything--errors, maintenance, multiple
> places to update things can add up. Totally situation dependent of
> course.
>
> Anyway... so, while the SQL-injection obsessed people will tell you
> not to write routines that auto-build the SQL query, you'll find those
> folks just aren't thinking far enough ahead. The key is to filter that
> auto writing routine to ensure that only the field you want to be
> added/updated are in fact included in the query and we're right back
> to something as good as a hand written query.
>
> So what I was alluding to is making sure that if you have written
> routines to build the SQL, or if you use action_params to auto stuff
> FMP, then those methods have to be controlled by filtering. The code
> has to specifiy exactly which fields are to be allowed. While that
> drops the convenience of the auto routine a little, it is a lot easier
> to maintain a list of fields than the whole query. Don't add fields to
> the SQL that don't belong there, and remove fields from action_params
> that don't belong there. Then you're good to go.


--
------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:  
http://www.listsearch.com/lassotalk.lasso?manage
Reply | Threaded
Open this post in threaded view
|

Re: Sql statement routines [Was: Re: Security measures]

Greg Willits
On Jun 5, 2005, at 3:02 PM, Nikolaj de Fine Licht wrote:

> Your post intrigued to think in automation again.... "but how can you
> build automatic sql statement routines without having form field names
> and db field names equal"?

I have a fairly evolved system for this. I presented an early version
of it back at the Oct 2002 Summit. It hasn't changed in concept a whole
lot since then (some additional security tweaks and features), but I
have a bunch of enhancements waiting to be implemented.

Anyway... in concept, what I do is this:

First, create a config file that looks like this:
http://www.fwpro.com/refc/dbTbl_{dbtblname}.lasso

Next, write a routine to read that file, and split the data into two
arrays: one for the input names and one for the field names. In my
case, I wrote that as a custom type so that each array was an instance
var of that ctype. This is more flexible than using regular vars.
http://www.fwpro.com/refc/fwp_tableConfig.lasso

You also need a general routine to convert form inputs into vars. There
are security issues to beware of there too. Autovalidate does that for
so in your case, you're set, and by the time your page code processes,
you have vars = to the form inputs.

Next, you need some generic code to create the INSERT an UPDATE
queries. My routines for that are buried in an overall fancy system for
abstracting query functions and combining it with several other steps.
I have those written with a custom type wrapper for custom tags, but
you can do it with a regular custom tag. One of the input parameters is
a list of allowed fields. Just enter a comma list of the form input
names. That input is form specific each time you use the tag. The work
of defining the whole table is a generic config file, but the task of
identifying just the allowed inputs for a given query is form specific.

The core query building routine iterates through the allowed input
names. The routine checks for a var with that name (i.e. data was
submitted by the form) then looks for the matching db field name from
the config data, and adds a field = value string to the SQL query
statement. Now, I have two arrays which goes back a ways and gets used
for other purposes too, but for a fixed purpose ctag like this, it'd be
just as easy to use a regular map for that initial config file.

The query statement building, of course, is done after all the data
type validation and business rules validation have been passed (and the
use of encode_sql).

Those are some general ideas. It's gets a little complex to explain my
whole system because it does many things beyond just the query
building. I integrated the record locking, the validation invocation,
the error handling, and the logging all in one place.

-- greg willits


--
------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:  
http://www.listsearch.com/lassotalk.lasso?manage
Reply | Threaded
Open this post in threaded view
|

Re: Sql statement routines [Was: Re: Security measures]

Nikolaj de Fine Licht
In reply to this post by Nikolaj de Fine Licht
Greg,

Sorry for being late with a response, thank you very much for your
walk-through! Of course what you need is a config file which
'translates' between input names and db field names, it's so logic once
you know ;-)

It seems I will have a project soon suitable for experimenting with a
routine based on your outline!

Thanks again, best wishes,

Nikolaj

On 6. jun 2005, at 2:08, Greg Willits wrote:

> On Jun 5, 2005, at 3:02 PM, Nikolaj de Fine Licht wrote:
>
>> Your post intrigued to think in automation again.... "but how can you
>> build automatic sql statement routines without having form field
>> names and db field names equal"?
>
> I have a fairly evolved system for this. I presented an early version
> of it back at the Oct 2002 Summit. It hasn't changed in concept a
> whole lot since then (some additional security tweaks and features),
> but I have a bunch of enhancements waiting to be implemented.
>
> Anyway... in concept, what I do is this:
>
> First, create a config file that looks like this:
> http://www.fwpro.com/refc/dbTbl_{dbtblname}.lasso
>
> Next, write a routine to read that file, and split the data into two
> arrays: one for the input names and one for the field names. In my
> case, I wrote that as a custom type so that each array was an instance
> var of that ctype. This is more flexible than using regular vars.
> http://www.fwpro.com/refc/fwp_tableConfig.lasso
>
> You also need a general routine to convert form inputs into vars.
> There are security issues to beware of there too. Autovalidate does
> that for so in your case, you're set, and by the time your page code
> processes, you have vars = to the form inputs.
>
> Next, you need some generic code to create the INSERT an UPDATE
> queries. My routines for that are buried in an overall fancy system
> for abstracting query functions and combining it with several other
> steps. I have those written with a custom type wrapper for custom
> tags, but you can do it with a regular custom tag. One of the input
> parameters is a list of allowed fields. Just enter a comma list of the
> form input names. That input is form specific each time you use the
> tag. The work of defining the whole table is a generic config file,
> but the task of identifying just the allowed inputs for a given query
> is form specific.
>
> The core query building routine iterates through the allowed input
> names. The routine checks for a var with that name (i.e. data was
> submitted by the form) then looks for the matching db field name from
> the config data, and adds a field = value string to the SQL query
> statement. Now, I have two arrays which goes back a ways and gets used
> for other purposes too, but for a fixed purpose ctag like this, it'd
> be just as easy to use a regular map for that initial config file.
>
> The query statement building, of course, is done after all the data
> type validation and business rules validation have been passed (and
> the use of encode_sql).
>
> Those are some general ideas. It's gets a little complex to explain my
> whole system because it does many things beyond just the query
> building. I integrated the record locking, the validation invocation,
> the error handling, and the logging all in one place.
>
> -- greg willits
>
>
> --
> ------------------------------
> Lasso Support: http://support.omnipilot.com/
> Search the list archives: http://www.listsearch.com/lassotalk.lasso
> Manage your list subscription:  
> http://www.listsearch.com/lassotalk.lasso?manage
>


--
------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:  
http://www.listsearch.com/lassotalk.lasso?manage
Reply | Threaded
Open this post in threaded view
|

Re: Sql statement routines [Was: Re: Security measures]

Bil Corry
In reply to this post by Nikolaj de Fine Licht
> Of course what you need is a config file which
> 'translates' between input names and db field names, it's so logic once
> you know ;-)

The other option, to avoid a config file, would be to encrypt the name with
blowfish and decrypt it to use for the db.  This method isn't as secure as a
config file (someone could break your blowfish encryption), but it's also less
work.


- Bil

------

Bil Corry
[hidden email]

Enterprise internet application development and security consulting
  http://www.fivegeeks.com/

Tools for Rapid Lasso Development
  http://www.lassoware.com/
 
-----Original Message-----
From: [hidden email] [mailto:[hidden email]] On Behalf Of
Nikolaj de Fine Licht
Sent: Wednesday, June 08, 2005 6:42 PM
To: [hidden email]
Subject: Re: Sql statement routines [Was: Re: Security measures]

Greg,

Sorry for being late with a response, thank you very much for your
walk-through! Of course what you need is a config file which
'translates' between input names and db field names, it's so logic once
you know ;-)

It seems I will have a project soon suitable for experimenting with a
routine based on your outline!

Thanks again, best wishes,

Nikolaj

On 6. jun 2005, at 2:08, Greg Willits wrote:

> On Jun 5, 2005, at 3:02 PM, Nikolaj de Fine Licht wrote:
>
>> Your post intrigued to think in automation again.... "but how can you
>> build automatic sql statement routines without having form field
>> names and db field names equal"?
>
> I have a fairly evolved system for this. I presented an early version
> of it back at the Oct 2002 Summit. It hasn't changed in concept a
> whole lot since then (some additional security tweaks and features),
> but I have a bunch of enhancements waiting to be implemented.
>
> Anyway... in concept, what I do is this:
>
> First, create a config file that looks like this:
> http://www.fwpro.com/refc/dbTbl_{dbtblname}.lasso
>
> Next, write a routine to read that file, and split the data into two
> arrays: one for the input names and one for the field names. In my
> case, I wrote that as a custom type so that each array was an instance
> var of that ctype. This is more flexible than using regular vars.
> http://www.fwpro.com/refc/fwp_tableConfig.lasso
>
> You also need a general routine to convert form inputs into vars.
> There are security issues to beware of there too. Autovalidate does
> that for so in your case, you're set, and by the time your page code
> processes, you have vars = to the form inputs.
>
> Next, you need some generic code to create the INSERT an UPDATE
> queries. My routines for that are buried in an overall fancy system
> for abstracting query functions and combining it with several other
> steps. I have those written with a custom type wrapper for custom
> tags, but you can do it with a regular custom tag. One of the input
> parameters is a list of allowed fields. Just enter a comma list of the
> form input names. That input is form specific each time you use the
> tag. The work of defining the whole table is a generic config file,
> but the task of identifying just the allowed inputs for a given query
> is form specific.
>
> The core query building routine iterates through the allowed input
> names. The routine checks for a var with that name (i.e. data was
> submitted by the form) then looks for the matching db field name from
> the config data, and adds a field = value string to the SQL query
> statement. Now, I have two arrays which goes back a ways and gets used
> for other purposes too, but for a fixed purpose ctag like this, it'd
> be just as easy to use a regular map for that initial config file.
>
> The query statement building, of course, is done after all the data
> type validation and business rules validation have been passed (and
> the use of encode_sql).
>
> Those are some general ideas. It's gets a little complex to explain my
> whole system because it does many things beyond just the query
> building. I integrated the record locking, the validation invocation,
> the error handling, and the logging all in one place.
>
> -- greg willits
>
>
> --
> ------------------------------
> Lasso Support: http://support.omnipilot.com/
> Search the list archives: http://www.listsearch.com/lassotalk.lasso
> Manage your list subscription:  
> http://www.listsearch.com/lassotalk.lasso?manage
>


--
------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:  
http://www.listsearch.com/lassotalk.lasso?manage



--
------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:  
http://www.listsearch.com/lassotalk.lasso?manage
Reply | Threaded
Open this post in threaded view
|

Re: Sql statement routines [Was: Re: Security measures]

Greg Willits
In reply to this post by Nikolaj de Fine Licht
On Jun 8, 2005, at 11:42 PM, Bil Corry wrote:

>> Of course what you need is a config file which
>> 'translates' between input names and db field names, it's so logic
>> once
>> you know ;-)
>
> The other option, to avoid a config file, would be to encrypt the name
> with
> blowfish and decrypt it to use for the db.  This method isn't as
> secure as a
> config file (someone could break your blowfish encryption), but it's
> also less
> work.

Not when you have these babies handy :-)

http://www.fwpro.com/refc/refc_list.lasso?api=cnfg

-- greg


(welcome back -- hope the move went smoothly)


--
------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:  
http://www.listsearch.com/lassotalk.lasso?manage