How do I make a "WHERE id IN (param)" with MyDAC ?

[dormky 1]

I'd like to run this query :

 

SELECT * FROM users WHERE id IN (:param)

 

To get a list of the users I need. But I can't figure out how to pass an array of values to a TMyQuery object. How is this supposed to be done ?

 

Thanks !

[Anders Melander 1639]

I don't think you can use a parameterized query with a list of values.

https://www.google.com/search?q=delphi+query+array+parameters

 

But you can try to ask here:

https://support.devart.com/portal/en/community/delphi-data-access-components

[Kas Ob. 91]

40 minutes ago, dormky said:

How is this supposed to be done ?

Refer to the documentation https://docs.devart.com/mydac/modify_data.htm

[Serge_G 85]

MyDac can use macro https://docs.devart.com/mydac/work_macros.htm

Something like this code (sorry, I don't use Mydac but Firedac)

Query1.SQL.Text := 'SELECT * FROM user Where ID IN (&list)';
Query1.MacroByName('list').asString := '1,2,3';
Query1.Open;

 

Otherwise, you can use a format string

sqlstring:='SELECT * FROM users WHERE id IN (%s)';
inlist := '1,2,3'
query.sql.text:=Format(sqlstring,[inlist]);

Ok, it's really a very poor code !

[dormky 1]

I'm not sure these answers will handle SQL sanitization properly, is there data on that ?

And in any case who builds an sql accessor without giving array types lol

[Kas Ob. 91]

2 hours ago, dormky said:

I'm not sure these answers will handle SQL sanitization properly, is there data on that ?

I had MyDac since 2009 and it is documented and shown in the demos, same goes for UniDac.

 

q.SQL.Add('SELECT * FROM users WHERE id IN :param');
.....
    q.ParamByName('param').AsString := 'Iam doing here what ever i want, including a useless try to inject SQL with select * from user or even delete * from users; that will not be ran as SQL !!!';

As shown in my screenshot from that link.