[HELP] Connecting to a SQL Database tables?- Console Application

[nobodyknowsme]

problem is now solved

[chrgra]

Query.sql =' Select count(*) from users where username =? And password = ?';
Query.open;
If query.count greater than zero then
Begin
   Showmessage('username and password correct')
End

[rvk]

You didn't create a proper users table (with username and password) yet.

But you could do something like this:

Code: [Select]

program game1;

uses
  sqldb,
  mysql56conn;

var
  AConnection: TSQLConnector;
  User: string;

  procedure CreateConnection;
  begin
    AConnection := TSQLConnector.Create(nil);
    AConnection.ConnectorType := 'MySQL 5.6';
    AConnection.Hostname := 'sql5.freemysqlhosting.net';
    AConnection.DatabaseName := 'sql561707';
    AConnection.UserName := 'sql561707';
    AConnection.Password := 'dX3%eP3!';
  end;

  function GetValidUsername: boolean;
  var
    Query: TSQLQuery;
    Password: string;
  begin
    Result := False;
    Write('Username: ');
    ReadLn(User);
    Write('Password: ');
    ReadLn(Password);
    Query := TSQLQuery.Create(nil);
    Query.Database := AConnection;
    Query.Transaction := TSQLTransaction.Create(Query);
    Query.Transaction.Database := AConnection;
    Query.SQL.Text := 'select * from users where username=:username';
    Query.ParamByName('username').AsString := User;
    Query.Open;
    if Query.FieldByName('password').AsString = Password then
      Result := True;
    Query.Close;
    Query.Free;
  end;

begin
  CreateConnection;
  AConnection.Open;
  if Aconnection.Connected then
  begin
    writeln('Successful connect!');
    if GetValidUsername then
      GotoTheGame(User) // YOUR GAME PROCEDURE
    else
      writeln('Wrong username or password!');
  end
  else
    writeln('This is not possible, because if the connection failed, an exception should be raised, so this code would not be executed');
  AConnection.Close;
  AConnection.Free;
  writeln('End program!');
  readln;
end.

The procedure GotoTheGame would be your game.
You'll need to create the table users with a field username and password and fill it.
Please note that the tablenames in MySQL are case sensitive so I would suggest always using lowercase !
Also note that I used ParamByName to submit the username. If you do something like "Select * from users where username="+username you leave youself open to an attack with SQL-injection (search Google for SQL-injection). This is not the case if you use parameters.
(See the wiki)

But if you're already struggling to create this 'simple' login via MySQL how are you going to code the rest? And is the game already finished or do you need to code that as well?

[chrgra]

give me 5 minutes

[rvk]

There is something wrong with the column as shown the username, password as you can see there ? It breaks at "   if GetValidUsername then " is that wrong at all?

Yes... I already fixed that.
It needed to be username instead of user.
(check the new code in the original post)

Your table should be users (all small letters) and the fields username and password.

[chrgra]

I just made an example in firebird.

too late.....

program project1;

{$mode objfpc}{$H+}

uses
  {$IFDEF UNIX}{$IFDEF UseCThreads}
  cthreads,
  {$ENDIF}{$ENDIF}
  Classes, SysUtils, CustApp
  { you can add units after this }
  ,ibconnection, db, sqldb;

type

  { TestFirebirdConsoleUsernameFirebird }

  TestFirebirdConsoleUsernameFirebird = class(TCustomApplication)
  protected
    procedure DoRun; override;
  public
    constructor Create(TheOwner: TComponent); override;
    destructor Destroy; override;
    procedure WriteHelp; virtual;
  end;

{ TestFirebirdConsoleUsernameFirebird }

procedure TestFirebirdConsoleUsernameFirebird.DoRun;
var
  ErrorMsg: String;
  ibconnection : TIBconnection;
  query : TSqlQuery;
  sqltransaction : TSQLTransaction;
  username, password : string;
begin
  // quick check parameters
  ErrorMsg:=CheckOptions('h','help');
  if ErrorMsg<>'' then begin
    ShowException(Exception.Create(ErrorMsg));
    Terminate;
    Exit;
  end;

  // parse parameters
  if HasOption('h','help') then begin
    WriteHelp;
    Terminate;
    Exit;
  end;

  { add your program here }
  IbConnection := TIBConnection.Create(nil);
  sqltransaction := tSqltransaction.Create(nil);
  query := tsqlquery.Create(nil);
  ibconnection.UserName:= 'sysdba';
  ibconnection.Password:= 'masterkey';
  ibconnection.HostName:= 'localhost';
  ibconnection.DatabaseName:='userdatabase';
  ibconnection.Transaction := sqltransaction;
  query.DataBase := ibconnection;
  try
    ibconnection.Open;
    ibconnection.close;
    ibconnection.DropDB;
  except
  end;
    ibconnection.CreateDB;
    query.SQL.Add('CREATE TABLE userpassword (username varchar(64) not null,password varchar(64) not null);');
    query.ExecSQL;
    sqltransaction.CommitRetaining;
    query.SQL.clear;
    query.sql.Add('insert into userpassword values(''user'',''pass'')');
    query.ExecSQL;
    sqltransaction.CommitRetaining;
    writeln('please give your username');
    readln(username);
    writeln('please give your password');
    readln(password);
    writeln('you username is '+ username + ' and your password is ' + password);
    query.sql.clear;
    query.sql.add('select * from userpassword where username = '''+username +''' and password = ''' +password + ''';');
    // or
  {  query.sql.add('select * from userpassword where username = '''?''' and password = '''?''';';
    query.Params[0] := username;
    query.params[1] := password; }
    query.Open;
    query.First;
    while not query.EOF do
    begin
      writeln('username  '+ query.Fields[0].AsString);
      query.Next;
    end;
    if query.RecordCount> 0 then
    begin
      writeln('credentials passed');
    end
    else
    begin
      writeln('access denied');
    end;

    readln;


  // stop program loop
  Terminate;
end;

constructor TestFirebirdConsoleUsernameFirebird.Create(TheOwner: TComponent);
begin
  inherited Create(TheOwner);
  StopOnException:=True;
end;

destructor TestFirebirdConsoleUsernameFirebird.Destroy;
begin
  inherited Destroy;
end;

procedure TestFirebirdConsoleUsernameFirebird.WriteHelp;
begin
  { add your help code here }
  writeln('Usage: ',ExeName,' -h');
end;

var
  Application: TestFirebirdConsoleUsernameFirebird;
begin
  Application:=TestFirebirdConsoleUsernameFirebird.Create(nil);
  Application.Title:='TestFirebirdConsoleUsernameFirebird';
  Application.Run;
  Application.Free;
end.

[nobodyknowsme]

Thank you anyway! I don't use firebird :S I should have mentioned that. Wait with firebird do the user need the files? As with the SQL stuff atm I need a dll file.

[chrgra]

a sql database needs a client for connection.

on linux installed on /usr/lib/fbclient.so2 or something like that.
on windows installed in the windows/system directory I believe.

By the way the same as mysql I assume. So no need to install on root directory.

[rvk]

1) How would I move the dll file? I don't want it to be in the root of the folder as it looks ugly.
2) How secure is this?

Yes... you can put libmysql.dll in a subdirectory (for example lib) and put this at the top of your program:

Code: [Select]

  SetCurrentDir(GetCurrentDir + '\lib');
Your current path after this is lib (in your program directory) and Lazarus will load libmysql.dll from there. If you need the current path to be that of your .exe afterwards you can set it back right after the CreateConnection-call.
(PS. You do need to add SysUtils to your uses-clause.)

The security depends on what you call "security" and on your users. (I already mentioned this in an earlier post) If there are really Tech-savvy  they won't have trouble finding out the username and password of your database. Others (with no access to the game) can only try to brute-force hack your account but seeing as there is no other option I think that wouldn't be a problem (as the default password is already strong).

[rvk]

If I just hit enter twice it lets me in? In my database it doesn't have empty databases??

I know you solved it already but you're right...

There should have been a check if there where records found with that username.
So you could change it to the following:

Code: [Select]

Query.Open;
if (Query.RecordCount > 0) and (Query.FieldByName('password').AsString = Password then
  Result := True;
That way the Result would only be true if there is at least one record with username and the correct password.

You could also do this (checking username and password at the same time):

Code: [Select]

  function GetValidUsername: boolean;
  var
    Query: TSQLQuery;
    Password: string;
  begin
    Result := False;
    Write('Username: ');
    ReadLn(User);
    Write('Password: ');
    ReadLn(Password);
    Query := TSQLQuery.Create(nil);
    Query.Database := AConnection;
    Query.Transaction := TSQLTransaction.Create(Query);
    Query.Transaction.Database := AConnection;
    Query.SQL.Text := 'select * from users where user=:username and password=:password';
    Query.ParamByName('username').AsString := User;
    Query.ParamByName('password').AsString := Password;
    Query.Open;
    Result := Query.RecordCount > 0;
    Query.Close;
    Query.Free;
  end;


[nobodyknowsme]

When I do that it breaks and doesn't work. Im confused.

[nobodyknowsme]

I replaced the function with the one you gave me and it breaks at  if GetValidUsername

[rvk]

Argh... 
I copied the piece of code from the wrong post where I still had user in the SQL.

Change the SQL-line to:

Code: [Select]

Query.SQL.Text := 'select * from users where username=:username and password=:password';
and you should be good to go

[nobodyknowsme]

wait so I change the function and that line?

[nobodyknowsme]

I just did that and now its never correct. The correct username and password is test and 0. You can never get in as the password and username are always wrong.