CODEWITHSUNDEEP
mysqldelphihashmd5
Peter Jones
Peter Jones

Reputation: 775

Storing MD5 in MySQL

Instead of storing a MD5 hash in a 32-byte field, I will like to store it in a 16-byte binary field. Mysql field "TEMP_MD5" is defined as Binary(16).

The MySQL CREATE TABLE with a sample row insert is:

CREATE TABLE `mytable` (
    `TEMP_MD5` binary(16) DEFAULT NULL,
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO mytable (TEMP_MD5) VALUES UNHEX("202cb962ac59075b964b07152d234b70") );

The sample code:

Let's say after the 16-byte binary field has been stored in the MySQL field TEMP_MD5, how do I compare this 16-byte field in Delphi code after I retrieve the value?

Is it possible to skip MySQL HEX/UNHEX internal functions, and just use Delphi code to compare the 16-byte binary field (32-byte string) in MySQL?

For example :

FDQuery1.Open( 'SELECT TEMP_MD5 from mytable;' );

if THashMD5.GetHashBytes('123') = fDQuery1.FieldByName('TEMP_MD5').VALUE then
  SHOWMESSAGE('MATCHED!');

However, it seems that the values for FieldByName('TEMP_MD5').value never matched the THashMD5.GetHashString('123') value

and another way of comparing by using SELECT statement also failed

FDQuery1.Open( 'SELECT TEMP_MD5 mytable ' +
                        'WHERE (TEMP_MD5=:myvalue)',
                               [THashMD5.GetHashBytes('123')] );

above also failed to give FDQuery1.RecordCount = 1.

Basically I'm trying to compare the 16-byte Binary I stored in MySQL to a value, let's say '123' in code to see if both matches.

I'm using Delphi 10.2 moving to 10.4 next year.

Upvotes: 2

Views: 519

Answers (1)

fpiette
fpiette

Reputation: 12322

Here is an example of code showing how to write an MD5 into your database and how to read it back and compare with a given MD5 hash:

Inserting data:

procedure TForm1.InsertDataButtonClick(Sender: TObject);
var
    MD5    : TArray<Byte>;
begin
    MD5 := THashMD5.GetHashBytes('123');
    FDConnection1.Connected := TRUE;
    FDQuery1.SQL.Text := 'INSERT INTO mytable (TEMP_MD5) VALUES(:MD5)';
    FDQuery1.ParamByName('MD5').SetBlobRawData(Length(MD5), PByte(MD5));
    FDQuery1.ExecSQL;
    Memo1.Lines.Add('Rows affected = ' + FDQuery1.RowsAffected.ToString);
end;

Reading data back and comparing with given hash:

procedure TForm1.ReadDataButtonClick(Sender: TObject);
var
    MD5      : TArray<Byte>;
    MD5_123  : TArray<Byte>;
    FieldMD5 : TField;
    RecCnt   : Integer;
begin
    MD5_123 := THashMD5.GetHashBytes('123');

    FDConnection1.Connected := TRUE;
    // First version: get all records
    // FDQuery1.SQL.Text := 'SELECT TEMP_MD5 FROM mytable';
    // Second version: Get only records where TEMP_MD5 is hash('123').
    FDQuery1.SQL.Text := 'SELECT TEMP_MD5 FROM mytable WHERE TEMP_MD5 = :MD5';
    FDQuery1.ParamByName('MD5').SetBlobRawData(Length(MD5_123), PByte(MD5_123));
    // Execute the query
    FDQuery1.Open;
    RecCnt := 0;
    while not FDQuery1.Eof do begin
        Inc(RecCnt);
        FieldMD5 := FDQuery1.FieldByName('TEMP_MD5');
        SetLength(MD5, FieldMD5.DataSize);
        FieldMD5.GetData(MD5);
        if (Length(MD5) = Length(MD5_123)) and
           (CompareMem(PByte(MD5), PByte(MD5_123), Length(MD5))) then
            Memo1.Lines.Add(RecCnt.ToString + ') MD5(123) = ' + MD5ToStr(MD5))
        else
            Memo1.Lines.Add(RecCnt.ToString + ') ' + MD5ToStr(MD5));
        FDQuery1.Next;
    end;
end;

As you can see reading the code, I compare the MD5 from database with given MD5 by comparing the memory containing the values (arrays of bytes).

Utility function:

function MD5ToStr(MD5 : TArray<Byte>) : String;
var
    B      : Byte;
begin
    Result := '';
    for B in MD5 do
        Result := Result + B.ToHexString(2);
end;

Upvotes: 1

Related Questions