Isn't the simple answer to use bind variables?
SQL using bind variables instead of making a new SQL string each time will
prevent malicious users from invoking functions and inserting other sql, as
well as handle the original problem regarding storage of newlines vs \n.
I don't know much about Postgres' SQL cache, but it is well known in Oracle
circles that using bind variables is is a critical part of system design,
not just for security, but for performance and scalability. I suspect that
the same issues apply more or less to postgres.
Correct me if I'm wrong, please...
regards
Iain
----- Original Message -----
From: "Richard Huxton" <dev@archonet.com>
To: "Denis" <sqllist@coralindia.com>; <pgsql-sql@postgresql.org>
Sent: Monday, December 22, 2003 7:48 PM
Subject: Re: [SQL] how to preserve \n in select statement
> On Monday 22 December 2003 09:37, Denis wrote:
> > Hi Richard..
> >
> > If your users are required to fire only SELECT and no DML, you can do
the
> > following:
> >
> > BEGIN;
> > execute the statements given by user
> > ROLLBACK;
> >
> > This will not affect your SELECT and also if any malicious user gives
> > DELETE statement, that will not have any impact too..
>
> An interesting idea, though you'd need to be careful with side-effects
> (triggers/functions etc). I seem to recall a "read-only" setting being
> discussed for transactions too (though not as a security measure, I should
> emphasise).
>
> The other thing is to use the database user/group mechanism - something
which
> tends to be neglected with web-based apps (partly because different DBs
have
> different setups here).
> If only an application super-user can add/delete users make sure the
> permissions reflect this and connect as a more restricted user for other
> logins.
>
> --
> Richard Huxton
> Archonet Ltd
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
> joining column's datatypes do not match