Encrypted password check

Question

I’m having a hard time logging in Delphi with encrypted password at the bank MySQL, I can do the registration and encrypt the password in the database through Delphi with the StoredProcedure that I created at MySQL, this encryption I did through the function MD5 of the own MySQL. In the Delphi, use TSQLStoredProc to use the StoredProcedure of the database.

The difficulty is in Function that I created at Mysql, it returns an integer. If zero the return:

o login está incorreto

If it is um:

o login está certo 

To Function works properly on MySQL, makes the correct comparison, however, what I do not know is how to show or compare this Function with this whole return in the Delphi.

Here’s my code so you’ll understand:

SQL:

DELIMITER $$ 
 CREATE TABLE `tbl_users` ( 
  `id` int(11) NOT NULL AUTO_INCREMENT, 
  `username` varchar(15) NOT NULL, 
  `firstlast_name` varchar(80) DEFAULT NULL, 
  `password` varchar(45) DEFAULT NULL, 
  PRIMARY KEY (`id`) 
)$$ 
 DELIMITER ; 

 DELIMITER $$  
 DROP FUNCTION IF EXISTS `fun_valida_usuario`$$  
 CREATE FUNCTION `fun_valida_usuario`(p_username VARCHAR(15)  
                , p_password VARCHAR(45) ) RETURNS INT(1)  
 BEGIN  
 DECLARE _ret            INT(1) DEFAULT 0;  
     SET _ret = IFNULL((SELECT DISTINCT 1  
                       FROM tbl_users  
                      WHERE `username` = p_username  
                       AND `password` = MD5(p_password)),0);                            
 RETURN _ret;  
 END$$  
 DELIMITER ; 

DELIMITER $$ 
CREATE PROCEDURE `proc_iae_tbl_users`( 
p_opc varchar(1), 
p_id int, 
p_username varchar(15), 
p_firstlast_name varchar(80), 
p_password varchar(45)) 
BEGIN 

IF ((p_opc = 'I') && (p_username != '') && (p_password != '')) THEN 

  INSERT INTO tbl_users (id, username, firstlast_name, `password`) VALUES (p_id, p_username, p_firstlast_name, MD5(p_password)); 

ELSE 

IF ((p_opc = 'E') && (p_id > 0)) THEN 

   delete from tbl_users where id = p_id; 

ELSE 

IF ((p_opc = 'A')) THEN 

  UPDATE tbl_users set id = p_id, username = p_username, firstlast_name = p_firstlast_name, `password` = p_password WHERE id = p_id; 

ELSE 
SELECT 'Você não pode realizar as alterações' AS Msg; 
END IF; 
END IF; 
END IF; 

END$$ 
DELIMITER ; 

PAS:

unit Unit1; 

interface 

uses 
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms, 
  Dialogs, WideStrings, DBXMySql, DB, SqlExpr, FMTBcd, Provider, DBClient, 
  StdCtrls, Mask, DBCtrls; 

type 
  TForm1 = class(TForm) 
    SQLConnection1: TSQLConnection; 
    ClientDataSet1: TClientDataSet; 
    DataSetProvider1: TDataSetProvider; 
    SQLQuery1: TSQLQuery; 
    SQLQuery1id: TIntegerField; 
    SQLQuery1username: TStringField; 
    SQLQuery1firstlast_name: TStringField; 
    SQLQuery1password: TStringField; 
    DataSource1: TDataSource; 
    ClientDataSet1id: TIntegerField; 
    ClientDataSet1username: TStringField; 
    ClientDataSet1firstlast_name: TStringField; 
    ClientDataSet1password: TStringField; 
    InsertUser: TButton; 
    Edit1: TEdit; 
    Edit2: TEdit; 
    proc_iae_tbl_users: TSQLStoredProc; 
    proc_iae_tbl_usersMsg: TStringField; 
    Edit3: TEdit; 
    Edit4: TEdit; 
    Edit5: TEdit; 
    VerifyLogin: TButton; 
    Label1: TLabel; 
    Label2: TLabel; 
    SQLStoredProc1: TSQLStoredProc; 
    SQLStoredProc1Value: TIntegerField; 
    Label3: TLabel; 
    DBEdit1: TDBEdit; 
    DataSource2: TDataSource; 
    SQLQuery2: TSQLQuery; 
    SQLQuery2Valor: TIntegerField; 
    Label4: TLabel; 
    DBEdit2: TDBEdit; 
    DataSource3: TDataSource; 
    procedure Button1Click(Sender: TObject); 
    procedure Button2Click(Sender: TObject); 
  private 
    { Private declarations } 
  public 
    { Public declarations } 
  end; 

var 
  Form1: TForm1; 

implementation 

{$R *.dfm} 

procedure TForm1.InsertUserClick(Sender: TObject); 
begin 
   proc_iae_tbl_users.Close; 
   proc_iae_tbl_users.ParamByName('p_opc').Value:= 'I'; 
   proc_iae_tbl_users.ParamByName('p_username').Value:= Edit1.Text; 
   proc_iae_tbl_users.ParamByName('p_firstlast_name').Value:= Edit2.Text; 
   proc_iae_tbl_users.ParamByName('p_password').Value:= Edit3.Text; 
   proc_iae_tbl_users.ExecProc; 
end; 

procedure TForm1.VerifyLoginClick(Sender: TObject); 
begin 
   // Aqui deve ficar o código para logar e verificar o login 
end; 

end.

Someone can give me a solution?

I appreciate your willingness from the start. Thank you.

Answer 1

Some considerations on safety

According to what you described, I suggest you check the following topics:

1. Do not send your password to be encrypted in the bank. During this process anyone listening to your interaction with it can discover the password. Even if you have some security measure in the transport layer, it is 1000% safer to encrypt in the client(Delphi).

2. MD5 is not a good encryption algorithm. As it is calculated very quickly, an attacker can calculate billions of hashes per second. Consider using SHA-1 or preferably SHA-2.

That said, the answer to the question

TSQLStoredProc inherits from the component TDataset. That is, it is a dataset. So all you need to consume the result of a stored Procedure is to open the dataset and use its values. Example:

StoredProc.Open;
try
    CredenciaisValidas := StoredProc.FieldByName('Result').AsInteger = 1;
finally
    StoredProc.Close;

Note: I do not remember if Result is the field name used for the return of functions, but nothing that a little debug does not solve ;)

Answer 2

So, guys, from trying so hard, I managed to solve.

On my login button, I implemented the following code:

  DM.sql_login.Close;
  DM.sql_login.SQL.Clear;
  DM.sql_login.SQL.Add('SELECT fun_valida_usuario("'+edt_username.Text+'","'+edt_password.Text+'") AS Ret');
  DM.sql_login.Open;

  if (DM.sql_login['Ret'] = 1 ) then
    begin
       DM.sql_login.Close;
      Close;
    end
  else
    begin
      MessageBox(handle,'Usuário ou Senha estão incorretos','Aviso',mb_IconError + mb_OK);
    end;

I hope it helps someone who is in the same doubt. Thank you who read.