Dapper Execute returns -1 value?

Question

The following SQL query returns 1 when the given values are available, and 0 when not available, and it works fine in SQL Server Management Studio,

select 
    case 
       when exists (select * from [dbo].[user] 
                    where userName = 'admin' 
                      and password = 'admin') 
          then cast(1 as bit) 
          else cast(0 as bit)
    end

The same query with dapper ORM is the following:

public int login(string userName, string password)
{
        string sql = "select case when exists(select * from user where userName = @usernamepara and password = @passwordpara) then cast(1 as bit) else cast(0 as bit )end";

        using(IDbConnection conn = dbConnection)
        {
            conn.Open();

            var res = conn.Execute(sql, 
                                   param: new { usernamepara = userName, 
                                                passwordpara = password });
            conn.Close();
            return res;
        }
    }

But when this method is called, it returns -1 for matching and non matching records. What's wrong here ?

I don't see how that `CASE` expression can return anything other than 0 and 1. Is there some exception happening from your Dapper code? — Tim Biegeleisen, Oct 18 '18 at 04:46
no exceptions it just returns -1,whether the values are matched or not — pl-jay, Oct 18 '18 at 04:48
Why don't you just make your query `SELECT COUNT(*) FROM user WHERE username = @user and password = @password` and compare the result in your code using int? ps, please assure me you salted and hashed your password and didn't just store it plain text.. — Caius Jard, Oct 18 '18 at 04:52
Possible duplicate of/can be solved by https://stackoverflow.com/questions/31277086/bool-type-return-rule-in-dapper — Caius Jard, Oct 18 '18 at 04:57
Salt/hash your password now- no excuses - "there is nothing so permanent as temporary" I was once told by a non techie in response to my saying "it's just a temporary bodge, I'll do it properly later" - took 6 years before it was fixed properly :) — Caius Jard, Oct 18 '18 at 04:59
actually it's not a issue with password, it's something wrong with dapper methods, i don't know what exactly it is — pl-jay, Oct 18 '18 at 05:06
I never said it was an issue with the password, I'm just pointing out that not hashing a password from the outset is a really shitty idea because it'll get all the way into production without being changed, and then it will be that way for years because everyone will be too busy with other things (and then eventually you'll end up hacked/on haveIbeenpwned, like so many other industry giants who also didn't hash their passwords). If something is worth doing once, it's worth doing right :) — Caius Jard, Oct 18 '18 at 05:08

score 5 · Accepted Answer · answered Oct 18 '18 at 05:02

Probably because you're using the wrong execute:

https://dapper-tutorial.net/execute

Says EXECUTE will return the number of rows affected, and that doc makes it clear that execute isn't used for queries that return rows

I'd say you should be using something in dapper that uses ExecuteScalar under the hood - see is there an ExecuteScalar in Dapper and also the link on my comment to the question, for how to turn a count query (also suggested in a comment) int a book result where 0 is false and anything else is true

thanks for help it worked !, though now i'll hash the password, not do later. :-) — pl-jay, Oct 18 '18 at 05:09

Dapper Execute returns -1 value?

1 Answers1