C # IndexOutOfRangeException?

Question

C # IndexOutOfRangeException?

Navigation

#1 by (1 votes)

1

I'm doing a login in C# and SQL Server , with a table that I just created. In this table, I store the passwords of the users with a hash SHA2_256 .

When making the login, I use this code:

 var.User = txtUser.Text;
 var.Pswd = txtPass.Text;

 BaseDatos conexion = new BaseDatos();
            using (conexion) {
                //try {
                    SqlCommand userQuery = new SqlCommand("SELECT Usuario, Pswd, Rol FROM UsuariosSHA WHERE Usuario = @usrn AND Pswd = HASHBYTES('SHA2_256', @pass);", conexion.conexionSistema());
                    userQuery.Parameters.AddWithValue("@usrn", var.User);
                    userQuery.Parameters.AddWithValue("@pass", var.Pswd);

                    DataSet ds = new DataSet();
                    SqlDataAdapter da = new SqlDataAdapter(userQuery);
                    da.Fill(ds, "UsuariosSHA");
                    DataRow DR;
                    DR = ds.Tables["UsuariosSHA"].Rows[0];

                    if ((var.User == DR["Usuario"].ToString()) || (var.Pswd == DR["Pswd"].ToString())) {
                        agPrincipal agendaPW = new agPrincipal(var.User);
                        agendaPW.Show();
                        Dispose();
                    }
             /*} catch (Exception) {
                /////
            }*/

I get an error in:

DR = ds.Tables ["SHA Users"]. Rows [0];, of type IndexOutOfRangeException .

I guess the query is not showing me results.

On the other hand, if I put the query

SELECT Usuario, Pswd, Rol FROM UsuariosSHA 
WHERE Usuario = 'PRUEBA' AND Pswd = HASHBYTES('SHA2_256', 'PRUEBA');

Static way, just test, I can effectively log in.

Also if I make the manual declaration in SSMS, the query works. The user TEST me back:

DECLARE @val2 varchar(30);   
DECLARE @val1 varchar(30);

SET @val1 = 'PRUEBA';
SET @val2 = 'PRUEBA';

SELECT Usuario, Pswd FROM UsuariosSHA 
WHERE Usuario=@val1 AND Pswd=HASHBYTES('SHA2_256',@val2);

My table has the following structure:

CREATE TABLE UsuariosSHA(
    Numemp INT NOT NULL,
    Nombre VARCHAR(50) NOT NULL,
    Usuario VARCHAR(20) NOT NULL,
    Pswd VARBINARY(150) NOT NULL,
    Rol VARCHAR(20) NOT NULL);

alter table UsuariosSHA ADD CONSTRAINT UK_Password UNIQUE (Numemp);
alter table UsuariosSHA ADD CONSTRAINT UK_UserName UNIQUE (Usuario);

At the start of the session, I tried to change the query to:

SELECT Usuario, Pswd, Rol FROM UsuariosSHA 
WHERE Usuario = @usrn AND Pswd = @pass;

And add the following method to make the hash of the string and compare it with the Pswd field of the table:

public string SHA256Encrypt(string input)
        {
            var crypt = new System.Security.Cryptography.SHA256Managed();
            var hash = new System.Text.StringBuilder();
            byte[] crypto = crypt.ComputeHash(Encoding.UTF8.GetBytes(input));
            foreach (byte theByte in crypto)
            {
                hash.Append(theByte.ToString("x2"));
            }
            return hash.ToString().ToUpper();
        }

And after collecting the textbox information, send it to call:

var.Pswd = SHA256Encrypt(var.Pswd);

However, I got the same results.

As a test, I tried to remove the two parameters, leaving only the user ... But when treating the password as a variable, it is where the system throws this exception to me.

Does anyone know what the reason for this exception is or what am I doing wrong? Thank you very much.

c# sql-server login sha256

asked by Nohemi 13.04.2018 в 22:04

source

1 answer

How to request GPS data in Android Studio Read an Excel file .csv (C ++)

score 1 · Answer 1

The exception is given to you because you try to access the first DataRow of a table that does not have any. Why? It's a question of your query.

To handle these cases I wrote the following code:

public static bool GetFirstRow(DataTable table, ref DataRow row)
{
    if (table.Rows.Count > 0)
    {
        row = table.Rows[0];
        return true;
    }
    else
    {
        row = null;
        return false;
    }
}

And you would consume it like this:

DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter(userQuery);
da.Fill(ds, "UsuariosSHA");
DataRow DR;
// Ojo aquí abajo
if (GetFirstRow(ds.Tables["UsuariosSHA"], ref DR)
{
    if ((var.User == DR["Usuario"].ToString()) || (var.Pswd == DR["Pswd"].ToString())) {
        ...
    }
}

How does it work?

When you use a parameter ref you use a reference to a variable that already exists and assign a value from the method. The reason for the method to return a bool is simply so that, if the variable can be used, it is just a matter of putting it in a if and thus making sure that the code will only be executed if we can use that data.

On the other hand, I would directly fill in a DataTable instead of a DataSet to make sure that the table I'm going to work with at least exists and do not give another IndexOutOfRangeException for not finding the name of the table:

try
{
    DataTable dt;
    using (SqlConnection con = ...)
    {
        using (SqlCommand cmnd = new SqlCommand("query", con))
        {
            ... // SqlParameters

            dt = new DataTable();
            con.Open();
            using (SqlDataAdapter da = new SqlDataAdapter(cmnd))
            {
                con.Close();
                da.Fill(dt);
                return dt;
            }
        }
    }
}

EDIT

Something that will be worth reviewing is the type of data that is being handled. To ensure that it is sent as you should do the following:

SqlCommand userQuery = new SqlCommand("SELECT Usuario, Pswd, Rol FROM UsuariosSHA WHERE Usuario = @usrn AND Pswd = @pass;", conexion.conexionSistema());
userQuery.Parameters.AddWithValue("@usrn", var.User);
SqlParameter passParam = new SqlParameter()
{
    ParameterName = "@pass",
    SqlDbType = SqlDbType.VarBinary,
    Value = SHA256Encrypt(var.Pswd)
};
userQuery.Parameters.Add(passParam);