C# sqlCommand.Parameters don't Select properly

Question

I have a code that should test a login. When I execute literally, it works, returning one row (that's expected). When I use parameters on sqlcommand, I don't get any row.

It works (literal values for username and password):

string strConn = 'string connection';
SqlConnection conn = new SqlConnection(strConn);
SqlCommand sqlCommand = new SqlCommand();
sqlCommand.Connection = conn;
sqlCommand.Parameters.Clear();
sqlCommand.CommandText = @"select *
                           from
                              Usuario
                           where
                              Username = 'test' and
                              Password = CONVERT(VARCHAR(32), ashBytes('MD5', 'test'), 2)";

conn.Open();
SqlDataReader ret = sqlCommand.ExecuteReader();

But it doesn't work (parameters values for username and password):

string strConn = 'string connection';
SqlConnection conn = new SqlConnection(strConn);
SqlCommand sqlCommand = new SqlCommand();
sqlCommand.Connection = conn;
sqlCommand.Parameters.Clear();
sqlCommand.CommandText = @"select *
                           from
                              Usuario
                           where
                              Username = @login and
                              Password = CONVERT(VARCHAR(32), ashBytes('MD5', @pass), 2)";

SqlParameter user = new SqlParameter("@login", SqlDbType.NVarChar, 50) { Value = "test" };
SqlParameter pass = new SqlParameter("@pass", SqlDbType.NVarChar, 50) { Value = "test" };

List<SqlParameter> list = new List<SqlParameter>();
list.Add(user);
list.Add(pass);

sqlCommand.Parameters.AddRange(list.ToArray<SqlParameter>());

conn.Open();
SqlDataReader ret = sqlCommand.ExecuteReader();

I don't have an sintax error or something like that. The second code just don't returns rows. I've tried to use sqlCommand.Parameters.AddWithValue, but I have no success too.

Do you mean HashBytes? What is ashBytes?

Steve
– Steve

2017-03-02 19:20:38 +00:00
Commented Mar 2, 2017 at 19:20 — Steve
– Steve, Commented Mar 2, 2017 at 19:20
@felipe-müller, have you solved your problem?

Paulo Pereira
– Paulo Pereira

2017-03-03 13:18:12 +00:00
Commented Mar 3, 2017 at 13:18 — Paulo Pereira
– Paulo Pereira, Commented Mar 3, 2017 at 13:18
Yeah! @Igor solved my problem.

Felipe Müller
– Felipe Müller

2017-04-02 15:42:34 +00:00
Commented Apr 2, 2017 at 15:42 — Felipe Müller
– Felipe Müller, Commented Apr 2, 2017 at 15:42

Graham · Accepted Answer · 2017-09-22 02:18:00Z

'test' and N'test' are not the same thing when you convert them to a hash. One is ASCII and the other is Unicode. If they are both ASCII then use SqlDbType.VarChar (not SqlDbType.NVarChar) in your parameter.

Difference illustrated in Sql

DECLARE @passUnicode Nvarchar(100) = N'test'
DECLARE @passAscii varchar(100) = 'test'

SELECT CONVERT(VARCHAR(32), HashBytes('MD5', @passAscii), 2) AS [Md5OfAscii]
     , CONVERT(VARCHAR(32), HashBytes('MD5', @passUnicode), 2) AS [Md5OfUnicode]

Results

098F6BCD4621D373CADE4E832627B4F6, C8059E2EC7419F590E79D7F1B774BFE6

Side notes

Password Hashing

I recommend against storing passwords as MD5, MD5 is simply not secure. There are plenty of alternatives out there like pbkdf2, bcrypt, and scrypt to name a few of the more generally accepted secure password hashing algorithms.

c# structure

When working with Ado.net (or with any resources for that matter) you should wrap your Connections, DataReaders, Adapters, etc that implement IDisposable in using blocks. This will ensure external resources are always freed, even in the event of a connection.

thks!!! I didn't know this diference between varchar and nvarchar. I didn't even knew the nvarchar type. The use of md5 was just for a simple aplication, will not be the final cryptography, but thanks for advices!

Paulo Pereira · Accepted Answer · 2017-03-02 19:59:18Z

string connectionString = "";

using (var connection = new SqlConnection(connectionString))
{
    using (var command = new SqlCommand
    {
        CommandText = @"select * from Usuario where Username = @login and Password = CONVERT(VARCHAR(32), HASHBYTES('MD5', @pass), 2)",
        CommandType = CommandType.Text,
        Connection = connection
    })
    {
        command.Parameters.Add(new SqlParameter("login", SqlDbType.VarChar, 50) { Value = "test" });
        command.Parameters.Add(new SqlParameter("pass", SqlDbType.VarChar, 50) { Value = "test" });

        connection.Open();

        using (var dataReader = command.ExecuteReader())
        {
            // do some stuff
        }
    }
}

Collectives™ on Stack Overflow

C# sqlCommand.Parameters don't Select properly

2 Answers 2

Side notes

Password Hashing

c# structure

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Side notes

Password Hashing

c# structure

1 Comment

Comments

Related