date:20080429

Peter Eisentraut [EMAIL PROTECTED] writes:

 Am Dienstag, 29. April 2008 schrieb Bruce Momjian:
 We do look at COLUMNS if the ioctl() fails, but not for file/pipe
 output.

 This is quite a useless complication.  Readline uses exactly the same ioctl() 
 call to determine the columns, so if ioctl() were to fail, then COLUMNS would 
 be unset or wrong as well.

COLUMNS is just a regular environment variable. The user is free to set it and
many people have dotfiles, aliases, or scripts which do just that. 

Consider, for example, someone with a cron job which runs several commands
such as ls -C, dpkg -l, and of course psql -Pformat=wrapped to generate
various reports and wants it all formatted to 72 columns. They would normally
just set COLUMNS=72 and run their commands and get an email all formatted to
72 columns.

But your point is valid, that's why I'm not too worried about cases where
COLUMNS is set to the desired width but readline interferes with it. In those
cases we would be using the ioctl value anyways. It would probably still be a
good idea to getenv(COLUMNS) early on before readline is initialized though.

-- 
  Gregory Stark
  EnterpriseDB  http://www.enterprisedb.com
  Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL 
training!

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

Re: [HACKERS] SRF in SFRM_ValuePerCall mode

2008-04-29 Thread dv @ nabble

- Original Message - 
From: Tom Lane [EMAIL PROTECTED]

To: Heikki Linnakangas [EMAIL PROTECTED]
Cc: dv @ nabble [EMAIL PROTECTED]; pgsql-hackers list 
pgsql-hackers@postgresql.org

Sent: Monday, April 28, 2008 5:07 PM
Subject: Re: [HACKERS] SRF in SFRM_ValuePerCall mode

Heikki Linnakangas [EMAIL PROTECTED] writes:

dv @ nabble wrote:
I am working on implementation of custom C SRF for our team. The SRF 
uses

SFRM_ValuePerCall mode. I know that sometimes even in SFRM_ValuePerCall
mode
all the rows returned from SRF are materialized (for performing JOINs,
for
example).

Yep, they are unfortunately always materialized. Back when set returning
functions were implemented, the original patch did actually support true
value per call mode, where the whole result set was not materialized.
However, it was dropped because of some issues I can't remember off the
top of my head. The value-per-call API was committed, so that it was
already in place when someone gets around to implement the backend
support for it.

That's a rather revisionist view of history ;-)  Value-per-call mode has
always been there, just not in nodeFunctionscan.c.

If you're not joining to the function result, and you don't need the
ability to determine its result type on the fly, you could declare it
as returning a specific rowtype and then call it in the targetlist:

select vpc();

You mean make the function return the only row?
This is not the functionality we need. What we want is to create a SETOF 
function that will
emulate a table and query this table with WHERE filter and LIMIT clauses 
to limit the row
count we want to return. We might pass the filter and the limit to the 
function, but we want to

implement it in more natural way.

Thanks,
Denis 

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

Re: [HACKERS] Protection from SQL injection

2008-04-29 Thread Thomas Mueller

Hi,

Meredith's libdejector

1) The last activity was 2005-12-17 :-(
2) From the docs: the techniques used ... are ... being explored for
patentability.
3) The tool validates the SQL statement. This is not required when
using parameterized queries.
4) An 'exemplar' query is required for each query.
It's an interesting idea, and can even find the ORDER BY injection
that 'disabling literals' can't find. However there are problems: 2) +
4).

zero developer pain

Actually it's not zero pain, but the main problem is: there is no way
to enforce using it.

[SQL injection] is the main security problem of applications

Yes and no. Is buffer overflow an application or language problem? In
C / C++ buffer overflow is a problem. Java enforces array bounds
checking. What I suggest is to enforce using parameterized statements.
This is like having a painless, enforcible 'array bounds checking
mode' in C / C++.

hasn't this been discussed to death already?

Yes, but no good solution has been found so far.

II have to do things like: WHERE a.f = 'lit' AND b.h = $1;

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'.

any literal (i.e. not just strings) can be quoted, think of dates in queries.

The problem is not only quotes. The problem is all kinds of user
input. For example: sql = SELECT * FROM ORDERS WHERE ORDER_ID = +
orderId; This is not a problem if orderId is a number. But what if
it's a String? For example 1 AND (SELECT * FROM USERS WHERE
NAME='admin' AND PASSWORD LIKE 'm%'). An attacker could then retrieve
the admin password quite quickly.

tainting of variables

See Meredith's libdejector: regular expression checking doesn't always
work. Also, programming languages such as Java don't support tainting.
And it's again in the hand of the developer to use it, not use it, or
use it in the wrong way. There should be a way for an admin to enforce
using it, and using it correctly.

Microsoft's approach of integrating SQL into the language

Yes, LINQ is a good approach. For Java there is a project called
'Quaere' that provides something similar (however only when using the
'Alias' syntax, I wrote this part, see
http://svn.codehaus.org/quaere/trunk/Quaere/src/test/java/org/quaere/alias/test/SamplesOrderByTest.java).
However it will take a long time until all applications are converted.
With 'disabling literals', applications can be converted step-by-step.
'Disabling literals' can be used as a development tool, and it can be
enabled or disabled at runtime. With LINQ / Quaere / HaskellDB
migration will be harder and slower because you need to re-write the
application.

HaskellDB

The query syntax seems to be quite 'different'. I would prefer if the
syntax is as close as possible to SQL to simplify migration.

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

Re: [HACKERS] Proposed patch - psql wraps at window width

2008-04-29 Thread Bruce Momjian

Peter Eisentraut wrote:
 Am Dienstag, 29. April 2008 schrieb Bruce Momjian:
  We do look at COLUMNS if the ioctl() fails, but not for file/pipe
  output.
 
 This is quite a useless complication.  Readline uses exactly the same ioctl() 
 call to determine the columns, so if ioctl() were to fail, then COLUMNS would 
 be unset or wrong as well.

I was thinking about Win32 or binaries that don't have readline.

-- 
  Bruce Momjian  [EMAIL PROTECTED]http://momjian.us
  EnterpriseDB http://enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

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

Re: [HACKERS] Proposed patch - psql wraps at window width

2008-04-29 Thread Bruce Momjian

Gregory Stark wrote:
 Bruce Momjian [EMAIL PROTECTED] writes:
 
  Gregory Stark wrote:
   Now, we could get fancy and honor $COLUMNS only in non-interactive mode,
   but that seems confusing.
  
  We could always read COLUMNS early on before readline is initialized and 
  stash
  the value away in a variable. But...
  
  We would only look at COLUMNS if the ioctl for window size failed. Does
  psql/readline do anything to COLUMNS in that case?
 
  We do look at COLUMNS if the ioctl() fails, but not for file/pipe
  output.
 
 Yeah, it looks like your most recent patch still has the bug that if the user
 specifies wrapped there are some complicated rules creating cases where it
 will ignore the user's request and use un-wrapped output instead.

Can you be more specific?  You mean if the headings don't fit?  Yea,
that is true.  I am thinking of adding a \pset auto format to \x in
those cases, but that if for later.

Also, I thiink you could do in your .psqlrc:

\pset columns `echo $COLUMNS`

to get the behavior you want.

-- 
  Bruce Momjian  [EMAIL PROTECTED]http://momjian.us
  EnterpriseDB http://enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

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

[HACKERS] Getting statistics

2008-04-29 Thread Suresh

Hello,

I want to collect various statistics like time taken, number of context 
switches, page faults etc.. for a query being run. postgres.c contains lots of 
getrusage related things.

Is there any way to calculate all the things without writing any custom code ?

Thanks and regards,
Suresh


   
-
Be a better friend, newshound, and know-it-all with Yahoo! Mobile.  Try it now.

Re: [HACKERS] Protection from SQL injection

2008-04-29 Thread Martijn van Oosterhout

On Tue, Apr 29, 2008 at 01:37:37PM +0200, Thomas Mueller wrote:
  any literal (i.e. not just strings) can be quoted, think of dates in 
  queries.
 
 The problem is not only quotes. The problem is all kinds of user
 input. For example: sql = SELECT * FROM ORDERS WHERE ORDER_ID =  +
 orderId; This is not a problem if orderId is a number. But what if
 it's a String? For example 1 AND (SELECT * FROM USERS WHERE
 NAME='admin' AND PASSWORD LIKE 'm%'). An attacker could then retrieve
 the admin password quite quickly.

In other words, your programmer was stupid. And your example doesn't
work because no matter what the string is it can't return anything
other than rows from the orders table. If you're worried about them
using semicolons to introduce another query, prepare has prohibited
that for a long time already.

But as far as I'm concerned, the real killer is that it would make
using any interactive query interface impossible. I don't think it's
reasonable to include a complete SQL parser into psql just so I can
type normal queries.

Have a nice day,
-- 
Martijn van Oosterhout   [EMAIL PROTECTED]   http://svana.org/kleptog/
 Please line up in a tree and maintain the heap invariant while 
 boarding. Thank you for flying nlogn airlines.


signature.asc
Description: Digital signature

Re: [HACKERS] Proposed patch - psql wraps at window width

2008-04-29 Thread PontoSI - Consultoria, Informática e Serviços LDA

Bruce Momjian [EMAIL PROTECTED] writes:

 Gregory Stark wrote:
 Bruce Momjian [EMAIL PROTECTED] writes:
 
  We do look at COLUMNS if the ioctl() fails, but not for file/pipe
  output.
 
 Yeah, it looks like your most recent patch still has the bug that if the user
 specifies wrapped there are some complicated rules creating cases where it
 will ignore the user's request and use un-wrapped output instead.

 Can you be more specific?  You mean if the headings don't fit?  Yea,
 that is true.  I am thinking of adding a \pset auto format to \x in
 those cases, but that if for later.

[No I wasn't thinking of that, that's an interesting case too though I think
we might need to think a bit harder about cases that wrap poorly. If you have
long column headings we could wrap those too. But what if you have enough
space for just a few characters per column and you have long text fields in
those columns?]

I just meant the same thing I've been on about all week. Currently the
decision about whether to use wrapped mode is tied up with the decision on
what width to use and the result is that we ignore -Pformat=wrapped according
to some arcane set of rules.

The cases where we ignore the user's selected format are quite complex and not
accurately described in the documentation. They're also not accurately
described by your not for file/pipe output description either.

An accurate description would appear to be something like:

 quoteWrapped/quote is like literalaligned/ but wraps to a target
 width of literal\pset columns/ or the width of the screen (unless screen
 size determination fails or output has been redirected using -o or \o in
 which case it is ignored and psql uses normal aligned mode unless \pset
 columns is used).

It's confusing and inconsistent. I think it's better to pick a simple set of
general principles and code to that. Trying to code to presumed use cases
often ends up with code which handles corner cases poorly or inconsistently.

I think the behaviour should be simply:

format=auto
 isatty(fout) ? format := wrapped : format := aligned
format=wrapped
 columns := \pset columns || ioctl(fout) || getenv(COLUMNS) || 79

[Note in the above that the ioctl is on fout, not stdout!]


That would be easy to explain in the documentation as two simple consistent
rules. And as a bonus it would be consistent with other programs which use
these variables.

So the description I would code to is simply:

 Wrapped is like aligned but wraps to \pset columns or an automatically
 determined screen size. The screen size is determined automatically if output
 is to a terminal which supports that, if that fails then by checking the
 COLUMNS environment variable, and if that's unset then by defaulting to 79.

 Auto selects wrapped format when output is a terminal and aligned
 format otherwise.

-- 
  Gregory Stark
  EnterpriseDB  http://www.enterprisedb.com
  Ask me about EnterpriseDB's Slony Replication support!

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

[HACKERS] table format specification



Hi,
I'm writing a small program to recover specific tables from a severely 
crippled database, and while I'm able to identify most fields on a hex 
dump of the table file, there are several 'gaps' (probably headers and 
footers and/or additional info) that I can't identify. Where can I find 
documentation regarding the table file layout? I'm working with files 
generated with Postgres 8.25.


Kind Regards,
   João Pinheiro

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

Re: [HACKERS] Protection from SQL injection

2008-04-29 Thread PFC


zero developer pain


Actually it's not zero pain, but the main problem is: there is no way
to enforce using it.


	Sure, there is no way to enforce it (apart from grepping the source for  
pg_query() and flogging someone if it is found), but is it really  
necessary when the right solution is easier to use than the wrong solution  
? Capitalizing on developer laziness is a win IMHO, lol.



The problem is not only quotes. The problem is all kinds of user
input. For example: sql = SELECT * FROM ORDERS WHERE ORDER_ID =  +
orderId; This is not a problem if orderId is a number. But what if
it's a String? For example 1 AND (SELECT * FROM USERS WHERE
NAME='admin' AND PASSWORD LIKE 'm%'). An attacker could then retrieve
the admin password quite quickly.


IMHO this is an example of what should never be done.

// very bad (especially in PHP where you never know the type of your  
variables)

sql = SELECT * FROM ORDERS WHERE ORDER_ID =  + orderId;

// slightly better (and safe)
sql = SELECT * FROM ORDERS WHERE ORDER_ID =  + int( orderId );

// correct (PHP syntax)
pg_query_params( SELECT * FROM ORDERS WHERE ORDER_ID = $1,  
array( orderId ))

db_query( SELECT * FROM ORDERS WHERE ORDER_ID = %s, array( orderId ))

// correct (Python syntax)
cursor.execute( SELECT * FROM ORDERS WHERE ORDER_ID = %s, ( orderId, ))

The last two don't complain if orderId is a string, it will be correctly  
quoted, and then postgres will complain only if it is a string which does  
not contain a number. This is useful in PHP where you never know what type  
you actually have.


The little function in my previous mail is also useful for mysql which has  
no support for parameterized queries.



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

Re: [HACKERS] Protection from SQL injection

Thomas Mueller [EMAIL PROTECTED] writes:

 Also, programming languages such as Java don't support tainting. And it's
 again in the hand of the developer to use it, not use it, or use it in the
 wrong way. There should be a way for an admin to enforce using it, and using
 it correctly.

I bet you could do something clever with Java.

Something like making the Execute() stmt take a special kind of string object
which enforces that it can only be constructed as static final and takes a
String as a constructor argument . That would let you use literals in the
queries but bar you from including any user input at runtime. You could even
include some methods for assembling such StaticStrings in useful ways which
would let you build queries dynamically out of immutable pieces.

I think you're tilting at windmills if you want to bar *all* literals. That's
just too big of a usability hit and as you pointed out with the common use
case of dynamically choosing ORDER BY it doesn't even catch other common
cases. You need to step back and find a way to prevent user input from ending
up in the query regardless of whether it's in a literal or not.

-- 
  Gregory Stark
  EnterpriseDB  http://www.enterprisedb.com
  Ask me about EnterpriseDB's PostGIS support!

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

Re: [HACKERS] table format specification

2008-04-29 Thread Martijn van Oosterhout

On Tue, Apr 29, 2008 at 02:19:24PM +0100, PontoSI - Consultoria, Informática e 
Serviços LDA wrote:
 I'm writing a small program to recover specific tables from a severely 
 crippled database, and while I'm able to identify most fields on a hex 
 dump of the table file, there are several 'gaps' (probably headers and 
 footers and/or additional info) that I can't identify. Where can I find 
 documentation regarding the table file layout? I'm working with files 
 generated with Postgres 8.25.

There's the manual which has quite a bit of detail. If you want code
there are pg_filedump and pgfsck which can parse datafiles (as long as
your database is in some kind of readable state).

Have a nice day,
-- 
Martijn van Oosterhout   [EMAIL PROTECTED]   http://svana.org/kleptog/
 Please line up in a tree and maintain the heap invariant while 
 boarding. Thank you for flying nlogn airlines.


signature.asc
Description: Digital signature

Re: [HACKERS] table format specification

2008-04-29 Thread Heikki Linnakangas


PontoSI - Consultoria, Informática e Serviços LDA wrote:
I'm writing a small program to recover specific tables from a severely 
crippled database, and while I'm able to identify most fields on a hex 
dump of the table file, there are several 'gaps' (probably headers and 
footers and/or additional info) that I can't identify. Where can I find 
documentation regarding the table file layout? I'm working with files 
generated with Postgres 8.25.


The file itself consists of 8k pages. src/include/storage/bufpage.h and 
src/include/access/htup.h would be a good place to start on 
understanding the page layout. There's also a chapter in the manual 
about it.


You might also want to take a look at the pageinspect contrib module, in 
8.3.


--
  Heikki Linnakangas
  EnterpriseDB   http://www.enterprisedb.com

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

Re: [HACKERS] Protection from SQL injection

2008-04-29 Thread Tom Dunstan

On Tue, Apr 29, 2008 at 12:25 AM, Thomas Mueller
[EMAIL PROTECTED] wrote:

  What do you think about it? Do you think it makes sense to implement
  this security feature in PostgreSQL as well? If not why not? Does
  PostgreSQL have another solution or plan to solve the SQL injection
  problem?

Damn, am I the only person who likes the idea?

To those suggesting that it's just treating the symptom: well of
course it is. But using e.g. Exec-Shield / PIE / stack protection
weren't bad ideas just because buffer overflows are the fault of the
application developer. And who wants to grep through every module they
install on their system every time they do an update just in case some
feature that they never use has added a bad query? Assuming they have
the source. PHP apps are notorious for it, of course, but it isn't
just them.

Now, I reckon the only way to sanely do it without mucking up people's
ad-hoc queries would be to have it as a permission that would default
to on, but be REVOKE-able. Then it can be revoked from the user/role
that $untrusted application connects as, but still allow people to get
in from a trusted account and get their hands dirty when they need to.

Would it catch ALL holes? No, as we saw in the order by case, and
there are probably others (not sure if I like the proposed solution
for that, btw). Would it catch a fair number? Absolutely.

Cheers

Tom

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

Re: [HACKERS] SRF in SFRM_ValuePerCall mode

dv @ nabble [EMAIL PROTECTED] writes:
 From: Tom Lane [EMAIL PROTECTED]
 If you're not joining to the function result, and you don't need the
 ability to determine its result type on the fly, you could declare it
 as returning a specific rowtype and then call it in the targetlist:
 
 select vpc();

 You mean make the function return the only row?

No, I'm pointing out that ValuePerCall SRFs can be called from the
targetlist.

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

Re: [HACKERS] Protection from SQL injection

2008-04-29 Thread Tino Wildenhain


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'; ? ;)

interesting idea, would that mean PG complaints on queries

SELECT state_active FROM sometable ... because
state_active is already defined as constant?

What about local session variables? Usefull as well...

I think this is really a big effort :-)

Greets
Tino

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

Re: [HACKERS] Protection from SQL injection

Tom Dunstan [EMAIL PROTECTED] writes:
 Damn, am I the only person who likes the idea?

Just about.  The reason that this idea isn't going anywhere is that its
cost/benefit ratio is untenably bad.  Forbidding literals will break
absolutely every SQL-using application on the planet, and in many cases
fixing one's app to obey the rule would be quite painful (consider
especially complex multi-layered apps such as are common in the Java
world).  In exchange for that, you get SQL injection protection that
has got a lot of holes in it, plus it stops protecting you at all
unless you are using a not-SQL-standard database.  That tradeoff is
not happening, at least not in any nontrivial application.

Analogies such as PIE just point up the difference: for 99% of
applications, you can enable PIE without doing any more work than
adding a compile switch.  If people were looking at major surgery
on most of their apps to enable it, the idea would never have gone
anywhere.

If you're going to ask people to do significant revision of their
apps to gain security, they're going to want it to work no matter
what database they run their apps against.  This is why you need
a client-side solution such as tainting.

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

Re: [HACKERS] Protection from SQL injection

2008-04-29 Thread Andrew Dunstan




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.




interesting idea, would that mean PG complaints on queries

SELECT state_active FROM sometable ... because
state_active is already defined as constant?


Right, this would be a major can of worms. The only way it could work, I 
suspect, is by segregating the identifier space to remove ambiguity 
between constants and other identifiers.


cheers

andrew


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

Re: [HACKERS] Protection from SQL injection

2008-04-29 Thread Aidan Van Dyk

* Tom Lane [EMAIL PROTECTED] [080429 10:59]:
 Tom Dunstan [EMAIL PROTECTED] writes:
  Damn, am I the only person who likes the idea?
 
 Just about.  The reason that this idea isn't going anywhere is that its
 cost/benefit ratio is untenably bad.  Forbidding literals will break
 absolutely every SQL-using application on the planet, and in many cases
 fixing one's app to obey the rule would be quite painful (consider
 especially complex multi-layered apps such as are common in the Java
 world).  In exchange for that, you get SQL injection protection that
 has got a lot of holes in it, plus it stops protecting you at all
 unless you are using a not-SQL-standard database.  That tradeoff is
 not happening, at least not in any nontrivial application.
 
 Analogies such as PIE just point up the difference: for 99% of
 applications, you can enable PIE without doing any more work than
 adding a compile switch.  If people were looking at major surgery
 on most of their apps to enable it, the idea would never have gone
 anywhere.

I guess my database apps qualify as nontrivial.  I'm pretty sure that
I *could* enable something like this in all my web-facing apps *and* my
compiled C/C++ apps and not have any troubles.

And I happen to have programs/code that fail on PIE/execshield stuff.

I guess everything is relative.

That said, though *I* like the idea (and since I develop against
PostgreSQL 1st and use params for my queries I would consider it a nice
tool to keep me honest), I can easily see that the cost/benefit ratio
on this could be quite low and make it not worth the code/support
necessary.

 If you're going to ask people to do significant revision of their
 apps to gain security, they're going to want it to work no matter
 what database they run their apps against.  This is why you need
 a client-side solution such as tainting.

Well, just because a tool is available doesn't mean people have to use
it.  I mean, we have PostgreSQL, and we think that's worth it, even
though to use it, everybody has to do significant revision of their
apps.

a.

-- 
Aidan Van Dyk Create like a god,
[EMAIL PROTECTED]   command like a king,
http://www.highrise.ca/   work like a slave.


signature.asc
Description: Digital signature

Re: [HACKERS] [DOCS] pg_total_relation_size() and CHECKPOINT

2008-04-29 Thread Zubkovsky, Sergey


Magnus,

Please, take a look at my implementation of stat().
It has at least two advantages: it's faster and doesn't have a bug with
local-to-UTC time conversion that native msvc's stat() has.
Maybe it will be useful.

Thank you.

-Original Message-
From: Magnus Hagander [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, April 29, 2008 4:54 PM
To: Zubkovsky, Sergey
Cc: Andrew Dunstan; Tom Lane; Alvaro Herrera; Gregory Stark;
[EMAIL PROTECTED]
Subject: Re: [HACKERS] [DOCS] pg_total_relation_size() and CHECKPOINT

I already applied a different patch for this quite some time ago. So 
what's in HEAD (and current in 8.3 and 8.2) should be fixed already.

//Magnus

Zubkovsky, Sergey wrote:
 Hi,
 
 Here is the patch.
 Check it, please.
 
 Locations of the added files:
   src\include\port\win32_msvc\sys\stat.inl
   src\port\stat_pg_fixed.c
 
 
 Thank you.
 
 
 -Original Message-
 From: Andrew Dunstan [mailto:[EMAIL PROTECTED] 
 Sent: Saturday, March 29, 2008 4:18 PM
 To: Zubkovsky, Sergey
 Cc: Tom Lane; Alvaro Herrera; Gregory Stark;
 pgsql-hackers@postgresql.org; Magnus Hagander
 Subject: Re: [HACKERS] [DOCS] pg_total_relation_size() and CHECKPOINT
 
 
 
 
 Zubkovsky, Sergey wrote:
 In the prepared custom build of PG 8.3.1 the native MSVC's stat() was
 rewrote by adding GetFileAttributesEx() to correct stat's st_size
value.
 I had seen that a result of MSVC's stat() and a result of
 GetFileAttributesEx() may be differ by the file size values at least.
 The most important thing is the test in the original post
 ( http://archives.postgresql.org/pgsql-docs/2008-03/msg00041.php )
 doesn't reproduce any inconsistence now.
 All work fine.

 This was tested on my WinXP SP2 platform but I suppose it will work
on
 any NT-based OS.

   
 
 
 If you have a patch, please send it to the -patches list.
 
 cheers
 
 andrew


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

Re: [HACKERS] Proposed patch - psql wraps at window width

2008-04-29 Thread Peter Eisentraut

Am Dienstag, 29. April 2008 schrieb Bruce Momjian:
 Peter Eisentraut wrote:
  Am Dienstag, 29. April 2008 schrieb Bruce Momjian:
   We do look at COLUMNS if the ioctl() fails, but not for file/pipe
   output.
 
  This is quite a useless complication.  Readline uses exactly the same
  ioctl() call to determine the columns, so if ioctl() were to fail, then
  COLUMNS would be unset or wrong as well.

 I was thinking about Win32 or binaries that don't have readline.

These rules don't seem very consistent.  You are mixing platform dependencies, 
build options, theoretical, unproven failures of kernel calls, none of which 
have anything to do with each other.  For example, if readline weren't 
installed, then there would be no one who sets COLUMNS, so why look at it?  
If you want to allow users to set COLUMNS manually (possibly useful, see Greg 
Stark's arguments), then it should have priority over ioctl(), not the other 
way around.

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

Re: [HACKERS] [COMMITTERS] pgsql: Remove typename from A_Const.

[EMAIL PROTECTED] (Alvaro Herrera) writes:
 Remove typename from A_Const.

I'm thinking this could be cleaned up further.  The patch as applied
removes the ::int4 typename decoration that had been inserted by
makeIntConst(), while leaving in place the ::float8 decoration inserted
by makeFloatConst().  The kindest thing that can be said about that
is that it's inconsistent.  Now as far as I can see in a look through
gram.y, these routines (and makeAConst) were used only in places where
the typename qualification was really unnecessary, that is typmods
and GUC variable values and so on, not general expression contexts where
we might really need to determine a data type for the constant.  So what
I'm thinking is that we should get rid of the ::float8 decoration too,
and thereby be able to revert some of the ugly code added elsewhere such
as guc.c.

Is there a reason it was done this way that I'm missing?

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

Re: [HACKERS] Protection from SQL injection

Aidan Van Dyk [EMAIL PROTECTED] writes:

 That said, though *I* like the idea (and since I develop against
 PostgreSQL 1st and use params for my queries I would consider it a nice
 tool to keep me honest), I can easily see that the cost/benefit ratio
 on this could be quite low and make it not worth the code/support
 necessary.

Note that using parameters even for things which are actually constants is not
really very desirable. If you have a query like:

SELECT * FROM users WHERE userid = ? AND status = 'active'

a) It makes things a lot clearer to when you call Execute($userid) which
   values are actually the key user-provided data. In more complex queries it
   can be quite confusing to have lots of parameters especially if the query
   itself only makes sense if you know what values will be passed.

b) It allows the database to take advantage of statistics on status that
   might not otherwise be possible.

Parameters are definitely the way to go for dynamic user data but for
constants which are actually an integral part of the query and not parameters
you're passing different values for each time it's actually clearer to include
them directly in the query.

-- 
  Gregory Stark
  EnterpriseDB  http://www.enterprisedb.com
  Ask me about EnterpriseDB's On-Demand Production Tuning

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

Re: [HACKERS] Protection from SQL injection


 If you're going to ask people to do significant revision of their
 apps to gain security, they're going to want it to work no matter
 what database they run their apps against.  This is why you need
 a client-side solution such as tainting.

Or if people are going to re-write their applications anyway, we'd want at 
least a theoretically robust and flexible approach like libdejector, which 
lets you identify which parts of a query structure are modifiable and 
which are not.  

For example, some applications need to replace whole phrases:

$criteria = WHERE $var1 = '$var2'

This is a very common approach for dynamic search screens, and really not 
covered by placeholder approaches.

-- 
--Josh

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

Re: [HACKERS] Protection from SQL injection

2008-04-29 Thread PFC


For example, some applications need to replace whole phrases:

$criteria = WHERE $var1 = '$var2'

This is a very common approach for dynamic search screens, and really not
covered by placeholder approaches.


Python, again :

params = {
'column1': 10,
'column2': a st'ring,
}

where =  AND .join( %s=%%s % (key,value) for key,value in  
params.items() )

cursor.execute( SELECT * FROM table WHERE  + where, params )

I use the same approach (albeit more complicated) in PHP.

	For complex expressions you can play with arrays etc, it is not that  
difficult.

Or you just do :

$criteria = db_quote_query( WHERE $var1 = %s, array( $var2 ))

using the function I posted earlier.

	This supposes of course that $var1 which is the column name, comes from a  
known source, and not user input.
	In that case, $var1 will probably be the form field name, which means it  
is specified by the programmer a few lines prior in the code.





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

Re: [HACKERS] Protection from SQL injection

2008-04-29 Thread Thomas Mueller

Hi Martijn,

   The problem is not only quotes. The problem is all kinds of user
   input. For example: sql = SELECT * FROM ORDERS WHERE ORDER_ID =  +
   orderId; This is not a problem if orderId is a number. But what if
   it's a String? For example 1 AND (SELECT * FROM USERS WHERE
   NAME='admin' AND PASSWORD LIKE 'm%'). An attacker could then retrieve
   the admin password quite quickly.

  In other words, your programmer was stupid. And your example doesn't
  work because no matter what the string is it can't return anything
  other than rows from the orders table. If you're worried about them
  using semicolons to introduce another query, prepare has prohibited
  that for a long time already.

The attack goes as follows: WHERE ORDER_ID = 1 yields 1 rows. WHERE
ORDER_ID = 1 AND (SELECT * FROM USERS WHERE NAME='admin' AND PASSWORD
LIKE 'a%') yields 0 rows. OK that means that the admin password
doesn't start with an 'a'. If WHERE ORDER_ID = 1 AND (SELECT * FROM
USERS WHERE NAME='admin' AND PASSWORD LIKE 'b%') yields 1 row we know
the admin password starts with 'b'. For an average password length of
6 it takes 6 * 64 queries to get the password, plus some to get the
user name, plus maybe a few to get the table name and column name
correct.

  But as far as I'm concerned, the real killer is that it would make
  using any interactive query interface impossible.

No. Literals is an access right, and the interactive query tool may
have that access right. Let's say we have a APP_ROLE (for the
application itself) and a QUERY_ROLE. The default is literals are
enabled, that means the query tool can use literals. For the
application, the administrator may chooses to revoke the right to use
text and number literals using REVOKE LITERAL_TEXT, LITERAL_NUMBER
FROM APP_ROLE. Or the developer himself may want to try out if his
application is safe, and temporarily disables LITERAL_TEXT first. He
then runs the test cases and fixes the problems. Afterwards, he may
disable even LITERAL_NUMBER and try again. For production, maybe
literals are enabled.

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

Re: [HACKERS] Protection from SQL injection

2008-04-29 Thread Aidan Van Dyk

* Gregory Stark [EMAIL PROTECTED] [080429 14:20]:
 Aidan Van Dyk [EMAIL PROTECTED] writes:
 
  That said, though *I* like the idea (and since I develop against
  PostgreSQL 1st and use params for my queries I would consider it a nice
  tool to keep me honest), I can easily see that the cost/benefit ratio
  on this could be quite low and make it not worth the code/support
  necessary.
 
 Note that using parameters even for things which are actually constants is not
 really very desirable. If you have a query like:
 
 SELECT * FROM users WHERE userid = ? AND status = 'active'
 
 a) It makes things a lot clearer to when you call Execute($userid) which
values are actually the key user-provided data. In more complex queries it
can be quite confusing to have lots of parameters especially if the query
itself only makes sense if you know what values will be passed.
 
 b) It allows the database to take advantage of statistics on status that
might not otherwise be possible.
 
 Parameters are definitely the way to go for dynamic user data but for
 constants which are actually an integral part of the query and not parameters
 you're passing different values for each time it's actually clearer to include
 them directly in the query.

These are all things to consider.  I haven't (yet) needed a dynamic
query like that in my published apps because I would have a prepared
statement for the various status options, and my choice was to have a
couple prepared statements around instead of having a dynamic statement
thats re-planned on every query.

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 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.  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.

And so far the statistic/plan selection problems haven't made any of my
queries yet become performance problems...

Again, everything is relative.

a.

-- 
Aidan Van Dyk Create like a god,
[EMAIL PROTECTED]   command like a king,
http://www.highrise.ca/   work like a slave.


signature.asc
Description: Digital signature

Re: [HACKERS] [COMMITTERS] pgsql: Remove typename from A_Const.

2008-04-29 Thread Alvaro Herrera

Tom Lane escribió:
 [EMAIL PROTECTED] (Alvaro Herrera) writes:
  Remove typename from A_Const.
 
 I'm thinking this could be cleaned up further.  The patch as applied
 removes the ::int4 typename decoration that had been inserted by
 makeIntConst(), while leaving in place the ::float8 decoration inserted
 by makeFloatConst().  The kindest thing that can be said about that
 is that it's inconsistent.

That's very kind, yes :-)  I think that cast can be removed safely.

 Now as far as I can see in a look through
 gram.y, these routines (and makeAConst) were used only in places where
 the typename qualification was really unnecessary, that is typmods
 and GUC variable values and so on, not general expression contexts where
 we might really need to determine a data type for the constant.  So what
 I'm thinking is that we should get rid of the ::float8 decoration too,
 and thereby be able to revert some of the ugly code added elsewhere such
 as guc.c.

Hmm, I'm not sure but I think the typecast is needed in the guc.c code
in order to pass the fact that the Const is an Interval.  This is used
to process things such as 

set time zone interval '-8:30' ;

Perhaps this can be detected by some other mechanism but currently it's
being driven by the cast.

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

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

Re: [HACKERS] [COMMITTERS] pgsql: Remove typename from A_Const.

Alvaro Herrera [EMAIL PROTECTED] writes:
 Tom Lane escribió:
 I'm thinking is that we should get rid of the ::float8 decoration too,
 and thereby be able to revert some of the ugly code added elsewhere such
 as guc.c.

 Hmm, I'm not sure but I think the typecast is needed in the guc.c code
 in order to pass the fact that the Const is an Interval.

Yeah, I had just found that out --- it's not so much that we care that
it's an interval, as that the original input might have had typmod
restrictions that need to be applied to the interval value.  Definitely
a kluge to support a corner case ...

Anyway, I'm in the middle of testing removal of the ::float8 cast
--- we can at least simplify parse_type.c's handling of typmods here.
I also see that we can eliminate some manual construction of A_Const's
in gram.y, since makeFloatConst and friends no longer do any unwanted
extra stuff.

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

Re: [HACKERS] Protection from SQL injection

2008-04-29 Thread Thomas Mueller

Hi,

For PostgreSQL the 'disable literals' feature would be great
publicity: PostgreSQL would be the first only major database that has
a good story regarding SQL injection. Yes it's not the magic silver
bullet, but databases like MS SQL Server, Oracle or MySQL would look
really bad.

 [literals...] a permission that would default to on, but be REVOKE-able.

Exactly.

 Forbidding literals will break absolutely every SQL-using application on the 
 planet

Well, it's optional. If a developer or admin wants to use it, he will
know that it could mean some work. Even if the feature is not enabled,
it's still good to have it. And using constants will help document the
application.

 CREATE CONSTANT state_active TEXT VALUE 'active'; ? ;)

Not necessarily. The database knows that 'active' is a text, no need
to repeat that. Auto-detecting data types already works: CREATE TABLE
TEST AS SELECT 1 AS ID FROM DUAL will result in an int4. That's enough
for constants. But I don't mind using explicit data types.

 Note that using parameters even for things which are actually constants is 
 not really very desirable. If you have a query like: SELECT * FROM users 
 WHERE userid = ? AND status = 'active'

Using 'active' anyway is bad: Think about typos. The constant concept
(that exists in every language except SQL) would be good in any case:
SELECT * FROM users WHERE userid = ? AND status = STATUS_ACTIVE (or
CONST.STATUS_ACTIVE if it's in the CONST schema).

 libdejector

It's a good tool, but it's more work for the developer than disabling
literals (because for each query you need to add a exemplar).

 dynamic search screens
 $criteria = WHERE $var1 = '$var2'

In Java (sorry about that ;-) I would write:
PreparedStatement prep = conn.prepareStatement(SELECT * FROM ITEMS
WHERE  + var1 +  = ?);
prep.setString(1, var2);

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

Re: [HACKERS] Protection from SQL injection

2008-04-29 Thread Andrew Dunstan




Thomas Mueller wrote:

Forbidding literals will break absolutely every SQL-using application on the 
planet



Well, it's optional. If a developer or admin wants to use it, he will
know that it could mean some work. Even if the feature is not enabled,
it's still good to have it. And using constants will help document the
application.

  
  


What is not optional is the probably maintenance complexity of this scheme.

Moreover, it seems unlikely that it will even cover the field. A partial 
cloak might indeed be worse than none, in that it will give some 
developers an illusion of having security.


Before we embarked on such an enterprise, I would personally want to see 
fairly loud clamor from our user base for it.


cheers

andrew

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

Re: [HACKERS] Protection from SQL injection

2008-04-29 Thread Andrew Sullivan

On Tue, Apr 29, 2008 at 04:33:01PM -0400, Andrew Dunstan wrote:

 Moreover, it seems unlikely that it will even cover the field. A partial 
 cloak might indeed be worse than none, in that it will give some developers 
 an illusion of having security.

I think this is a really important point, and one that isn't getting
enough attention in this discussion.   Half a security measure is
almost always worse than none at all, exactly because people stop
thinking they have to worry about that area of security at all.  I
think without a convincing argument that the proposal will even come
close to covering most SQL injection cases, it's a bad idea.

A

-- 
Andrew Sullivan
[EMAIL PROTECTED]
+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

Re: [HACKERS] Protection from SQL injection

Thomas,

 For PostgreSQL the 'disable literals' feature would be great
 publicity: PostgreSQL would be the first only major database that has
 a good story regarding SQL injection. Yes it's not the magic silver
 bullet, but databases like MS SQL Server, Oracle or MySQL would look
 really bad.

Please don't let the debate over this break your enthusiasm for improving 
PostgreSQL security.  We really care about security, which is why we want 
to run your proposal throught the gauntlet.

You said you've done this for H2.  Isn't H2 only accessable through Java, 
though?  How many people are using literals in Java?

And, as of this week MSSQL already looks really bad.  300,000 worm-infected 
servers, and counting!

-- 
--Josh

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

Re: [HACKERS] Protection from SQL injection

2008-04-29 Thread Andrew Sullivan

[I know, I know, bad form]

On Tue, Apr 29, 2008 at 04:55:21PM -0400, Andrew Sullivan wrote:
 thinking they have to worry about that area of security at all.  I
 think without a convincing argument that the proposal will even come
 close to covering most SQL injection cases, it's a bad idea.

To be perfectly clear, I also think that the reverse is true: if a
fairly complete design was demonstrated to be possible such that it
covered just about every case, I'd be all for it.  (I sort of like the
suggestion up-thread, myself, which is to have a GUC that disables
multi-statement commands.  That'd probably cover a huge number of
cases, and combined with some sensible quoting rules in client
libraries, would quite possibly be enough.)

A

-- 
Andrew Sullivan
[EMAIL PROTECTED]
+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

Re: [HACKERS] Protection from SQL injection


 (I sort of like the
 suggestion up-thread, myself, which is to have a GUC that disables
 multi-statement commands.  That'd probably cover a huge number of
 cases, and combined with some sensible quoting rules in client
 libraries, would quite possibly be enough.)

MySQL did this already.

-- 
--Josh

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

Re: [HACKERS] Protection from SQL injection

2008-04-29 Thread Andreas 'ads' Scherbaum

On Tue, 29 Apr 2008 22:18:48 +0200 Thomas Mueller wrote:

 For PostgreSQL the 'disable literals' feature would be great
 publicity: PostgreSQL would be the first only major database that has
 a good story regarding SQL injection. Yes it's not the magic silver
 bullet, but databases like MS SQL Server, Oracle or MySQL would look
 really bad.

I don't think so.
Given the fact that enabling this feature by default would break almost
all applications, you have to disable this by default. No use here
because almost nobody will know about it. Oh, and i can see the
headlines: New PostgreSQL feature breaks 99% applications.


  Forbidding literals will break absolutely every SQL-using application on 
  the planet
 
 Well, it's optional. If a developer or admin wants to use it, he will
 know that it could mean some work.

The developers and admins who know about this feature and want to use
it are also the developers and admins who know about SQL injections.
Eventually the code quality produced by this ppl is higher than
average and less likely to have such basic faults.


 Even if the feature is not enabled, it's still good to have it.

Huh? How this?
Just because one can say We have a feature against SQL injections
which will not be used by literally anyone?


Kind regards

-- 
Andreas 'ads' Scherbaum
German PostgreSQL User Group

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

Re: [HACKERS] Protection from SQL injection

2008-04-29 Thread Gurjeet Singh

On Wed, Apr 30, 2008 at 1:48 AM, Thomas Mueller 
[EMAIL PROTECTED] wrote:

 Hi,

 For PostgreSQL the 'disable literals' feature would be great
 publicity:


'publicity' is something this community does not crave for, at least not
feature wise. If that were the case we would have had a million half-baked
features in Postgres by now.

-- 
[EMAIL PROTECTED]
[EMAIL PROTECTED] gmail | hotmail | indiatimes | yahoo }.com

EnterpriseDB http://www.enterprisedb.com

Mail sent from my BlackLaptop device

Re: [HACKERS] Protection from SQL injection

Josh Berkus [EMAIL PROTECTED] writes:

 (I sort of like the
 suggestion up-thread, myself, which is to have a GUC that disables
 multi-statement commands.  That'd probably cover a huge number of
 cases, and combined with some sensible quoting rules in client
 libraries, would quite possibly be enough.)

 MySQL did this already.

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

-- 
  Gregory Stark
  EnterpriseDB  http://www.enterprisedb.com
  Ask me about EnterpriseDB's Slony Replication support!

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

Re: [HACKERS] Protection from SQL injection

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

[HACKERS] Optimizer sorting an already sorted result

2008-04-29 Thread Gurjeet Singh

In the plan below, we can see that the optimizer is sorting an already
sorted result. It seems to forget the sort order across the UNIQUE node. My
question is, do we make any attempts in the optimizer to remember the sort
order of a result, to avoid any further sorting on same sort-key? If not,
can we do something about it?

postgres=# explain select * from del where ctid in ( select ('''(' || i ||
',' || j || ')''')::tid from generate_series( 0, 1) s1(i), generate_series(
1, 1 ) s2(j) );
   QUERY
PLAN

 Merge Join  (cost=177447.07..182043.29 rows=4 width=97)
   Merge Cond: ((('''('::text || (s1.i)::text) || ','::text) ||
(s2.j)::text) || ')'''::text))::tid) = del.ctid)
   -  Sort  (cost=155639.89..155739.89 rows=4 width=8)
 Sort Key: (('''('::text || (s1.i)::text) || ','::text) ||
(s2.j)::text) || ')'''::text))::tid)
 -  Unique  (cost=147032.84..152032.84 rows=4 width=8)
   -  Sort  (cost=147032.84..149532.84 rows=100 width=8)
 Sort Key: (('''('::text || (s1.i)::text) ||
','::text) || (s2.j)::text) || ')'''::text))::tid)
 -  Nested Loop  (cost=13.50..20026.00 rows=100
width=8)
   -  Function Scan on generate_series s1
(cost=0.00..12.50 rows=1000 width=4)
   -  Materialize  (cost=13.50..23.50 rows=1000
width=4)
 -  Function Scan on generate_series s2
(cost=0.00..12.50 rows=1000 width=4)
   -  Materialize  (cost=21807.19..23055.61 rows=99874 width=103)
 -  Sort  (cost=21807.19..22056.87 rows=99874 width=103)
   Sort Key: del.ctid
   -  Seq Scan on del  (cost=0.00..2586.74 rows=99874
width=103)
(15 rows)

Best regards,

-- 
[EMAIL PROTECTED]
[EMAIL PROTECTED] gmail | hotmail | indiatimes | yahoo }.com

EnterpriseDB http://www.enterprisedb.com

Mail sent from my BlackLaptop device

Re: [HACKERS] Optimizer sorting an already sorted result