Insert field name in Delphi SQL parameter instead of string

Question

I have written an SQL query in delphi that has a parameter

WHERE L1.IdListino = :IdListino

Based on some input from the user, I want this parameter to be either a field of a form (tParsIdListinoExport is the name of the field), or a column of another table (something like WHERE L1.IdListino = fat.IdListino).

if tParsIdListinoExport.AsString <> '' then
        qSel.ParamByName( 'IdListino' ).AsString := tParsIdListinoExport.AsString
    else
        qSel.ParamByName( 'IdListino' ).Value := 'fat.IdListino';
end;

Sadly, looks like I can't insert a column name as a parameter, because it adds the '' around the name of the column, thus treating it as plain text. Is it possible to remove the '' from the parameter? Thank you very much,

Fabio

That's not how parameters work. If you need to do string replacement for field names, use Format. — Ken White
– Ken White, Commented Mar 13, 2017 at 17:05
What @KenWhite says. Generally, In SQL queries, parameters are placeholders for values in columns, not placeholders for entity-names, like columns, tables, views etc. — MartynA
– MartynA, Commented Mar 13, 2017 at 17:23
As Ken states... that is not how parameters work. If the client has a value to use then a parameter is the best way to comunicate it to the server. Parameter values are not burnt into the SQL code, but are passed separately. If you want the server to join and use a value from another table, then you must build a correct SQL statement (without parameters). — Frazz
– Frazz, Commented Mar 14, 2017 at 8:34

Rafael Rossi · Accepted Answer · 2017-03-14 13:28:58Z

3

You need create your SQL Instruction in runtime, like:

with qSel do
begin
    Close;
    SQL.Clear;
    SQL.Add(addYourSqlHere, without Where clause);
    if Condition1 then
        SQL.Add('where FIELD1 = :PARAM01')
    else
        SQL.Add('where FIELD2 = :PARAM01');
    ParamByName('PARAM01').Value := UserFilter;
end;

edited Mar 14, 2017 at 13:28

answered Mar 13, 2017 at 18:23

Rafael Rossi

881 silver badge8 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Sam M Over a year ago

You should change UserFilter to be a parameter to avoid sql injection.

Hugh Jones · Accepted Answer · 2017-03-14 14:12:17Z

0

You might be able to achieve what you need with sql. The details are a little dependent upon the RDBMS but something along the lines of

where (:param1 = 'use_field' and :param2 = OtherTable.field) or (:param1 = 'use_param' and Table.field = :param3)

This assumes that Table and OtherTable are joined. It also assumes Param1 can be mentioned more than once - not all databases will allow this.

answered Mar 14, 2017 at 14:12

Hugh Jones

2,7641 gold badge20 silver badges34 bronze badges

Collectives™ on Stack Overflow

Insert field name in Delphi SQL parameter instead of string

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related