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

Collapse
1: Error whether using POSIX-style or SIMILAR TO for RE's
Posted by Iuri Sampaio on 09/19/20 12:27 AM

Hi there, I'm trying to run the following query, on OACS layer, whether using POSIX-style RE or SIMILAR TO RE, to match string patterns, however it sill returns the error below.

1) POSIX-style RE

db_0or1row select_revision "SELECT cr.title,  
    MAX(o.creation_date) AS creation_date, COUNT(*) AS occurency
    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  ;" -column_array revisions

2) or SIMILAR TO RE

db_0or1row select_revision "SELECT cr.title,  
    MAX(o.creation_date) AS creation_date, COUNT(*) AS occurency
    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 NOT SIMILAR TO '^[0-9]'  
    AND cr.title NOT IN ('UNKNOWN', 'FBF724','FBF124') 
    GROUP BY cr.title HAVING COUNT(*) > 1 
    ORDER BY COUNT(*) DESC  ;" -column_array revisions

p.s. The very same query runs just fine in the PSQL prompt command line.

 title  |     creation_date      | occurency 
--------+------------------------+-----------
 111111 | 2020-09-18 17:21:31+00 |      1340
 849500 | 2020-09-18 14:49:04+00 |       165
 411111 | 2020-09-18 16:53:23+00 |       120
 311111 | 2020-09-18 16:47:35+00 |        54
 333333 | 2020-09-18 14:19:34+00 |        50
 502246 | 2020-08-14 13:29:39+00 |        29
 502106 | 2020-08-13 10:07:24+00 |        28
 365507 | 2020-08-10 18:16:22+00 |        25
 900763 | 2020-09-01 10:21:08+00 |        22
 211111 | 2020-09-15 11:07:17+00 |        21
 895030 | 2020-09-17 10:15:28+00 |        20
 184950 | 2020-09-18 14:38:22+00 |        17

Is there any change to how we write PGSQL queries using regular expression in POSIX-style or SIMILAR TO ?

Best wishes, I

Reference: https://www.postgresql.org/docs/9.3/functions-matching.html

ERROR LOG

[18/Sep/2020:19:03:12][18107.7efbf3d70700][-conn:qonteo:default:0:121-] Error: invalid command name "0-9"
:        while executing
:    "0-9"
:        ("uplevel" body line 92)
:        invoked from within
:    "uplevel {
:        ad_page_contract {
:        @author Neophytos Demetriou <k2pts@cytanet.com.cy>
:        @creation-date September 01, 2001
:        @cvs-id $Id: search...."
:        (procedure "code::tcl::/var/www/qonteo/oacs//packages/qt-rest/www/search" line 2)
:        invoked from within
:    "code::tcl::$__adp_stub"
:        ("uplevel" body line 12)
:        invoked from within
:    "uplevel {
:    
:            if { [file exists $__adp_stub.tcl] } {
:    
:                # ensure that data source preparation procedure exists and is up-to-date
:          ..."
:        (procedure "adp_prepare" line 2)
:        invoked from within
:    "adp_prepare"
:        invoked from within
:    "template::adp_parse $themed_template {}"
:        (procedure "adp_parse_ad_conn_file" line 14)
:        invoked from within
:    "$handler"
:        ("uplevel" body line 2)
:        invoked from within
:    "uplevel $code"
:        invoked from within
:    "ad_try {
:                    $handler
:                } ad_script_abort val {
:                    # do nothing
:                }"
:        invoked from within
:    "rp_serve_concrete_file [ad_conn file]"
:        (procedure "::nsf::procs::rp_serve_abstract_file" line 60)
:        invoked from within
:    "rp_serve_abstract_file "$root/$extra_url""
:        ("uplevel" body line 2)
:        invoked from within
:    "uplevel $code"
:        invoked from within
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.