Using like queries in query template with dynamic search values

[Former Member]

Hi,

Following is my query syntax to search the items in Oracle data base

select * from xyz where material like '%' || search value|| '%'.

But I am not able to write the search query in query template using [Param.1] to dynamically pass the search value.

I have tried the following syntax which is throwing execptions.

select * from xyz where material like '%' & [Param.1] & '%'.

Any help in this regard is very much appreciated.

Thank you,

Kola

[Former Member · Accepted solution]

Hi Rajendra,

Try this : select * from xyz where material like '%[Param.1]%'

This will work.

Thanks,

Sushma.

<<Request for points removed>>

Message was edited by:

Abesh Bhattacharjee

[jcgood25 · Accepted solution]

The token replacement done by the query object is very simple, [Param.1] will get replaced in the query string (up to and including the square brackets), which is why you don't need to do any string concatination or manipulation - the server side does all of this for you. The same is true for the date tokens: [SD] and [ED]

Sushma's answer will do the trick, but depending upon your requirements you might consider not putting the % symbols in the query itself and allowing the user to provide these. If the user is providing Param.1 from an html form input he cannot control the query search for records specifically starting with or ending with the substring - in your example he will get every match from beginning, middle, or end.

Regards,

Jeremy

[Former Member]

Hi,

My search query is working fine.

I really appreciate both of you for quick response.

[sufw]

Hi Rajendra,

just be careful to avoid SQL injection issues - you will want to 'sanitise' the user input to avoid a user passing in something like this into Param.1:

'; DROP TABLE xyz; select * from xyz where material like '

which would result in the following SQL being executed on the server:

select * from xyz where material like '%'; DROP TABLE xyz; select * from xyz where material like '%'

One way of getting around this is to remove all non-alphanumeric characters from the user input, but there's other options.