[BUG] MSSQL Safe SQL Parameter Not Working

[alexwijoyo]

Description

When I run query using safe SQL parameter, I got Incorrect syntax near '?'.

Steps to reproduce

1. Create Safe parameterized select statement using MSSQL Resource
2. Click on Save
3. Clic on Run
4. You will get run action error: mssql: Incorrect syntax near '?'.
5. Sent SQL:
   exec sp_executesql N'select der.*
   from DailyExchangeRate der
   where der.SrcCurrencyId<>''USD'' and
   Date between ? and ?',N'@p1 nvarchar(max),@p2 nvarchar(max)',@p1=N'',@p2=N''
6. Should be:
   exec sp_executesql N'select der.*
   from DailyExchangeRate der
   where der.SrcCurrencyId<>''USD'' and
   Date between @p1 and @p2',N'@p1 nvarchar(10),@p2 nvarchar(10)',@p1=N'2024-03-01',@p2=N'2024-07-31'

Version

self-host

ILLA Builder Version

latest

Browser

No response

Operating System

• macOS
• Windows
• Linux

Additional Information

No response

[naj1n]

This is an example provided by the third-party library the illa-builder is using: https://github.com/microsoft/go-mssqldb?tab=readme-ov-file#parameters. The ? is not supported as a parameter placeholder.

[alexwijoyo]

@naj1n the problem is because illa-builder send the query using positional ? parameter placeholder not named parameter such as @p1. Please read my step 5 & 6. That a trace from sql server profiler.

[naj1n]

According to the code comments at https://github.com/illacloud/builder-backend/blob/8664551df998df0fe2711b51d6ef8e439fa8b2d5/src/utils/parser/sql/escaper.go#L371, changes need to be made in the way SQL is written.

[alexwijoyo]

@naj1n ,
How should I rewrite my sql:
select der.* from DailyExchangeRate der where der.SrcCurrencyId<>'USD' and Date between '{{dtrDer.value[0]}}' and '{{dtrDer.value[1]}}'

[alexwijoyo]

@naj1n ,
Any progress about this?

[naj1n]

I will try to run some tests and will update here with any progress.