Skip to content
Tutorials Center
My learning

Topics

Lesson 100 of 125 ยท Database Systems

Dynamic SQL

Build SQL at run time without opening injection holes. Learn PREPARE/EXECUTE, sp_executesql, EXECUTE format(), identifier quoting, search builders and the limits of dynamic SQL.

Most SQL is written once and never changes: SELECT name FROM products WHERE price > ?. The table, the columns and the shape of the query are fixed, and only a value varies. Dynamic SQL is the opposite: a program assembles the text of a statement at run time and then asks the database to run it. You need it when something you can't pass as a parameter has to change: a table name, a column to sort by, the list of filters a user switched on, or the list of columns of a report.

Dynamic SQL is powerful and dangerous in equal measure. Done carelessly it is the root of SQL injection; done well it is how search screens, admin tools and reporting engines work. This lesson shows the mechanics in the major databases, then the rules that keep it safe, and ends with the costs.

Static versus dynamic

In a static statement the database parses the text once and the values arrive separately as parameters. In dynamic SQL the text itself is a value computed by your code:

Static SQL Dynamic SQL
Statement text fixed when you write the code built while the program runs
What can vary values (through parameters) values and structure: identifiers, clauses, columns
Parsed and checked when the code is compiled or the procedure is created every time it is built and run
Injection risk none for parameter values yes, wherever you join strings

The golden rule is to keep two kinds of input apart. Values always travel as parameters, never as pieces of the string. Structure (identifiers and keywords) can't be parameters, so it must come from your own code or from an allowlist, or be quoted with the database's identifier-quoting function.

Generating SQL with SQL

The simplest dynamic SQL doesn't execute anything: a query writes statements that a human or script runs afterwards. DBAs do this constantly, for example to add an index for every foreign key or to count the rows of every table. Here SQLite's catalog is used to generate CREATE INDEX statements for every foreign key column, with the table-valued function pragma_foreign_key_list:

SQL Write SQL with SQL ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

The four rows are ready-to-run statements such as CREATE INDEX IF NOT EXISTS orders_customer_id_idx ON "orders" ("customer_id");. Note the replace(name, '"', '""'): identifiers are wrapped in double quotes, and a double quote inside a name must be doubled. That is the standard way to quote an identifier safely, and every database has its own helper for it, listed below.

Dynamic SQL in application code

This is the version you will write most often: a search builder. Users tick filters and pick a sort order, and your code assembles one query. The pattern has three rules: (1) filter names are looked up in an allowlist that maps them to real columns, (2) filter values go into ? placeholders, and (3) the sort column and direction are checked against allowlists before they reach the string.

Python A safe dynamic search builder

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The three searches print different WHERE clauses (one condition, then two, then two with a LIKE) from the same code, and each carries only ? placeholders in the text. The empty name_contains in the second call adds nothing at all. A quote-laden category value is just a value that matches no row. The three structural attacks (a poisoned sort column, a fake filter name and a poisoned direction) are all rejected before any SQL exists, which is exactly where you want to stop them.

An empty filter is not a NULL comparison. The builder leaves unused filters out of the string, so the database never sees category = NULL. This also keeps the statement as simple as possible, which helps the optimizer, as the limits section explains.

Another popular dynamic statement is the dynamic pivot: the report's columns depend on the data, for example one column per month that exists. The column list can't be a parameter, but the values that decide it come from a query, and you quote each alias as an identifier:

Python A report whose columns come from the data

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The first report has columns region, 2025-01, 2025-02, 2025-03 and total. After the April rows arrive, the same function produces a 2025-04 column without any code change. Each month appears twice: as a parameter inside the CASE and as a quoted identifier for the column alias. That split is the whole discipline of dynamic SQL in one example.

PREPARE and EXECUTE in MySQL and PostgreSQL

Both databases can prepare a statement by name and run it later. The parameters are values only:

SQL
-- MySQL: the text can be built with CONCAT, so a table name can be dynamic
SET @sql = CONCAT('SELECT COUNT(*) FROM `', REPLACE(@table_name, '`', '``'), '` WHERE status = ?');
PREPARE stmt FROM @sql;
SET @status = 'shipped';
EXECUTE stmt USING @status;
DEALLOCATE PREPARE stmt;

-- PostgreSQL: SQL-level PREPARE fixes the text, only values change
PREPARE orders_by_status (text) AS
  SELECT id, ordered_at FROM orders WHERE status = $1;
EXECUTE orders_by_status('shipped');
DEALLOCATE orders_by_status;

MySQL's prepared statement accepts one statement (no ; chains), lives for the session, and its USING clause takes user variables (@status), not literals. PostgreSQL's SQL-level PREPARE can't change the table name; for that you need EXECUTE format() inside PL/pgSQL (below). Drivers use the same wire-level mechanism for the ? placeholders in code, so you rarely write PREPARE by hand outside stored procedures.

sp_executesql in SQL Server

EXEC('...') runs a string with no parameters, which is why it is the dangerous form. sp_executesql takes the text, a declaration of the parameters and their values, so values stay values:

SQL
DECLARE @sql nvarchar(max) =
    N'SELECT COUNT(*) FROM dbo.' + QUOTENAME(@table_name) + N' WHERE status = @status;';

EXEC sp_executesql @sql, N'@status nvarchar(20)', @status = N'shipped';

-- Getting a value back with an OUTPUT parameter
DECLARE @n int;
EXEC sp_executesql
    N'SELECT @n = COUNT(*) FROM dbo.orders WHERE status = @status',
    N'@status nvarchar(20), @n int OUTPUT',
    @status = N'shipped', @n = @n OUTPUT;
SELECT @n AS shipped_orders;

QUOTENAME(name) wraps an identifier in [ ] and doubles any ] inside it, and it returns NULL for names longer than 128 characters, so a hostile oversized value breaks the statement instead of injecting. Concatenating with a NULL also gives NULL, which makes EXEC fail rather than run half a statement. The text passed to sp_executesql is compiled and cached by its exact wording, so a parameterized statement is reused for different values.

EXECUTE format() in PostgreSQL

In PL/pgSQL, EXECUTE runs a string, format() builds it, and USING supplies values:

SQL
DO $$
DECLARE
    t text;
    n bigint;
BEGIN
    FOR t IN SELECT tablename FROM pg_tables WHERE schemaname = 'public' LOOP
        EXECUTE format('SELECT count(*) FROM %I', t) INTO n;   -- %I = quoted identifier
        RAISE NOTICE '% has % rows', t, n;
    END LOOP;
END
$$;

-- Values as parameters; the identifier through %I
EXECUTE format('SELECT id FROM %I WHERE status = $1', p_table) INTO v_id USING p_status;

%I quotes an identifier only when needed, %L produces a safely quoted literal (use it only when a value can't be a parameter, such as inside DDL), and %s inserts text unchanged, which is unsafe for anything that came from outside. quote_ident(), quote_literal() and quote_nullable() do the same as plain functions.

EXECUTE IMMEDIATE in Oracle

Oracle's PL/SQL runs a string with EXECUTE IMMEDIATE, passing values through bind variables with USING:

SQL
EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' || DBMS_ASSERT.SQL_OBJECT_NAME(p_table)
                  || ' WHERE status = :1'
   INTO v_count
   USING p_status;

DBMS_ASSERT.SQL_OBJECT_NAME (and SIMPLE_SQL_NAME, ENQUOTE_NAME) validates an object name and raises an error for anything suspicious. DDL statements, such as CREATE INDEX, accept no bind variables at all, so every part of them has to come from validated names.

Quoting identifiers safely

Identifiers can't be parameters, so each database has a quoting function. Use it and prefer an allowlist when the set of valid names is known:

Database Quote an identifier Quote a literal
PostgreSQL quote_ident(x) or format('%I', x) quote_literal(x) or format('%L', x)
SQL Server QUOTENAME(x) (brackets) or QUOTENAME(x, '"') QUOTENAME(x, '''') for short values; better a parameter
MySQL / MariaDB wrap in backticks, doubling any backtick inside use a prepared parameter
Oracle DBMS_ASSERT.ENQUOTE_NAME(x) or SQL_OBJECT_NAME(x) bind variables
SQLite and the SQL standard wrap in double quotes, doubling any double quote inside parameters, or quote(x) in SQL

Quoting makes a name safe to embed, not correct to use: a quoted identifier is also case-sensitive, so "Orders" and orders may be different tables. And quoting alone doesn't answer "may this user touch this table?". An allowlist does.

Generic search and report builders: design notes

  • Build only what is needed. A query with just the active filters is shorter and gets a better plan than a catch-all. The static alternative, WHERE (:category IS NULL OR category = :category) AND ..., is easy to write, but the database must prepare one plan that works for every combination of filters, and it often picks a scan. In SQL Server OPTION (RECOMPILE) is a common compromise; in PostgreSQL a dynamic query avoids "generic plan" surprises.
  • Always paginate and cap sizes. A builder that can return everything will be used to return everything. See LIMIT and OFFSET.
  • Map user-facing names to columns. The browser sends price, not p.unit_price_cents; the allowlist is also your documentation.
  • Escape LIKE wildcards. A user typing 50% into a name search is sending a wildcard. Escape %, _ and the escape character, then use LIKE ? ESCAPE '\', as the LIKE lesson shows.
  • Log the generated text and the parameters (not secrets), so a slow or failing query can be reproduced.

Limits and costs of dynamic SQL

  • It is harder to test and read. A static query is checked when you create the procedure; a dynamic one fails at run time, maybe in production on a branch nobody exercised.
  • Plan caching. Each distinct text is a distinct statement. Parameterized text is reused; text with literals glued in creates thousands of one-off plans and floods the cache (see plan cache).
  • Permissions. In SQL Server, ownership chaining doesn't cross into a dynamic string, so the caller needs permissions on the underlying tables unless you add EXECUTE AS or sign the procedure. PostgreSQL's SECURITY DEFINER functions need a safe search_path.
  • Scope. Local variables and temporary tables created inside a dynamic batch may not be visible outside it (SQL Server #temp tables created in sp_executesql vanish when it ends; MySQL PREPARE uses session variables).
  • Single statement and size limits. MySQL prepares one statement at a time, and statement text has a maximum length.
  • Risk grows with every concatenation. If you can solve a problem with parameters, CASE, COALESCE or a static query, do that and keep dynamic SQL for structure that really varies.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. A search page lets users choose which column to sort by. Which approach is safe?

    Identifiers and keywords can't be parameters (a placeholder in ORDER BY sorts by a constant). An allowlist guarantees that only names you chose can reach the statement; quoting or stripping characters is easier to get wrong.
  2. 2. What does sp_executesql give you that EXEC('...') does not?

    sp_executesql accepts a parameter list and values, so user input is never part of the statement text, and the cached plan is reused. It does not quote identifiers: use QUOTENAME for those.
  3. 3. In PL/pgSQL, which format() specifier should you use to insert a table name that came from outside?

    %I quotes the text as an identifier. %s inserts it unchanged (unsafe), %L quotes it as a string literal (wrong kind of quoting for a name), and %% is just a percent sign.
  4. 4. Why does a dynamic search that builds only the active filters often run better than a static query with (:x IS NULL OR col = :x) for every filter?

    The catch-all form is valid, but one cached plan must serve every combination of filters, so indexes are often ignored. A tailored statement can use the right index for the filters that are actually present.

Summary

  • Dynamic SQL builds statement text at run time; use it only when structure (identifiers, optional clauses, report columns) must vary, never just to insert values.
  • Values always go through parameters (?, $1, @name, :1); identifiers come from an allowlist or a quoting function (QUOTENAME, %I, quote_ident, DBMS_ASSERT, doubled quotes).
  • The statements: PREPARE/EXECUTE (MySQL, PostgreSQL), sp_executesql (SQL Server), EXECUTE format() (PL/pgSQL), EXECUTE IMMEDIATE (Oracle); prefer them over EXEC('...')-style string execution.
  • Search and report builders add only the clauses that are needed, validate sort columns, cap result size and log the generated text.
  • Costs: harder testing, more plan-cache entries, permission and scope surprises, and an injection risk that grows with each concatenation.

Next: Encrypting Data in the Database, where you'll protect data at rest and in transit.