CODEWITHSUNDEEP
sqlfirebird
Andrey Shiryaev
Andrey Shiryaev

Reputation: 113

Use variable inside "from" in firebird

I try use variable inside "from" SELECT COUNT(*) FROM :T_NAME, but is not work. How can i fix this? This my code:

SET TERM ^ ;
CREATE OR ALTER PROCEDURE Myfunction
RETURNS(
    T_NAME varchar(100),
    NUM_RECORDS integer
)
AS
BEGIN
    FOR SELECT RDB$RELATION_NAME FROM RDB$RELATIONS
        WHERE (RDB$SYSTEM_FLAG <> 1 OR RDB$SYSTEM_FLAG IS NULL) AND RDB$VIEW_BLR IS NULL
        ORDER BY RDB$RELATION_NAME
        INTO :T_NAME
    DO
    BEGIN
        SELECT COUNT(*) FROM :T_NAME
        INTO :NUM_RECORDS;
        SUSPEND;
    END
END^
SET TERM ; ^

Upvotes: 2

Views: 770

Answers (1)

Mark Rotteveel
Mark Rotteveel

Reputation: 109172

You can't parametrize object names. You will need to dynamically build the query (which if you aren't careful may leave you open to SQL injection).

Specifically you need to use:

...
BEGIN
    EXECUTE STATEMENT 'select count(*) from "' || T_NAME || '"'
        INTO :NUM_RECORDS;
    SUSPEND;
END

This should be safe except for table names that contain double quotes. I haven't explicitly checked the behavior with single quotes in an object name, which technically is possible. You may need to take extra steps to protect against these forms of SQL injection if you use this in real production code.

Upvotes: 4

Related Questions