CREATE OR REPLACE FUNCTION increment(key character varying)
RETURNS character varying AS $$
BEGIN
SELECT code, name from tbl where $1;
END;
$$ LANGUAGE plpgsql;
select * from increment('code = ''ati'' ');
=> notice error "where $1 ";
2 Answers
what you try to do is prune to SQL injection, eg:
t=# CREATE OR REPLACE FUNCTION increment(key character varying)
RETURNS character varying AS $$
BEGIN
return format('SELECT code, name from tbl where %s',$1);
END;
$$ LANGUAGE plpgsql;
CREATE FUNCTION
Time: 1.179 ms
t=# select * from increment('code = ''ati'' ');
increment
------------------------------------------------
SELECT code, name from tbl where code = 'ati'
(1 row)
but you don't control what statement is returned, look here:
t=# select * from increment('true; drop table b;');
increment
------------------------------------------------------
SELECT code, name from tbl where true; drop table b;
(1 row)
3 Comments
JustMe
maybe execute the key in dsql to see if it returns true:
execute 'select ' || key into some_boolean. If it throws an error then don't execute the rest.
Vao Tsun
do $$ begin execute 'select true; drop table b;'; end; $$ ; safely dropt table
IMSoP
@JustMe That won't help; the attacker can just adjust their attack to work in the context of the first
EXECUTE instead. There is a huge body of research into different ways of constructing SQL Injection attacks for different contexts.A function does not simply concatenate the variables you give it and run the resulting SQL, so you can't pass a whole WHERE clause in as a parameter like that.
The biggest reason for this is security: input to a function might ultimately have come from an untrusted user (e.g. input from a website), and you don't want them to be able to arbitrarily change your query. It would also be awkward to use: if you wanted to search for the surname O'Reilly, functions would break if they didn't keep track of whether the ' was escaped or not.
So instead queries and data are kept separate - the only time WHERE $1 will work is if $1 is a boolean. Similarly, WHERE name=$1 will only ever compare name against an appropriate value (probably text or varchar), etc.
All that said, if you really have need for a dynamic SQL query, which occasionally happens, but not very often if you've designed your DB well, you can use a plpgsql function with an EXECUTE statement.
In this case, you probably just want the input to be the value of code, and the query to check that explicitly:
SELECT code, name from tbl where code = $1;
You can then add variant functions for different use cases, or have explicit boolean flags like search_by_name which select different queries, all without the risk and complexity of dynamic SQL.
where $1means$1isboolean- and define varchar... maybe you meantSELECT code, name from tbl where name = $1;?..where column = $1