Parameter binding failure with string containing "{{value}}" and lack of named parameter support

[robd003]

Problem Statement

I have rather complicated SQL with many repeated parameters in different CTEs. It's much easier to do something like this: SELECT field1, field2, field3 FROM table WHERE param_a = %(input_a)s AND param_b = %(input_b)s

Unfortunately CrateDB seems to only work with an ordered list of parameters corresponding to either %s or $1, $2, etc. This absolutely sucks when you have 5 - 10 parameters being used over and over again.

When using psycopg3 with Django you can only use %s placeholders and even in that case it seems like it ends up replacing every instance with literals.

I recently found an issue where an input of {{site_source_name}} was passed to CrateDB via the Postgres Wire Protocol and it turned into this: SELECT field1, field2 FROM table WHERE utm_field = {{site_source_name}}

This worked just fine when manually queried through the Crate Admin UI with: SELECT field1, field2 FROM table WHERE utm_field = '{{site_source_name}}'

Crate then mis-interpreted the unquoted input as another column name rather than a quoted string. Error below:

psycopg.errors.UndefinedColumn: Column site_source_name unknown
CONTEXT:  io.crate.analyze.relations.FullQualifiedNameFieldProvider.resolveField(FullQualifiedNameFieldProvider.java:169)
io.crate.analyze.expressions.ExpressionAnalyzer$InnerExpressionAnalyzer.visitQualifiedNameReference(ExpressionAnalyzer.java:1039)
io.crate.analyze.expressions.ExpressionAnalyzer$InnerExpressionAnalyzer.visitQualifiedNameReference(ExpressionAnalyzer.java:449)
io.crate.sql.tree.QualifiedNameReference.accept(QualifiedNameReference.java:38)
io.crate.analyze.expressions.ExpressionAnalyzer$InnerExpressionAnalyzer.visitArrayLiteral(ExpressionAnalyzer.java:1098)
io.crate.analyze.expressions.ExpressionAnalyzer$InnerExpressionAnalyzer.visitArrayLiteral(ExpressionAnalyzer.java:449)
io.crate.sql.tree.ArrayLiteral.accept(ArrayLiteral.java:40)
io.crate.analyze.expressions.ExpressionAnalyzer$InnerExpressionAnalyzer.visitArrayLiteral(ExpressionAnalyzer.java:1098)
io.crate.analyze.expressions.ExpressionAnalyzer$InnerExpressionAnalyzer.visitArrayLiteral(ExpressionAnalyzer.java:449)
io.crate.sql.tree.ArrayLiteral.accept(ArrayLiteral.java:40)
io.crate.analyze.expressions.ExpressionAnalyzer$InnerExpressionAnalyzer.visitComparisonExpression(ExpressionAnalyzer.java:870)
io.crate.analyze.expressions.ExpressionAnalyzer$InnerExpressionAnalyzer.visitComparisonExpression(ExpressionAnalyzer.java:449)
io.crate.sql.tree.ComparisonExpression.accept(ComparisonExpression.java:80)
io.crate.analyze.expressions.ExpressionAnalyzer$InnerExpressionAnalyzer.visitLogicalBinaryExpression(ExpressionAnalyzer.java:853)
io.crate.analyze.expressions.ExpressionAnalyzer$InnerExpressionAnalyzer.visitLogicalBinaryExpression(ExpressionAnalyzer.java:449)
io.crate.sql.tree.LogicalBinaryExpression.accept(LogicalBinaryExpression.java:58)
io.crate.analyze.expressions.ExpressionAnalyzer$InnerExpressionAnalyzer.visitLogicalBinaryExpression(ExpressionAnalyzer.java:852)
io.crate.analyze.expressions.ExpressionAnalyzer$InnerExpressionAnalyzer.visitLogicalBinaryExpression(ExpressionAnalyzer.java:449)
io.crate.sql.tree.LogicalBinaryExpression.accept(LogicalBinaryExpression.java:58)
io.crate.analyze.expressions.ExpressionAnalyzer$InnerExpressionAnalyzer.visitLogicalBinaryExpression(ExpressionAnalyzer.java:852)

Possible Solutions

Ideally support named parameters in SQL and params input.

Open to any suggestions @amotl has!

Considered Alternatives

I've written a simple named parameter to list parameter converter:

BIND_PARAMS_REGEX = re.compile(r'%\((\w+)\)s')

class PositionParamReplacer:
    """Example:
         template = "SELECT * FROM foo WHERE a = %(a)s AND b = %(b)s"
         param_map = {"a": 123, "b": 456}
         sql, params = make_bind_params(template, param_map)
         # sql -> "SELECT * FROM foo WHERE a = $1 AND b = $2"
         # params -> [123, 456]"""

    __slots__ = ("param_map", "params", "counter")
    def __init__(self, param_map):
        self.param_map = param_map
        self.params = []
        self.counter = 1

    def __call__(self, match):
        name = match.group(1)
        if name not in self.param_map:
            raise KeyError(f"Missing parameter: {name}")
        value = self.param_map[name]
        self.params.append(value)
        placeholder = f"${self.counter}"
        self.counter += 1
        return placeholder

class SParamReplacer:
    """Example:
         template = "SELECT * FROM foo WHERE a = %(a)s AND b = %(b)s"
         param_map = {"a": 123, "b": 456}
         sql, params = make_bind_params(template, param_map)
         # sql -> "SELECT * FROM foo WHERE a = %s AND b = %s"
         # params -> [123, 456]"""

    __slots__ = ("param_map", "params")
    def __init__(self, param_map):
        self.param_map = param_map
        self.params = []

    def __call__(self, match):
        name = match.group(1)
        if name not in self.param_map:
            raise KeyError(f"Missing parameter: {name}")
        value = self.param_map[name]
        self.params.append(value)
        return "%s"

def make_bind_params(template, param_map):
    """Input SQL template with %(param)s placeholders
    and returns a version with ordered placeholders and a list of params
    This is because CrateDB has no support for named parameters
    """

    replacer = SParamReplacer(param_map)
    sql = BIND_PARAMS_REGEX.sub(replacer, template)
    return sql, replacer.params

Unfortunately the SParamReplacer doesn't work with Django as all of the values turn into literals rather than quoted strings.

The PositionParamReplacer fails and returns: django.db.utils.ProgrammingError: the query has 0 placeholders but 32 parameters were passed

[seut]

Crate then mis-interpreted the unquoted input as another column name rather than a quoted string. Error below:

I'm not sure if I understand this issue correctly as all unquoted strings are treat as identifiers and not literals, this is SQL standard.

I recently found an issue where an input of {{site_source_name}} was passed to CrateDB via the Postgres Wire Protocol and it turned into this: SELECT field1, field2 FROM table WHERE utm_field = {{site_source_name}}

So the issue here is that you declared a prepared statement with SELECT field1, field2 FROM table WHERE utm_field = ?
and bound the string {{site_source_name}} to the first parameter, but CrateDB did not use it as a string literal?
Where did you spot that CrateDB uses that a wrong query with the unquoted string?

Would be great if you can provide reproducible steps and and best, create a dedicate bug report to move this out of the feature request for the named parameter support. Thanks!

[mfussenegger]

Moved this to the crate/crate-python repository. We can't really support named parameters on the server side because it would also require protocol level support which the pgwire protocol doesn't have. It only supports a list of values as part of the bind message. No names.

The way this works in clients like psycopg is that the named parameter -> positional parameter mapping happens within the client.

For example, the following already works with CrateDB and psycopg3:

import psycopg

with psycopg.connect("host=127.0.0.1 dbname=doc user=crate") as conn:
    with conn.cursor() as cur:
        cur.execute("create table if not exists tbl (x int)")
        cur.execute("insert into tbl (x) values (%(x)s)", {"x": 10})

(See https://www.psycopg.org/psycopg3/docs/basic/params.html or https://www.psycopg.org/psycopg3/docs/basic/tstrings.html#template-strings)

What we'd need to do in crate-python is to change the supported paramstyle. Currently it is qmark:

crate-python/src/crate/client/__init__.py

Line 49 in 778881a

paramstyle = "qmark"