Parameter query at runtime

[JacobusOpperman]

Hi! I am hoping someone will be able to help me. I have already configured many things for TIBCConnection and TIBCQuery at design time but want to do the rest at runtime.
Here is my code:

Code: Select all

Var
 PParamScore : TDAParam;
 PParamNameAndSurname :TDAParam;

Begin
 IBCConnection.Connect;
 IBCQuery.Params.Clear;
 PParamScore := IBCQuery.Params.CreateParam(TFieldType.ftInteger, 'SCORE', TParamType.ptInput);
 PParamScore.Value := 1000; (*Random score value of 1000*)
 PParamNameAndSurname := IBCQuery.Params.CreateParam(TFieldType.ftString, 'NAMEANDSURNAME', 
                                                                                                                   TParamType.ptInput);
 PParamNameAndSurname.Value := 'Jacobus Opperman';

 IBCQuery.Params.AddParam(PParamScore);
 IBCQuery.Params.AddParam(PParamNameAndSurname);
 IBCQuery.ParamCheck := True;
 IBCQuery.SQL.Text := 'insert into snake (SCORE, NAMEANDSURNAME) '
                                                  + 'values (:@PParamScore, :@ParamNameAndSurname);';
 IBCQuery.Execute;
End;                          
 

Everything compiles without error. At IBCQuery.Execute I get the following runtime error:
'Dynamic SQL Error
SQL error code = -206
Column unknown
PPARAMSCORE'

SCORE and NAMEANDSURNAME are the two columns allready created in a Firebird database table SNAKE. I can make everything work with pure SQL without paramaters but I would like to do it with parameters to prevent SQL code injection attacks. I hope someone will be able to provide me some code because I have tried many things and nothing already written at the forums help. Thank you!

[ViktorV]

To solve the task, please try to follow these recommendations:
- change this line:

Code: Select all

IBCQuery.SQL.Text := 'insert into snake (SCORE, NAMEANDSURNAME) values (:@PParamScore, :@ParamNameAndSurname);';

to this line:

Code: Select all

IBCQuery.SQL.Text := 'insert into snake (SCORE, NAMEANDSURNAME) values (:PParamScore, :ParamNameAndSurname);';

- set the parameter values after setting the TIBCQuery.SQL.Text property;
- the TIBCQuery.ParamCheck property is responsible for enabling/disabling automatic parameter generation when a SQL property is changed: https://devart.com/ibdac/docs/devart.da ... mcheck.htm
This is why you can use the following code:

Code: Select all

var
  PParamScore : TDAParam;
  PParamNameAndSurname :TDAParam;
begin
  IBCQuery.ParamCheck := False;
  IBCQuery.SQL.Text := 'insert into snake (id, SCORE, NAMEANDSURNAME) values (4, :PParamScore, :ParamNameAndSurname);';
  IBCQuery.Params.Clear;
  PParamScore := IBCQuery.Params.CreateParam(TFieldType.ftInteger, 'SCORE', TParamType.ptInput);
  PParamScore.Value := 1001; (*Random score value of 1000*)
  PParamNameAndSurname := IBCQuery.Params.CreateParam(TFieldType.ftString, 'NAMEANDSURNAME', TParamType.ptInput);
  PParamNameAndSurname.Value := 'Jacobus Opperman1';
  IBCQuery.Params.AddParam(PParamScore);
  IBCQuery.Params.AddParam(PParamNameAndSurname);
  IBCQuery.Execute;
end;

Alternatively, you can use this code:

Code: Select all

var
  PParamScore : TDAParam;
  PParamNameAndSurname :TDAParam;
begin
  IBCQuery.ParamCheck := True;
  IBCQuery.SQL.Text := 'insert into snake (id, SCORE, NAMEANDSURNAME) values (5, :PParamScore, :ParamNameAndSurname);';
  PParamScore := IBCQuery.ParamByName('PParamScore');
  PParamScore.Value := 1000; (*Random score value of 1000*)
  PParamNameAndSurname := IBCQuery.ParamByName('ParamNameAndSurname');
  PParamNameAndSurname.Value := 'Jacobus Opperman1';
  IBCQuery.Execute;
end;

[JacobusOpperman]

Hi VictorV!
Thank you so much for your thorough explanation! You have totally solved my problem.
Thank you for your help, I appreciate it!

[ViktorV]

Thank you for interest to our product.
Feel free to contact us if you have any further questions about our products.