MySQL, reference textInput.value for table name

Hi,

I want to enable the user to create a stored procedure with a custom name but I cannot figure out how to remove the ' ' that come up when referencing {{textInput1.value}}

This is the code:
DELIMITER //
CREATE PROCEDURE {{ textInput1.value }} ()
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT 'hello'
BEGIN
truncate ls_ppc_daterange_comparison_tanguy;
INSERT IGNORE INTO ls_ppc_daterange_comparison_tanguy (country, short_sku, campaignname,campaignid, adgroupname, adgroupid, keywordid, matchtype, keyword )
SELECT country, short_sku, campaignname,campaignid, adgroupname, adgroupid, keywordid, matchtype, keyword
FROM ads_sp_report_targeting_v3_all
WHERE date >= {{dateRange5.value.start}} AND date <= {{dateRange5.value.end}}
GROUP BY keywordid;

But I get the error:
"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 'DELIMITER // CREATE PROCEDURE 'tanguy_test' () LANGUAGE SQL NOT DETERMINISTI' at line 4"

Any ideas?!

Thank you!

Tanguy

1 Like

hey @Tanguy if you take out the back ticks ` it should wrap in single quotes, does it work then or did you already try that?

there's also an option for converting queries into prepared statements with JS interpolation called "Disable converting queries to prepared statements", located in the Resources > at the bottom.

Hi @trz-justin-dev ,

I need the back ticks `` in order for the name to be passed to create the stored procedure. The issue is that {{textInput1.value}} returns like this 'tanguy_test'() instead of tanguy_test().

I'm not too keen on disabling the prepared statement option due to security risk and having to update hundreds of queries to add ' quotation marks across all the apps. I've test it and it works but I'm worried about the security implications, which is why ideally I'd like another solution if it exists :innocent: :thinking:

Do you or anyone else have any other suggestions?

Thank you :pray:

Try writing the entire statement as a JS query and using an EXEC call to execute it.

return "CREATE PROCEDURE " + {{ textInput1.value }} + "()"

...should get you the statement the way you want it.

2 Likes