From: Iain <iain@mst.co.jp>
To: Richard Huxton <dev@archonet.com>
To: Denis <sqllist@coralindia.com>
To: pgsql-sql@postgresql.org
Subject: Re: how to preserve \n in select statement
Date: Wed, 24 Dec 2003 12:56:55 +0900
Message-ID: <00c401c3c9d1$f90184c0$7201a8c0@mst1x5r347kymb> (raw)
References: <BAY9-F42t0CI76rOJhK00098f93@hotmail.com>
<200312220904.11730.dev@archonet.com>
<004c01c3c86f$389c5380$0f32a8c0@denisnew>
<200312221048.20783.dev@archonet.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: iain@mst.co.jp, dev@archonet.com, sqllist@coralindia.com
Subject: Re: how to preserve \n in select statement
In-Reply-To: <00c401c3c9d1$f90184c0$7201a8c0@mst1x5r347kymb>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox