纯SQLite环境下能否加载表字段文本并作为SQL语句执行？

Alright, let's tackle this problem head-on. In pure SQLite (no external tools, just raw SQL), you can dynamically execute SQL stored in a table's field using a feature introduced in SQLite 3.35.0: EXECUTE IMMEDIATE. This lets you pull SQL statements directly from a table column and run them natively within the SQLite environment.

Prerequisite #

First, confirm your SQLite version supports EXECUTE IMMEDIATE by running:

SELECT sqlite_version();

You'll need version 3.35.0 or newer for this method to work.

Step 1: Set Up Your Command Table #

First, create the table to store your SQL commands, then populate it with example statements:

-- Create the command storage table
CREATE TABLE command_table (
    COMMAND_NAME TEXT PRIMARY KEY,
    COMMAND TEXT NOT NULL
);

-- Insert example commands (note: single quotes are escaped with another single quote)
INSERT INTO command_table VALUES
('FetchAllTable1', 'SELECT * FROM table1'),
('FilterTable1ByRow1', 'SELECT * FROM table1 WHERE table1.row1 = ''1'''),
('CreateBackupTable', 'CREATE TABLE IF NOT EXISTS table1_backup AS SELECT * FROM table1'),
('UpdateTable1Row', 'UPDATE table1 SET row2 = ''updated'' WHERE row1 = ''1''');

Step 2: Execute a Single Stored Command #

To run a specific command from command_table, use EXECUTE IMMEDIATE with a subquery that fetches the SQL string. For example, to run the FetchAllTable1 command:

EXECUTE IMMEDIATE (SELECT COMMAND FROM command_table WHERE COMMAND_NAME = 'FetchAllTable1');

This will execute the stored SELECT * FROM table1 statement and return the result set directly. For DDL/DML commands like CreateBackupTable or UpdateTable1Row, the same syntax works—they'll execute the modification without returning a result (unless the command itself produces output).

Step 3: Batch Execute All Stored Commands #

If you need to run every command in command_table sequentially, you can use a recursive CTE to iterate through all rows and execute each command:

WITH RECURSIVE command_iterator(idx, sql_cmd) AS (
    -- Initialize with the first command
    SELECT 1, COMMAND 
    FROM command_table 
    ORDER BY COMMAND_NAME 
    LIMIT 1
    
    UNION ALL
    
    -- Fetch the next command in sequence
    SELECT idx + 1, (
        SELECT COMMAND 
        FROM command_table 
        ORDER BY COMMAND_NAME 
        LIMIT 1 OFFSET idx
    )
    FROM command_iterator
    -- Stop when we've processed all commands
    WHERE idx < (SELECT COUNT(*) FROM command_table)
)
-- Execute each command in the iterator
SELECT EXECUTE IMMEDIATE sql_cmd FROM command_iterator;

Important Notes #

• Security Warning: Be extremely careful if the COMMAND field contains user-provided input. This method executes raw SQL, so untrusted input can lead to SQL injection attacks.
• Escaping Quotes: When inserting SQL strings into command_table, remember that single quotes in the SQL must be escaped with another single quote (e.g., ''1'' instead of '1').
• Result Handling: For SELECT commands, EXECUTE IMMEDIATE returns the result set. For commands that modify data or schema (like CREATE, UPDATE, DELETE), the statement runs silently (you can verify changes with separate queries).

内容的提问来源于stack exchange，提问作者Robert Jednachowski