.NET Core 2.1/2.2 & Mysql Parameters not working

[amaszorek]

This code doesn't work, returns null:

User user;
string query = "select user_id from users where email = @email";
using (IDbConnection conn = _connectionFactory.GetConnection())
{
conn.Open();
User queryuser = new User {email = email};
var result = conn.Query(query, queryuser).SingleOrDefault();
user = result;
}

This code returns the user data:

User user;
string query = $"select user_id from users where email = '{email}';";
using (IDbConnection conn = _connectionFactory.GetConnection())
{
conn.Open();
var result = conn.Query(query).SingleOrDefault();
user = result;
}

[mgravell]

That looks like it should work. Are you sure there isn't an exception being thrown? Unfortunately different ado.net providers have different parameter syntax, so it is hard to know exactly which parameter prefix should be used but again: that looks valid.

is the email property public? is it a property? or is it a naked field?

just to be explicit: please don't use he second version - it is a sql injection hole

[amaszorek]

That looks like it should work. Are you sure there isn't an exception being thrown? Unfortunately different ado.net providers have different parameter syntax, so it is hard to know exactly which parameter prefix should be used but again: that looks valid.

is the email property public? is it a property? or is it a naked field?

just to be explicit: please don't use he second version - it is a sql injection hole

Hey,
thanks for the replay.

This is a very simple code:
I also think that should work, but it isn't. The second option I just tried to be sure that the email is in the db.

I think that it's some problem with my environment, but I don't have an idea how to track down it.

I use Devart.Data.MySql 8.10.1086 provider (I check also the latest version).

Only code which works after many combinations (but this is not what shuld be :)):

    public async Task<Uzytkownicy> GetAsync(string email)
    {
        try
        {
            Uzytkownicy user;
            using (IDbConnection conn = _connectionFactory.GetConnection())
            {
                conn.Open();

                DynamicParameters parameters = new DynamicParameters();
                parameters.Add("@@email", email);

                var result = conn
                    .Query<Uzytkownicy>(
                        "select uzytkownik_id, firma_id, imie, nazwisko, email from uzytkownicy where email = @email;",
                        parameters).SingleOrDefault();
                user = result;
            }

            return user;
        }
        catch (Exception ex)
        {
            return null;
        }
    }

(edited by admin for markdown reasons)

[amaszorek]

It works correctly with MySql.Data, so this behavior is caused by Devart library.

[mgravell]

OK, from here

Note that the name of the MySqlParameter object in the collection of the command should contain the '@' prefix if parameter in CommandText is used with the '@' prefix. If the parameter in CommandText contains the ':' prefix, the name of the MySqlParameter object in the command's collection should be used without any prefix.

Dapper doesn't add a prefix, because it doesn't know the SQL variant, so it sounds like the second half of that sentence applies. As such, try using :email instead of @email in the query

[amaszorek]

I tried this, and also doesn't work. It works in this way but with MySql.Data, not Devart.

[mgravell]

sounds like a support question for devart, then?

[amaszorek]

Yes, thanks.