SQL Like sentence only works 1 time on ADS12

[Former Member]

Hi all,

on ADS12 doesn't works the SQL "Like" sentence, well to be correct, only works the first time you invoke it.

From Delphi XE8,

I have an EditText, a DbGrid and a Button to search in the Country.ADT table from ADTDemoData in a Remote Server.

The button OnClick procedure is that:

procedure TForm5.Button1Click(Sender: TObject);

begin

  AdsQuery1.Close;

  AdsQuery1.SQL.Clear;

  AdsQuery1.SQL.Add('SELECT * FROM COUNTRY WHERE NAME LIKE :paramvalue');

  AdsQuery1.Params[0].Value  := '%'+Edit1.Text+'%';

  AdsQuery1.Open;

end;

When I press the button "Search", looking for countries with "en" in their name, the DbGrid shows "Argentina" and "Venezuela". CORRECT!!!!

But, if I repeat the search, looking for countries with "ol" in their name, the DbGrid shows again "Argentina" and "Venezuela".

In fact the search won't works anymore.

I test it in my old server with ADS7 and works fine.

I test it with .DBF files and doesn't works.

Why the SELECT LIKE sentence only works 1 time?

Does it changed something in the way I have to invoke the SELECT LIKE sentence?

I need help.

Thanks a lot

Cheers from Barcelona

Eugeni.

[Former Member · Accepted solution]

Hi Eugeni,

The issue seems to be in a way we handle parameters with LIKE clause in SQL query in ADS 12. It works fine till ADS 11.1.

We will fix this in an upcoming SP, as soon as possible.

Meanwhile, can you please consider using a workaround?

begin

  AdsQuery1.Close;

  AdsQuery1.SQL.Clear;

  AdsQuery1.SQL.Add('SELECT * FROM COUNTRY WHERE NAME LIKE :paramvalue');

AdsQuery1.SQL.Text := 'SELECT * FROM COUNTRY WHERE NAME LIKE  ''%' + Edit1.Text + '%'' ';

  AdsQuery1.Params[0].Value  := '%'+Edit1.Text+'%';

  AdsQuery1.Open;

end;

Let me know if this works.

Thanks,

Parag

[Former Member · Accepted solution]

Thanks Parag

it works with this workaround,

also works changing LIKE for CONTAINS(country, '%'+Edit1.Text+'%')

and also works passing parameter to CONTAINS like this:

AdsQuery1.SQL.Add('SELECT * FROM COUNTRY WHERE CONTAINS(country, : paramvalue)');

AdsQuery1.Params[0].Value:='%'+Edit1.Text+'%';

But this solution involves changing all code in my applications using LIKE.

I hope you release the Service Pack as soon as possible.

Best regards

Eugeni

[joachim_drr · Accepted solution]

<pre>

AdsQuery1.SQL.Text := 'SELECT * FROM COUNTRY WHERE NAME LIKE  ''%' + Edit1.Text + '%'' ';

</pre>

!!!!!!!!!!! NEVER DO THAT !!!!!!!!!

This makes your application vulnerable for SQL injections.

[Former Member · Accepted solution]

You are right Joachim!!

And also my solution using CONTAINS, works but not allways, because CONTAINS only search whole words.

Thanks Joachim, I hope SAP releases the SP for ADS12 asap

Eugeni

[Former Member · Accepted solution]

Any solution?

I opened other thread with the same question in here

[Former Member · Accepted solution]

Hi Amadis,

no, I don't have a solution, it still works bad.

But I made some changes in my code to solve that situation:

procedure TForm5.Button1Click(Sender: TObject);

begin

  AdsQuery1.Close;

  AdsQuery1.SQL.Clear;

  AdsQuery1.SQL.Add('SELECT * FROM COUNTRY WHERE NAME LIKE ''%'+Edit1.Text+'%'' ');

  AdsQuery1.Open;

end;

Don't use the parameter, concatenate the Edit1.Text with the SELECT sentence.

This is not a good solution, it works, but you are in danger of injection sql.

Use with your own risk.

Cheers

Eugeni

[Former Member · Accepted solution]

Hi Eugeni, thanks for your answer. I'm awaiting  SP. Meanwhile in this case, for this project I'm doing, I ships the project with the latest ADS 11 dll and everithing works well, and when get the Service pack I will replace for the 12 dlls.

Joachim Dürr

Says using the edit1.text without the parameter will create vulnerabilities.

So I fixed in this case with the old dlls to ship

[Former Member]

Is there an FTS index on Name?

If so, maybe "ol"  is a noise word and cannot be searched for. In that case you might not see the exception maybe?

Did you try longer words?

[Former Member]

Hi Stephan,

in your example table COUNTRY.ADT there are only a PRIMARY index. None FTS index.

If I use that table in LOCAL mode, not in REMOTE SERVER, it works fine.

I try with longer words with the same result, doesn't works. It has nothing to do with the searched word.

I try with other table and different table types, like .DBF with the same result, doesn't works.

I try with my old ADS7 server and works perfectly, in fact, all my software worked fine until I upgraded to ADS12.

Did you test my sample code? It works with your ADS12 server?

Thanks

Eugeni

[HakanHaslaman]

Eugen,

I forward your request to the engineering, so they will check this behavior.

[Former Member]

Thanks Hakan

I forgot to say you that ADS12 is a purchased Linux 64bits final version 250 users.

I'm waiting for their answer.

Cheers

[HakanHaslaman]

Thanks for this additional information!