database usernames and passwords in delphi

Question

Using Delphi XE2.

I have a database with a table in it called users. It has a user_id field, username field, a password field and an Active field in it. (the user_id is a unique number that identifies each user... 1,2,3 etc).

I am writing a database software package which requires username and passwords to login to it. (I already have a login form created).

How do I get it to match/check the usernames and passwords in the database then allow user to continue into the software? Also I would like the Active Field to store in the database 'Y' if user is logged in or 'N' if user is not logged in. Is this doable?

I am connected to the users table via TADQuery and TDataSource.

Example below of function which I thought would get me started(calling it at point of clicking the login button on the login form).

function TfrmLogin.CheckUser: Boolean;
begin
while not dmData.qryUser.Eof do
begin
if(editName.Text <> qryUser.FieldByName('uname').AsString) or (editPassword.Text <> qryUser.FieldByName('pword').AsString)
  then ShowMessage('Username and/or Password not recognised');
    Exit;
End;

Answer 1

Looping through all rows in the database will not suffice, especially when you get more than a handful of users. You need to SELECT from the database for the specific user, and see if you get results back. You can do the SELECT based just on the username:

qryUser.SQL.Text := 'SELECT uname, pword FROM users WHERE uName = :uname';
qryUser.ParamByName('uname').AsString := editName.Text;
try
  qryUser.Open;
  if qryUser.IsEmpty then // No record found for user
    // Handle error
  else
  begin
    if qryUser.FieldByName('pword').AsString <> editPassword.Text then
      // Handle password mismatch;
  end;
finally
  qryUser.Close;
end;

It's not clear from your question which database components you're using (TADQuery might be a typo for TADOQuery, or it might be something else). If in fact it is TADOQuery, you'll need to make a couple of small changes to the code. (Actually, only three minor changes; two in the assignment of the parameter and one that reads the password value.)

qryUser.SQL.Text := 'SELECT uname, pword FROM users WHERE uName = :uname';
qryUser.Params.ParamByName('uname').Value := editName.Text;
try
  qryUser.Open;
  if qryUser.IsEmpty then // No record found for user
    // Handle error
  else
  begin
    if qryUser.FieldByName('pword').Value <> editPassword.Text then
      // Handle password mismatch;
  end;
finally
  qryUser.Close;
end;

Answer 2

I'm with Sam, if possible do not store passwords in the database. If the database supports Active Directory Authentication (MS/SQL, Oracle, DB2, MySQL, SyBase) use the user name and password to validate against Active Directory before trying a connection to the database. Then only store the user name and active flag in the Users table.

This function ask Active Directory if the user and password are valid, before you ever try to make a connection to the database. Then you can build your connection parameters to the database (I assume you are using FireDAC and a TADConnection see here for instructions). Try to open the connection, if it fails the user does not have access to the database at all. If it passes, then query the Users table like Ken suggest but test the active field instead of the password. This way no one can see a users password and you do not have to manage passwords in your application. By using this method someone still needs to know a users Active Directory password to access your application even though your using Active Directory Authentication on the database.

function TfrmPassword.ActiveDirectoryValidate: Boolean;
var
  LHandle: THandle;
  lDomainName: String;
begin
  Screen.Cursor := crHourglass;
  try
   // Get the Domain Name
   lDomainName := GetEnvironmentVariable('USERDOMAIN');
   // Test the user Logon
   Result := LogonUser(PWideChar(edtUserID.Text),
                       PWideChar(lDomainName),
                       PWideChar(edtPassword.Text),
                       LOGON32_LOGON_NETWORK,
                       LOGON32_PROVIDER_DEFAULT,
                       LHandle);
   // If True, we got a Handle, so close it
   if Result then
    CloseHandle(LHandle);

  finally
   Screen.Cursor := crDefault;
  end;
end;