Adding parameters to a query with Informix and Dapper fails with syntax exception
asp.net-mvc c# dapper informix
Question
I'm attempting to use parameterized queries with Informix over ODBC, but any attempt to add parameters fails with this exception:
$exception {"ERROR [42000] [Informix .NET provider][Informix]A syntax error has occurred."} System.Exception {IBM.Data.Informix.IfxException}
Here is the code that fails:
List<ItemAttribute> items = con.Query<ItemAttribute>("select * from oe_cnvwrk where cwr_genero = @cwr_genero", new{cwr_genero = cwr_genero}).ToList();
Using it without parameters, like this example, works flawlessly, but opens the application up to injection attacks:
ItemHeader itemHeader = con.Query<ItemHeader>("select * from oe_cnvhdr where hdr_control_id = " + hdr_control_id).Single();
I was able to find a previously-listed question about this exact same issue here, but it was never answered. I'm hoping somebody will have an idea of how to handle the problem: Dapper not adding parameters
Any ideas a way to resolve this, or is there a different method for handling parameterization with Dapper that might work?
Expert Answer
Not yet released to NuGet, but the source code now contains support for pseudo-positional parameters. These are implemented by pattern recognition, such that ?abc? in your text maps to the parameter named from member abc, but using positional ? syntax. So if you issue:
List<ItemAttribute> items = con.Query<ItemAttribute>(
"select * from oe_cnvwrk where cwr_genero = ?cwr_genero?",
new{cwr_genero = cwr_genero}).ToList();
it should work; the actual query executed is:
select * from oe_cnvwrk where cwr_genero = ?
where the parameter added is the one from the member marked cwr_genero. This allows the member to be resolved correctly. The special ?foo? pattern is used as the indicator to switch to positional parameters.
Note that individual parameters can only be referenced once per query; the following will not work:
select * from sometable where foo = ?x? or bar = ?x?