?
Solved

About the TQuery Component

Posted on 2003-03-05
19
Medium Priority
?
209 Views
Last Modified: 2010-04-04
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
Comment
Question by:sunslinger
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
  • 4
  • +2
19 Comments
 
LVL 12

Accepted Solution

by:
esoftbg earned 80 total points
ID: 8078675
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
 
LVL 17

Expert Comment

by:geobul
ID: 8078686
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
 
LVL 17

Expert Comment

by:geobul
ID: 8078710
Yes, that's it. esoftbg's code is what you need.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 1

Expert Comment

by:DjamD
ID: 8078748
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
 
LVL 17

Expert Comment

by:geobul
ID: 8078823
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
 
LVL 1

Expert Comment

by:DjamD
ID: 8078878
Lol geobul,
its just an example, for sure you can promt for a password.

:-)

Sorry :-P

DjamD
0
 

Author Comment

by:sunslinger
ID: 8085815
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
 
LVL 1

Expert Comment

by:merry_prince
ID: 8110593
Please use
if not TypeQuery.eof then
instead of
if (TypeQuery.RecordCount>0) then

It will fast the speed.
0
 

Author Comment

by:sunslinger
ID: 8116121
Thanks merry_prince...

That is enlightening!
0
 
LVL 12

Expert Comment

by:esoftbg
ID: 8117829
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
 
LVL 1

Expert Comment

by:merry_prince
ID: 8118078
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
 
LVL 12

Expert Comment

by:esoftbg
ID: 8132065
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
 
LVL 1

Expert Comment

by:merry_prince
ID: 8132952
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
 
LVL 1

Expert Comment

by:merry_prince
ID: 8132953
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
 
LVL 12

Expert Comment

by:esoftbg
ID: 8139902
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
 
LVL 1

Expert Comment

by:merry_prince
ID: 8140879
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
 
LVL 12

Expert Comment

by:esoftbg
ID: 8173278
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
 
LVL 17

Expert Comment

by:geobul
ID: 8176592
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
 
LVL 12

Expert Comment

by:esoftbg
ID: 8177413
Hi geobul,
Glad to meet real bulgarian expert like you :-)
I am from Asenovgrad.
I love Varna !!!!
Regards, Emo
0

Featured Post

Industry Leaders: 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!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to create forms/units independent of other forms/units object names in a delphi project. Have you ever created a form for user input in a Delphi project and then had the need to have that same form in a other Delphi proj…
Introduction The parallel port is a very commonly known port, it was widely used to connect a printer to the PC, if you look at the back of your computer, for those who don't have newer computers, there will be a port with 25 pins and a small print…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
Suggested Courses
Course of the Month11 days, 20 hours left to enroll

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question