> 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@sss.pgh.pa.us> wrote: "Willy-Bas Loos" <willybas@gmail.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
