Create function in postgresql error

Question

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ドル ";

• Vao Tsun: where 1ドル means 1ドル is boolean - and define varchar... maybe you meant SELECT code, name from tbl where name = 1ドル;?..
• JustMe: you've got error in where clause, should be something like where column = 1ドル
• niemnv: I know. But parameter in function is 'name = ...'
• IMSoP: For future reference, you should always include the full error message you receive, not just a summary of what you think it means. There may be details in the message which you don't understand, or think are not important, that other people can explain to you, or will help them understand the problem.

Answer 1

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)

• 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.

Answer 2

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.