Re: [HACKERS] ecmascript 5 DATESTYLE
On tis, 2011-12-06 at 15:15 -0500, Tom Lane wrote: TBH, I think that inventing a new datestyle setting ECMA would be a more appropriate investment of effort. So we'd have a setting called ECMA that's really ISO, and a setting called ISO that's really SQL, and a setting called SQL that's really Postgres, and a setting called Postgres that's also Postgres but different. -- 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] ecmascript 5 DATESTYLE
On Dec 7, 2011, at 3:56 PM, Peter Eisentraut wrote: On tis, 2011-12-06 at 15:15 -0500, Tom Lane wrote: TBH, I think that inventing a new datestyle setting ECMA would be a more appropriate investment of effort. So we'd have a setting called ECMA that's really ISO, and a setting called ISO that's really SQL, and a setting called SQL that's really Postgres, and a setting called Postgres that's also Postgres but different. ...and a setting called XSD that's also ISO. for now i'm backing away from the ECMA option - what i was thinking of would be exactly the same as XSD except rather than a timezone of '+00:00' it would be a 'Z'. from some quick searching, it seems that XSD should be capable of understanding 'Z' rather than '+00:00' so if i was going to do anything i'd work towards making that change to 'XSD'. however, as it turns out, the constraint i have that is requiring me to use 'Z' is not actually from ECMAScript 5 but from json-schema (http://tools.ietf.org/html/draft-zyp-json-schema-03#section-5.23 ). XSD is fully compatible with ECMAScript 5 date time string format (http://es5.github.com/#x15.9.1.15 ) so i'm going to sit on this again for a little while and think some more. maybe try to convince json-schema to relax their definition of date-time format. i'll be back when i have a clear picture of what i think makes the most sense. thanks, ben...
Re: [HACKERS] ecmascript 5 DATESTYLE
2011/12/7 ben hockey neonstalw...@gmail.com: On Dec 7, 2011, at 3:56 PM, Peter Eisentraut wrote: On tis, 2011-12-06 at 15:15 -0500, Tom Lane wrote: TBH, I think that inventing a new datestyle setting ECMA would be a more appropriate investment of effort. So we'd have a setting called ECMA that's really ISO, and a setting called ISO that's really SQL, and a setting called SQL that's really Postgres, and a setting called Postgres that's also Postgres but different. ...and a setting called XSD that's also ISO. for now i'm backing away from the ECMA option - what i was thinking of would be exactly the same as XSD except rather than a timezone of '+00:00' it would be a 'Z'. from some quick searching, it seems that XSD should be capable of understanding 'Z' rather than '+00:00' so if i was going to do anything i'd work towards making that change to 'XSD'. however, as it turns out, the constraint i have that is requiring me to use 'Z' is not actually from ECMAScript 5 but from json-schema (http://tools.ietf.org/html/draft-zyp-json-schema-03#section-5.23). XSD is fully compatible with ECMAScript 5 date time string format (http://es5.github.com/#x15.9.1.15) so i'm going to sit on this again for a little while and think some more. maybe try to convince json-schema to relax their definition of date-time format. i'll be back when i have a clear picture of what i think makes the most sense. please do it - we still would to have JSON support, so date style can be processed together Regards Pavel thanks, ben... -- 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] ecmascript 5 DATESTYLE
i know its been over a year without any activity on this thread but did anything ever come of this? i'd really like to be able to get dates to match the format specified for date time strings in ecmascript 5. a generic way to specify the format would be ideal if it can be done securely. has there been other threads discussing this more recently? thanks, ben... On Wed, May 19, 2010 at 6:21 AM, Pavel Stehule pavel.steh...@gmail.comwrote: I am not a security expert - you can simply don't allow apostrophe, double quotes - but I am not sure, if this can be safe - simply - I am abe to write this patch, but I am not able to ensure security. Regards Pavel
Re: [HACKERS] ecmascript 5 DATESTYLE
On Tue, Dec 6, 2011 at 1:11 PM, Ben Hockey neonstalw...@gmail.com wrote: i know its been over a year without any activity on this thread but did anything ever come of this? i'd really like to be able to get dates to match the format specified for date time strings in ecmascript 5. a generic way to specify the format would be ideal if it can be done securely. has there been other threads discussing this more recently? Not to my knowledge, though I don't read pgsql-general. I think this is the sort of thing that really only gets done if someone cares enough about it to settle down and put together a detailed design proposal, get consensus, and write a patch. IOW, it's unlikely that anyone else will do this for you, but you can certainly make a try at doing it yourself, and get help from others along the way. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] ecmascript 5 DATESTYLE
Robert Haas robertmh...@gmail.com writes: On Tue, Dec 6, 2011 at 1:11 PM, Ben Hockey neonstalw...@gmail.com wrote: i know its been over a year without any activity on this thread but did anything ever come of this? i'd really like to be able to get dates to match the format specified for date time strings in ecmascript 5. a generic way to specify the format would be ideal if it can be done securely. has there been other threads discussing this more recently? Not to my knowledge, though I don't read pgsql-general. I think this is the sort of thing that really only gets done if someone cares enough about it to settle down and put together a detailed design proposal, get consensus, and write a patch. IOW, it's unlikely that anyone else will do this for you, but you can certainly make a try at doing it yourself, and get help from others along the way. TBH, I think that inventing a new datestyle setting ECMA would be a more appropriate investment of effort. Generic format strings sound like a nightmare. Maybe I've just been turned off by the to_date/to_char mess, but I'm very down on the idea of anything like that propagating into the main datetime I/O code. 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] ecmascript 5 DATESTYLE
2011/12/6 Tom Lane t...@sss.pgh.pa.us: Robert Haas robertmh...@gmail.com writes: On Tue, Dec 6, 2011 at 1:11 PM, Ben Hockey neonstalw...@gmail.com wrote: i know its been over a year without any activity on this thread but did anything ever come of this? i'd really like to be able to get dates to match the format specified for date time strings in ecmascript 5. a generic way to specify the format would be ideal if it can be done securely. has there been other threads discussing this more recently? Not to my knowledge, though I don't read pgsql-general. I think this is the sort of thing that really only gets done if someone cares enough about it to settle down and put together a detailed design proposal, get consensus, and write a patch. IOW, it's unlikely that anyone else will do this for you, but you can certainly make a try at doing it yourself, and get help from others along the way. TBH, I think that inventing a new datestyle setting ECMA would be a more appropriate investment of effort. Generic format strings sound like a nightmare. Maybe I've just been turned off by the to_date/to_char mess, but I'm very down on the idea of anything like that propagating into the main datetime I/O code. I am for ECMA datestyle it is there but just is not public, if I remember well Theoretically some custom output/input transform routine can be very interesting - for domains, for boolean type - but on second hand - the usage of this feature is minimal and there is risk for less advanced users - so ECMA datestyle is very adequate solution. Regards Pavel 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] ecmascript 5 DATESTYLE
On 12/6/2011 3:20 PM, Pavel Stehule wrote: I am for ECMA datestyle it is there but just is not public, if I remember well Theoretically some custom output/input transform routine can be very interesting - for domains, for boolean type - but on second hand - the usage of this feature is minimal and there is risk for less advanced users - so ECMA datestyle is very adequate solution. Regards Pavel i don't particularly need anything other than ECMA datestyle - i was just under the impression that a more generic solution was preferred. so, ECMA is enough to stop me from making any more noise about this. pavel, is there a way i can use this currently? if not, would it take much effort to make this public? thanks, ben... -- 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] ecmascript 5 DATESTYLE
2011/12/6 ben hockey neonstalw...@gmail.com:
On 12/6/2011 3:20 PM, Pavel Stehule wrote:
I am for ECMA datestyle
it is there but just is not public, if I remember well
Theoretically some custom output/input transform routine can be very
interesting - for domains, for boolean type - but on second hand - the
usage of this feature is minimal and there is risk for less advanced
users - so ECMA datestyle is very adequate solution.
Regards
Pavel
i don't particularly need anything other than ECMA datestyle - i was just
under the impression that a more generic solution was preferred. so, ECMA
is enough to stop me from making any more noise about this.
pavel, is there a way i can use this currently? if not, would it take much
effort to make this public?
I am not sure, if this patch is 100% correct
but it does something
the name is not ECMA but XSD - I hope, so both formats are same
postgres=# set datestyle TO 'XSD';
SET
postgres=# select current_timestamp;
now
──
2011-12-06T21:50:34.142933+01:00
(1 row)
postgres=# select '2011-12-06T22:46:53.455866+01:00'::timestamp;
timestamp
2011-12-06T22:46:53.455866
(1 row)
but maybe this will be some more, if XSD format is not exact ECMA
Regards
Pavel
thanks,
ben...
*** ./src/backend/commands/variable.c.orig 2011-09-22 23:57:57.0 +0200
--- ./src/backend/commands/variable.c 2011-12-06 21:46:03.489229819 +0100
***
*** 123,128
--- 123,135
newDateOrder = DATEORDER_MDY;
have_order = true;
}
+ else if (pg_strcasecmp(tok, XSD) == 0)
+ {
+ if (have_style newDateStyle != USE_XSD_DATES)
+ ok = false; /* conflicting styles */
+ newDateStyle = USE_XSD_DATES;
+ have_style = true;
+ }
else if (pg_strcasecmp(tok, DEFAULT) == 0)
{
/*
***
*** 191,196
--- 198,206
case USE_GERMAN_DATES:
strcpy(result, German);
break;
+ case USE_XSD_DATES:
+ strcpy(result, XSD);
+ break;
default:
strcpy(result, Postgres);
break;
--
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] ecmascript 5 DATESTYLE
2011/12/6 ben hockey neonstalw...@gmail.com: On 12/6/2011 3:20 PM, Pavel Stehule wrote: I am for ECMA datestyle it is there but just is not public, if I remember well Theoretically some custom output/input transform routine can be very interesting - for domains, for boolean type - but on second hand - the usage of this feature is minimal and there is risk for less advanced users - so ECMA datestyle is very adequate solution. Regards Pavel i don't particularly need anything other than ECMA datestyle - i was just under the impression that a more generic solution was preferred. so, ECMA is enough to stop me from making any more noise about this. a general solution is not simple - there is possible a SQL injection and therefore result must be escaped, and it means some overhead else - is very common a good style to use functions to_char, to_date or to_timestamp functions. Then your application will be more robust. Using default datestyle is user friendly technique, but it can be source of some issues - is better don't use it for large and complex application. Regards Pavel pavel, is there a way i can use this currently? if not, would it take much effort to make this public? thanks, ben... -- 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] ecmascript 5 DATESTYLE
On 12/6/2011 3:53 PM, Pavel Stehule wrote:
I am not sure, if this patch is 100% correct
but it does something
the name is not ECMA but XSD - I hope, so both formats are same
that format works for me. in fact a simple test to see if it would do
what i hope for would be to open the developer console (firebug, web
developer, etc) of a browser and take the formatted output and pass it
to the Date constructor - eg using chrome:
var a = new Date('2011-12-06T22:46:53.455866+01:00');
Date
a.toDateString();
Tue Dec 06 2011
a.toTimeString();
16:46:53 GMT-0500 (Eastern Standard Time)
you can see that the Date was properly created with the time converted
to my local time zone. this would be of great help to anyone passing
data from postrgres to a web browser since it eliminates the need to
have to transform the format somewhere between the database and the browser.
i'm not familiar with the process of getting this feature added to
postgres - what needs to happen now?
ben...
--
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] ecmascript 5 DATESTYLE
2011/12/6 ben hockey neonstalw...@gmail.com:
On 12/6/2011 3:53 PM, Pavel Stehule wrote:
I am not sure, if this patch is 100% correct
but it does something
the name is not ECMA but XSD - I hope, so both formats are same
that format works for me. in fact a simple test to see if it would do what
i hope for would be to open the developer console (firebug, web developer,
etc) of a browser and take the formatted output and pass it to the Date
constructor - eg using chrome:
var a = new Date('2011-12-06T22:46:53.455866+01:00');
Date
a.toDateString();
Tue Dec 06 2011
a.toTimeString();
16:46:53 GMT-0500 (Eastern Standard Time)
you can see that the Date was properly created with the time converted to my
local time zone. this would be of great help to anyone passing data from
postrgres to a web browser since it eliminates the need to have to transform
the format somewhere between the database and the browser.
i'm not familiar with the process of getting this feature added to postgres
- what needs to happen now?
it can be in 9.2 (if will be accepted) - it will be release at summer 2012
http://wiki.postgresql.org/wiki/Submitting_a_Patch
Regards
Pavel Stehule
ben...
--
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] ecmascript 5 DATESTYLE
On 12/6/2011 4:19 PM, Pavel Stehule wrote: it can be in 9.2 (if will be accepted) - it will be release at summer 2012 http://wiki.postgresql.org/wiki/Submitting_a_Patch Regards Pavel Stehule ok, so i assume your patch is now considered submitted and is waiting to be reviewed. i'll wait to see what happens. thanks, ben... -- 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] ecmascript 5 DATESTYLE
2011/12/6 ben hockey neonstalw...@gmail.com: On 12/6/2011 4:19 PM, Pavel Stehule wrote: it can be in 9.2 (if will be accepted) - it will be release at summer 2012 http://wiki.postgresql.org/wiki/Submitting_a_Patch Regards Pavel Stehule ok, so i assume your patch is now considered submitted and is waiting to be reviewed. i'll wait to see what happens. not yet :) there should be proposal, and maybe more hacking - documentation is missing, there are no regression tests. It needs half of day. Patch that I sent you is just VIP patch Regards Pavel thanks, ben... -- 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] ecmascript 5 DATESTYLE
i may have spoken a little too soon about the format being right... i just took a look at the postgres source code and it would need one more change to completely meet my needs. EncodeDateTime should put a 'Z' for UTC timezone rather than '+0'. with this being the case, do you think there would need to be an ECMA datestyle or would XSD be compatible with this change? i haven't touched any c code in quite a while but the changes look simple enough that i could work towards an ECMA patch if that's the best way to go about this. thanks, ben... -- 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] ecmascript 5 DATESTYLE
Hello 2011/12/6 ben hockey neonstalw...@gmail.com: i may have spoken a little too soon about the format being right... i just took a look at the postgres source code and it would need one more change to completely meet my needs. EncodeDateTime should put a 'Z' for UTC timezone rather than '+0'. with this being the case, do you think there would need to be an ECMA datestyle or would XSD be compatible with this change? probably we can't to change a XSD format - but new format, that is exactly ECMA should not be problem. i haven't touched any c code in quite a while but the changes look simple enough that i could work towards an ECMA patch if that's the best way to go about this. you can become a postgreql's hacker :) - anybody starts on simple jobs PostgreSQL hacking is good lecture Pavel thanks, ben... -- 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] ecmascript 5 DATESTYLE
On May 19, 2010, at 4:31 AM, Mike Fowler wrote: Pavel Stehule wrote: 2010/5/19 Peter Eisentraut pete...@gmx.net: On tis, 2010-05-18 at 18:26 -0400, Ben Hockey wrote: ecmascript 5 is the most recent specification for JavaScript and i would think that having a DATESTYLE format to simplify interoperability with JavaScript applications would be highly desirable. Note that we haven't got any other datestyles that are intended to support interoperability with some language. It is usually the job of the client driver to convert PostgreSQL data (plural of datum) to the appropriate type and format for the client environment or language. Is there any reason why JavaScript would be different? I wouldn't be keen to see dedicated language specific handling of date/datetime formats. It would lead to an explosion of functions with new languages needing adding as and when their users jumped up and down on us. However a generic format could be very useful and would give the opportunity for people who need a language specific short cut the opportunity to do a CREATE FUNCTION wrapping the generic one with a hard coded format specifier. Other platforms have generic support for this kind of task, for example SQLServer: http://msdn.microsoft.com/en-us/library/ms187928.aspx . I wouldn't recommend the SQLServer way, I think numeric format specifiers are clumsy. Perhaps a mechanism like Java which is nicely summarized here: http://java.sun.com/j2se/1.5.0/docs/api/java/text/SimpleDateFormat.html i think that http://unicode.org/reports/tr35/#Date_Format_Patterns is very similar to (maybe the same as) the java simple date format but the unicode link gives a more complete explanation of exactly how the formatters will be interpreted - ie y will represent the full representation of the year but yy will represent the 2 digit form of the year, etc.. just thought i'd share the reference since it provides a powerful way to generically specify date formats and is possibly something that many people might already be familiar with. thanks for looking into adding this feature. custom formats for parsing and formatting of dates would certainly be the better option if it can be done securely. thanks, ben.. -- 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] ecmascript 5 DATESTYLE
On Thu, May 20, 2010 at 9:25 PM, Ben Hockey neonstalw...@gmail.com wrote: thanks for looking into adding this feature. custom formats for parsing and formatting of dates would certainly be the better option if it can be done securely. Well, Pavel expressed a concern about SQL injection, but I can't see why that would be a problem. If having multiple date formats is insecure, then we are already insecure. If it's not, then I don't see why having user-definable formats would be any more insecure than having formats from a fixed list. In any case, I can't see the connection to SQL injection - it seems like the worst case scenario is that some client gets confused about what the date format is and some dates get misinterpreted. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- 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] ecmascript 5 DATESTYLE
On tis, 2010-05-18 at 18:26 -0400, Ben Hockey wrote: ecmascript 5 is the most recent specification for JavaScript and i would think that having a DATESTYLE format to simplify interoperability with JavaScript applications would be highly desirable. Note that we haven't got any other datestyles that are intended to support interoperability with some language. It is usually the job of the client driver to convert PostgreSQL data (plural of datum) to the appropriate type and format for the client environment or language. Is there any reason why JavaScript would be different? -- 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] ecmascript 5 DATESTYLE
2010/5/19 Peter Eisentraut pete...@gmx.net: On tis, 2010-05-18 at 18:26 -0400, Ben Hockey wrote: ecmascript 5 is the most recent specification for JavaScript and i would think that having a DATESTYLE format to simplify interoperability with JavaScript applications would be highly desirable. Note that we haven't got any other datestyles that are intended to support interoperability with some language. It is usually the job of the client driver to convert PostgreSQL data (plural of datum) to the appropriate type and format for the client environment or language. Is there any reason why JavaScript would be different? JavaScript isn't special language, but JSON is wide used format for interoperability. And same is true for XML datestyle format. Regards Pavel -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- 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] ecmascript 5 DATESTYLE
Pavel Stehule wrote: 2010/5/19 Peter Eisentraut pete...@gmx.net: On tis, 2010-05-18 at 18:26 -0400, Ben Hockey wrote: ecmascript 5 is the most recent specification for JavaScript and i would think that having a DATESTYLE format to simplify interoperability with JavaScript applications would be highly desirable. Note that we haven't got any other datestyles that are intended to support interoperability with some language. It is usually the job of the client driver to convert PostgreSQL data (plural of datum) to the appropriate type and format for the client environment or language. Is there any reason why JavaScript would be different? I wouldn't be keen to see dedicated language specific handling of date/datetime formats. It would lead to an explosion of functions with new languages needing adding as and when their users jumped up and down on us. However a generic format could be very useful and would give the opportunity for people who need a language specific short cut the opportunity to do a CREATE FUNCTION wrapping the generic one with a hard coded format specifier. Other platforms have generic support for this kind of task, for example SQLServer: http://msdn.microsoft.com/en-us/library/ms187928.aspx. I wouldn't recommend the SQLServer way, I think numeric format specifiers are clumsy. Perhaps a mechanism like Java which is nicely summarized here: http://java.sun.com/j2se/1.5.0/docs/api/java/text/SimpleDateFormat.html Pavel: Why do you believe a generic format function would lead to SQL injections attacks? JavaScript isn't special language, but JSON is wide used format for interoperability. And same is true for XML datestyle format. Regards Pavel I think that the postgres handling of those data types should handle the date encoding themselves. For example, a XMLELEMENT call that was passed a date would format the date string to the xs:date format (e.g. 2010-05-19) and when passed a timestamp format to xs:datetime (e.g. 2010-05-19T09:29:52+01:00). I would see the JSON handling as being no different. Thanks, -- Mike Fowler Registered Linux user: 379787 I could be a genius if I just put my mind to it, and I, I could do anything, if only I could get 'round to it -PULP 'Glory Days' -- 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] ecmascript 5 DATESTYLE
2010/5/19 Mike Fowler m...@mlfowler.com: Pavel Stehule wrote: 2010/5/19 Peter Eisentraut pete...@gmx.net: On tis, 2010-05-18 at 18:26 -0400, Ben Hockey wrote: ecmascript 5 is the most recent specification for JavaScript and i would think that having a DATESTYLE format to simplify interoperability with JavaScript applications would be highly desirable. Note that we haven't got any other datestyles that are intended to support interoperability with some language. It is usually the job of the client driver to convert PostgreSQL data (plural of datum) to the appropriate type and format for the client environment or language. Is there any reason why JavaScript would be different? I wouldn't be keen to see dedicated language specific handling of date/datetime formats. It would lead to an explosion of functions with new languages needing adding as and when their users jumped up and down on us. However a generic format could be very useful and would give the opportunity for people who need a language specific short cut the opportunity to do a CREATE FUNCTION wrapping the generic one with a hard coded format specifier. Other platforms have generic support for this kind of task, for example SQLServer: http://msdn.microsoft.com/en-us/library/ms187928.aspx. I wouldn't recommend the SQLServer way, I think numeric format specifiers are clumsy. Perhaps a mechanism like Java which is nicely summarized here: http://java.sun.com/j2se/1.5.0/docs/api/java/text/SimpleDateFormat.html Pavel: Why do you believe a generic format function would lead to SQL injections attacks? see google: lateral sql injection oracle NLS_DATE_FORMAT I would to like this functionality too - and technically I don't see a problem - It's less than 100 lines, but I don't need a new security problem. So my proposal is change nothing on this integrated functionality and add new custom date type - like cdate that can be customized via GUC. Regards Pavel JavaScript isn't special language, but JSON is wide used format for interoperability. And same is true for XML datestyle format. Regards Pavel I think that the postgres handling of those data types should handle the date encoding themselves. For example, a XMLELEMENT call that was passed a date would format the date string to the xs:date format (e.g. 2010-05-19) and when passed a timestamp format to xs:datetime (e.g. 2010-05-19T09:29:52+01:00). I would see the JSON handling as being no different. Thanks, -- Mike Fowler Registered Linux user: 379787 I could be a genius if I just put my mind to it, and I, I could do anything, if only I could get 'round to it -PULP 'Glory Days' -- 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] ecmascript 5 DATESTYLE
Pavel Stehule wrote: see google: lateral sql injection oracle NLS_DATE_FORMAT I would to like this functionality too - and technically I don't see a problem - It's less than 100 lines, but I don't need a new security problem. So my proposal is change nothing on this integrated functionality and add new custom date type - like cdate that can be customized via GUC. Regards Pavel OK I found www.databasesecurity.com/dbsec/lateral-sql-injection.pdf. From the way I read this, the exploit relies on adjusting the NLS_DATE_FORMAT to an arbitrary string which is then used for the attack, To me this is easy to code against, simply lock the date format right down and ensure that it is always controlled. IMHO I don't see an Oracle specific attack as a reason why we can't have a generic format. Surely we can learn from this known vulnerability and get another one up on Oracle? Thanks, -- Mike Fowler Registered Linux user: 379787 I could be a genius if I just put my mind to it, and I, I could do anything, if only I could get 'round to it -PULP 'Glory Days' -- 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] ecmascript 5 DATESTYLE
2010/5/19 Mike Fowler m...@mlfowler.com: Pavel Stehule wrote: see google: lateral sql injection oracle NLS_DATE_FORMAT I would to like this functionality too - and technically I don't see a problem - It's less than 100 lines, but I don't need a new security problem. So my proposal is change nothing on this integrated functionality and add new custom date type - like cdate that can be customized via GUC. Regards Pavel OK I found www.databasesecurity.com/dbsec/lateral-sql-injection.pdf. From the way I read this, the exploit relies on adjusting the NLS_DATE_FORMAT to an arbitrary string which is then used for the attack, To me this is easy to code against, simply lock the date format right down and ensure that it is always controlled. IMHO I don't see an Oracle specific attack as a reason why we can't have a generic format. Surely we can learn from this known vulnerability and get another one up on Oracle? I am not a security expert - you can simply don't allow apostrophe, double quotes - but I am not sure, if this can be safe - simply - I am abe to write this patch, but I am not able to ensure security. Regards Pavel Thanks, -- Mike Fowler Registered Linux user: 379787 I could be a genius if I just put my mind to it, and I, I could do anything, if only I could get 'round to it -PULP 'Glory Days' -- 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] ecmascript 5 DATESTYLE
Pavel Stehule wrote: 2010/5/19 Mike Fowler m...@mlfowler.com: Pavel Stehule wrote: see google: lateral sql injection oracle NLS_DATE_FORMAT I would to like this functionality too - and technically I don't see a problem - It's less than 100 lines, but I don't need a new security problem. So my proposal is change nothing on this integrated functionality and add new custom date type - like cdate that can be customized via GUC. Regards Pavel OK I found www.databasesecurity.com/dbsec/lateral-sql-injection.pdf. From the way I read this, the exploit relies on adjusting the NLS_DATE_FORMAT to an arbitrary string which is then used for the attack, To me this is easy to code against, simply lock the date format right down and ensure that it is always controlled. IMHO I don't see an Oracle specific attack as a reason why we can't have a generic format. Surely we can learn from this known vulnerability and get another one up on Oracle? I am not a security expert - you can simply don't allow apostrophe, double quotes - but I am not sure, if this can be safe - simply - I am abe to write this patch, but I am not able to ensure security. Regards Pavel Well you've rightly identified a potential security hole, so my recommendation would be to put the patch together bearing in mind the Oracle vulnerability. Once you've submitted the patch it can be reviewed and we can ensure that you've managed to steer clear of introducing the same/similar vulnerability into postgres. Am I right in thinking that you're now proposing to do the generic patch that Robert Haas and I prefer? Thanks, -- Mike Fowler Registered Linux user: 379787 -- 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] ecmascript 5 DATESTYLE
2010/5/19 Mike Fowler m...@mlfowler.com: Pavel Stehule wrote: 2010/5/19 Mike Fowler m...@mlfowler.com: Pavel Stehule wrote: see google: lateral sql injection oracle NLS_DATE_FORMAT I would to like this functionality too - and technically I don't see a problem - It's less than 100 lines, but I don't need a new security problem. So my proposal is change nothing on this integrated functionality and add new custom date type - like cdate that can be customized via GUC. Regards Pavel OK I found www.databasesecurity.com/dbsec/lateral-sql-injection.pdf. From the way I read this, the exploit relies on adjusting the NLS_DATE_FORMAT to an arbitrary string which is then used for the attack, To me this is easy to code against, simply lock the date format right down and ensure that it is always controlled. IMHO I don't see an Oracle specific attack as a reason why we can't have a generic format. Surely we can learn from this known vulnerability and get another one up on Oracle? I am not a security expert - you can simply don't allow apostrophe, double quotes - but I am not sure, if this can be safe - simply - I am abe to write this patch, but I am not able to ensure security. Regards Pavel Well you've rightly identified a potential security hole, so my recommendation would be to put the patch together bearing in mind the Oracle vulnerability. Once you've submitted the patch it can be reviewed and we can ensure that you've managed to steer clear of introducing the same/similar vulnerability into postgres. Am I right in thinking that you're now proposing to do the generic patch that Robert Haas and I prefer? I'll look on code and I'll see Pavel Thanks, -- Mike Fowler Registered Linux user: 379787 -- 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] ecmascript 5 DATESTYLE
On Tue, May 18, 2010 at 6:26 PM, Ben Hockey neonstalw...@gmail.com wrote: hi, i mistakenly had posted this to pgsql-bugs already and got a response (see below - edited). i'm posting here since afaik it is the way i should be requesting new features. my suggestion is to add a DATESTYLE format to match the format specified for date time strings in ecmascript 5. the following is from the ecmascript 5 specification at http://www.ecmascript.org/docs/tc39-2009-043.pdf page 168: 15.9.1.15 Date Time String Format ECMAScript defines a string interchange format for date-times based upon a simplification of the ISO 8601 Extended Format. The format is as follows: -MM-DDTHH:mm:ss.sssZ ecmascript 5 is the most recent specification for JavaScript and i would think that having a DATESTYLE format to simplify interoperability with JavaScript applications would be highly desirable. I don't object, if someone wants to write a patch. I guess the question is whether to keep adding named formats, or try to create a general mechanism to allow the user to specify an arbitrary format, as we do with to_char(). -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- 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] ecmascript 5 DATESTYLE
2010/5/19 Robert Haas robertmh...@gmail.com: On Tue, May 18, 2010 at 6:26 PM, Ben Hockey neonstalw...@gmail.com wrote: hi, i mistakenly had posted this to pgsql-bugs already and got a response (see below - edited). i'm posting here since afaik it is the way i should be requesting new features. my suggestion is to add a DATESTYLE format to match the format specified for date time strings in ecmascript 5. the following is from the ecmascript 5 specification at http://www.ecmascript.org/docs/tc39-2009-043.pdf page 168: 15.9.1.15 Date Time String Format ECMAScript defines a string interchange format for date-times based upon a simplification of the ISO 8601 Extended Format. The format is as follows: -MM-DDTHH:mm:ss.sssZ ecmascript 5 is the most recent specification for JavaScript and i would think that having a DATESTYLE format to simplify interoperability with JavaScript applications would be highly desirable. I don't object, if someone wants to write a patch. I guess the question is whether to keep adding named formats, or try to create a general mechanism to allow the user to specify an arbitrary format, as we do with to_char(). I can write patch. I am against to general solution - It can be new way for SQL injection. Regards Pavel -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
