Insert into SQL syntax error delphi

Question

I am wanting to insert a record into a database table (website) by using a procedure with parameters. The SQL code has been tested in mysql workbench and works properly to insert new data. However, with delphi i am getting an 'SQL Syntax error near [insert whole line of code here]'. I was wondering if one of you could tell me where I'm going wrong. Thanks again.

procedure TNewWebsite.InsertData(WID, D, T, Wh, Dr, Od, Rd, Rc, Pm, OStat,  Cstat, Rstat, N, U1, P1, P2, PStat, CID : string);
  begin
   WebsiteTable.WebsiteQuery.SQL.Add('INSERT INTO website VALUES ( '+WID+', '''+D+''', '''+T+''', '''+Wh+''', '''+D+''', '''+Od+''', '''+Rd+''', '+Rc+', '''+Pm+''', '+Ostat+', '+Cstat+', '''+Rstat+''', '''+N+''', '''+U1+''', '''+P1+''', '''+P2+''', '+Pstat+', '+CID+';)');
   WebsiteTable.WebsiteQuery.Open;
 end;

Please print out the result of the statement creation expression (that is, the SQL string that you create) and you will almost certainly see the problem immediately. — Larry Lustig, Jan 17 '15 at 19:09

score 5 · Accepted Answer · edited May 23 '17 at 12:14

5

You have quite a few problems in your code.

A) Don't exaggerate with function parameters, if you have a lot of variables, assemble them in a record or class depending on your needs.

B) Your SQL code is vulnerable for SQL injection. You probably never heard of SQL injection, please Google it or read this really good answer. The solution against SQL injection is to use parameters (see my code example). An added bonus is that your SQL statement will be human readable, and less error prone.

C) The Open function is only used in conjunction for queries that return a result set, like SELECT statements. For INSERT, DELETE and UPDATE statements, you need to use the ExecSQL function.

Sanitized code:

interface

type 
  TMyDataRecord = record
   WID : String;
   D : String; 
   T : String;
   Wh : String; 
   Dr : String; 
   Od : String; 
   Rd : String; 
   Rc : String; 
   Pm : String; 
   OStat : String;
   Cstat : String; 
   Rstat : String; 
   N : String; 
   U1 : String; 
   P1 : String; 
   P2 : String;
   PStat : String; 
   CID : String;
  end;
...
implementation

procedure TNewWebsite.InsertData(Data : TMyDataRecord);

var 
  SQL : String;

begin
 SQL := 'INSERT INTO website VALUES (:WID, :D1, :T, :Wh, :D2, :Od, :Rd, :Rc',+   
        'Pm, :Ostat, :Cstat, :Rstat, :N, :U1, :P1, :P2, :Pstat, :CID)'; 
 WebsiteTable.WebsiteQuery.ParamCheck := True;
 WebsiteTable.WebsiteQuery.SQL.Text := SQL;
 WebsiteTable.WebsiteQuery.Params.ParamByName('WID').AsString := Data.WID;
 WebsiteTable.WebsiteQuery.Params.ParamByName('D1').AsString := Data.D;
 ...// rest of parameters
 WebsiteTable.WebsiteQuery.Params.ParamByName('CID').AsString := Data.CID;
 WebsiteTable.WebsiteQuery.ExecSQL;
end;

edited May 23 '17 at 12:14

Community

1
1

answered Jan 17 '15 at 18:52

whosrdaddy

11,720
4
50
99

wow thank you very much. I'm just completing it and I'll mark complete if it does.EDIT: Just to let you know, you've left ,+' in your code – Toby Fox Jan 17 '15 at 19:13
Sorry I'm getting error 'tsqlquery does not contain a member called 'parameters'' – Toby Fox Jan 17 '15 at 19:17
I do apologise haha. Thanks so much. Nearly done now – Toby Fox Jan 17 '15 at 19:27
Ok now I'm getting error 'not enough actual parameters' and parambyname['WID'] is highlighted. Hmm. Nearly there. – Toby Fox Jan 17 '15 at 19:36
Fixed it! The square brackets need to be round ones – Toby Fox Jan 17 '15 at 19:39
Really? Haha that's impressive – Toby Fox Jan 17 '15 at 19:40
New error: 'dbexpress driver does not support the tdbxtypes.UNKNOWN data type. Vendor error message: .' It doesn't look like a problem with the sql this time so progress! Any ideas how to fix? Thanks again – Toby Fox Jan 17 '15 at 19:43
Do you have any ideas? – Toby Fox Jan 17 '15 at 20:17
@TobyFox: One or more of the columns in your `website` table is probably using a data type that your chosen Dbxpress driver does not support. – Remy Lebeau Jan 17 '15 at 20:19
All working now, except I'm getting a mySQL error 'near''at line 1'. What could be causing this? Thanks a lot for what you've done so far, Toby – Toby Fox Jan 17 '15 at 21:13

score 1 · Answer 2 · answered Jan 17 '15 at 18:36

1

Please replace '+CID+';)'); with '+CID+');'); in the end of your query line. The ; was in wrong place.

answered Jan 17 '15 at 18:36

score 1 · Answer 3 · answered Jan 17 '15 at 20:06

There is insufficient information about the table and the data you are trying to insert to fix your code. T_G's answer may be correct and the answers recommending parameterized queries are good but there may be other errors. I will offer you some suggestions on how to diagnose your exact problem.

First, do not try to debug your SQL statements through Delphi. Use a database admin tool specifically designed for your database, such as MySQL Workbench http://www.mysql.com/products/workbench/ It will give much more detailed error messages which can help you find what is causing the error.

If you create your SQL statement programatically, as shown in your question, assign it to a variable so you can capture it while debugging and then copy it to the database admin tool of you choice to debug it.

var
  SQL : string;
begin
  SQL := 'INSERT INTO website VALUES ( '+WID+', '''+D+''', '''+T+''', '''+Wh+''', '''+D+''', '''+Od+''', '''+Rd+''', '+Rc+', '''+Pm+''', '+Ostat+', '+Cstat+', '''+Rstat+''', '''+N+''', '''+U1+''', '''+P1+''', '''+P2+''', '+Pstat+', '+CID+';)');
  WebsiteTable.WebsiteQuery.SQL.Text := SQL;

Second, it is bad practice to not include the column names in the SQL statement. You should make your SQL like the following

INSERT INTO WEBSITE (COLUMN1, COLUMN2, COLUMN3) VALUES (1, 'ABC', 'XYZ')

or using a parameterized query

INSERT INTO WEBSITE (COLUMN1, COLUMN2, COLUMN3) VALUES (:C1, :C2, :C3)

Yes, it is more typing, but it prevents assumptions from being the cause of errors, such data going into the wrong columns.

Also, if you are going to manually wrap the inserted values with quote characters instead of using a parameterized query, at least use `(Ansi)QuotedStr()` to account for quotes that are inside the values. Using a parameterized query is best to avoid injection attacks, but `(Ansi)QuotedStr()` is the next best option when building up SQL strings. Simply wrapping the values with surrounding quotes, as shown, is not injection-safe, you have to escape any quotes that are contained inside the values, eg: `SQL := 'INSERT INTO website VALUES ( '+WID+', '+QuotedStr(D)+', '+QuotedStr(T)+', '+...;` — Remy Lebeau, Jan 17 '15 at 20:13
Hi. I've been using workbench and my sql works fine (without parameterisation). I'm getting this error 'dbexpress driver does not support tdbxtypes. Unknown data type'. What does this mean? — Toby Fox, Jan 17 '15 at 20:20
That should be another question because more information is needed. — crefird, Jan 17 '15 at 20:37

score 0 · Answer 4 · answered Jan 17 '15 at 19:16

perhaps you can use it like this

function FormatQuery(Query: String; Args: array of const): string;
var S:string; FmtSet: TFormatSettings;
begin
  Result:= Query;
  GetLocaleFormatSettings(LOCALE_SYSTEM_DEFAULT, FmtSet);
  FmtSet.DecimalSeparator := '.';
  FmtStr(S, Query, Args, FmtSet);
  Result:= S;
end;

procedure TNewWebsite.InsertData(Query: String; Args: array of const);
var MyQuery: string;
begin
  MyQuery:= FormatQuery(Query, Args);
  WebsiteTable.WebsiteQuery.SQL.Clear;
  WebsiteTable.WebsiteQuery.SQL.Add(MyQuery);
  WebsiteTable.WebsiteQuery.Open;
end;

After that you can Call it like this

var MyQuery: string;
MyQuery:= 'INSERT INTO website VALUES (%s,"%s","%s","%s","%s","%s","%s",%s,"%s",%s,%s,"%s","%s","%s","%s","%s",%s,%s;)';
InsertData(MyQuery,[WID,D,T,Wh,D,Od,Rd,Rc,Pm,Ostat,Cstat,Rstat,N,U1,P1,P2,Pstat,CID]);

I hope that can Help u.

this code is still vulnerable as outlined in my answer, downvoted — whosrdaddy, Jan 17 '15 at 19:24

Insert into SQL syntax error delphi

4 Answers4