BuildSafe_InClause to avoid SQL injection.
Question

I have a input variable CVSNumberlist  from datatypea 'text' with this input value: " 457033,457031'

I have a local variable TempCSVNumberList from datatype 'IntegerLiteral list'

In a action I do a sprinsplit of the CSVNumberList

The TempCSVNumberList = springsplitCVSNumberList by mapping text to IntegerLiteral (TextToLongInteger(Text.Value)).

Then I use the BuildSafe_InclauseIntegerList with the tempCSVNumberList as ValueList

In my SQL query I have this in where_clause

@RequestIdList = '0' or {AFFILIATION_FORM}.[RequestId] in (@RequestIdList)

The income parameter @RequestIdList- is set to BuildSafe_InClauseInwtegerList.Ouput. 

This works as long theCVSNumberlist exist of none or one value. 

From the moment I use  a comma-separated list like "457033,457031" , I get the error: 'An expression of non-boolean type specified in a context where a condition is expected, near ','.

What do I wrong ????

Knipsel.PNG

I found the solution by my self. The where-clause is not correct because the left side of the or is treated as inline parameter and the other side not.

The where clause must be this:

(0 in (@RequestIdList) or {AFFILIATION_FORM}.[RequestId] in (@RequestIdList))
mvp_badge
MVP

That's indeed one solution. I usally add an extra boolean parameter to indicate whether the expand inline parameter is present, something like:

@HasList or {MyEntity}.[Id] IN (@MyIdList)

Also note that in your example, @RequestIdList must never be completely empty, as the SQL will be rendered as "IN ()" which will fail. I typically add a ",0" to the end of the text (or a "0," at the start), but in that case your "0 IN (@RequestList)" wouldn't work (or rather, it would always be true).

Do your variable 

@RequestIdList 

is a "expand Inline"? 

If not the 

{AFFILIATION_FORM}.[RequestId] in (@RequestIdList)

will not work.

If is "expand inline" 

@RequestIdList = '0'

will not work , your query condition will look like 

457033,457031 = '0'

with no quotes in the first part. 

Regards 

Graça

Hi Danny,

Have you set SQL widget parameter property Expand Inline as "Yes". Below is the screen -


Thanks

Vinod

I found the solution by my self. The where-clause is not correct because the left side of the or is treated as inline parameter and the other side not.

The where clause must be this:

(0 in (@RequestIdList) or {AFFILIATION_FORM}.[RequestId] in (@RequestIdList))
mvp_badge
MVP

That's indeed one solution. I usally add an extra boolean parameter to indicate whether the expand inline parameter is present, something like:

@HasList or {MyEntity}.[Id] IN (@MyIdList)

Also note that in your example, @RequestIdList must never be completely empty, as the SQL will be rendered as "IN ()" which will fail. I typically add a ",0" to the end of the text (or a "0," at the start), but in that case your "0 IN (@RequestList)" wouldn't work (or rather, it would always be true).

Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.