News for hackers. Stuff that matters.
Re:Advanced SQL window function quiz by pzxc on Friday February 09, 2018 @03:50PM (#16343712)
I really really like this creation of yours, and I've already learned a couple things from it.

However, you really really need to be careful about SQL injection. I can see that you tried to lock it down as much as possible, as far as I can tell the account the queries are running under only has SELECT permission (no update/delete/etc). However I was still able to get some data you probably don't want me to have...

This query returns a list of all tables in your database:

SELECT c.relname||'' FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace

And this query returns a list of users and their privileges:

SELECT usename||'', usecreatedb||'', usesuper||'', usecatupd||'' FROM pg_user

I guess your name is Andy based on your HN username, but who is Rusty? :) And that's just what I've been able to get in 5 minutes of trying, let alone a determined attacker.

Re:Advanced SQL window function quiz by andy_boot on Friday February 09, 2018 @04:37PM (#16344042)
Noted, I am currently running on the assumption that there is nothing interesting in that PG (There isn't). But yes I would like to lock that user down further it really shouldn't be able to access those things.
Re:Advanced SQL window function quiz by andy_boot on Saturday February 10, 2018 @12:18PM (#16348258)
It is now more locked down (at least "pg_catalog, public, information_schema" are no longer available). Thanks.
Re:Advanced SQL window function quiz by postila on Saturday February 10, 2018 @01:13PM (#16348595)
||'' is another way to do ::text ? :-)