CODEWITHSUNDEEP
delphipascal
ikutluay
ikutluay

Reputation: 142

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.

Upvotes: 0

Views: 2811

Answers (2)

Roland Bengtsson
Roland Bengtsson

Reputation: 5166

Maybe not the reply you want. I think you need to raise the abstraction level. You need to skip SQL. An ORM framework can do this for you. It maybe feels like a big step for you but I promise it is also a relief to just use code like:

Person.name := 'Bob';
Invoice.customer.address.street := 'Abbey road';
Edit1.text := Invoice.customer.name;

To actually update database you need to call an update method that differ depending on framework. For a list of frameworks see here. I am also aware of TMS Aurelius. I use Bold on daily use. Bold also have features like OCL, derived attributes and links in the model, some boldaware components (it updates whenever db changes). But it has one big disadvantage. It is only available for D2006/D2007. I am working for a solution on this because I think it is the best and most mature ORM framework for Delphi. See also my blog on Bold for Delphi. Ask if you have questions!

Upvotes: 3

Johan
Johan

Reputation: 76753

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;

Upvotes: 2

Related Questions