[GRASS-user] Problem with executing some type od queries (with regular expresion parser)

[Glynn Clements]

Jarek Jasiewicz wrote:

> D3/3: db_select_int()
> D3/3:   SQL: SELECT cat FROM streams_coor WHERE link::text IN(SELECT 
> regexp_split_to_table((SELECT branch FROM links WHERE keyid='168'), E'\\.'))
> D3/3: Escaped SQL: SELECT cat FROM streams_coor WHERE link::text 
> IN(SELECT regexp_split_to_table((SELECT branch FROM links WHERE 
> keyid='168'), E'\\\\.'))
> D3/3: describe_table()
> 
> As we see, there is a problem with escape \ backslash
> grass (I don't know gis.m or  db_select_int()) adds backslash before.
> In pgAdmin I must add escape character manually as in example in post 
> before.
> 
> In gis.m this command should look like:
> 
> link::text IN(
>     SELECT regexp_split_to_table((
>         SELECT branch FROM links WHERE keyid='168'), E'\.'))
> 
> And everythik is OK. Porblem solved.

db/drivers/postgres/execute.c has the following:

    /* Postgres supports in addition to standard escape character ' (apostrophe) also \ (basckslash)
     * as this is not SQL standard, GRASS modules cannot work escape all \ in the text
     * because other drivers do not support this feature. For example, if a text contains 
     * string \' GRASS modules escape ' by another ' and string passed to driver is \''
     * postgres takes \' as ' but second ' remains not escaped, result is error.
     * Because of this, all occurencies of \ in sql are escaped by \ */
    str = G_str_replace(db_get_string(sql), "\\", "\\\\");

[Note that "\\" is a string containing a single backslash, as C also
uses backslash as an escape character.]

IOW, the "pg" DBMI driver assumes that every backslash which it sees
is supposed to be a literal backslash, and converts it to a
double-backslash escape sequence to ensure that it is interpreted as
such.

So you can't use PostgreSQL's C-like escapes (\n etc) within SQL
syntax.

-- 
Glynn Clements <glynn at gclements.plus.com>