Greg,

> Did you guys miss Tom's comment up-thread? Postgres already does this if
> you use PQExecParams().

Keen. Now we just need to get the driver developers to implement it. I
imagine Java does.

--
Josh Berkus
PostgreSQL @ Sun
San Francisco

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Tue, 2008-04-29 at 16:01 -0400, Aidan Van Dyk wrote:

> Most of my published applications *are* simple, and I tend to
> consolidate as much of my "business logic" in the database as possible
> and a "known" set of queries shared by all the related apps, relying
> heavily on view, triggers, and functions, so the queries in my web-side
> and C-side applications really are very simple and straight forward.

I a company I worked, we got ( almost ? ) the same result by doing all
access using functions and REVOKE-ing frontend app users all privileges
on anything else.

So almost all sql issued by apps looks like
"SELECT * FROM some_func(p1, p2, ..., pn)"

This has a lot of nice properties, among others ability to do lots of
database code fixing on live 27/4 apps without frontends never noticing.

> I purposely choose to have "simple static queries" in my apps. So a
> mode which "rejects" queries with literals/constants in them would catch
> "bugs" in my code.

Hmm - maybe a mode where functions accept only parameters would be
needed for enforcing this on current server code.

Anyway, with pl/proxy partitioning/loadbalancing running on data-empty
servers, code injection would be quite hard even without params-only
mode.

> Those "bugs" really could be cosmetic, and still
> "valid SQL" queries, but one of them could be a valid one which could be
> an injection vector.

Could we also get a mode, where PREPARE would only be allowed for
queries of the form "SELECT * FROM func(?,?,?,?,?);

---------------------
Hannu





--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Tue, 29 Apr 2008, Josh Berkus wrote:

>> Did you guys miss Tom's comment up-thread? Postgres already does this if
>> you use PQExecParams().
>
> Keen. Now we just need to get the driver developers to implement it. I
> imagine Java does.
>

The JDBC driver takes a multi-command statement and splits it up to be
able to use the extended query protocol. So the JDBC driver is actually
doing the reverse of your suggestion. For us it was a decision to ease
the transition from V2 to V3 protocol and not break code that used to
work.

Kris Jurka

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Tue, Apr 29, 2008 at 09:02:30PM -0400, Gregory Stark wrote:

> Did you guys miss Tom's comment up-thread? Postgres already does this if you
> use PQExecParams().

I did, yes. Thanks for the clue. OTOH, I do see the OP's point that
it'd be nice if the DBA could enforce this rule. Maybe a way of
insisting on PQExecParams() instead of anything else?

A

--
Andrew Sullivan
ajs@commandprompt.com
+1 503 667 4564 x104
http://www.commandprompt.com/

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Hi,

> How many people are using literals in Java?

Not sure if I understood the question... In Java most people use
constants (final static). 'Checkstyle' can find 'magic numbers' in the
source code.

If the constants feature was very important in SQL, people would have
requested it, and it would be in the SQL standard by now. There is a
workaround: user defined functions.

> Disabling multi-statement commands

Disabling multi-statement commands just limits the effect of SQL
injection. Disabling literals actually protects from SQL injection.
Both features are important.

> ( almost ? ) the same result by doing all access using functions

This also doesn't protect from SQL injection, it only limits the effect.

> Half a security measure is almost always worse than none at all

Cars and houses have locks. Locks can't fully protect you. Do they
give the illusion security? Maybe. But it's definitely better to have
them.

> headlines: "New PostgreSQL feature breaks 99% applications"

Not if it's disabled by default. What about "New PostgreSQL feature
offers 95% protection from SQL injection"?

> The developers and admins who know about this feature and want to use it...
> quality produced by this ppl is higher than average and less likely to have such basic faults.

Maybe. I found some problems in my code when enabling this feature,
and I thought I was save (or paranoid :-).

Regards,
Thomas

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Andrew Dunstan wrote:
>
>
> Tino Wildenhain wrote:
>> Hi,
>>
>>> In C the best practice is to use #define for constants. In C++ you
>>> have 'const', in Java 'static final'. Unfortunately the 'named
>>> constant' concept doesn't exist in SQL. I think that's a mistake. I
>>> suggest to support CREATE CONSTANT ... VALUE ... and DROP CONSTANT
>>> ..., example: CREATE CONSTANT STATE_ACTIVE VALUE 'active'.
>>
>> of course you mean:
>>
>> CREATE CONSTANT state_active TEXT VALUE 'active'; ?
>
> Why does he mean that? Manifest constants are not typed in plenty of
> languages.

Well but in this case we want them to prevent easy sql injection and
therefore arbitrary macro expansion like in those "plenty of languages"
does not seem like a good idea to me.

Cheers
Tino

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

> Could we also get a mode, where PREPARE would only be allowed for
> queries of the form "SELECT * FROM func(?,?,?,?,?);

Actually, that is similar to the concept of "global prepared statements"
that I proposed some time ago, but I will not have time to write the
patch, alas...
Idea was that the DBA can create a list of SQL statements (with
privileges about who can execute them, just like functions) which are
prepared on-demand at the first EXECUTE by the client.
This would enhance performance (but for performance I like the idea of
caching plans better).
It would be pretty cumbersome, though, to execute dynamic SQL like the
typical search query...

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Hi,

Constants are just convenience: instead of constants, user defined
functions can be used. This already works, however it's a bit verbose:

CREATE FUNCTION STATE_ACTIVE() RETURNS VARCHAR AS
$$ BEGIN RETURN 'active'; END; $$ LANGUAGE PLPGSQL;

Usage is almost the same:
SELECT * FROM USERS WHERE STATE=STATE_ACTIVE();

> therefore arbitrary macro expansion like in those "plenty of languages"
> does not seem like a good idea to me.

This is _not_ macro expansion as in C '#define'. Constants are typed,
as in C++ 'const' and Java 'static final'. The question is only:
should the user explicitly state the data type, or should the data
type be deduced from the value. Both is possible:

CREATE CONSTANT STATE_ACTIVE VALUE 'active';
CREATE CONSTANT STATE_ACTIVE TEXT VALUE 'active';

Regards,
Thomas

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Wed, Apr 30, 2008 at 8:52 PM, Thomas Mueller <
thomas.tom.mueller@gmail.com> wrote:

> Hi,
>
> Constants are just convenience: instead of constants, user defined
> functions can be used. This already works, however it's a bit verbose:
>
> CREATE FUNCTION STATE_ACTIVE() RETURNS VARCHAR AS
> $$ BEGIN RETURN 'active'; END; $$ LANGUAGE PLPGSQL;
>
> Usage is almost the same:
> SELECT * FROM USERS WHERE STATE=STATE_ACTIVE();
>
> > therefore arbitrary macro expansion like in those "plenty of languages"
> > does not seem like a good idea to me.
>
> This is _not_ macro expansion as in C '#define'. Constants are typed,
> as in C++ 'const' and Java 'static final'. The question is only:
> should the user explicitly state the data type, or should the data
> type be deduced from the value. Both is possible:
>
> CREATE CONSTANT STATE_ACTIVE VALUE 'active';
> CREATE CONSTANT STATE_ACTIVE TEXT VALUE 'active';
>
>
Maybe we can extend the SQL's WITH clause do declare the constant along with
the query, and not separate from the query.

WITH CONSTANT c_jobrole = 'clerk', CONSTANT c_dept = 10
SELECT * FROM emp WHERE jobrole = c_jobrole and deptno = c_dept;

and let postgres allow literals only in the WITH clause.

Also, IMHO, the type of the expression should be automatically deduced. The
right hand side should be an expression and not just a string or numeric
literal. For eg. the above query can be written as:

WITH
CONSTANT c_jobrole = 'clerk',
CONSTANT c_deptname = 'FINANCE'::text,
CONSTANT c_dept = (SELECT dname FROM dept WHERE dname = c_deptname)
SELECT * FROM emp WHERE jobrole = c_jobrole and deptno = c_dept;

so the expression can be CAST'd into appropriate type wherever needed.

Best regards,
--
gurjeet[.singh]@EnterpriseDB.com
singh.gurjeet@{ gmail | hotmail | indiatimes | yahoo }.com

EnterpriseDB http://www.enterprisedb.com

Mail sent from my BlackLaptop device

"Gurjeet Singh" <singh.gurjeet@gmail.com> writes:
> Maybe we can extend the SQL's WITH clause do declare the constant along with
> the query, and not separate from the query.

> WITH CONSTANT c_jobrole = 'clerk', CONSTANT c_dept = 10
> SELECT * FROM emp WHERE jobrole = c_jobrole and deptno = c_dept;

[ scratches head... ] And that will provide SQL injection protection how?

Anyway, you hardly need new syntax to do that, I'd expect

WITH SELECT 'clerk' AS c_jobrole ...

to accomplish it just fine.

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers