Using IN Operator Is Unsafe?

Wappler General How To
1

Wappler 5.3.1
NodeJS
MariaDB

Discovered a weird thing today.
Had a query built using the Query Builder with a condition saying column1 equals {{x}}.
When looking at the JSON code, {{x}} is replaced by :P1… indicating that it has been parameterized.

Then I changed the condition to column1 IN {{y}}, where y is an array.
Now in the JSON code, {{y}} is no longer parameterized. :P1 is gone… which means the binding will just be parsed like any other binding - which opens the query to SQL Injection.

Is my understanding correct? Or even if JSON does not show the binding as a parameter, it will still be parameterized in run-time?
Also, I don’t know if this a new change, or has been like this since day one.

Another interesting observation is that in custom query builder, until few months ago, IN operator could not be used when its operand value was passed as parameter. But then it started working with some update. So, if my above understanding is correct, regular query builder can use IN conditional binding as a parameter, and current setup is a bug.

Lastly, if this is a bug and will be fixed, please let the community know if its going be a breaking change.

3

The query is dynamically build on the server, it is not the exact query you see in the SQL tab. The values for the IN operator will be parameterized. You can check it when enabling the Debug on the query, it will then output the query it generated on the server.

4

This is the debug output.
I see params array is empty, but there is a bindings array at the end. Not sure if this is ok.

{"productarray":["PRODUCT1","PRODUCT2","PRODUCT3","PRODUCT4"],"query":{"options":{"connection":"db","sql":{"type":"select","columns":[{"table":"content_page_creation","column":"cpc_id"},{"table":"content_page_creation","column":"cpc_product_code"}],"params":[],"table":{"name":"content_page_creation"},"primary":"cpc_id","joins":[],"wheres":{"condition":"AND","rules":[{"id":"content_page_creation.cpc_product_code","field":"content_page_creation.cpc_product_code","type":"string","operator":"in","value":["PRODUCT1","PRODUCT2","PRODUCT3","PRODUCT4"],"data":{"table":"content_page_creation","column":"cpc_product_code","type":"text","columnObj":{"type":"string","default":"NULL","maxLength":45,"primary":false,"nullable":false,"name":"cpc_product_code"}},"operation":"IN","table":"content_page_creation"}],"conditional":null,"valid":true},"query":"SELECT cpc_id, cpc_product_code\nFROM content_page_creation\nWHERE cpc_product_code IN (PRODUCT1,PRODUCT2,PRODUCT3,PRODUCT4)","sort":null,"dir":"asc"},"test":true},"query":{"sql":"select `cpc_id`, `cpc_product_code` from `content_page_creation` where `content_page_creation`.`cpc_product_code` in (?, ?, ?, ?)","bindings":["PRODUCT1","PRODUCT2","PRODUCT3","PRODUCT4"]}}}
5

The generated sql query is:

select `cpc_id`, `cpc_product_code` from `content_page_creation` where `content_page_creation`.`cpc_product_code` in (?, ?, ?, ?)

The bindings (parameter values):

["PRODUCT1","PRODUCT2","PRODUCT3","PRODUCT4"]
6

Looks safe then. Thanks. :slight_smile: