Re: escaped rolenames in pg_has_role

> A quote in the data might be real data or quoting, and you have no way to
tell the difference.
It´s not so bad, because a parameter of type name cannot start with a quote.

But you´re right, i have been having a hard time with the use of quoting.
The rule i used until now was: "every string that has been entered by a user
must be escaped, so that there can never be sql inserts".
Every function has all string input escaped. When you call another function
from inside your function, don´t pass any un-escaped strings, you can´t
allways be sure that others escape their strings properly.
Just for safety´s sake.
This hasn´t been verfy practical in it´s use.

Do I understand correctly that you mean to say:
"User-input strings do not need to be escaped, except when you build an SQL
string with it, i.e. when using EXECUTE. When calling functions from inside
your functions, it is the responability of the receiving function to handle
escaping of its input."
Or in other words,i suppose, are EXECUTE and PERFORM really the only ways
that sql in the form of input string parameters could be executed inside my
function?

WBL

On 6/26/07, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> "Willy-Bas Loos" <willybas(at)gmail(dot)com> writes:
> > The problem is that pg_has_role does not recognize the usernames when
> they
> > are escaped by quote_literal or quote_ident.
>
> Don't use quote_ident here. In fact, I'd say that you appear to have
> completely misunderstood the appropriate rules for quoting at all.
> If you are passed data that you don't know if it's quoted or not, what
> the heck are you supposed to do with it? A quote in the data might be
> real data or quoting, and you have no way to tell the difference.
>
> You need to redesign your function stack to eliminate that ambiguity.
> I'd recommend that data items never have quoting, except at the instant
> that they get embedded into SQL strings (which this function isn't doing
> at all, and so it does not need to quote the name).
>
> regards, tom lane
>