Re: Pgsql roles, SQL injection, and utility statements

chris(at)travelamericas(dot)com (Chris Travers) writes:
> Since the utility statements are not parameterized, the easiest way to
> manage the roles in an application is to use stored procedures which
> EXECUTE strings to create SQL queries. These EXECUTE statements
> include user-supplied data, and since these would generally run with
> some sort of administrative rights, I am worried about people doing
> things like:
> select * from add_user_to_role('username', 'rolename; drop table foo;');
>
> Is this a problem? Is there a way to do this safely?

Sure - validate that 'rolename; drop table foo;' is the legitimate
name of a role, and raise an exception if it isn't.

And have the stored function use "double quotes" to make sure that the
names are suitably quoted.

That provides a "belt" and a "pair of suspenders" for this case...
--
output = ("cbbrowne" "@" "acm.org")
http://www3.sympatico.ca/cbbrowne/nonrdbms.html
I just removed the instructions in MC:COMMON;LINS > which specify that
it should be installed on AI. We'll certainly miss that machine, and
probably spend the rest of our lives fixing programs that mention it.