Обсуждение: Pgsql roles, SQL injection, and utility statements
Hi all; I have a bit of concern about writing applications which use Pgsql roles for security. 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? Best Wishes, Chris Travers
chris@travelamericas.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.