- I need to use user variable in
preparestatement of mysql stored procedure. (The purpose is to substitute the table name in
drop tablecommand, which is unable to be injected via
?and passed via
execute using...statement because it is not a data element).
- I suppose the user variables are session-wide global variables.
- I suppose the stored procedure accessing the user variable must be synchronized to protect against unwanted behaviour when it is called simultaneously more times within single session (which I cannot prevent).
How to perform such synchronization?
Is there any chance it is performed internally by the mysql?
It seems like mysql
get_lock() & co. uses logic that does not help much:
get_lock('a') followed by
get_lock('b') destroys state of a. Maybe I have terribly missed some point here...
For those who would ask the "what exactly would you like to do" question:
drop procedure if exists drop_t_table; delimiter $$ create procedure drop_t_table(in in_t_table_name varchar(128)) begin declare sql_drop varchar(256) default 'drop temporary table if exists '; -- -- I would suspect sql_drop_table user variable guard should be locked here... -- set @sql_drop_table = concat(sql_drop, in_t_table_name); -- -- What if the procedure is preempted to another call here -- and the sql_drop_table gets different table name? -- prepare exe from @sql_drop_table; -- -- ...and unlocked here -- execute exe; deallocate prepare exe; end$$ delimiter ;