Error: QueryAll SQLite3 can only bind numbers, strings, bigints, buffers, and null

[kfawcett]

Wappler Version: 6.1.1 Stable
Operating System: Windows 11
Server Model: Capacitor
Database Type: SQLite
Hosting Type:

Expected behavior

Using a json array field as a custom query condition should work with parameters.

Actual behavior

I have a json array field and I’m trying to use it in the database query condition, but I’m getting this error.

Error invoking remote method 'CapacitorSQLite-query': Error: Query: Error: SelectSQL: Error: QueryAll SQLite3 can only bind numbers, strings, bigints, buffers, and null

If I hardcode the value then it works.

select `Subject` 
from `Email`, json_each(`Email`.`GmailLabelIds`) 
where json_each.value = 'INBOX' 

If I use a parameter then it throws an error.

select `Subject` 
from `Email`, json_each(`Email`.`GmailLabelIds`) 
where json_each.value = ? 

[scalaris]

Try building that element of the custom query in a string in the server connect and embedding it in the custom query

SC:
set value _JSON_eachSQL

CQ:
where {{_JSON_eachSQL}}

(NOTE: Be aware of the security risks and code accordingly)

[kfawcett]

Thanks @scalaris. I’ll give that a try. This is all client-side with a local SQLite db, so no security concerns.

[scalaris]

Good luck.

The security risk comment was purely to fend off the SQL injection comments interfering with the thread

[kfawcett]

It doesn’t look like App Connect Capacitor can handle an expression in the query. I created a variable and then attempted to put the variable in the query, but it throws an error at runtime.

select `Subject`
from `Email`, json_each(`Email`.`GmailLabelIds`) 
where {{EmailsGmailLabels}} 
order by `GmailInternalDate` DESC

Error: Error invoking remote method 'CapacitorSQLite-query': 
Error: Query: 
Error: SelectSQL: 
Error: QueryAll no such column: object Object

[George]

No you can’t have complete sql statements as parameters, just values. This is for increased security.

[kfawcett]

ok, then I think this is a defect. Going to change this into a bug report.

[kfawcett]

Good day, @patrick. Curious if this is actually a defect or something I might be doing incorrectly.

[patrick]

It is indeed a bug.

Please test if the following update fixes the issue: dmxCapacitorSQLite.js.zip (10.4 KB)

[scalaris]

Would it not be {{EmailsGmailLabels.value}}

[kfawcett]

Thanks @patrick! Your fix worked.

[Teodor]

Fixed in Wappler 6.1.3