MySQL Query issue with apostrophe

[macphreak]

I have a query that I is a long query and I also have a multiselect dropdown box that I am wanting to select several "ids" from.

Here is the query:

l.deleted = 0
    {{ cboCampaign.value ? " AND leads.campaign_id = " + cboCampaign.selectedItems[0].id : "" }} AND 

So, what I am doing here is wanting to check if the cboCampaign.value is empty or not. If it's not empty, then I want to embed the string " AND leads.campaign_id = " + cboCampaign.selectedItems[0].id. I have sql code before and after this string that continues on.

I have 2 issues going on with the above:

1. It is adding the apostrophe at the end of the line correctly but it is not adding in the apostrophe at the beginning. I tried forcing it and making it add both and that results in an error. How do I get this to correctly escape the string I want to add into the query?
2. I am unable to figure out how to get multiple values (since it is a multiselect dropdown). I was going to try something like cboCampaign.selectedItems.join() but then I just get a weird [Object][Object] back and the query failes with no explanation as to why.

Any ideas? Thanks!

[macphreak]

I have swapped out the multiselect for a more simple drop down single select just to make this one easier.

But, the main issue here is getting a tertiary operator to work in a sql query (well, mysql query).

It seems it just does not want to add in the the correct apostrophe when using a tertiary operator

Now, I have a more simplified statement with the tertiary operator:

{{ !cboCampaign.value ? "" : " AND l.campaign_id = " + cboCampaign.value}}

Which, returns this error if the cboCampaign value is empty:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''' AND l.date_entered BETWEEN '2025-03-16 05:00:00' AND '2025-03-19 19:0' at line 10

And it returns this error if I select a campaign from the dropdown:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' AND l.campaign_id = 50dd4bdc-69a6-dcd8-05ee-65de662d88d7' AND l.date_ente' at line 10

You'll notice that the campaign ID has the "end" apostrophe but not the apostrophe at the start of the sentence. It needs to be like this:

and l.campaign_id = '50dd4bdc-69a6-dcd8-05ee-65de662d88d7'

if I type the above in, the query runs fine. If I try and set this line dynamically, it error's out on me.

[macphreak]

I also looked into this post Querying MultiSelect Dropdown not returning data if no values selected - #4 by OmarShendy

But it was not what I was looking for

[Tess]

Hi @macphreak Thanks for joining office hours!

Generally, putting sql syntax inside double curly brackets {{}} can lead to prepared statements issues, as you're seeing. Ideally, we can refactor this to not have the sql syntax (like the and l.campaign_id = part) inside {{}}.

I'm surprised to hear the other ticket wasn't a good solution It is working well on my side. I have different data so I can't use the exact same query as you, but it seems quite similar:
select * from customers where revenue >250000 and ({{ cboCampaign.value.length===0 }} OR licenses IN ({{ cboCampaign.value.length == 0 ? [''] : cboCampaign.selectedItems.map(x=>x.value) }})) and customer_name like {{ '%a%' }}

Or, if you want to use a single select:

[macphreak]

So, I attempted the multiselect again. However, that didn't work. It brought back this for the generate MySQL Statement:

WHERE l.deleted = 0 and (true OR l.campaign_id IN ()) AND l.date_entered BETWEEN...

Which then, just brought back "0"'s across the board for the data of course.

I then switched over to just the regular dropdown select and that worked!!
I can just use that for now but it would be great to have a couple things logged as bugs for future fixes:

1. The multiselect drop down should work similar and not bring back blank campaign id's in the sql. It does seems like this could have been a bug with the state management insight instead? Not sure on this one.
2. It would be great if we didn't have to do these workarounds and the sql query area was updated to allow us to more easily use tertiary operators (especially for mysql calls )

Thanks for all your help!

[Tess]

Hi @macphreak,

Glad you have a path forward!

Do you know if you can consistently reproduce the issue described in 1? If so, can you share screenshots for a bug report? I haven't been able to reproduce this issue on our side yet.

For 2, I know we have a feature request on file for better error messaging, which should help with troubleshooting. The restrictions in place are due to our prepared statements setting, which is a security feature. If you disable the prepared statements setting on the resource, the original query (with l.deleted = 0 {{ cboCampaign.value ? " AND leads.campaign_id = " + cboCampaign.selectedItems[0].id : "" }} AND) should work as expected. That said, we do not advise disabling the prepared statements feature, as it also prevents SQL injection, exposing you to potential risk. If you find that you still want to disable it, we recommend creating two resources with the same credentials and only using the one with prepared statements disabled when you need to, to reduce exposure.