Trying to pass a regular expression parameter, dollar sign is doubled

[rad-pat]

Trying to do similar to:

sqlalchemy.select(sqlalchemy.func.regexp_like(col, '^[0-9]*$'))

The dollar sign is doubled in the generated SQL because of this line

databend-sqlalchemy/databend_sqlalchemy/connector.py

Line 41 in 3b65a30

item.replace("\\", "\\\\").replace("'", "\\'").replace("$", "$$").replace("%", "%%")

@hantmac, any idea about the reason for this replacement?

[hantmac]

@rad-pat Hi , Replacing a single $ with $$ ensures that $ in the original string is correctly interpreted as a literal character and
this can also helps prevent SQL injection and syntax errors.

[rad-pat]

Thanks, do you perhaps have an example of this so I can set up a test for it? Obviously I want to fix the issue I have without breaking other use cases.

[rad-pat]

The best solution by far would be to be able to push the parameters into the driver, but for now the bendSQL doesn't seem to support query parameters for some reason

[hantmac]

The best solution by far would be to be able to push the parameters into the driver, but for now the bendSQL doesn't seem to support query parameters for some reason

Got it, so the bendSQL supporting query parameters will solve your user case?

[hantmac]

Thanks, do you perhaps have an example of this so I can set up a test for it? Obviously I want to fix the issue I have without breaking other use cases.

For example:

price = "$100"
query = "SELECT * FROM products WHERE price_display=%s"

after

SELECT * FROM products WHERE price_display='$$100'

orignal

path = "C:\Users\file"
query = "SELECT * FROM files WHERE path=%s"

after

SELECT * FROM files WHERE path='C:\\Users\\file'

[rad-pat]

@hantmac, I tried these queries with the bendsql, the escaped paths work fine, but the double dollar signs are not substituted..

CREATE TABLE IF NOT EXISTS t_str (c1 string);
INSERT INTO t_str VALUES ('$100');
INSERT INTO t_str VALUES ('$$100');
INSERT INTO t_str VALUES ('c:\\some\\path');
INSERT INTO t_str VALUES ('c:\some\path');
select * from t_str;
┌──────────────────┐
│        c1        │
│ Nullable(String) │
├──────────────────┤
│ c:\some\path     │
│ $$100            │
│ $100             │
│ c:\some\path     │
└──────────────────┘
select * from t_str where c1 = '$100';
┌──────────────────┐
│        c1        │
│ Nullable(String) │
├──────────────────┤
│ $100             │
└──────────────────┘
select * from t_str where c1 = '$$100';
┌──────────────────┐
│        c1        │
│ Nullable(String) │
├──────────────────┤
│ $$100            │
└──────────────────┘

Possible to just remove the replacement of $ with $$ ?

[hantmac]

@hantmac, I tried these queries with the bendsql, the escaped paths work fine, but the double dollar signs are not substituted..

CREATE TABLE IF NOT EXISTS t_str (c1 string);
INSERT INTO t_str VALUES ('$100');
INSERT INTO t_str VALUES ('$$100');
INSERT INTO t_str VALUES ('c:\\some\\path');
INSERT INTO t_str VALUES ('c:\some\path');
select * from t_str;
┌──────────────────┐
│        c1        │
│ Nullable(String) │
├──────────────────┤
│ c:\some\path     │
│ $$100            │
│ $100             │
│ c:\some\path     │
└──────────────────┘
select * from t_str where c1 = '$100';
┌──────────────────┐
│        c1        │
│ Nullable(String) │
├──────────────────┤
│ $100             │
└──────────────────┘
select * from t_str where c1 = '$$100';
┌──────────────────┐
│        c1        │
│ Nullable(String) │
├──────────────────┤
│ $$100            │
└──────────────────┘

Possible to just remove the replacement of $ with $$ ?

Oh I forgot it. The replacement is suit for databend-py, but now the driver has been changed to bendSQL, so it should be removed. Thanks!