in reply to Magical SQL

You are announcing code without telling where the code can be found. That isn't really useful.

If you want database abstraction, the ultimate DBI SQL abstracter is DBIx::Abstract.

Adding per-column update privileges in the database seems like a bad idea to me, I'd just never give my argument hash directly to any database interface. Security isn't only about which fields you can or cannot update, you must also consider invalid values and insecure values. And when parsing/checking that, you might as well build yourself another hash while you're at it. 

- Yes, I reinvent wheels.
- Spam: Visit eurotraQ.

Replies are listed 'Best First'.
Re: Re: Magical SQL
by powerman (Friar) on Apr 26, 2002 at 11:24 UTC
    1) I'm not announcing code, I'm announcing only idea and some examples about how this may look like in real code. I have at this time code which realize this idea, but this code is not ready for publishing at this time (it was written in ~6 hours and commented only in Russian) and most part of this code WILL be changed in near future becouse we work up hardly on idea itself, functions interface and so on.

    2) Checking for invalid and insecure values is out of scope of this idea and must be done as usual before calling Update().

    I don't want waste code with building another hash with many not important fields. This is ugly. See this example: 

    # ... here user-supplied values was checked

# variant 1, selecting all fields
%newQ = ( id_cust=>$CustID, login => $Q{login}, pass => $Q{pass}, emai
+l => $Q{email}, addr1 => $Q{addr1}, addr2 => $Q{addr2}, phone => $Q{p
+hone}, ........... );

# variant 2, force only required fields
%newQ = (%Q, id_cust=>$CustID);

    [download]
    I think my way is right and secure becouse:
    • Really required fields like login and pass will be checked before calling Update(), and if user do not send values for these fields Update() will not be called.
    • If field like addr1 is not required, does not matter send user &addr1= or not if in table this field declared as NOT NULL.
    • Variant 2 mean user can update any users's own field. This is flexible and secure.

    Update:
    3) About DBIx::Abstract - my inferface is more suitable for lazy programmers becouse it is designed to parse all required information from single hash in form, which can be supplied by user running CGI. And for best security this hash NEVER contain part of SQL queries like "DATE_ADD(NOW(), INTERVAL ? HOUR)". Such type of queries can be done with help of special field names like FIELDNAME__date_add.

[reply]
[d/l]
      I think this is good, in fact I've just done something like it, but with one extra feature: it is a class, so for a particular application it can be overriden. Here is the routine that makes an input field in a form:
      # print an input element for a field, when replacing a record
sub replaceField {
    my ($this, $name, $value, $type, $isKey) = @_;

    # check for special handler in subclass
    my $h = "replace_$name";
    if ($this->can($h)) {
        $this->$h($name, $value, $type, $isKey);
		return
	};
	
    # default processing		
    my $attr = ''; # special attributes for tag
    if ('_' eq substr $name,0,1 ) {$attr .= 'READONLY ';};
    print qq($name: <input name="$name" class="$name" value="$value" $attr type="text" ></input>);
}
[reply]

In Section Meditations