0

I encountered IndexOutOfRangeException issue when using IDataReader in C#. Below are my sample code. The code thrown me the Exception when it executed the functions of GetDataValue.

public List<Users> SelectUsersbyUsernamePassword(string username, string password)
    {
        const string SQL_STATEMENT =
            "SELECT U.Username, U.Password, U.Role " +
            "FROM Users U ";

        List<Users> listusers = new List<Users>();
        Database db = new Microsoft.Practices.EnterpriseLibrary.Data.Sql.SqlDatabase(CONNECTION_NAME);

        using (DbCommand cmd = db.GetSqlStringCommand(SQL_STATEMENT))
        {
            db.AddInParameter(cmd, "@username", DbType.String, username);
            db.AddInParameter(cmd, "@password", DbType.String, password);
            try
            {
                using (IDataReader dr = db.ExecuteReader(cmd))
                {
                    while (dr.Read())
                    {
                        Users users = new Users();
                        users.UserID = base.GetDataValue<int>(dr, "UserID");
                        users.Username = base.GetDataValue<string>(dr, "Username");
                        users.Password = base.GetDataValue<string>(dr, "Password");
                        users.Role = base.GetDataValue<string>(dr, "Role");

                        listusers.Add(users);
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
                //throw new DbException(ex.Message, SQL_STATEMENT, "username = " + username, ex.InnerException);
            }
        }
        return listusers;
    }

Data Access

protected T GetDataValue<T>(IDataReader dr, string columnName)
    {
        int i = dr.GetOrdinal(columnName);

        if (!dr.IsDBNull(i))
            return (T)dr.GetValue(i);
        else
            return default(T);
    }
RogerSK
  • 393
  • 1
  • 18
  • What does `GetSqlStringCommand` do? (Why not just use `connection.CreateCommand`?) – Dai Sep 30 '22 at 09:11
  • 1
    Also, **why are you storing passwords as plaintext in your database** ?!? – Dai Sep 30 '22 at 09:11
  • 1
    `sers.UserID = base.GetDataValue(dr, "UserID");` <-- Your `SELECT` query only gets the columns named `Username`, `PasswordOHGODNOYOUMUSTNEVERDOTHIS, and `Role`, but not `UserID`. – Dai Sep 30 '22 at 09:13
  • `return default(T);` <-- This is a bad design: if used on an `Int32` column then it will return `0` which might be meaningful in your applicaiton. You should respect `NULL` results and treat them correctly, rather than glossing over them. – Dai Sep 30 '22 at 09:14
  • @Dai I knew the password surely will not going to do like that. I try to treat it as plain text just for ease understanding as my current project is implementing some `GetMd5Hash` Encryption. – RogerSK Sep 30 '22 at 09:27
  • You shouldn't be using MD5 either: https://security.stackexchange.com/questions/19906/is-md5-considered-insecure – Dai Sep 30 '22 at 09:28
  • Thanks @Dai, really good to know that, I am actually considering to revamp the encryption, but it might take time to convert lots of code as the project is using the .NET 4 and implementing company itself reference/code engine. – RogerSK Sep 30 '22 at 09:34
  • `catch (Exception ex) {throw ex;` just no. That will wipe the stack trace. Either `throw;` or don't catch it at all. Also `db` needs `using` to dispose it – Charlieface Sep 30 '22 at 11:43

1 Answers1

0

As per commented from @Dai, the IndexOutOfRangeException Problem had been resolved and it is due to the SELECT query not getting the correct column.

// Read values.
users.UserID = base.GetDataValue<int>(dr, "UserID");
users.Username = base.GetDataValue<string>(dr, "Username");
users.Password = base.GetDataValue<string>(dr, "Password");
users.Role = base.GetDataValue<string>(dr, "Role");

SQL Query (To Be)

const string SQL_STATEMENT =
   "SELECT U.UserID, U.Username, U.Password, U.Role " +
   "FROM Users U " +
RogerSK
  • 393
  • 1
  • 18