• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 210
  • Last Modified:

About the TQuery Component

I have 3 Edit Components called EdUsername, EdPassword and EdType. I have done up the username and password checking but I have difficulty with the Login Type (e.g.admin or guest). I already have some records in the database (MSSQL). When the user enters the username and password, I want to be able to fetch the record form the database and display it in EdType.text . The second question is, looking at my code below, is there a more efficient way of doing things?

procedure TLoginForm.BBtnLoginClick(Sender: TObject);
var
  UsernameFound : boolean;
  PasswordFound : boolean;

begin
  //Initialize values
  UsernameFound := False;
  PasswordFound := False;

  //To check username exists
  with LoginQuery do begin
    Close;
    with SQL do begin
      Clear;
      Add('SELECT * FROM login WHERE username= (:Username)');
      Params[0].AsString := EdUsername.Text;
      Prepare;
    end;
    Open;  //alternatively can use ExecSQL
  end;

  if LoginQuery.RecordCount = 1 then
    UsernameFound := True;

  //To check password exists
  LoginQuery.Close;
  LoginQuery.SQL.Clear;
  LoginQuery.SQL.Add('SELECT * FROM login WHERE password= (:Password)');
  LoginQuery.Params[0].AsString := EdPassword.Text;
  LoginQuery.Open;

  if LoginQuery.RecordCount = 1 then
    PasswordFound := True;

  if (UsernameFound and PasswordFound) = True then begin
    ShowMessage('Login Successful!');
    TypeQuery.Close;
    TypeQuery.SQL.Clear;
    TypeQuery.SQL.Add('SELECT type FROM login WHERE username= (:Username) AND password= (:Password)');
    TypeQuery.Params[0].AsString := EdUsername.Text;
    TypeQuery.Params[1].AsString := EdPassword.Text;
    TypeQuery.Open;

    //EdType.Text := TypeQuery.Fieldname('type').AsString;

  end
  else
    ShowMessage('Login Fail!');

end;
0
sunslinger
Asked:
sunslinger
  • 6
  • 5
  • 4
  • +2
1 Solution
 
esoftbgCommented:
procedure TLoginForm.BBtnLoginClick(Sender: TObject);
var
  S:      string;
  T:      string;
begin
  T := '';
  S := 'Login Fail!';
  try
    TypeQuery.Close;
    TypeQuery.SQL.Clear;
    TypeQuery.SQL.Add('SELECT type FROM login WHERE username=(:Username) AND password=(:Password)');
    TypeQuery.Params[0].AsString := EdUsername.Text;
    TypeQuery.Params[1].AsString := EdPassword.Text;
    TypeQuery.Prepare;
    TypeQuery.Open;
    if (TypeQuery.RecordCount>0) then
    begin
      S := 'Login Successful';
      T := TypeQuery.FieldByName('type').AsString;
    end;
  finally
    EdType.Text := T;
    ShowMessage(S);
  end;
end;
0
 
geobulCommented:
Hi,

Your code will allow someone to access your program if he enters a password that doesn't match the entered username.

Suppose that you have two users:

'john' with password 'smith'
'anrew' with password 'web'

If someone enters 'john' username and 'web' password he will be allowed to enter.

Use only one query:

'SELECT * FROM login WHERE username= (:Username) AND password= (:Password)'

Regards, Geo
0
 
geobulCommented:
Yes, that's it. esoftbg's code is what you need.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
DjamDCommented:
to do something really efficient you could do it that way, using an IniFile that stores your login parameters :

Example of the IniFile content :
;-------------------------------
[OPTIONS]
server=SERVER
database=MASTER
UserID=sa
Pwd=pass
;-------------------------------

.
.
.

const
  ParamFile : string = 'CONFIG.CFG';

resourcestring
   strConnect   = 'Provider=SQLOLEDB.1;Persist Security Info=False;User ID=%s;Password=%s;Initial Catalog=%s;Data Source=%s;Network Library=DBMSSOCN;Use Procedure for Prepare=0';

.
.
.
  // Public type
  public
   strServer     : string;
   strDatabase   : string;
   strPassword   : string;
   strUserID     : string;
  end;
.
.
.

function ReadConfigFile: boolean;
var
  MyIniFile : Tinifile;
begin
  result := true;

  try
     MyIniFile := Tinifile.Create(IncludeTrailingBackslash(ExtractFilePath(ParamStr(0)))+ParamFile));
     strPassword := MyIniFile.ReadString('OPTIONS','PWD','ERR');
     strUserID   := MyIniFile.ReadString('OPTIONS','USERID','ERR');
     strServer := MyIniFile.readstring('OPTIONS','SERVER','ERR');
     strdatabase := MyIniFile.readstring('OPTIONS','DATABASE','ERR');
  finally
     MyIniFile.Free;
  end;
     
  if (strserver = 'ERR') or
     (strdatabase = 'ERR') or
     (strPassword ='ERR') or
     (strUserID = 'ERR') then
  begin
    strServer := '';
    strdatabase := '';
    strPassword := '';
    strUserID := '';
    result := false;
  end;
end;

//-------------------------------------------------------//
Using this you can create Configuration files that can be distributed.

Now you can use an SQL Server account to logon and query the desired tables.

function LogonServer : boolean;
begin
  AdoConnection1.Close;
  AdoConnection1.ConnectionString := Format(strConnect,[strUserID, strPassword, strDatabase ,strServer]);

  try
     AdoConnection1.Open;
     Result := AdoConnection1.Active;
  Except
    on Exception do
    begin
     MessageDlg('Login failed !'+#13+#10+'Unknown Database or Server.', mtWarning, [mbOK], 0);
     Result := False
    end;
  end;
end;

Now to Login into your application you can use this code :

with TypeQuery do
begin
   Close;
   SQL.Clear;
   SQL.Add('SELECT type FROM login WHERE username= (:Username) AND password= (:Password)');
   Params[0].AsString := EdUsername.Text;
   Params[1].AsString := EdPassword.Text;
   Open;

   if Active then
     if RecordCount = 1 then
     begin
        //EdType.Text := TypeQuery.Fieldname('type').AsString;
        ShowMessage('Login Successful !');
     end
     else
     begin
        //EdType.Clear;
        ShowMessage('Login Failed !');
     end;

   Close;
end;

Hope this helps,
sorry if I forgot some code lines,
cordially

DjamD

0
 
geobulCommented:
Wow, a password in an ini file. The most secure approach ever! Why do you need a login screen then? Am I missing something?
0
 
DjamDCommented:
Lol geobul,
its just an example, for sure you can promt for a password.

:-)

Sorry :-P

DjamD
0
 
sunslingerAuthor Commented:
Thanks esoftbg... that was really neat, compact and efficient. It was what I was looking for.

Thanks also to geobul... your insights have made me better understand the logic behind it and my own errors.

DJamD, wow that is really a nice piece of work, but i must admit that as a newbie I am a bit intimidated by that long line of code :P . But i will bear that in mind in the future. Thanks!
0
 
merry_princeCommented:
Please use
if not TypeQuery.eof then
instead of
if (TypeQuery.RecordCount>0) then

It will fast the speed.
0
 
sunslingerAuthor Commented:
Thanks merry_prince...

That is enlightening!
0
 
esoftbgCommented:
Excuse me merry_prince, but You are not right !
It is not correct:

if not TypeQuery.eof then

because after
 TypeQuery.Open;
 TypeQuery.EOF is False !!!!!!!!!
0
 
merry_princeCommented:
Hi esoftbg,

When TypeQuery.Open;
The pointer of Query will point to first record.

if TypeQuery.EOF = false, then announce the dataset isn't empty. i.e existing records. The effect is same to TypeQuery.RecordCount > 0

Because TypeQuery.RecordCount need all over all records. It's so slow. Please think of it.
0
 
esoftbgCommented:
Hi merry_prince,
you are right when TypeQuery contains one or more records. But is it correct to ask for TypeQuery.EOF when TypeQuery do not contains any record ? (Is it correct to ask for End Of File in case this file has no Begin Of File, because it is empty ?)
0
 
merry_princeCommented:
Hellow esoftbg,

Delphi can dispose this situation in Eof func without any error. Its result will be true. If convenient, you can try it.
0
 
merry_princeCommented:
Hello esoftbg,

Delphi can dispose this situation in Eof func without any error. Its result will be true. If convenient, you can try it.
0
 
esoftbgCommented:
Dear merry_prince,
I accept your clarification !
I love Borland Delphi and i am happy that Delphi can dispose this situation in Eof func without any error.
Thanks !
0
 
merry_princeCommented:
Hello esoftbg,
Glad to hear from you. Can you list other language tools didn't support similar func like Eof? And will get the error?

Please advise.
0
 
esoftbgCommented:
Hi merry_prince,
that is my last comment about this question:
1. I accepted your clarification just to finish these comments.
2. I never will use your solution, because I dont like it. I like clear and perfect solution:
if (TypeQuery.RecordCount>0) then
3. That is my opinion.
Be happy !
0
 
geobulCommented:
Hi esoftbg,

I just read your profile. I'm from Bulgaria too (Varna). Glad to meet you :-)

Regards, Geo

P.S.: Sorry for being off topic.
0
 
esoftbgCommented:
Hi geobul,
Glad to meet real bulgarian expert like you :-)
I am from Asenovgrad.
I love Varna !!!!
Regards, Emo
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

  • 6
  • 5
  • 4
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now