X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (neptune.hub.org [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id BF5FCD1B430 for ; Wed, 24 Dec 2003 03:57:55 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 92446-08 for ; Tue, 23 Dec 2003 23:57:10 -0400 (AST) Received: from millenium.mst.co.jp (unknown [210.230.185.241]) by svr1.postgresql.org (Postfix) with ESMTP id 0EA92D1B439 for ; Tue, 23 Dec 2003 23:57:05 -0400 (AST) Received: from mst1x5r347kymb (lc12114 [192.168.1.114]) by millenium.mst.co.jp (8.11.6p2/3.7W) with SMTP id hBO3joJ28499; Wed, 24 Dec 2003 12:45:50 +0900 Message-ID: <00c401c3c9d1$f90184c0$7201a8c0@mst1x5r347kymb> From: "Iain" To: "Richard Huxton" , "Denis" , References: <200312220904.11730.dev@archonet.com> <004c01c3c86f$389c5380$0f32a8c0@denisnew> <200312221048.20783.dev@archonet.com> Subject: Re: how to preserve \n in select statement Date: Wed, 24 Dec 2003 12:56:55 +0900 MIME-Version: 1.0 Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1158 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165 X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200312/169 X-Sequence-Number: 16335 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" To: "Denis" ; 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