@p# collisions when more than 10 parameters

[chadbergeron]

Ran into an issue today with this where the parameters being assigned were not getting incremented correctly inside of a CTE that also had a subquery in it using a list of parameters.

pseudocode example:

;with cte_store_basic_data as(
    select distinct columns 
    from (    
    select columns
    from table pb WITH (NOLOCK)
    inner join table2 sb WITH (NOLOCK) on <on...>
    where pb.domain in (@p01,@p02)
    union ALL
    ...
    where sb.domain in (@p11,@p12)
    union ALL
    ...
    where sb.ssl_domain in (@p21,@p22)       
    ) d
        where pb.[state] <> @p3 
    )
    select ..... 
    where <a whole mess of other parameters are here, they all increment correctly>

Just guessing at what the issue is here, but it looks like when array-based parameters are being created they're getting a created with the format of @p<parameternumber><elementnumber>, so if you have an array parameter that's first in the list @p11 will collide if you have more than 12 total parameters.

Think the fix is in SqlParameterMapper.cs (IsEnumerable(parameter.Argument) ? options.ParameterArrayNameSuffix : "") should go to (IsEnumerable(parameter.Argument) ? $"{options.ParameterArrayNameSuffix}s" : "") or something similar.

[Drizin]

I didn't get it. Can you please provide a sample code to reproduce the problem?

[shishank-chhetri]

I came across the same issue as well; if you have following example:

string[] test = ["test", "test2"];
FormattableString sql = @$"
select * from INFORMATION_SCHEMA.COLUMNS
where {1} = 0 
   	or PlainText in {test} 
   	or {1} = 0 or {1} = 0 or {1} = 0 or {1} = 0 or {1} = 0 or {1} = 0 or {1} = 0 or {1} = 0 or {1} = 0
	or PlainText in {test.Skip(2)}";

using var connection = _connectionProvider.GetConnection();
var query = connection.QueryBuilder(sql);
using var dbResult = await query.QueryMultipleAsync();

it executes following sql:

exec sp_executesql N'
select * from INFORMATION_SCHEMA.COLUMNS
where @p0 = 0 
   	or PlainText in ((SELECT @parray11 WHERE 1 = 0),@parray12) 
   	or @p2 = 0 or @p3 = 0 or @p4 = 0 or @p5 = 0 or @p6 = 0 or @p7 = 0 or @p8 = 0 or @p9 = 0 or @p10 = 0
	or PlainText in (SELECT @parray11 WHERE 1 = 0)',N'@p0 int,@parray11 nvarchar(4000),@parray12 nvarchar(4000),@p2 int,@p3 int,@p4 int,@p5 int,@p6 int,@p7 int,@p8 int,@p9 int,@p10 int,@parray11 nvarchar(4000)',@p0=1,@parray11=N'test',@parray12=N'test2',@p2=1,@p3=1,@p4=1,@p5=1,@p6=1,@p7=1,@p8=1,@p9=1,@p10=1,@parray11=NULL

with two @parray11 (one for @parray1 item 1 and one for the empty @parray11).

[Drizin]

Looks like this bug happens when Dapper expands an empty array. Please test with latest package 2.4.0

[Drizin]

Relevant commit https://github.com/Drizin/InterpolatedSql/commit/a2333126910a4b537cd0e9849528d656e80967ac