Why some attributes arrive null and others not using get method in ASP.NET?-CodePudding

Ok, so I have an API developed in ASP.NET with all the CRUD methods. One of the models looks like this:

public class Client
{
    public int Id { get; set; }
    public string First_Name { get; set; }
    public string Second_Name { get; set; }
    public string First_Last_Name { get; set; }
    public string Second_Last_Name { get; set; }
    public string Phone { get; set; }

    [DataType(DataType.Date)]
    public DateTime Birth_Date { get; set; }
    public string Password { get; set; }
    public string User { get; set; }
}

And the GetAllClients method looks like this:

public async Task<IEnumerable<Client>> GetAllClients()
{
    var db = dbConnection();
    var sql = @"
                SELECT id, first_name, second_name, first_last_name, second_last_name, phone, birth_date, _password, _user
                FROM public.""Clients"" ";
    return await db.QueryAsync<Client>(sql, new { });
}

But the password and user attributes arrive null when they have values inside the database. This is the response I get:

[
  {
    "id": 101110111,
    "first_Name": "Primer Nombre",
    "second_Name": "Segundo Nombre",
    "first_Last_Name": "Primer Apellido",
    "second_Last_Name": "Segundo Apellido",
    "phone": "11111111",
    "birth_Date": "2021-01-01T00:00:00",
    "password": null,
    "user": null
  },
  {
    "id": 202220222,
    "first_Name": "Nombre1",
    "second_Name": "SegNombre1",
    "first_Last_Name": "Apellido1",
    "second_Last_Name": "SegApellido1",
    "phone": "22222222",
    "birth_Date": "2000-01-01T00:00:00",
    "password": null,
    "user": null
  }
]

I know it's a bad practice having passwords explicit in the database, but for now I'm working with the authentication given password and user, but I can't if they're null in the response. I thought it was the name of the attribute, but it isn't. BTW, I have to work with postgreSQL if that helps.

This is the table definition:

CREATE TABLE IF NOT EXISTS public."Clients"
(
    id integer NOT NULL,
    first_name text COLLATE pg_catalog."default" NOT NULL,
    second_name text COLLATE pg_catalog."default",
    first_last_name text COLLATE pg_catalog."default" NOT NULL,
    second_last_name text COLLATE pg_catalog."default",
    phone text COLLATE pg_catalog."default" NOT NULL,
    birth_date date NOT NULL,
    _password text COLLATE pg_catalog."default" NOT NULL,
    _user text COLLATE pg_catalog."default" NOT NULL,
    CONSTRAINT "Clients_pkey" PRIMARY KEY (id)
);

Thanks!

CodePudding user response：

You can make your query like

... birth_date, _password as "Password", _user as "User"

So that dapper can map them to your property names, which are different. Any time your props are different to your columns it's easiest to use AS to rename the column in the query so it matches the property

..such is the problem of using reserved words for column names. I think for my money I'd have called them UserName and PasswordHash

..or you can look at the Dapper.DefaultTypeMap.MatchNamesWithUnderscores = true; setting, whcih should essentially strip underscores from db column names before attempting to match to your props (many of your pros will need renaming)

I think I'd have also skipped on using the underscores too, and just called my Postgres columns BirthDate etc (but I wouldn't have used quotes to make them PascalCase; that just becomes a headache - code like BirthDate, accept that PoatGres will call it whatever it likes so long as it's case insensitive, and keep my C# props to pascal Cade convention)

I know it's a bad practice having passwords explicit in the database

It's no excuse for not pulling some hash algorithm off SO, running the password supplied through it and storing the hash in the db rather than the password

Heck even calling password.GetHashcode and storing the int would be better than storing the password; your system security wouldn't be super but the big problem with storing passwords in plaintext is that people reuse passwords so when your db is stolen the hacker has the password the user uses everywhere else too. Code like this is the reason sites like haveIBeenPwned exist; even just getting the hash code of the password hides the true password which better protects people who reuse passwords