CODEWITHSUNDEEP
mysqldelphilazarus
Andrew Zitsew
Andrew Zitsew

Reputation: 45

Lazarus insert sql results int string grid

I have problem inserting sql results into TStringGrid.I have following code:

 var i:Integer;
 begin
   SqlQuery1.SQL.Text:= 'SELECT * FROM `users`'; 
   SqlQuery1.Open;
   MySql55Connection1.Open;
   i:= 0;
   while not SQLQUERY1.EOF do
   begin
     i:= i+1;
     StringGrid1.Cells[0,i]:= SqlQuery1.FieldByName('Username').AsString;
     StringGrid1.Cells[1,i]:= SqlQuery1.FieldByName('Password').AsString;
     StringGrid1.Cells[2,i]:= SqlQuery1.FieldByName('id').AsString;
   end;
 end;

So in my database only one line. But program adding a lot of copies of this line in StringGrid and it causes error(Index out of bounds).

Upvotes: 2

Views: 2800

Answers (1)

Johan
Johan

Reputation: 76537

Danger
It appears you are storing passwords in plain text form in a database.
This is an extremely bad idea.
Never store passwords in a database.
Use salted hashes instead.
See: How do I hash a string with Delphi?

There are a couple of other problems in your code:

  • You don't ensure that the stringgrid has enough rows to hold your data.
  • You're not moving to the next line in the query.
  • You're opening the query before the connection is open.
  • You're using FieldByName inside a loop, this is going to be very slow.

Simple solution
Use a DBGrid.

If you insist on using a StringGrid
I suggest refactoring the code like so: 

 var 
   i,a:Integer;
   FUsername, FPasswordHash, Fid, FSalt: TField;
 begin
   if not(MySQl55Connection.Active) then MySql55Connection1.Open;
   SqlQuery1.SQL.Text:= 'SELECT * FROM users';  //only use backticks on reserved words.
   SqlQuery1.Open;
   FUsername:= SqlQuery1.FieldByName('Username');
   //do not use plain text passwords!!
   FPasswordHash:= SQLQuery1.FieldByName('SaltedPasswordHashUsingSHA256');
   FId:= SqlQuery1.FieldByName('id');
   FSalt:= SQLQuery1.FieldByName('SaltUsingCryptoRandomFunction');
   a:= StringGrid1.FixedRowCount;
   if SQLQuery1.RecordCount = -1 then StringGrid1.RowCount = 100 //set it to something reasonable.  
   else StringGrid1.RowCount:= a + SQLQuery1.RecordCount;
   //SQLQuery1.DisableControls 
   try
     i:= StringGrid1.FixedRowCount;
     while not(SQLQuery1.EOF) do begin
       if i >= StringGrid1.RowCount then StringGrid1.RowCount:= i;
       StringGrid1.Cells[0,i]:= FUserName.AsString;
       StringGrid1.Cells[1,i]:= FPasswordHash.AsString;
       StringGrid1,Cells[3,i]:= FSaltInHex.AsString;
       StringGrid1.Cells[2,i]:= FId.AsString;
       SQLQuery1.Next;  //get next row.
       Inc(i);
     end; {while}
   finally
     //just in case you want to do endupdate or close the SQLQuery or do SQLQuery1.EnableControls 
   end;
 end;

Basic security example
Here's how to hash a password:

Download Lockbox3.
Put a THash on your form and set the hash property to SHA-512.
Use the following code to produce a hash result. 

function StringToHex(const input: string): AnsiString;
var
  NumBytes, i: Integer;
  B: Byte;
  W: word;
  Wa: array[0..1] of byte absolute W;
begin
  NumBytes := input.length * SizeOf(Char);
  SetLength(Result, NumBytes * 2);
  for i := 1 to NumBytes do begin
    if SizeOf(Char) = 1 then begin
      B:= Byte(input[i]);
      BinToHex(@B, @Result[(I*2)+1], 1);
    end else begin
      W:= Word(input[i]);
      BinToHex(@Wa[0], @Result[(i*4+0)],1);
      BinToHex(@Wa[1], @Result[(i*4+1)],1);
    end; {else}  
  end;
end;

function TForm1.HashPassword(var Password: string; const Salt: string): string;
var
  KillPassword: pbyte;
begin
  Hash1.HashString(StringToHex(Password)+StringToHex(Salt));
  KillPassword:= PByte(@Password[1]);
  FillChar(KillPassword^, Length(Password)*SizeOf(Char), #0); //remove password from memory.  
  Password:= ''; //Now free password.
end;

function GenerateSalt( ByteCount: integer = 32): string;
var
  Buffer: TMemoryStream;
begin
  Buffer := TMemoryStream.Create;
  try
    Buffer.Size := ByteCount;
    RandomFillStream( Buffer);
    result := Stream_to_Base64( Buffer);
  finally
    Buffer.Free
  end;
end;

This is the minimum amount of work you can get away with whilst still having things secure.
Do not think that your passwords are unimportant because you just have a toy database, because people reuse passwords and thus your toy passwords end up being the same passwords used for online banking and such.
People are lazy....

Upvotes: 4

Related Questions