SQL Return values in Delphi via ADO

Question

Hi. Im having this function to determine weather a user exists in a database or not

DM is my DataModule

AQ_LOGIN an ADOQuery

BENU is my Table filled with Users and their Password

Here comes the code:

function UserCheckExist(Login, pw: string): boolean;
begin
    with DM do
    begin
        AQ_LOGIN.Close;
        AQ_LOGIN.SQL.Clear;
        AQ_LOGIN.SQL.Add('select BLOGIN from BENU where BLOGIN = ''Login'' AND BPW = ''pw''');
        AQ_LOGIN.Open;
    end;
end;

My question now is: How can I make the function return true or false weather the User with the according password exists?

Thanks in advance.

Answer 1

Use:

function UserCheckExist(Login, pw: string): boolean;
begin
  with DM do
  begin
    AQ_LOGIN.Close;
    AQ_LOGIN.SQL.Clear;
    {Use COUNT in select to determine if user exists}
    AQ_LOGIN.SQL.Add('select count(BLOGIN) from BENU where BLOGIN = ''Login'' AND BPW 'pw''');
    AQ_LOGIN.Open;
    Result:= (AQ_LOGIN.Fields[0].AsInteger = 1);
    AQ_LOGIN.Close;
 end;

end;

Two changes: first, do not select username, you should rather count values - COUNT always returns something, if no user exists - it will be zero. Second: Calculate result using comparision if count (Fields[0], since no more fields exist) is equal to one. If count of such records will be different then one, this function will return false.

Answer 2

I'd go with smok1's answer (I was just posting something like it) but I'd parameterise your inputs, thus;

AQ_LOGIN.SQL.Add('select count(*) from BENU where BLOGIN=:login and BPW=:pw');
AQ_LOGIN.Parameters.ParamByName('login').AsString:=login;
AQ_LOGIN.Parameters.ParamByName('pw').AsString:=pw;

then as with smok1 - open the dataset and look at the value of the returned count.

NB - don't have an ADO delphi component handy but 99.9% sure that's the syntax :-)

edit: one of the advantages of using parameters like this is that you won't have to sanitise your input strings (for things like quotes) - the component knows what to do with your strings. You wouldn't expect to have a username with a single quote in it, but you might have a password with one. :-)

Answer 3

function UserCheckExist(Login, pw: string): boolean;
begin
    with DM do
    begin
        AQ_LOGIN.Close;
        AQ_LOGIN.SQL.Clear;
        AQ_LOGIN.SQL.Add('select BLOGIN from BENU where BLOGIN = ''Login'' AND BPW = ''pw''');
        AQ_LOGIN.Open;
        Result := (AQ_LOGIN.RecordCount > 0);
        AQ_LOGIN.Close;
    end;
end;

Answer 4

You can check for Eof.

function UserCheckExist(Login, pw: string): boolean;
begin    
  with DM do    
  begin        
    AQ_LOGIN.Close;        
    AQ_LOGIN.SQL.Clear;        
    AQ_LOGIN.SQL.Add('select BLOGIN from BENU where BLOGIN = ' + QuotedStr(Login) + ' AND BPW = ' + QuotedStr(pw));        
    AQ_LOGIN.Open;        
    Result := (not AQ_Login.Eof);
    AQ_LOGIN.Close;    
  end;
end;

Answer 5

I added one more check, weather the user is active. But it isnt working fine.

function UserCheck(Login, pw: string): boolean;
  begin
    with DM do
    begin
        AQ_LOGIN.Close;
        AQ_LOGIN.SQL.Clear;
        AQ_LOGIN.SQL.Add('select COUNT(*) from BENU where BLOGIN = ''Login'' AND BPW = ''pw'' AND AKTIV = 1');
        AQ_LOGIN.Open;
        Result := (AQ_LOGIN.RecordCount > 0);
        AQ_LOGIN.Close;
    end;
end;

This is where I use the function:

procedure TBenu_Login_Form.btnLoginClick(Sender: TObject);
  var pwhashed: string;
  begin
  pwhashed := MD5Print(MD5String(edtBPass.Text));
    if UserCheck(meBLogin.Text, pwhashed) then
      ShowMessage('User exists, Password is fine and active!')
    else
      ShowMessage('User does not exist, Password is wrong or not active!');
  end;

Would like to know why this does not work as intendet. It always returns the UserCheck true, never false when I i.e. enter a not existing user name.