MYSQL - Binary Data, writing to Varbinary or Varchar

[4dk2]

Hallo, i have the Zeos. 6.6.6 using BDS 2006
I want to store crypted data in a Varbinary or Varchar Field.

when entering data in mysql with the mysql-QueryBrowser for example:
insert into test values (default,null,'\n\0\n',null);
=#10#00#10
it works and the data is stored correctly in the database.

in my example function u find the create-table and some of my tries.

Code: Select all

procedure TForm1.CastTest();
var binstr:string;
    ftb:Tblobfield;
    ftbin:TBytesField;
    st:TStringStream;
begin
{
  CREATE TABLE  .`test` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `fieldBlob` text,
    `fieldChar` varchar(48) DEFAULT NULL,
    `bin1` varbinary(10) DEFAULT NULL,
    PRIMARY KEY (`id`)
  ) ENGINE=InnoDB AUTO_INCREMENT=35 DEFAULT CHARSET=latin1;
}
  //the binary data :
  binstr:='123'#0'4567';

  //First: This is Working:
  st:=TStringStream.Create(binstr);
  try
    ftb:=(ZQuery1.FieldByName('FieldBlob') as TBlobField);
    ftb.LoadFromStream(st);
  finally
    st.free;
  end;
  //........

  //Second: this doesnt work:
  ZQuery1.FieldByName('FieldChar').asString:=binstr;
  //the string will be '123' (cut from #0, as expected)

  //calling the "FieldChar" as TBlobField this will cause a typecast error
  // (ZQuery1.FieldByName('FieldChar') as TBlobField)

  //Calling a typecast 4 the bin1 field as TByesField:
  ftbin:=(ZQuery1.FieldByName('bin1') as TBytesField);
  ftbin.AsString:=binstr;

  //this gives an Read Accessviolation at : 0041885B read from adress: 14000000
  //at line : 1319 in ZAbstractRODataset.pas
  {....
      else if Field.DataType = ftBytes then
      begin
        RowAccessor.SetBytes(ColumnIndex, VarToBytes(PVariant(Buffer)^)); //<<<<<<<<<<<ERORR
      end
  }
end;

What is the best way to write data over the fields?
You can see, doing this over Blobs works.

I have to write many records, so doing by foot over
'insert' scritps is not a solution.

Thx for your answers

Karsten

[4dk2]

So it has taken some time but here is my own 1.part of the solution:


Writing Binary Data to a myslq VarBinary Field (VarChar cant work, cause its been set via Widechars, so terminated by #0):

Code: Select all

procedure TForm1.WriteBinaryTest();
var binstr:string;
    ftbin:TBytesField;
    aoi:array of integer;
    i:integer;
    v:variant;
begin
  //the binary data :
  binstr:='123'#0'1'; //5 bytes

  //typecast the field (must be varbinary(size) in mysql...
  ftbin:=(ZQuery1.fieldbyname('bin1') as TBytesField);

  if length(binstr)>ftbin.Size then
  begin
    showmessage('too long');
    exit;
  end;

  SetLength(aoi,2);
  aoi[0]:=0;
  aoi[1]:=length(binstr)-1;
  v:=VarArrayCreate(aoi,varbyte); //create a VarArray, aoi is like [0..10]

  //write data in the VariantArray
  for I := 0 to aoi[1] do
    v[i]:=ord(binstr[i+1]);

  ftbin.SetData(@v); //set the data to the Field

end;

This works really good.
Data is written as declared, with any chars, no more breaks on char 0

The not really good reading:

Code: Select all

procedure TForm1.ReadBinaryTest();
var binstr:string;
    ftbin:TBytesField;
    aoi:array of integer;
    aob:array of byte;
    aob2:TByteDynArray;   //array of byte...
    i:integer;
    v:variant;
begin
  //typecast the field (must be varbinary(size) in mysql...
  ftbin:=(ZQuery1.fieldbyname('bin1') as TBytesField);

  SetLength(aob,ftbin.size);
  ftbin.GetData(@aob[0]); //reads data correctly, but is right filled with #1 , so i dont know the real length
  binstr:='';
  //binstr:='123'#0'1'; //5 bytes, thats how it is set in database...
  for I := 0 to length(aob)-1 do //length(aob)= what we set above, not the real length
    binstr:=binstr+chr(aob[i]);

  ShowMessage(binstr); //data filled right with #1...
  exit;//the next part doesnt work so exit...

  //that was my first try:
  //if i can set it by vararray, why not back :)

  v:=NULL;

  ftbin.GetData(@v); //get data to vararray, no expections so perhaps it works...

  //VarType(v) = array of unknown, so somethings done

  //aob2:=VarToBytes(v); //this function i copied from ZSysUtils.pas :)
                       //the Brother BytesToVar() is used for ByteFields to convert the data
                       //function TZAbstractRODataset.GetFieldData(Field: TField; Buffer: Pointer): Boolean;
                       //^^line 1183
                       //......
                       //   { Processes binary array fields. }
                       //   ftBytes:
                       //     begin
                       //       PVariant(Buffer)^ := BytesToVar(
                       //         RowAccessor.GetBytes(ColumnIndex, Result));
                       //       System.Move((PChar(RowAccessor.GetColumnData(ColumnIndex, Result)) + 2)^, Buffer^,
                       //         RowAccessor.GetColumnDataSize(ColumnIndex)-2);
                       //       Result := not Result;
                       //     end;
  //but it throws exception, cause the dimension is 0

  //so here my small VarToBytes...

  //back to string:
  binstr:='';
  if VarIsArray(v) then //ok
    if VarArrayDimCount(v)=1 then //not ok, is 0, should be 1
      for I := 0 to VarArrayHighBound(v, 1) do //exception...
        binstr:=binstr+ VarToStr(v[i]);
end;

So perhaps any dev has an idea
Bug perhaps? Im not really good with variants.

[Pitfiend]

Depending on how much crypted data you want to store, maybe you can give a shot to this crypting method (look at the last post) http://www.afterwarp.net/forum/thread460-3.html. They claim that it is very fast and optimized routines, with little footprint.

[4dk2]

Hi Pitfiend, i use the AES Mysql standart, to ensure that the data could also be
decrypted for example using: select aes_decrypt('data','password');

And my fieldtests had shown, that AES could generate data with zero chars.

I dont know the XTEA method, sorry

But perhaps u can help me with the AES_Standart:
First u will need http://www.michael-puff.de/Developer/De ... C_v5.2.zip

(u dont need to compile the package for my functions :

Code: Select all

uses DECUtil, DECFmt, DECCipher,DECHash
.......
function Mysql_AES_Encrypt_asHex(keyA:string;ValueA:string):string;
var aes:TCipher_Rijndael;
    Salt: Binary;
    pad_len:integer;
    pad_char:char;
begin
  if Length(keyA)>16 then
  begin
    showmessage('Mysql_AES_Encrypt_asHex, works only with max 16byte keys!');
    exit;
  end;
  
  Salt := RandomBinary(16); //IV Verctor initialisieren
  aes:=TCipher_Rijndael.Create();
  try
    aes.Mode:=cmECBx;
    //the length of the to crypt data must be filled to 16bytes blocks
    pad_len:=(Length(ValueA) div 16)*16;
    if (Length(ValueA) mod 16)>=0 then
      pad_len:=pad_len+16;

	//fillchar is the difference from the data to the block length, see with complier how it works
    //example, data length = 13
    //char would be 16-3 = 3
	//if the length of the data is 8 , the block length will be 16, filled with #16, dont know why :D
    pad_char:=chr(16-(Length(ValueA) mod 16));
    ValueA:=fillCharRight(ValueA,pad_char,pad_len);
    aes.Init(keyA);
    result := aes.EncodeBinary(ValueA, TFormat_HEX);
  finally
    aes.Free;
  end;
end;



function Mysql_AES_Decrypt_fromHex(keyA:string;HexValueA:string):string;
var aes:TCipher_Rijndael;
    BinValue:string;
    Salt: Binary;
    len,cutlen:integer;
begin
  if Length(keyA)>16 then
  begin
    showmessage('Mysql_AES_Decrypt_fromHex, only works with 16byte keys!');
    exit;
  end;

  Salt := RandomBinary(16); //IV Verctor init
  aes:=TCipher_Rijndael.Create();
  try
    aes.Mode:=cmECBx;

    uppercase(HexValueA);
    aes.Init(keyA);
    result := aes.DecodeBinary(HexValueA, TFormat_HEX);


    // cut dummy bytes, see in encrypt
    len:=length(Result);
    cutlen:=ord(Result[len]);
    Delete(Result,len-cutlen+1,cutlen);
  finally
    aes.Free;
  end;
end;

Now to my problem
I translated this from php to delphi. I couldnt find any delphi examples...
This functions work, but they only work with keys that with maximum of 16bytes.

Would be nice if you have an idea what to fix, to get it run with longer keys.