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" <[EMAIL PROTECTED]> To: "Denis" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> 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 ---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
