1

Can i call a mysql stored function from another mysql stored function or stored procedure, passing a string to the first function which is the name of the second function to call. I.e choosing the the second function via a value in field.

I suspect I can't and I will have to use a case statement, but I have to ask :)

Something like this?

CREATE FUNCTION `string_processing_chain` (function_name    VARCHAR(1000),
                                            data_to_process VARCHAR(1000))
RETURNS VARCHAR(1000)
BEGIN

RETURN ($function_name)(to_process);
END
0

I have never heard of something like this and I have never attempted before

Here is my best guess (using Dynamic SQL)

CREATE FUNCTION `string_processing_chain` (function_name    VARCHAR(1000),
                                        data_to_process VARCHAR(1000))
RETURNS VARCHAR(1000)
BEGIN

    DECLARE rv VARCHAR(1000);

    @sql = CONCAT('SET rv = ',function_name,'(',data_to_process,')');
    PREPARE s FROM @sql;
    EXECUTE s;
    DEALLOCATE PREPARE s;

    RETURN rv ;
END

or

CREATE FUNCTION `string_processing_chain` (function_name    VARCHAR(1000),
                                        data_to_process VARCHAR(1000))
RETURNS VARCHAR(1000)
BEGIN

    SET @rv = '';

    @sql = CONCAT('SET @rv = ',function_name,'(',data_to_process,')');
    PREPARE s FROM @sql;
    EXECUTE s;
    DEALLOCATE PREPARE s;

    RETURN @rv ;
END

Give it a Try and tell us all if it worked, please !!!


From OP I used this :

CREATE PROCEDURE `string_processing_chain`( 
    function_name   VARCHAR(1000),
    data_to_process VARCHAR(1000) )
BEGIN
    SET @sql2 = CONCAT('CALL ',function_name,'(?)');
    SELECT @sql2;
    PREPARE s FROM @sql2;
    SET @a = data_to_process;
    EXECUTE s USING @a;
    DEALLOCATE PREPARE s;
END
|improve this answer|||||
  • I think I tried that earlier and got an error that you can't execute a prepared statement from a function. Something along those lines. It does work calling a procedure this way from a procedure. – Conrad Jones Dec 28 '14 at 0:37
  • I was able to use a procedure, and added what I used to your answer. Thanks. – Conrad Jones Dec 28 '14 at 0:44
  • Glad I could help – RolandoMySQLDBA Dec 28 '14 at 2:46
0

You can, with a stored procedure, as you have figured out by now... but you should not. It's a dreadful idea in principle, and potentially a massive security vulnerability.

Data... and object identifiers... should never be interchangeable like this. Essentially the whole point of prepared statements and positional placeholders is to prevent this kind of blurring of that line.

Data input to a stored procedure should never be concatenated into a prepared statement.

There is never a valid justification for this, because there is always a better and safer way. "Just this one time, it should be fine" is how security gets breached. ("But I sanitized my inputs..." is also insufficient.)

While choosing a function with a case statement or expression seems awkward, if choosing a function based on input values is really what you need, then that's really the only safe way to go.

|improve this answer|||||
  • That's something to bear in mind, and I may move back to using a case statement in the end, but my question was how to do it, not whether it was safe or not. If i was to move back to a case statement, developing it using this method is preferable for testing the individual processing functions and then to construct the case statement once all individual processing functions are written. – Conrad Jones Dec 30 '14 at 9:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.