MySQL Stored Procedure, Execute SQL Taken from Table using PREPARE/EXECUTE

Question

I'm having trouble using PREPARE and EXECUTE as I'm doing something a little odd - executing a pre-prepared SQL statement taken from a database (rather than building it in the Stored Procedure).

I saw the question MySQL :: Run SQL statement within Variable and this one My SQL Dynamic query execute and get ouput into a variable in stored procedure and from these gathered that I cannot use a SP DECLARED variable in the prepare statement (MySQL WorkBench gives an error as soon as I try) and so have to use a variable with 'AT symbol' (not sure what this means?). So it is I have been trying to get the following to work:

DECLARE sqlUpdateSp TEXT;

IF (SELECT EXISTS (SELECT updateSql FROM sys_instantUpdates WHERE expiryDate >= NOW() AND securityToken = securityTokenIn AND updateType = updateTypeIn)) THEN
    SELECT updateSql INTO sqlUpdateSp FROM sys_instantUpdates WHERE expiryDate >= NOW() AND securityToken = securityTokenIn AND updateType = updateTypeIn;  
    SET @phlanx = sqlUpdateSp;
    PREPARE sqlToExecute FROM @phlanx;
    EXECUTE sqlToExecute;
END IF

I get no errors and the rest of the SP functions fine but this SQL never gets executed, I'm guessing because 'sqlToExecute' isn't being formed properly.

Everything I can find on PREPARE/EXECUTE in SPs assumes that the SQL being executed is being created in the SP, not pulled from a DB.

As an aside, how does your sys_instantUpdates table get populated? Beware of SQL injection! — eggyal
– eggyal, Commented Apr 23, 2014 at 12:53
See also MySQL: @variable vs. variable. Whats the difference? — eggyal
– eggyal, Commented Apr 23, 2014 at 12:58
@eggyal - thanks for the link! Fear not, the SQL statements are constructed start to finish by code without using any user input. This system essentially allows a website owner to mark a submitted comment as visible, hide it or delete it with a single click without logging in. Everything is encapsulated and seperated from the user input and combines a 32 character alphanumeric security token that expires within 24 hours too (although worse case scenario if a psychic were able to guess all the tokens, the worse that could happens would be visitor comments getting deleted). — Mr Fett
– Mr Fett, Commented Apr 23, 2014 at 13:06

Ronak Shah · Accepted Answer · 2014-04-23 12:56:01Z

0

try to use stmt instead of sqlToExecute

DECLARE sqlUpdateSp TEXT;

IF (SELECT EXISTS (SELECT updateSql FROM sys_instantUpdates WHERE expiryDate >= NOW() AND securityToken = securityTokenIn AND updateType = updateTypeIn)) THEN
    SELECT updateSql INTO sqlUpdateSp FROM sys_instantUpdates WHERE expiryDate >= NOW() AND securityToken = securityTokenIn AND updateType = updateTypeIn;  
    SET @phlanx = sqlUpdateSp;
    PREPARE stmt FROM @phlanx;
    EXECUTE stmt ;
END IF

answered Apr 23, 2014 at 12:56

Ronak Shah

1,5592 gold badges13 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Mr Fett Over a year ago

Thanks Ronak - I assumed that 'stmt' was just an arbitrary name - is that not the case?

Mr Fett Over a year ago

Ronak - the sqlToExecute (or stmt) is indeed an arbitrary name and can be whatever you want (so should be descriptive ideally).

Mr Fett · Accepted Answer · 2014-04-23 13:49:24Z

0

Apologies to all - this appears to have randomly started working. I'm not sure if during my tests MySQL was caching the results and so the update wasn't showing but not it's working fine with no changes to the code.

answered Apr 23, 2014 at 13:49

Mr Fett

8,5696 gold badges24 silver badges24 bronze badges

1 Comment

dsolimano Over a year ago

Can you mark this as accepted, so the questions doesn't show up in the unanswered question list? Thanks,David.

Collectives™ on Stack Overflow

MySQL Stored Procedure, Execute SQL Taken from Table using PREPARE/EXECUTE

2 Answers 2

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related