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

Question

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.

I have query1. it contains the fieldlist.
I'm creating a parameter list in another query from this fieldlist.
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.

I'm a bit stucked. You're asking how to generate a code for INSERT statement from existing ADO query field definitions somewhere on your datamodule or something ? — TLama, Nov 08 '13 at 13:05
So, you're expecting that someone has written some magical code which guesses your intentions of an ADO Query component to produce new SQL script? I highly doubt this has been done. — Jerry Dodge, Nov 08 '13 at 13:20
@JerryDodge Of course i'm not. Wtih my methos i can eliminate "sql injection tries" without an effort but. But in this way i have to use static fields... Maybe i should get fieldlist from msql master database ? — ikutluay, Nov 08 '13 at 13:22
@TLama yes you are right. I want to do something like that. I want to create the insert (or update) code and add values to it easily. — ikutluay, Nov 08 '13 at 13:24
So, something [`like this`](http://pastebin.com/hAFHUjgn) for instance ? (you can call it like `GenerateInsertQuery('SomeTable', ADOQuery1)`). Note that it's ugly code, I don't have time right now... — TLama, Nov 08 '13 at 13:54
Wait, using `append` and `post` is vulnerable to SQL injection? I thought it just used parameters and field names to create the insert query internally. — Marcus Adams, Nov 08 '13 at 14:01
@MarcusAdams, one of the key points of using such data access components is that (to my knowledge) they safely cope with SQL injection. — Uwe Raabe, Nov 08 '13 at 14:36
There is no need for writing it on your own the components will handle this for you: [Is FieldByName injection-safe?](http://stackoverflow.com/questions/17209680/is-fieldbyname-injection-safe) — bummi, Nov 08 '13 at 16:08
Which database are you using? Some db's (e.g. MySQL) have build-in support for this. — Johan, Nov 08 '13 at 20:10
You might find it easier to use a dataset (TADODataset or TADOTable) to do a dataset-like-job, and use a TADOQuery to do a query-like-job. Oh, and there is TADOCommand to run a Command. So if you're writing SQL "insert" strings, you might want to look at running those with TADOCommand. And you might want to avoid writing them at all, and just set field values and insert into a `TADODataset`. — Warren P, Nov 09 '13 at 12:45
(One of my favorite statements: When all you have is a hammer, everything looks like a nail. Something worth thinking about) — Warren P, Nov 09 '13 at 12:47
@WarrenP If i'm right, i heard your name at previous years in JVCL project disccusions. Thanks for interest and proposals... I ll work on this and other proposal... — ikutluay, Nov 09 '13 at 12:53
It's good to grow your experience with a variety of tools and techniques, and then pick which one works best for a particular situation. Good luck! — Warren P, Nov 09 '13 at 15:31

score 3 · Answer 1 · edited May 23 '17 at 10:31

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!

Bengsson Thanks for proposal; but as you said it is a big step and i want to go on the way step bu step. Also, again as you said they are extended components and i want to use standart components... But who knows the future. I'll try to get the techniq when i have enoug knowledge for that. — ikutluay, Nov 12 '13 at 10:07

score 2 · Accepted Answer · edited May 23 '17 at 12:21

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;

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

2 Answers2