-2

I am trying to use parameters for my project but somehow it does not return an output. Please see my code below.

SqlCommand zSqlComm = new SqlCommand("SELECT * FROM tblAppUsers WHERE Username = @Username AND PasswordHash = HASHBYTES('SHA2_512', @Password)");
zSqlComm.CommandType = CommandType.Text;
zSqlComm.Parameters.AddWithValue("@Username", "admin");
zSqlComm.Parameters.AddWithValue("@Password", "admin");
return DatabaseDAL.ExecuteSelect(zSqlComm);

However if I try to use this, it returns a value from the dataset:

SqlCommand zSqlComm = new SqlCommand(
  "SELECT * FROM tblAppUsers WHERE Username = 'admin' AND PasswordHash = HASHBYTES('SHA2_512', 'admin')"
);

Please see my ExecuteSelect code:

public static DataTable ExecuteSelect (SqlCommand zSQLComm)
{
    DataTable zDataTable = new DataTable();
    SqlConnection zSQLConn = new SqlConnection(DatabaseConfiguration.ZSQLConnectionString);
    zSQLComm.Connection = zSQLConn;
    SqlDataAdapter zSQLDataAdapter = new SqlDataAdapter(zSQLComm);
    try
    {
        zSQLConn.Open();
        zSQLDataAdapter.Fill(zDataTable);    
        zSQLConn.Close();
    }
    catch
    {
        zSQLConn.Close();
    }

    return zDataTable;
}

I also tried removing the single quotes in the parameter in the CommandText but it also still did not return any output.

GSerg
  • 76,472
  • 17
  • 159
  • 346
John Rivera
  • 3
  • 2
  • 4
    You should check out [Can we stop using AddWithValue() already?](http://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already/) and stop using `.AddWithValue()` - it can lead to unexpected and surprising results... – marc_s Oct 17 '20 at 18:04
  • 1
    Observe the result of `select HASHBYTES('SHA2_512', 'admin'), HASHBYTES('SHA2_512', N'admin');`. The former is what you have, the latter is what `AddWithValue` does. Don't use `AddWithValue`. – GSerg Oct 17 '20 at 18:08
  • 2
    Why are you passing the password **unhashed** and hashing it in the SQL? It's too late then, the password has already been leaked/compromised. – Thom A Oct 17 '20 at 18:24
  • @Larnu What is the leak point? – GSerg Oct 17 '20 at 18:25
  • Anyone, or thing who can run a trace or configure XE would be able to easily find out what the value is, @GSerg . Obviously the OP is just hashing admin here (which for a password for the admin is as good as not having a password) but hashing at the database is too late when you've passed the unhashed value. – Thom A Oct 17 '20 at 18:28
  • @Larnu Don't these activities require admin privileges on the server? – GSerg Oct 17 '20 at 18:29
  • @GSerg as a Server Admin I should **not** be able to find out what my user's passwords are... The only person who should know what their password is is *that* person, no one else. The only exception to that might be in the event of temporary passwords. – Thom A Oct 17 '20 at 18:30
  • @Larnu You should not, but as a server admin, you can set a MITM on the server to view the password as it arrives via https from the user. You cannot protect from your own admin. This is by design. So I don't see this code as a security issue because exploiting it requires admin privileges. – GSerg Oct 17 '20 at 18:32
  • 1
    @GSerg so why have 2 vulnerabilities? Just cause you have 1 doesn't excuse a second. You might as well start injecting parameters at that stage... – Thom A Oct 17 '20 at 18:34
  • 1
    @Larnu *Neither* is a vulnerability. Please see https://devblogs.microsoft.com/oldnewthing/20060508-22/?p=31283 and https://www.google.com/search?q=airtight+hatchway+site%3Adevblogs.microsoft.com%2Foldnewthing in general. – GSerg Oct 17 '20 at 18:39

1 Answers1

0

As you can check by the MS SQL Server, there are different results between pass direct string and pass variable to the HASHBYTES function:

declare @Password nvarchar(50)
set @Password = 'admin'
select HASHBYTES('SHA2_512', 'admin')
select HASHBYTES('SHA2_512', @Password)

So try pass parameters with their types:

zSqlComm.Parameters.Add("@Password", SqlDbType.NVarChar, 100).Value = "admin";
Saeed Heidarbozorg
  • 146
  • 2
  • 11
  • `different results between pass direct string and pass variable` - no, there are different results between passing `varchar` and passing `nvarchar`. There is no difference between a string literal and a variable of the same type. – GSerg Oct 19 '20 at 07:36