Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.lang.javascript > #21974 > unrolled thread

Date to MS Access

Back to article view | Back to comp.lang.javascript

Contents

  Date to MS Access Andrew Poulos <ap_prog@hotmail.com> - 2013-11-12 11:05 +1100
    Re: Date to MS Access Andrew Poulos <ap_prog@hotmail.com> - 2013-11-12 11:34 +1100
    Re: Date to MS Access Christoph Michael Becker <cmbecker69@arcor.de> - 2013-11-12 01:35 +0100
    Re: Date to MS Access Thomas 'PointedEars' Lahn <PointedEars@web.de> - 2013-11-12 01:57 +0100
      Re: Date to MS Access Andrew Poulos <ap_prog@hotmail.com> - 2013-11-12 14:38 +1100
        Re: Date to MS Access Thomas 'PointedEars' Lahn <PointedEars@web.de> - 2013-11-12 13:21 +0100
          Re: Date to MS Access Andrew Poulos <ap_prog@hotmail.com> - 2013-11-12 23:42 +1100
    Re: Date to MS Access "Michael Haufe (TNO)" <tno@thenewobjective.com> - 2013-11-11 17:41 -0800

#21974 — Date to MS Access

I have an MS Access field of type Date/Time with no specific format set. 
Here Access dates appear in this type of format
   12/11/2013 7:57:46 AM

I need to take a date from the client side and pass it via a posted form 
to an ASP page running JScript.

On the client I have
   var nowDate = new Date();

On the server I have

var d = ... // nowDate retrieved from the form

var SQLstr = "UPDATE myTable " +
              "Set tbl_Date=#" + d +"# " +
              "WHERE myID=1;";

Running it though results in a "500: Internal Server Error".

How do I "format" the date on the client so that its valid SQL for Access?

Andrew Poulos

[toc] | [next] | [standalone]

#21975

On 12/11/2013 11:05 AM, Andrew Poulos wrote:
> I have an MS Access field of type Date/Time with no specific format set.
> Here Access dates appear in this type of format
>    12/11/2013 7:57:46 AM
>
> I need to take a date from the client side and pass it via a posted form
> to an ASP page running JScript.
>
> On the client I have
>    var nowDate = new Date();
>
> On the server I have
>
> var d = ... // nowDate retrieved from the form
>
> var SQLstr = "UPDATE myTable " +
>               "Set tbl_Date=#" + d +"# " +
>               "WHERE myID=1;";
>
> Running it though results in a "500: Internal Server Error".
>
> How do I "format" the date on the client so that its valid SQL for Access?

I resolved the 500 error (a syntax error in my code) but still I can't 
get nowDate into the table.

Andrew Poulos

[toc] | [prev] | [next] | [standalone]

#21976

Andrew Poulos wrote:

> I have an MS Access field of type Date/Time with no specific format set.
> Here Access dates appear in this type of format
>   12/11/2013 7:57:46 AM
> 
> I need to take a date from the client side and pass it via a posted form
> to an ASP page running JScript.
> 
> On the client I have
>   var nowDate = new Date();
> 
> On the server I have
> 
> var d = ... // nowDate retrieved from the form
> 
> var SQLstr = "UPDATE myTable " +
>              "Set tbl_Date=#" + d +"# " +
>              "WHERE myID=1;";
> 
> Running it though results in a "500: Internal Server Error".
> 
> How do I "format" the date on the client so that its valid SQL for Access?

It might be easier to format the date *server* side (especially
regarding validation of the user supplied input).

Anyway, how to format a Date value is demonstrated in the NG's FAQ[1].
ASP JScript might offer a more convenient method, though.

[1] <http://pointedears.de/scripts/faq/cljs/#formatDate>

-- 
Christoph M. Becker

[toc] | [prev] | [next] | [standalone]

#21977

Andrew Poulos wrote:

> I have an MS Access field of type Date/Time with no specific format set.
> Here Access dates appear in this type of format
>    12/11/2013 7:57:46 AM
> 
> I need to take a date from the client side and pass it via a posted form
> to an ASP page running JScript.
> 
> On the client I have
>    var nowDate = new Date();
> 
> On the server I have
> 
> var d = ... // nowDate retrieved from the form

The part that is missing from your question is how you get “nowDate” into 
the form.
 
> var SQLstr = "UPDATE myTable " +
>               "Set tbl_Date=#" + d +"# " +
>               "WHERE myID=1;";

Hopefully you clean your input so that SQL injection like

  d = "12/11/2013 7:57:46 AM#; DELETE myTable;"
    + " UPDATE myTable SET tbl_Date=12/11/2013 7:57:46 AM";

is prevented.  The proper approach is Prepared Statements, but apparently MS 
JetSQL does not support that; you could emulate it.
 
> Running it though results in a "500: Internal Server Error".
> 
> How do I "format" the date on the client so that its valid SQL for Access?

You could use Date.prototype.strftime("%d/%m/%Y %I:%M:%S %p") as provided by 
JSX:string/date.js [1] on the client side, but I would strongly recommend to 
pass a timestamp (Date.prototype.getTime()) and let the server-side code do 
the SQL formatting (in which case I would be interested to know if date.js 
worked there if there is no ASP-native way).

(Pseudo-)integers are safer, and you do not want to update the client-side 
code when you change the database on the server.

[1] <http://PointedEars.de/scripts/test/date>

-- 
PointedEars

Twitter: @PointedEars2
Please do not Cc: me. / Bitte keine Kopien per E-Mail.

[toc] | [prev] | [next] | [standalone]

#21981

On 12/11/2013 11:57 AM, Thomas 'PointedEars' Lahn wrote:
> Andrew Poulos wrote:
>
>> I have an MS Access field of type Date/Time with no specific format set.
>> Here Access dates appear in this type of format
>>     12/11/2013 7:57:46 AM
>>
>> I need to take a date from the client side and pass it via a posted form
>> to an ASP page running JScript.
>>
>> On the client I have
>>     var nowDate = new Date();
>>
>> On the server I have
>>
>> var d = ... // nowDate retrieved from the form
>
> The part that is missing from your question is how you get “nowDate” into
> the form.

I added nowDate.toISOString() as a value for a hidden input.

>> var SQLstr = "UPDATE myTable " +
>>                "Set tbl_Date=#" + d +"# " +
>>                "WHERE myID=1;";
>
> Hopefully you clean your input so that SQL injection like
>
>    d = "12/11/2013 7:57:46 AM#; DELETE myTable;"
>      + " UPDATE myTable SET tbl_Date=12/11/2013 7:57:46 AM";
>
> is prevented.  The proper approach is Prepared Statements, but apparently MS
> JetSQL does not support that; you could emulate it.

Yes.

>> Running it though results in a "500: Internal Server Error".
>>
>> How do I "format" the date on the client so that its valid SQL for Access?
>
> You could use Date.prototype.strftime("%d/%m/%Y %I:%M:%S %p") as provided by
> JSX:string/date.js [1] on the client side, but I would strongly recommend to
> pass a timestamp (Date.prototype.getTime()) and let the server-side code do
> the SQL formatting (in which case I would be interested to know if date.js
> worked there if there is no ASP-native way).

I starting testing the Access function CDATE and passing it (yyyy-mm-d 
hours:minutes:seconds) which seems cumbersome but I'll look into  using 
a timestamp instead.

> (Pseudo-)integers are safer, and you do not want to update the client-side
> code when you change the database on the server.

ACK. Thanks to everybody for their input.

Andrew Poulos

[toc] | [prev] | [next] | [standalone]

#21984

Andrew Poulos wrote:

> On 12/11/2013 11:57 AM, Thomas 'PointedEars' Lahn wrote:
>> Andrew Poulos wrote:
>>> I have an MS Access field of type Date/Time with no specific format set.
>>> Here Access dates appear in this type of format
>>>     12/11/2013 7:57:46 AM
>>>
>>> I need to take a date from the client side and pass it via a posted form
>>> to an ASP page running JScript.
>>>
>>> On the client I have
>>>     var nowDate = new Date();
>>>
>>> On the server I have
>>>
>>> var d = ... // nowDate retrieved from the form
>>
>> The part that is missing from your question is how you get “nowDate” into
>> the form.
> 
> I added nowDate.toISOString() as a value for a hidden input.

Can you rely on a conforming implementation of ECMAScript Ed. 5 or later?
 
> >> var SQLstr = "UPDATE myTable " +
> >>                "Set tbl_Date=#" + d +"# " +
> >>                "WHERE myID=1;";
> >
> > Hopefully you clean your input so that SQL injection like
> >
> >    d = "12/11/2013 7:57:46 AM#; DELETE myTable;"
> >      + " UPDATE myTable SET tbl_Date=12/11/2013 7:57:46 AM";
> >
> > is prevented.  The proper approach is Prepared Statements, but
> > apparently MS JetSQL does not support that; you could emulate it.
> 
> Yes.

Yes to what?

>>> Running it though results in a "500: Internal Server Error".
>>>
>>> How do I "format" the date on the client so that its valid SQL for
>>> Access?
>>
>> You could use Date.prototype.strftime("%d/%m/%Y %I:%M:%S %p") as provided
>> by JSX:string/date.js [1] on the client side, but I would strongly
>> recommend to pass a timestamp (Date.prototype.getTime()) and let the
>> server-side code do the SQL formatting (in which case I would be
>> interested to know if date.js worked there if there is no ASP-native
>> way).
> 
> I starting testing the Access function CDATE and passing it (yyyy-mm-d
> hours:minutes:seconds) which seems cumbersome

It also appears to be unsafe as it is still a string and code injection is 
possible.

> but I'll look into  using a timestamp instead.

ACK.
 
-- 
PointedEars

Twitter: @PointedEars2
Please do not Cc: me. / Bitte keine Kopien per E-Mail.

[toc] | [prev] | [next] | [standalone]

#21985

On 12/11/2013 11:21 PM, Thomas 'PointedEars' Lahn wrote:
> Andrew Poulos wrote:
>
>> On 12/11/2013 11:57 AM, Thomas 'PointedEars' Lahn wrote:
>>> Andrew Poulos wrote:
>>>> I have an MS Access field of type Date/Time with no specific format set.
>>>> Here Access dates appear in this type of format
>>>>      12/11/2013 7:57:46 AM
>>>>
>>>> I need to take a date from the client side and pass it via a posted form
>>>> to an ASP page running JScript.
>>>>
>>>> On the client I have
>>>>      var nowDate = new Date();
>>>>
>>>> On the server I have
>>>>
>>>> var d = ... // nowDate retrieved from the form
>>>
>>> The part that is missing from your question is how you get “nowDate” into
>>> the form.
>>
>> I added nowDate.toISOString() as a value for a hidden input.
>
> Can you rely on a conforming implementation of ECMAScript Ed. 5 or later?

On the client I should be able to.

>>>> var SQLstr = "UPDATE myTable " +
>>>>                 "Set tbl_Date=#" + d +"# " +
>>>>                 "WHERE myID=1;";
>>>
>>> Hopefully you clean your input so that SQL injection like
>>>
>>>     d = "12/11/2013 7:57:46 AM#; DELETE myTable;"
>>>       + " UPDATE myTable SET tbl_Date=12/11/2013 7:57:46 AM";
>>>
>>> is prevented.  The proper approach is Prepared Statements, but
>>> apparently MS JetSQL does not support that; you could emulate it.
>>
>> Yes.
>
> Yes to what?

Yes I clean my input but in any case I don't think that Access allows 
the batching of SQL statements.

>>>> Running it though results in a "500: Internal Server Error".
>>>>
>>>> How do I "format" the date on the client so that its valid SQL for
>>>> Access?
>>>
>>> You could use Date.prototype.strftime("%d/%m/%Y %I:%M:%S %p") as provided
>>> by JSX:string/date.js [1] on the client side, but I would strongly
>>> recommend to pass a timestamp (Date.prototype.getTime()) and let the
>>> server-side code do the SQL formatting (in which case I would be
>>> interested to know if date.js worked there if there is no ASP-native
>>> way).
>>
>> I starting testing the Access function CDATE and passing it (yyyy-mm-d
>> hours:minutes:seconds) which seems cumbersome
>
> It also appears to be unsafe as it is still a string and code injection is
> possible.

Ok, I'll make doubly sure that code injection is not possible.

Andrew Poulos

[toc] | [prev] | [next] | [standalone]

#21980

On Monday, November 11, 2013 6:05:53 PM UTC-6, Andrew Poulos wrote:
> I have an MS Access field of type Date/Time with no specific format set. 

> [...]

> How do I "format" the date on the client so that its valid SQL for Access?

Access has a built-in function "Format(...)" that takes a timestamp and a format string:

<https://office.microsoft.com/en-us/access-help/format-function-HA001228839.aspx>

Also, you might want to consider using a parameterized query to specify the types as you cannot trust the data from the client.

[toc] | [prev] | [standalone]

Back to top | Article view | comp.lang.javascript

csiph-web