Delphi: how to pass a list as a parameter to a SQL query?

Asked 10/4, 2012 at 15:35 Answered 2/2, 2017 at 19:3

I have a list of integers or of strings and need to pass it as a parameter for a Delphi DataSet. How to do it?

Here is an example. MyQuery is something like:

select * from myTable where intKey in :listParam

I'd set a parameter as a list or array or something else:

MyQuery.ParamByName('listParam').AsSomething := [1,2,3];

and it would result in this query sent to the sql server:

select * from myTable where intKey in (1, 2, 3)

It would be even better if the solution would also work with strings, making this query:

select * from myTable where stringKey in :listParam

become:

select * from myTable where stringKey in ('a', 'b', 'c')

I believe this is a simple question, but "IN" isn't a good keyword for searching the web.

Please answer how I should configure the parameter in the IDE, the query and how to pass the parameters.

I'm using Delphi 7.

Edited: I'm considering the answer is "it isn't possible to do directly". If someone give me a non-hackish answer, the accepted answer will be changed.

Flowery answered 10/4, 2012 at 15:35 Comment(4)

You can't, unfortunately. It's a deficiency in the SQL language: it doesn't have any concept of "a list type". – Sideling 10/4, 2012 at 16:19

There might be some options for you depending on the DBMS you use. What are you using? SQL Server, Oracle, .... ? – Philosophy 10/4, 2012 at 16:37

@MikaelEriksson: I'm using Sql Server, but I believe it is a Delphi language issue. – Flowery 10/4, 2012 at 17:8

It's not a Delphi issue, but an SQL one. See this answer to a similar question for more information, and some possible workarounds. – Idolatrize 10/4, 2012 at 18:3

AFAIK, it is not possible directly.

You'll have to convert the list into a SQL list in plain text.

For instance:

function ListToText(const Args: array of string): string; overload;
var i: integer;
begin
  result := '(';
  for i := 0 to high(Args) do 
    result := result+QuotedStr(Args[i])+',';
  result[length(result)] := ')';
end;


function ListToText(const Args: array of integer): string; overload;
var i: integer;
begin
  result := '(';
  for i := 0 to high(Args) do 
    result := result+IntToStr(Args[i])+',';
  result[length(result)] := ')';
end;

To be used as such:

SQL.Text := 'select * from myTable where intKey in '+ListToText([1,2,3]);
SQL.Text := 'select * from myTable where stringKey in '+ListToText(['a','b','c']);

Circumpolar answered 10/4, 2012 at 15:40 Comment(10)

Sure, this is my current solution, but I want to pass it as a parameter instead of changing the SQL property. Concatenating won't let the DBMS prepare the query and allow for sql injection attacks. – Flowery 10/4, 2012 at 17:8

Parameterization is still possible (although you do have to update the SQL property). See my long-winded answer below. – Borax 10/4, 2012 at 18:40

@Flowery The two functions above won't allow SQL injection attacks, since the first will "quote" the supplied text, and the 2nd will create values from integers. With modern databases (at least with Oracle, the one I know), it won't be slow - just ensure you have a proper index on the key. Internally, the DB will prepare the SQL statement and reuse the whole "in ()" expression as one parameter in its execution plan. It is SQL: code what you want, not how you want the DB to retrieve it. – Circumpolar 10/4, 2012 at 19:33

@LarryLustig It is SQL: code what you want, not how you want the DB to retrieve it. Using such a "growing statement" or a temporary table is not a better alternative IMHO (unless timing on true data shows the contrary). A modern DB is already preparing the statements on the Client side, even with no explicit parameter list. On production, you won't see any difference, I suspect. And by the way, I think that the two Delphi functions are not prone to SQL injection. – Circumpolar 10/4, 2012 at 19:37

I think you have a bug? Your parser will add ',' to the last item from array always? – Falkirk 10/4, 2012 at 20:20

@neves, from where are you expecting a SQL injection attack? From your own provided list? – Falkirk 10/4, 2012 at 20:22

@Falkirk Take a look at the next source code line: it will replace the latest ',' with a ')'. Just as expected. ;) – Circumpolar 11/4, 2012 at 7:47

Ohh, sure. ha. My bad. I would try using this as a parameter after that parsing. Like: MyQuery.ParamByName('listParam').AsString := ListToText(['a','b','c']); – Falkirk 11/4, 2012 at 13:53

@Falkirk I suspect it won't work. From the SQL point of view, the "intKey in (...)" statement expect the "(...)" expression to be plain text, or a set of parameters like "(?,?,?)". You can't replace a "(...)" by a string parameter. And IMHO it won't be worth it (I suspect there won't be any performance change). – Circumpolar 11/4, 2012 at 15:34

yes, I'm not sure too. Maybe it could depend on DBMS too. But I would try it anyway, just to be sure. :) – Falkirk 11/4, 2012 at 16:52

SQL accepts only single values as parameters so you cannot create a statement with one parameter that can map to a variable number of values, such as the example you gave.

However, you can still use parameterized SQL in this situation. The solution is to iterate over the list of values you have, adding a parameter marker to the SQL and a parameter to the parameter list for each value.

This is easiest to do with positional rather than named parameters but can be adapted for named parameters as well (you may need to adjust this code since I don't have Delphi available and don't remember the Parameter creation syntax):

 //AValues is an array of variant values
 //SQLCommand is some TDataSet component with Parameters.
 for I := Low(AValues) to High(AValues) do
 begin

    if ParamString = '' then
       ParamString = '?'
    else
      ParamString = ParamString + ', ?';

    SQLCommand.Parameters.Add(AValues[I]);

  end

  SQLCommand.CommandText = 
     'SELECT * FROM MyTable WHERE KeyValue IN (' + ParamString + ')';

This will produce an injection-safe parameterized query.

Borax answered 10/4, 2012 at 18:38 Comment(1)

This is the first time I saw someone using Parameters.Add. Won't assigning CommandText override them? – Far 24/7, 2017 at 5:36

There are several options for you but basically you need to get your values into a table. I would suggest a table variable for that.

Here is a version that unpacks an int list.

declare @IDs varchar(max)
set @IDs = :listParam

set @IDs = @IDs+','

declare @T table(ID int primary key)

while len(@IDs) > 1
begin
  insert into @T(ID) values (left(@IDs, charindex(',', @IDs)-1))
  set @IDs = stuff(@IDs, 1, charindex(',', @IDs), '')
end

select *
from myTable
where intKey in (select ID from @T)

It is possible to have multi-statement queries. The parameter :listParam should be a string:

MyQuery.ParamByName('listParam').AsString := '1,2,3';

You can use the same technique for strings. You just need to change the data type of ID to for instance varchar(10).

Instead of unpacking with a while loop you could make use of a split function

declare @T table(ID varchar(10))

insert into @T 
select s
from dbo.Split(',', :listParam)

select *
from myTable
where  charKey in (select ID from @T)

A string param could look like this:

MyQuery.ParamByName('listParam').AsString := 'Adam,Bertil,Caesar';

Philosophy answered 10/4, 2012 at 19:8 Comment(3)

... and it won't work if one of your string will have a ',' character within its content (which is likely)... – Circumpolar 11/4, 2012 at 15:36

@ArnaudBouchez - The comma is not a requirement for this solution. It can be any character of your choice. You can even add the delimiter as a parameter of its own. Any way if you have very diverse data where you can't figure out a delimiter you can resort to setup a XML string instead that you shred to a table in TSQL code. – Philosophy 11/4, 2012 at 15:45

@ArnaudBouchez - Would it be of interest to you if I add the XML version as well? – Philosophy 11/4, 2012 at 15:46

Create a temporary table and insert your values in it. Then use that table as part of a subquery.

For example, create MyListTable in your database. Insert your values into MyListTable. Then do

select * from myTable where keyvalue in (select keyvalue from MyListTable)

This avoids SQL injection attacks. But it's not elegant, is not performance friendly because you have to insert records before running your query, and can lead to concurrency issues.

Not my first choice to deal with your situation but it addresses your concern about sql injection.

Vertigo answered 10/4, 2012 at 18:19 Comment(0)

If someone still having the same problem, if you are using firedac you can use macros like this:

Query -> "select * from myTable where intKey in (&listParam)"

Setting the macro -> MyQuery.MacroByName('listParam').AsRaw := '1, 2, 3';

Radar answered 2/2, 2017 at 19:3 Comment(0)

I use some "IN" replacement. Here is the query I use:

SELECT * FROM MyTable WHERE CHARINDEX(','+cast(intKey as varchar(10))+',', :listParam) > 0

the code to send parameter:

MyQuery.ParamByName('listParam').AsString := ',1,2,3,';

The array item value can partially match some other values. For instance, "1" can be part of "100". To protect against it, I use comma as delimiter

Fablan answered 10/4, 2012 at 18:25 Comment(0)

Why not make a dynamic sql:

Quick and dirty, but still using parameters. check 10 elements. I don't know how well this scales.

    MyQuerySQL.Text:='SELECT * FROM myTable WHERE intKey in (:listParam0'
    for i := 1 to 9 do begin
      MyQuerySQL.Text := MyQuerySQL.Text + ',:listParam'+IntToStr(i)
    end;
    MyQuerySQL.Text := MyQuerySQL.Text+')';
    for i:=0 to 9 do begin
      MyQuery.ParamByName('listParam'+IntToStr(i)).AsInteger := ArrayofInt[0];
    end;

Inebriety answered 11/4, 2012 at 7:20 Comment(0)

Hot tags

Godot Unity Godot Help Programming Godot 4.X GUI GDScript 3D 2D Physics CSharp Godot 3.X VR XR Projects C++

Recommended topics

Hot tags