PL/pgSQL中quote_literal用法与SQL注入防护及INSERT语句疑问

Hey there! Let's work through your PL/pgSQL and SQL injection questions one by one—since you're new to this, it's totally normal to hit these roadblocks.

Understanding the quote_literal Issue #

First, let's clear up why your inserts are saving values with single quotes: quote_literal()'s whole job is to wrap a string in single quotes and escape any internal quotes to make it safe for dynamic SQL. When you use it directly in a static INSERT statement (like VALUES(quote_literal($1))), you're telling PostgreSQL to insert the escaped string itself (including the outer quotes) instead of the original value. That's why 'hey' gets saved instead of hey.

The good news is: you don't need quote_literal for static INSERT statements at all. PostgreSQL's parameterized queries (using $1, $2, etc.) automatically handle escaping to prevent SQL injection. Here's your fixed function:

CREATE FUNCTION __a_inj(creator text) RETURNS integer AS $query$
DECLARE
    new_id integer;
BEGIN
    -- No quote_literal needed here! $1 is safely parameterized
    INSERT INTO deleteme(name) VALUES($1) RETURNING id INTO new_id;
    RETURN new_id;
END;
$query$ LANGUAGE plpgsql;

When you call select __a_inj('hey'); now, it'll save hey without quotes, and select __a_inj('drop table deleteme'); will save that exact string (no injection happens, since the parameter is treated as plain text).

When to Use quote_literal (and EXECUTE for Dynamic SQL) #

quote_literal is meant for dynamic SQL—when you need to build a SQL string on the fly (like using a user-provided table name). For that, you have to use EXECUTE, but you need to do it safely.

A better alternative to quote_literal is the format() function, which makes dynamic SQL cleaner and safer. Here's an example where you might need dynamic SQL (say, inserting into a user-specified table):

CREATE FUNCTION __a_inj_dynamic(creator text, target_table text) RETURNS integer AS $query$
DECLARE
    new_id integer;
BEGIN
    -- %I escapes identifiers (like table/column names), %L escapes literals
    EXECUTE format('INSERT INTO %I(name) VALUES(%L) RETURNING id', target_table, creator)
    INTO new_id;
    RETURN new_id;
END;
$query$ LANGUAGE plpgsql;

This way, even if someone passes a malicious table name or value, format() escapes it properly to prevent injection.

Answering Your Additional Questions #

• Why are INSERT SQL injection examples rare? Most injection examples focus on SELECT because it's easier to demonstrate data exfiltration, but INSERT risks are real too. However, if you use parameterized queries (like the static INSERT above), injection is impossible. Injection only happens when you directly concatenate user input into SQL strings without escaping. Since many developers use parameterized inserts by default, there's less need for examples of insecure INSERTs.
• Why couldn't you use EXECUTE with INSERT? You absolutely can—you just had a syntax error. Common mistakes include forgetting to capture the RETURNING value with INTO, or failing to properly escape dynamic parts of the SQL string. The example above shows the correct syntax for using EXECUTE with INSERT.

Final Takeaway #

• For static queries (fixed table/column names), use parameterized values ($1, $2)—no need for quote_literal.
• For dynamic queries (dynamic table/column names), use format() with %I (for identifiers) and %L (for literals) to safely build your SQL string with EXECUTE.

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