UnsafeError on aliases, can I bypass it ?

[euri10]

I haven't found a way to build an alias column with "unsafe" characters

Currently when doing this I get an UnsafeError

V('col1').as_('alias can be any string')

    raise UnsafeError(f'str contain unsafe (non word) characters: "{s}"')
buildpg.components.UnsafeError: str contain unsafe (non word) characters: "alias can be any string"

it's legit to have spaces in alias, slashes also

[samuelcolvin]

Short answer:

from buildpg import V, RawDangerous
V('col1').operate(RawDangerous(' as '), RawDangerous('"foo bar"'))

I think we need to modify as_var to allow RawDangerous through. You should avoid using RawDangerous unless absolutely necessary, it's so named to make it scary to use and hard to use by mistake, a bit like "hazmat" in the cryptography package and dangerouslySetInnerHTML={{__html: in react.

I guess we should also change .as() to take strings with spaces.

---

Longer answer:

buildpg has to have two ways of building queries:

• concatenating strings to build up SQL, this allows completely flexibility but also comes with the risk of SQL injection and the risks associated with that
• using positional parameters, here postgresql takes care of the substitution so SQL injection is impossible, but this doesn't work for all forms of dynamic query

There's always a risk that someone mistakenly uses substitution when they should used parameters, to mitigate the risk that this provides a vector for full blown SQL injection I have some crude checks on what's allowed to V (short for "Variable"), including preventing spaces. I didn't think about aliases having spaces, hence why it's difficult.

[euri10]

thanks for the detailed explanation @samuelcolvin

while the short answer solved the problem I exposed, I should have been more precise at first.

I'm trying to build a query with dynamic columns and their respective aliases that may contain buildpg "forbidden" chars. I originally did that feeding the column names and their respective aliases with a dictionary r={'col1': 'label of col1', 'col2': 'label2 with /'}

without aliases there is no issue: render("select :alias_columns from table1", alias_columns=funcs.comma_sep(*[V(k) for k,v in r.items()]))

with the aliases it's the issue mentionned: render("select :alias_columns from table1", alias_columns=funcs.comma_sep(*[V(k) for k,v in r.items()]))

if I try your workaround, unfortunately the generated SQL is incorrect, there are parenthesis around each col/alias

render("select :alias_columns from table1", alias_columns=funcs.comma_sep(*[V(k).operate(RawDangerous(' as '), RawDangerous(f'"{v}"')) for k,v in r.items()]))
Out[76]: 
('select (col1 as "label of col1"), (col2 as "label2 with / or -") from table1',
 [])

[samuelcolvin]

I'm still not clear on the SQL you're trying to write?

If you can't get buildpg's primitives to build the SQL you need, in the end you could just build the string using standard python string operations and wrap the whole thing in RawDangerous, just be absolutely sure you haven't included any user generated content in that string.

[euri10]

I'm still not clear on the SQL you're trying to write?

If you can't get buildpg's primitives to build the SQL you need, in the end you could just build the string using standard python string operations and wrap the whole thing in RawDangerous, just be absolutely sure you haven't included any user generated content in that string.

I'd like to write

select col1 as "label of col1", col2 as "label2 with / or -" from table1

from this dictionary:

 r={'col1': 'label of col1', 'col2': 'label2 with /'}

Using as_ yields the UnsafeError

render("select :alias_columns from table1", alias_columns=funcs.comma_sep(*[V(k).as_(v) for k,v in r.items()]))

and the workaround puts parenthesis that aren't correct (asyncpg.exceptions.PostgresSyntaxError: syntax error at or near "as" ), not sure where this happens in buildpg

render("select :alias_columns from table1", alias_columns=funcs.comma_sep(*[V(k).operate(RawDangerous(' as '), RawDangerous(f'"{v}"')) for k,v in r.items()]))
Out[76]: 
('select (col1 as "label of col1"), (col2 as "label2 with / or -") from table1',
 [])

Will try differently as you advised if this seems not possible :)