[Bug]: Escaping single quotes (') in Dropdown when used in a query

[benhur07b]

Describe the bug

I have a table configured as a source query from a SQLite datasource.

There's a column/field in this table that contains country names (country). One of its values is Cote d'Ivoire which contains a single quote in the name.

There's a dropdown (country_select) that lists the country names.

The value from the dropdown is used in a SQL query on Evidence as below:

SELECT * from db.table
WHERE country = '${inputs.country_select.value}'

When Cote d'Ivoire is selected on the dropdown, the query breaks because of the single quote in the value.

SELECT * from db.table
WHERE country = 'Cote d'Ivoire'

Parser Error: syntax error at or near "Ivoire" LINE 3: WHERE country = 'Cote d'Ivoire' ^

If the single quote is manually escaped, the query works.

SELECT * from db.table
WHERE country = 'Cote d''Ivoire'

I may have missed something in the documentation but what would be the best way to resolve this or how to escape the single quote (') in the value obtained from the dropdown so it becomes usable as a parameter in a query?

I've tried using replace, using double quotes ("${inputs.country_select.value}" results in Binder Error: Referenced column "Cote d'Ivoire" not found in FROM clause!, among others.

P.S. There are other string/text columns in the database that contains single quotes in their values that will be used in other dropdowns/queries.

Steps to Reproduce

Add a column/field (e.g. country) in a table that contains a value that has a single quote (e.g. Cote d'Ivoire).

Add a Dropdown component referencing the column.

<Dropdown 
    data={sample_list}
    name=country_select
    value="country" 
    title="Country" 
    noDefault=true
/>

Add a query that uses the Dropdown value as parameter

SELECT * from db.table
WHERE country = '${inputs.country_select.value}'

Select Cote d'Ivoire in the Dropdown.

Severity

blocking all usage of Evidence

Additional Information, or Workarounds

I use the same sqlite database in a Datasette (https://github.com/simonw/datasette) instance and the filters work as intended even when selecting values that have single quotes (').

[archiewood]

Evidence is quite opinionated here, and does exact string replacememt

I'd suggest using a replace function to replace all instances of ' with ''

REPLACE('${inputs.thing.value}', '''', '''''')

(which is admittedly rather confusing due to the escaping of the ' characters!)

[benhur07b]

Hi @archiewood. Thanks for the quick response. I've tried using REPLACE before as mentioned in my initial report but it didn't work. I used the snippet you shared but got the same result.

Am I missing something or should I put REPLACE somewhere else?

Thanks in advance!

[archiewood]

My bad, before my morning tea, here's a worked example - do the replacement in js

```sql countries
select 'Cote d''Ivoire' as country union all
select 'Canada'union all
select 'USA'
```


<Dropdown data={countries} name="country" value="country"/>

```sql country_sales
with country_sales as (
    select 'Cote d''Ivoire' as country, 1000 as sales union all
    select 'Canada' as country, 2000 as sales union all
    select 'USA' as country, 3000 as sales
)
select * from country_sales
where country = '${inputs.country.value.replace("'", "''")}'
```

https://github.com/archiewood/escaping/blob/main/pages/index.md

[benhur07b]

Thanks @archiewood! Didn't think about doing the replace inside the codeblock with JS. Pretty neat.

Btw, I found that when there are more than 1 instance of a single quote in the text, the replace snippet fails (since replace only replaces the first instance) so I added a g modifier to replace or just used replaceAll() for a more generic solution since there are values with a lot of single quotes in other fields (e.g. some values are in other languages or have contractions).

This works:

'${inputs.country.value.replace(/'/g, "''")}'

This also works:

'${inputs.country.value.replaceAll("'", "''")}'

[nickfanion]

Have you tried dollar quoting? You wrap the filter input in double-dollar symbols ($$). I use it for my filter values with apostrophes and other special characters. I find it simpler than string replacement. For example:

SELECT * from db.table
WHERE country = $$${inputs.country_select.value}$$

DuckDB docs: https://duckdb.org/docs/stable/sql/data_types/literal_types.html#dollar-quoted-string-literals

[benhur07b]

Have you tried dollar quoting? You wrap the filter input in double-dollar symbols ($$). I use it for my filter values with apostrophes and other special characters. I find it simpler than string replacement. For example:

SELECT * from db.table WHERE country = $$${inputs.country_select.value}$$

DuckDB docs: https://duckdb.org/docs/stable/sql/data_types/literal_types.html#dollar-quoted-string-literals

Thanks @nickfanion! Can confirm that this also works.

Now we're only waiting for https://github.com/evidence-dev/evidence/issues/2507 to be resolved (although there are workarounds) and Evidence will be perfect for our use-case.