How can I assign a variable with a prepared statement in a stored procedure?

Question

I've put together a simple stored procedure in which two parameters are passed through to make it more dynamic. I've done this with a prepared statement in the "First Two Digits and Count of Records" section.

What I'm not sure of is if I can make the SET vTotalFT section dynamic with a prepared statement as well.

At the moment I have to hard-code the table names and fields. I want my vTotalFT variable to be assigned based on a prepared dynamic SQL statement, but I'm not sure of the syntax. The idea is that when I call my procedure, I could tell it which table and which field to use for the analysis.

CREATE PROCEDURE `sp_benfords_ft_digits_analysis`(vTable varchar(255), vField varchar(255))
    SQL SECURITY INVOKER
BEGIN

    -- Variables
    DECLARE vTotalFT int(11);

    -- Removes existing table
    DROP TABLE IF EXISTS analysis_benfords_ft_digits;

    -- Builds base analysis table
    CREATE TABLE analysis_benfords_ft_digits
    (
        ID int(11) NOT NULL AUTO_INCREMENT,
        FT_Digits int(11),
        Count_of_Records int(11),
        Actual decimal(18,3),
        Benfords    decimal(18,3),
        Difference Decimal(18,3),
        AbsDiff decimal(18,3),
        Zstat decimal(18,3),
        PRIMARY KEY (ID),
        KEY id_id (ID)
    );

    -- First Two Digits and Count of Records
    SET @s = concat('INSERT INTO analysis_benfords_ft_digits
                        (FT_Digits,Count_of_Records)
                            select substring(cast(',vField,' as char(50)),1,2) as FT_Digits, count(*) as Count_of_Records
                            from ',vTable,'
                            where ',vField,' >= 10
                            group by 1');

    prepare stmt from @s;
    execute stmt;
    deallocate prepare stmt;

    SET vTotalFT = (select sum(Count_of_Records) from
                            (select substring(cast(Gross_Amount as char(50)),1,2) as FT_Digits, count(*) as Count_of_Records
                                from supplier_invoice_headers
                                where Gross_Amount >= 10
                                group by 1) a);


    -- Actual
    UPDATE analysis_benfords_ft_digits
    SET Actual = Count_of_Records / vTotalFT;

    -- Benfords
    UPDATE analysis_benfords_ft_digits
    SET Benfords = Log(1 + (1 / FT_Digits)) / Log(10);

    -- Difference
    UPDATE analysis_benfords_ft_digits
    SET Difference = Actual - Benfords;

    -- AbsDiff
    UPDATE analysis_benfords_ft_digits
    SET AbsDiff = abs(Difference);

    -- ZStat
    UPDATE analysis_benfords_ft_digits
    SET ZStat = cast((ABS(Actual-Benfords)-IF((1/(2*vTotalFT))<ABS(Actual-Benfords),(1/(2*vTotalFT)),0))/(SQRT(Benfords*(1-Benfords)/vTotalFT)) as decimal(18,3));

Broome

Answer 1

First, to use dynamic table/column names, you'll need to use a string/Prepared Statement like your first query for @s. Next, to get the return-value from COUNT() inside of the query you'll need to use SELECT .. INTO @vTotalFT.

The following should be all you need:

SET @vTotalFTquery = CONCAT('(select sum(Count_of_Records) INTO @vTotalFT from
                        (select substring(cast(', vField, ' as char(50)),1,2) as FT_Digits, count(*) as Count_of_Records
                            from ', vTable, '
                            where ', vField, ' >= 10
                            group by 1) a);');
PREPARE stmt FROM @vTotalFTquery;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Please note: the variable name has changed from vTotalFT to @vTotalFT. It doesn't seem to work without the @. And also, the variable @vTotalFT won't work when declared outside of/before the query, so if you encounter an error or empty results that could be a cause.

Innkeeper

Thanks for this. I'll try this on Monday. Cheers – Broome

There's a serious problem here, the whole point of using a prepared statement is to sanitize user input but you're blindly concatenating vTable and vField into the select statement. This answer is open to SQL injection. – One

@One While I agree with your statement in general, I believe you have misread the intent of the question/answer. The question is asking how to dynamically insert table and column names - both of which cannot be replaced via Prepared Statements. That said, my answer is directed at the OP's "how to," not a side-answer of "this is a list of steps to properly sanitize table/column names directly in MySQL". If you believe my answer to still be incorrect, please feel free to either edit it to make it more appropriate or add your own answer to illustrate your point =] – Innkeeper

That's a good point -- unfortunately, I don't know how you could do this in a safe way. Closest I can think of is to limit the input parameters to as few characters as possible, to reduce the amount of code an attacker could inject. If all your columns and table names are under, say, 10 characters, for example, that's probably (pretty much) "safe". – One

@One Not to nitpick, but you've given my answer a -1 as you feel it is "not useful" to answer the question that was asked. Your reasoning for this is based on a side-topic that, you yourself, are unable to immediately answer. When you vote up/down answers in the future, please consider the actual question being asked in your reasoning - not just your personal requirements. On the same note, if you feel additional information is required for an answer (such as mine above), always feel free to leave a comment requesting it. – Innkeeper

I haven't downvoted in ages, and didn't realize that votes are now locked in after a period of time. I tried to take my downvote back but it won't let me unless the question is edited. I think it's a stupid policy, frankly, but in future I'll be sure to comment first, then downvote only after commenting and waiting a reasonable time for the response. In the meantime, I might go complain about this (IMHO) broken process on Meta. If you feel like editing your question, I'll gladly change the downvote to an upvote. Sorry! – One

Aaaand apparently this is a huge huge issue. The suggested resolution is that the OP should update the post to clarify/address any concerns that have been hashed out in the comments. In this particular case, I feel like it was just my ignorance that led to the problem, and you don't actually need to update the post, but I guess that's not good enough :( – One

@Coderer, There is a reason why you must change variable v to @v. This is because the scope of v ends after the procedure end, while the prepared statement (along with @v) will last throughout the entire session. You can edit the answer to put this point in. – Dannielledannon

@Innkeeper You mentioned that declaring the variable @vTotalFT before the query won't work, so where could I declare this variable. I am getting stuck in this problem as my variable won't be updated after the execution. – Alvie

Answer 2

SELECT CONCAT (
        'SELECT DATE(PunchDateTime) as day , '
        ,GROUP_CONCAT('GROUP_CONCAT(IF(PunchEvent=', QUOTE(PunchEvent), ',PunchDateTime,NULL))
       AS `', REPLACE(PunchEvent, '`', '``'), '`')
        ,'
         FROM     tbl_punch
         GROUP BY DATE(PunchDateTime) 
         ORDER BY PunchDateTime ASC
       '
        )
INTO @sql
FROM (
    SELECT DISTINCT PunchEvent
    FROM tbl_punch
    ) t;

PREPARE stmt
FROM @sql;

EXECUTE stmt;

DEALLOCATE PREPARE stmt;

Hindorff