Are SQL injections in non EXECUTE rpc functions possible?
Do I understand it correct that SQL injections are NOT possible if I do NOT use the "execute keyword"?
Like this should be safe:
CREATE OR REPLACE FUNCTION public.search_user(
search_term text
)
RETURNS table
(
id uuid,
first_name text,
last_name text,
username text
)
LANGUAGE plpgsql
SECURITY INVOKER
AS
$$
BEGIN
RETURN QUERY
SELECT
id,
first_name,
last_name,
username
FROM
profiles
WHERE
first_name = search_term;
END
$$;
But this is not safe?:
CREATE OR REPLACE FUNCTION public.search_user(
search_term text
)
RETURNS table
(
id uuid,
first_name text,
last_name text,
username text
)
LANGUAGE plpgsql
SECURITY INVOKER
AS
$$
BEGIN
RETURN QUERY ***EXECUTE***
SELECT
id,
first_name,
last_name,
username
FROM
profiles
WHERE
first_name = search_term;
END
$$;2 Replies
If you are not composing a string to pass to the parser to evaluate, there's nothing to inject. All of this is just variables being passed around, and the data in those variables can be anything allowed by that data type. Nothing is interpreting those values.
sql injection is more when you're trying to dynamically build sql queries.