CODEWITHSUNDEEP
sqldelphiado
Acron
Acron

Reputation: 1398

SQL Return values in Delphi via ADO

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.

Upvotes: 2

Views: 6935

Answers (6)

Rudi
Rudi

Reputation: 43

Since you are using a adoquery component without the connection I assume the database is all on the system or linked network. An adotable component is just as safe then although people always think only sql will work....use either adotable.locate function or adoquery.locate although the tables fields will have to be retrieved then before hand making it unsafe. The locate function already prevents injection with its parameters and only returns a boolean value based on if it is found. Might be unsafe some will say, I dont know your app but its much faster.

Upvotes: 0

Acron
Acron

Reputation: 1398

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.

Upvotes: 0

Pieter van Wyk
Pieter van Wyk

Reputation: 2378

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;

Upvotes: 1

gath
gath

Reputation: 25472

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;

Upvotes: 3

robsoft
robsoft

Reputation: 5585

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. :-)

Upvotes: 8

smok1
smok1

Reputation: 2950

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.

Upvotes: 3

Related Questions