Parse data from a C # bank?

2

I need to deserialize information from a database in C# , I have the following SQL in C# :

select 
      Id, 
      Email, 
      Name, 
      Login 
FROM usuarios 
WHERE Login = @login
      and Senha = CONVERT(VARCHAR(2000), HASHBYTES('MD5', @password) ,2)

And to deserialize I'm doing the following:

while (reader.Read())
{
    toReturn.Id = int.Parse(reader["Id"].ToString());
    toReturn.Email = reader["Email"].ToString();
    toReturn.Name = reader["Name"].ToString();
    toReturn.UserName = reader["Login"].ToString();
}

However, it never returns any value and throws no exception. What am I doing wrong?

Querying like this:

select 
      Id, 
      Email, 
      Name, 
      Login 
FROM usuarios 
WHERE Login = 'usuario'
      and Senha = CONVERT(VARCHAR(2000), HASHBYTES('MD5', '123') ,2)

It returns the correct user.

To run, I'm using this code:

cmd.CommandText = "select Id, Email, Name, Login FROM [vw_usuarios] WHERE 
Login = '@login' and Senha = CONVERT(VARCHAR(2000), HASHBYTES('MD5', '@password') ,2)";

cmd.Parameters.AddWithValue("@login", username);
cmd.Parameters.AddWithValue("@password", password);
cmd.CommandType = System.Data.CommandType.Text;

reader = cmd.ExecuteReader();

Notes:

  • I already tested it in the database and it worked.
  • When I remove the parameters, it returns all values.
asked by anonymous 16.06.2017 / 22:33

1 answer

1

The error is how the information in the parameter is sent, it does not need single quotes in SQL , it looks like this:

cmd.CommandText = "select Id, Email, Name, Login FROM [vw_usuarios] WHERE
 Login = '@login' and Senha = CONVERT(VARCHAR(2000), HASHBYTES('MD5', '@password') ,2)";

switch to :

cmd.CommandText = "select Id, Email, Name, Login FROM [vw_usuarios] WHERE
 Login = @login and Senha = CONVERT(VARCHAR(2000), HASHBYTES('MD5', @password) ,2)";

What is the difference:

Removing single quotation marks in @login and @password does not require, Parameters itself takes care of putting quotation marks in mind that the data is two texts.

Complete and Fixed Code:

cmd.CommandText = "select Id, Email, Name, Login FROM [vw_usuarios] WHERE 
        Login = @login and
        Senha = CONVERT(VARCHAR(2000), HASHBYTES('MD5', @password) ,2)";

cmd.Parameters.AddWithValue("@login", username);
cmd.Parameters.AddWithValue("@password", password);
cmd.CommandType = System.Data.CommandType.Text;
reader = cmd.ExecuteReader();

if (reader.Read())
{
    toReturn.Id = int.Parse(reader["Id"].ToString());
    toReturn.Email = reader["Email"].ToString();
    toReturn.Name = reader["Name"].ToString();
    toReturn.UserName = reader["Login"].ToString();
}

Tip: in this link has an example that you can tell the difference.

16.06.2017 / 23:10