SQL Return values in Delphi via ADO

Question

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.

robsoft · Accepted Answer · 2009-07-14 09:29:53Z

8

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

edited Jul 14, 2009 at 9:29

answered Jul 14, 2009 at 9:21

robsoft

5,5954 gold badges37 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

skamradt Over a year ago

+1 Using parameters also much safer. Injecting user entered data DIRECTLY into a SQL statement allows for abuse. If allowed, then a sophisticated user might change the behavior of the query beyond what is expected. For instance, using this example, one can bypass the login by typing the following into the password field "' or bpw > '". Using parameters as suggested would now allow this to happen.

gath · Accepted Answer · 2009-07-14 09:31:33Z

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;

answered Jul 14, 2009 at 9:31

gath

25.6k36 gold badges102 silver badges128 bronze badges

Comments

smok1 · Accepted Answer · 2009-07-14 09:14:10Z

3

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.

answered Jul 14, 2009 at 9:14

smok1

2,95027 silver badges35 bronze badges

2 Comments

robsoft Over a year ago

+1 from me, as you posted pretty-much what I was going to put. However, I would have used parameters instead of concatenating the string inputs - and also, as the posted code stands (in the question and in the other answers) - it won't actually work, surely? :-)

robsoft Over a year ago

@smok1 - indeed - and I think his code is just confusing him, because it seems he's still struggling to get the test to work. :-(

Pieter van Wyk · Accepted Answer · 2009-07-14 13:45:23Z

1

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;

answered Jul 14, 2009 at 13:45

Pieter van Wyk

2,3889 gold badges48 silver badges66 bronze badges

3 Comments

Acron Over a year ago

So what does that do? Eof = End of File?

smok1 Over a year ago

@pr0wl - from help: Test Eof (end-of-file) to determine if the active record in a dataset is the last record. If Eof is true, the current record is unequivocally the last row in the dataset. Eof is true when an application: Opens an empty dataset. (...)

Pieter van Wyk Over a year ago

Yes, EOF = End of File. In this case, if the user exists you want to return True, so when EOF is false, record(s) were returned and the user exists. If EOF is True, the user does not exist.

Acron · Accepted Answer · 2009-07-14 14:20:01Z

0

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.

answered Jul 14, 2009 at 14:20

Acron

1,3984 gold badges20 silver badges32 bronze badges

5 Comments

smok1 Over a year ago

Beacause select COUNT(*) will ALWAYS return one record - one record with the numer of records in datatable. Use either mine or gath's solution, do not mix them.

robsoft Over a year ago

Recordcount will always return 1 in that query - you should be testing the value of the result, not the number of rows that come back. Change your test to Result:=(AQ_LOGIN.Fields[0].AsInteger>0); and you should be fine. And do something about those strings - you will get caught out one day just concatenating your input into the SQL like that! :-)

Acron Over a year ago

I used now AQ_LOGIN.SQL.Add('select count(BLOGIN) from BENU where BLOGIN = ''Login'' AND BPW = ''pw'''); still, this does not work

smok1 Over a year ago

Using COUNT in SQL = Comparing with Result:=(AQ_LOGIN.Fields[0].AsInteger>0); NOT Using COUNT in SQL = Comparing with Result := (AQ_LOGIN.RecordCount > 0);

Acron Over a year ago

Got it working as intended now :> Thank you smok1 and robsoft as well as gath

Rudi · Accepted Answer · 2012-10-25 15:33:58Z

0

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.

answered Oct 25, 2012 at 15:33

Rudi

433 bronze badges

Collectives™ on Stack Overflow

SQL Return values in Delphi via ADO

6 Answers 6

1 Comment

Comments

2 Comments

3 Comments

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

1 Comment

Comments

2 Comments

3 Comments

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related