Need a setting to disable unreliable OLEDB parameter check

[StruanT]

The smellsLikeOleDb regex on SqlMapper doesn't work correctly in all kinds of scenarios. If you are passing a model that has properties that you don't intend to parameterize and cannot be parameterized this results in a NotSupportedException

I think this would most easily solved by having a setting turn it off on this line (just make filterParams true and ignore this regex if a setting is enabled)

Dapper/Dapper/SqlMapper.cs

Line 2538 in 47ef1e7

filterParams = !smellsLikeOleDb.IsMatch(identity.sql);

Some examples that break this "smell check".

• Question marks in SQL Comments
  -- Why is the question mark in this comment treated as a parameter ?

• Question marks in strings. Issue with handling '?' character in SQL strings when using SQL Server #1914

• Using openquery execute LDAP Queries that start with <LDAP://:>;

• Postgres type casts Postgres type cast is interpreted as parameter #1299

This would all be solved by having a way to turn it off (make filterParams true and ignore this regex)

I see no reason not to have the option to disable this footgun completely for anyone not using OLEDB at all. For that matter... why not default it to off and let anyone using OLEDB turn it on?

[mgravell]

I can't disagree with anything you've said. There are no scenarios when the pseudo-positional parameters (?abc?) wouldn't be preferable. Agree, we should:

• add a property to turn it off, but leave the default behaviour as-is for now
• add release notes advertising that this default will be changed in a future version, and that anyone using naked ? parameters should manually set it
• release a "major" maybe start of 2024 with the default swapped, again with release notes highlighting it

[Giorgi]

Should the new setting live in SqlMapper.Settings ?

[mgravell]

Yes. I was planning to take a look when I get a moment, but I'd happily consider a PR if that's your plan.

[mgravell]

https://github.com/DapperLib/Dapper/releases/tag/2.1.11