I am using Mono/C# with ADO.NET and unixODBC with libmyodbc to connect to a local MySql Server on a x64 Ubuntu Server.
The relevant part of /etc/odbcinst.ini:
[Default]
Driver = /usr/lib/x86_64-linux-gnu/odbc/libmyodbc.so
Connecting using a DSN and querying / updating / inserting works well, with one exception: When I try to use a query with a parameter thats a string in c# and varchar type in MySql, it does not work: the query is executed as if I had passed an empty string as a parameter.
This only happens with string as parameters - If I encode the string value inside the query string myself, the query works as expected. But to guard against SQL injections, I need to use parameters.
So the following code works:
string name = "test";
...
using (var cmd = new OdbcCommand("delete from Table1 where Table1.Name = " + name, conn))
{
cmd.ExecuteNonQuery();
}
but not:
using (var cmd = new OdbcCommand("delete from Table1 where Table1.Name = ?", conn))
{
cmd.Parameters.Add("@Name", OdbcType.VarChar, name.Length).Value = name;
cmd.ExecuteNonQuery();
}
If the type of the parameter is not a string, so for example an Integer, the inline string as well as the parameter version work as expected.
Another interesting point: if I use
cmd.Parameters.AddWithValue("@name", name);
an OdbcException with an Memory allocation error is thrown (that is why I set the type OdbcType.VarChar explicitly, setting the Size makes no difference)
Any pointers are appreciated - Thank you!
Take care,
Martin
