Missing Support for Parameterized Queries in databend-driver

[Shanmugavel-J]

Currently, the databend-driver does not support the ability to pass parameters during query execution. This limitation can significantly impact usability and developer experience, especially when working with dynamic or user-supplied values.

Example:
Consider the following query: SELECT * FROM TEST WHERE id = :1
In this case, the Bindings would be: [10]

It would be highly beneficial to support both : and ? as parameter placeholders in queries.

It would be beneficial to have the ability to bind parameters to queries like this, similar to other database drivers. This would allow for safer, more efficient queries, and help avoid SQL injection vulnerabilities.

Implementing this feature would enhance both performance and developer experience. Thanks!

Note: Wanted for nodejs. That is npm databend-driver

[rad-pat]

Yes, we would like this too. I believe already raised here #374

This would also help to pass binary values through from SQLAlchemy driver, I would hope.

[Shanmugavel-J]

@rad-pat Okay Cool! That package is for python. Wanted paramterized support for the npm databend-driver package though.

[Shanmugavel-J]

@BohuTANG @sundy-li Please let me know once this ticket is resolved. waiting for it!!

[sundy-li]

We may need to resolve it on databend server side finally.

But I will try to have a quick implementation on bendsql side.

Yet now we can use variable

set variable id = 3;
SELECT * FROM TEST WHERE id = $id;

[rad-pat]

Please also consider adding a method for returning a query string once the parameters have been bound, equivalent of mogrify in psycopg2 driver.

[Shanmugavel-J]

@sundy-li Please add support for both $ or ?.

For $ it would be like below,

Example query: SELECT * FROM TEST WHERE ID = $2 AND PAYLOAD = $1

Bindings should be: [2, 5]

The output would be: SELECT * FROM TEST WHERE ID = 5 AND PAYLOAD = 2

In nodejs script should be like for example function in databend-driver

dbconn = client.getConn();
const result = await dbconn.queryAll(sql, bindings);

PostgreSQL supports multiple binding styles, including $, ?. It would be great if this feature could be added to support all these formats for better flexibility.

[sundy-li]

SELECT * FROM TEST WHERE ID = $2 AND PAYLOAD = $1

@Shanmugavel-J $ may be conflicted with variable or column position ident in databend server

Because we already support to fetch fields from csv stage

select $1, $2 from @unload(file_format => 'csv_gzip');
----
1 2
3 4
5 6

we plan to support these in driver

1. ? , select *, ? from test where name = ? and age = ?
2. :name , select * from test where name = :name
3. :1, select * from test where name = :1 and age = :2 --- seems useless, it's covered by 1

two kinds of parameters

[Shanmugavel-J]

@sundy-li

:1, select * from test where name = :2 and age = :1 and test = :3 and test1 = :4

This would be beneficial, specifically when using positional bindings like $1 or :1.

Here, the values for the parameters :1, :2, :3, and :4 appear in a different order than they are referenced in the query. This approach allows for greater flexibility when the positions of the values in the bindings do not necessarily follow the same order.

[sundy-li]

@sundy-li

:1, select * from test where name = :2 and age = :1 and test = :3 and test1 = :4

This would be beneficial, specifically when using positional bindings like $1 or :1.

Here, the values for the parameters :1, :2, :3, and :4 appear in a different order than they are referenced in the query. This approach allows for greater flexibility when the positions of the values in the bindings do not necessarily follow the same order.

Ok, I choose to support $1 , $2

[Shanmugavel-J]

@sundy-li Thanks for understanding !!

If there is a function which would return the SQL query to execute That will be good as well.

ex:- dbconn.exec(sql, values).toString() or something else which would provide the whole query with the bindings as string.

select * from test where name = 6 and age = 2 and test = 5 and test1 = 1

[sundy-li]

If there is a function which would return the SQL query to execute That will be good as well.

Connection has a format_sql method now

    #[napi]
    pub fn format_sql(&self, sql: String, params: Option<Params>) -> Result<String> {
        Ok(self.inner.format_sql(&sql, params))
    }