delphi - How Can I Create Insert and Update SQL's by pure code

I'm currently using AdoQuery's and append post commands. But for data security I want to change my code with insert into and update table name...

But I have a lot of forms and tables...

Because of that I think maybe someone has already developed code for generating insert statements.

Actually I have found a way but I'm stuck.

1. I have query1. it contains the fieldlist.


2. I'm creating a parameter list in another query from this fieldlist.


3. I'm updating the parameters field by field.

This is not very convenient

Can someone give me a easy ways to do this.

Note: I prefer coding this job with only standard components. I don't want to install additional components.

Answer

You take the fieldlist from your query.
Create a new query with parameters.
And fill in the values.

Something like this:

const 
  TableNameEscapeStart = '['; //SQL server, use '`' for MySQL
  TableNameEscapeEnd = ']';   //SQL server, use '`' for MySQL

  FieldNameEscapeStart = '[';
  FieldNameEscapeEnd = ']';

function CreateInsertStatementFromTable1ToTable2(Table1, Table2: TTable): String;
var
  i: integer;
  comma: string;
begin
  i:= 0;
  Result:= 'INSERT INTO '+TableNameEscapeStart + Table2.TableName + TableNameEscapeEnd + ' (';

  comma:= ' , '
  while i < Table1.FieldCount do begin
    if (i = Table1.FieldCount -1) then begin comma:= ' '; end;
    Result:= Result + FieldNameEscapeStart + Table1.Fields.Field[i].Name + FieldNameEscapeEnd + comma;
  end;
  Result:= Result +' ) VALUES ( ';
  i:= 0;
  comma:= ' , '
  while i < Table1.FieldCount do begin
    if (i = Table1.FieldCount -1) then begin comma:= ' '; end;

    Result:= Result +':' + IntToStr(i+1) + comma;  
  end; {while}
  Result:= Result + ' ); ';
end;

There are three avenues for SQL injection here.
1. The field values
2. The table name
3. The field names

The first is covered by the use of parameters.
The second and third are covered, because you're using the table and field names of the table directly.
If you don't have a trusted source of table and fields names, then you need to compare these against the table and fieldnames obtained directly from the table.
See: Delphi - prevent against SQL injection

You insert the data using ParamByName (slowly) or more efficiently using Param[i] where i starts at 0.

In MySQL it's even easier:
If table1 and table2 have the same fields, the following SQL will insert all data in table2 into table1:

INSERT INTO table1 SELECT * FROM table2;