I'm trying to write a prepared statement with placeholder syntax as explained here, but my placeholder seems to be returning unexpected things which breaks the query.
I'm using a select input to pick a table name and that table name should then be placed in a simple 'SELECT *' sql query. The problem is that my placeholder seems to be returning a colon as first character instead of simply the string of the selected value. When I hover over {{select2.selectedItem}} I see the correct string in the little light green dialog box, but the error message clearly complains about the colon at position 53, which is the starting position of the placeholder:
but I thought retool would resolve that into something that makes sense when used in other scripts since my other select inputs work fine and as expected. (The other select input values feed into components or other js scripts, not sql queries.)
Can you provide a screenshot of the state of the Select component with the selectedItem property expanded? Usually these take the form of an object and you call out the item's properties but I am not sure how the component is setup for you.
If I were to call upon {{select1.selectedItem}} I would get an [object Object] back:
If I call upon {{select1.selectedItem.pay_group_name}} I would get the appropriate string:
ETA: you might also find the string by just calling upon the component's selectedLabel property. It seems to pull the default Value if it (the Label property) isn't defined in the mapped options settings of the Select component.
I'm not quite sure, it seems like it should be returning the string properly (based on the values you see in the preview -- but those can sometimes not tell the whole story).
Seeing the JS query returning : "FINANCIAL_PROFILE" makes it seem like it is still processing the value as an object, but somehow it is an object with no key and just a value? This thread might need the bug tag added
Did trying select2.selectedLabel have any effect on the return value in your JS query?
I think help is on the way, but just keeping the thread lively because I'm interested in knowing if this behavior is intended in certain cases (and if not how to avoid it).
Unfortunately, SQL doesn't support dynamic table names with prepared statements.
One work around is to disable prepared statements, but it's important to keep in mind that would put you at risk of an Insertion Attack. Another option could be to write out all the possible queries you'd like to run and select them based off of the value selected from your drop down.
Hope that helps you move forward! Thank you @pyrrho for the help!
Oh I see! I thought retool internally was converting the select2.selectedItem value into a string but that's right, that would lead to possible sql injection attacks, so well done retool for using prepared statements instead. I'll keep things safe and keep the prepared statements! Thanks so much for your help!
