Forum OpenACS Development: Re: Error whether using POSIX-style or SIMILAR TO for RE's

Collapse
2: Re: Error whether using POSIX-style or SIMILAR TO for RE's (response to 1)
Posted by Gustaf Neumann on 09/21/20 01:43 PM

This is actually a Tcl quoting question: Since you provided the SQL query between double quotes ("), Tcl applies substitution, finds something that looks like a Tcl command via command substitution and tries to execute the command 0-9.

In case, you do not want Tcl substitution, use curly braces like here:

db_0or1row select_revision {
  SELECT cr.title,  
    MAX(o.creation_date) AS creation_date, 
    COUNT(*) AS occurrence
  FROM cr_items ci, cr_revisionsx cr, acs_objects o 
  WHERE ci.item_id = cr.item_id 
    AND ci.item_id = o.object_id AND ci.content_type = 'qt_vehicle' 
    AND cr.title !~ '^[0-9]'  
    AND cr.title NOT IN ('UNKNOWN', 'FBF724','FBF124') 
  GROUP BY cr.title HAVING COUNT(*) > 1 
  ORDER BY COUNT(*) DESC
}

In general, Tcl substitutions should be avoided in SQL statements (or applied with special care), since these are potential attack vectors for SQL injection. Bind variables should be used instead wherever possible.

-g

PS: the markup character for code is for markdown a backtick (`) not a single quote (').

Collapse
3: Re: Error whether using POSIX-style or SIMILAR TO for RE's (response to 2)
Posted by Iuri Sampaio on 09/22/20 04:28 AM

... and I agree! Each code chunk must be in its respective layer ( preferably in its own file). Meaning TCL must be in the TCL layer (.tcl files) as well ADP and SQL. Plus, I've always preferred to use curly braces. It's even better/easier to indent code in Emacs editor.

The bad habit took place when I was learning to pass array variables within SQL blocks. Using double quotes becomes much simpler. For example:

 db_foreach select_groups {
     select ap.package_id, r.object_id_one as group_id, g.group_name, mr.member_state
                from   acs_rels r,
                membership_rels mr,
                groups g,
                application_groups ap
                where  r.rel_type      = 'membership_rel'
                and    r.object_id_two = $auth_info(user_id)
                and    mr.rel_id   = r.rel_id
                and    g.group_id  = r.object_id_one
                and    ap.group_id = g.group_id
                order by lower(g.group_name)
            " { ... }

As a matter of fact, I have no idea how one would pass TCL arrays within SQL blocks. Neither :authinfo(userid) nor :authinfo.userid work. Both return syntax error.

[21/Sep/2020:23:12:56][13720.7efbf356f700][-conn:qonteo:default:1:0-] Error: undefined variable 'auth_info' : while executing ...

Collapse
4: Re: Error whether using POSIX-style or SIMILAR TO for RE's (response to 3)
Posted by Gustaf Neumann on 09/22/20 11:01 AM

The bindvars support of the PostgreSQL driver is modeled after the Oracle bindvars support to provide compatibility. In Oracle, only scalar values are allowed (no Tcl arrays, dicts, etc.).

The OpenACS API supports the "-bind" argument to db_foreach, such one can write like:

db_foreach select_groups {
    select ap.package_id, ...
    from ...
    where  r.object_id_two = :user_id
    ...
} -bind [list user_id $auth_info(user_id)] {
    # loop body
}

or even

db_foreach select_groups {
    select ap.package_id, ...
    from ...
    where  r.object_id_two = :user_id
    ...
} -bind [array get auth_info] {
    # loop body
}

The advantage of the explicit list of bind names and values is that these can be passed around between procs without the danger of interfering with other Tcl variables. When when the variables and values form a Tcl dict, also "dict foreach" can be used.

PS: It seems that the "-bind" functionality for db_foreach was lost over the years. i've just committed a patch to oacs-5-10 to revive it.