Hey there,
I'm trying to add rows to a table and pass a date value through. I use Format(Date.now, "dd MMM yyyy") however when I check in SQL Server Managment Studio it has swapped the day and month round (e.g. input of "7/9/2006" will be "9/7/2006" on the actual table. Actually the output is 2006-07-09 00:00:00:00.) I've put the full source code below.
Can anyone help with this?
Cheers,
Danny
Imports System.Data
Imports System.Data.SqlClient
Public Class Form1
Dim dtdate As Date
Dim cn As New SqlConnection("server=*********;database=********;USER ID=*********;password=*********")
Dim objCommand As New SqlCommand("", cn)
Dim ws As New wrtRefID.Service
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
dtdate = Now
AddHistory("ActivityID", 1, "AccountID", "AccountName", "ContactName", "Category", Microsoft.VisualBasic.Format(dtdate, "dd MMM yyyy"), 2, "Description", "UserName", Microsoft.VisualBasic.Format(dtdate, "dd MMM yyyy"), "CreateUser", "Notes", "LongNotes")
MessageBox.Show("DONE")
End Sub
Private Sub AddHistory(ByVal ActivityID As String, ByVal Type As Integer, ByVal AccountID As String, _
ByVal AccountName As String, ByVal ContactName As String, ByVal Category As String, ByVal StartDate As Date, _
ByVal Duration As Integer, ByVal Description As String, ByVal UserName As String, _
ByVal CreateDate As Date, ByVal CreateUser As String, ByVal Notes As String, ByVal LongNotes As String)
cn.Open()
objCommand.CommandText = "INSERT INTO HISTORY (HISTORYID, ACTIVITYID, Type, ACCOUNTID, ACCOUNTNAME, CONTACTNAME, " & _
"CATEGORY, STARTDATE, DURATION, DESCRIPTION, USERNAME, CREATEDATE, CREATEUSER, NOTES, LONGNOTES) " & _
"VALUES ('" & ws.MCS2_SLXID("HISTORY") & "', " & "'" & ActivityID & "', '" & Type & "', '" & AccountID & "', '" & AccountName & "', '" & ContactName & _
"', '" & Category & "', '" & StartDate & "', '" & Duration & "', '" & Description & "', '" & UserName & _
"', '" & CreateDate & "', '" & CreateUser & "', '" & Notes & "', '" & LongNotes & "')"
' MessageBox.Show(Format(dtdate, "dd MMM yyyy"))
TextBox1.Text = objCommand.CommandText
objCommand.ExecuteNonQuery()
cn.Close()
End Sub
End Class
Before explaining how to fix the date issue, it's important
to point out that your code may be vulnerable to a damaging
SQL injection attack. If the USER ID here has permission
to do more than insert rows into the table HISTORY, and if
you are not validating input in any way on the client side,
you are in trouble. A malicious user of your application
could enter into LongNotes, for example, a single quote
character followed by any SQL statement he or she wants to
run (like DELETE FROM HISTORY, or much worse, if this session
is opened by the sa account). See this article for more
information: www.sommarskog.se/dynamic_sql.html, or search
the web for "SQL injection."
Please, please, please, always use parameterized queries
or SQL stored procedures (and in the latter case, not
ones that just concatenate user input themselves).
You may be validating input on the client side or using
an account with limited privilege, in which case you should
at least add a comment in your code to note that this code
relies on security to be implemented elsewhere. However,
in my opinion, client-side-only security for this sort
of thing is not sufficient. Just to give one example,
some web sites will enforce what is typed into a form,
but overlook the fact that the form may not be the only
place to supply parameter values - direct specification
in the URL might be, too, and those values won't be
validated.
The best way to pass a date is through a parameterized
query or stored procedure, in which case you don't have
to use any format - you just pass it as a datetime value,
such as a SqlDateTime.
If you must pass your date as a string (and it's unlikely you
must, but it's a quicker, if more reckless, fix from what
you have now than to address the security and use parameters),
use the one date-only string format that SQL Server will
interpret consistently across culture settings: 'yyyymmdd'.
Be sure it's the string 'yyyymmdd', and not the integer yyyymmdd.
The only other safe format is the full datetime format
'YYYY-MM-DDTHH:MM:SS.mmm' (including the T character).
Vulnerable code like this is all too common, and I want
to say something whenever I see it.
Steve Kass
Drew University
http://www.stevekass.com
JediDanny@.discussions.microsoft.com wrote:
> This post has been edited either by the author or a moderator in the
> Microsoft Forums: http://forums.microsoft.com
>
> Hey there,
>
> I'm trying to add rows to a table and pass a date value through. I use
> Format(Date.now, "dd MMM yyyy") however when I check in SQL Server
> Managment Studio it has swapped the day and month round (e.g. input of
> "7/9/2006" will be "9/7/2006" on the actual table. Actually the output
> is 2006-07-09 00:00:00:00.) I've put the full source code below.
>
> Can anyone help with this?
>
> Cheers,
>
> Danny
>
>
>
> Imports System.Data
>
> Imports System.Data.SqlClient
>
> Public Class Form1
>
> Dim dtdate As Date
>
> Dim cn As New SqlConnection("server=*********;database=********;USER
> ID=*********;password=*********")
>
> Dim objCommand As New SqlCommand("", cn)
>
> Dim ws As New wrtRefID.Service
>
> Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
> System.EventArgs) Handles Button1.Click
>
> dtdate = Now
>
> AddHistory("ActivityID", 1, "AccountID", "AccountName", "ContactName",
> "Category", Microsoft.VisualBasic.Format(dtdate, "dd MMM yyyy"), 2,
> "Description", "UserName", Microsoft.VisualBasic.Format(dtdate, "dd MMM
> yyyy"), "CreateUser", "Notes", "LongNotes")
>
> MessageBox.Show("DONE")
>
> End Sub
>
> Private Sub AddHistory(ByVal ActivityID As String, ByVal Type As
> Integer, ByVal AccountID As String, _
>
> ByVal AccountName As String, ByVal ContactName As String, ByVal Category
> As String, ByVal StartDate As Date, _
>
> ByVal Duration As Integer, ByVal Description As String, ByVal UserName
> As String, _
>
> ByVal CreateDate As Date, ByVal CreateUser As String, ByVal Notes As
> String, ByVal LongNotes As String)
>
> cn.Open()
>
> objCommand.CommandText = "INSERT INTO HISTORY (HISTORYID, ACTIVITYID,
> Type, ACCOUNTID, ACCOUNTNAME, CONTACTNAME, " & _
>
> "CATEGORY, STARTDATE, DURATION, DESCRIPTION, USERNAME, CREATEDATE,
> CREATEUSER, NOTES, LONGNOTES) " & _
>
> "VALUES ('" & ws.MCS2_SLXID("HISTORY") & "', " & "'" & ActivityID & "',
> '" & Type & "', '" & AccountID & "', '" & AccountName & "', '" &
> ContactName & _
>
> "', '" & Category & "', '" & StartDate & "', '" & Duration & "', '" &
> Description & "', '" & UserName & _
>
> "', '" & CreateDate & "', '" & CreateUser & "', '" & Notes & "', '" &
> LongNotes & "')"
>
> ' MessageBox.Show(Format(dtdate, "dd MMM yyyy"))
>
> TextBox1.Text = objCommand.CommandText
>
> objCommand.ExecuteNonQuery()
>
> cn.Close()
>
> End Sub
>
> End Class
>
>
|||It's doing exactly what you told it to do.
Your format string is saying day + month + year (ddMMyyyy) This is not wrong. You're reading it wrong.
If you want it to say month + day + year then write the format string thataway (MMddyyyy)
Adamus
|||WTF? lol|||NNTP User wrote:
Before explaining how to fix the date issue, it's important
to point out that your code may be vulnerable to a damaging
SQL injection attack. If the USER ID here has permission
to do more than insert rows into the table HISTORY, and if
you are not validating input in any way on the client side,
you are in trouble. A malicious user of your application
could enter into LongNotes, for example, a single quote
character followed by any SQL statement he or she wants to
run (like DELETE FROM HISTORY, or much worse, if this session
is opened by the sa account). See this article for more
information: www.sommarskog.se/dynamic_sql.html, or search
the web for "SQL injection."
Please, please, please, always use parameterized queries
or SQL stored procedures (and in the latter case, not
ones that just concatenate user input themselves).
You may be validating input on the client side or using
an account with limited privilege, in which case you should
at least add a comment in your code to note that this code
relies on security to be implemented elsewhere. However,
in my opinion, client-side-only security for this sort
of thing is not sufficient. Just to give one example,
some web sites will enforce what is typed into a form,
but overlook the fact that the form may not be the only
place to supply parameter values - direct specification
in the URL might be, too, and those values won't be
validated.
The best way to pass a date is through a parameterized
query or stored procedure, in which case you don't have
to use any format - you just pass it as a datetime value,
such as a SqlDateTime.
If you must pass your date as a string (and it's unlikely you
must, but it's a quicker, if more reckless, fix from what
you have now than to address the security and use parameters),
use the one date-only string format that SQL Server will
interpret consistently across culture settings: 'yyyymmdd'.
Be sure it's the string 'yyyymmdd', and not the integer yyyymmdd.
The only other safe format is the full datetime format
'YYYY-MM-DDTHH:MM:SS.mmm' (including the T character).
Vulnerable code like this is all too common, and I want
to say something whenever I see it.
Steve Kass
Drew University
http://www.stevekass.com
JediDanny@.discussions.microsoft.com wrote:
> This post has been edited either by the author or a moderator in the
> Microsoft Forums: http://forums.microsoft.com
>
> Hey there,
>
> I'm trying to add rows to a table and pass a date value through. I use
> Format(Date.now, "dd MMM yyyy") however when I check in SQL Server
> Managment Studio it has swapped the day and month round (e.g. input of
> "7/9/2006" will be "9/7/2006" on the actual table. Actually the output
> is 2006-07-09 00:00:00:00.) I've put the full source code below.
>
> Can anyone help with this?
>
> Cheers,
>
> Danny
>
>
>
> Imports System.Data
>
> Imports System.Data.SqlClient
>
> Public Class Form1
>
> Dim dtdate As Date
>
> Dim cn As New SqlConnection("server=*********;database=********;USER
> ID=*********;password=*********")
>
> Dim objCommand As New SqlCommand("", cn)
>
> Dim ws As New wrtRefID.Service
>
> Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
> System.EventArgs) Handles Button1.Click
>
> dtdate = Now
>
> AddHistory("ActivityID", 1, "AccountID", "AccountName", "ContactName",
> "Category", Microsoft.VisualBasic.Format(dtdate, "dd MMM yyyy"), 2,
> "Description", "UserName", Microsoft.VisualBasic.Format(dtdate, "dd MMM
> yyyy"), "CreateUser", "Notes", "LongNotes")
>
> MessageBox.Show("DONE")
>
> End Sub
>
> Private Sub AddHistory(ByVal ActivityID As String, ByVal Type As
> Integer, ByVal AccountID As String, _
>
> ByVal AccountName As String, ByVal ContactName As String, ByVal Category
> As String, ByVal StartDate As Date, _
>
> ByVal Duration As Integer, ByVal Description As String, ByVal UserName
> As String, _
>
> ByVal CreateDate As Date, ByVal CreateUser As String, ByVal Notes As
> String, ByVal LongNotes As String)
>
> cn.Open()
>
> objCommand.CommandText = "INSERT INTO HISTORY (HISTORYID, ACTIVITYID,
> Type, ACCOUNTID, ACCOUNTNAME, CONTACTNAME, " & _
>
> "CATEGORY, STARTDATE, DURATION, DESCRIPTION, USERNAME, CREATEDATE,
> CREATEUSER, NOTES, LONGNOTES) " & _
>
> "VALUES ('" & ws.MCS2_SLXID("HISTORY") & "', " & "'" & ActivityID & "',
> '" & Type & "', '" & AccountID & "', '" & AccountName & "', '" &
> ContactName & _
>
> "', '" & Category & "', '" & StartDate & "', '" & Duration & "', '" &
> Description & "', '" & UserName & _
>
> "', '" & CreateDate & "', '" & CreateUser & "', '" & Notes & "', '" &
> LongNotes & "')"
>
> ' MessageBox.Show(Format(dtdate, "dd MMM yyyy"))
>
> TextBox1.Text = objCommand.CommandText
>
> objCommand.ExecuteNonQuery()
>
> cn.Close()
>
> End Sub
>
> End Class
>
>
Gotta lay off the crackpipe Steve.to point out that your code may be vulnerable to a damaging
SQL injection attack. If the USER ID here has permission
to do more than insert rows into the table HISTORY, and if
you are not validating input in any way on the client side,
you are in trouble. A malicious user of your application
could enter into LongNotes, for example, a single quote
character followed by any SQL statement he or she wants to
run (like DELETE FROM HISTORY, or much worse, if this session
is opened by the sa account). See this article for more
information: www.sommarskog.se/dynamic_sql.html, or search
the web for "SQL injection."
Please, please, please, always use parameterized queries
or SQL stored procedures (and in the latter case, not
ones that just concatenate user input themselves).
You may be validating input on the client side or using
an account with limited privilege, in which case you should
at least add a comment in your code to note that this code
relies on security to be implemented elsewhere. However,
in my opinion, client-side-only security for this sort
of thing is not sufficient. Just to give one example,
some web sites will enforce what is typed into a form,
but overlook the fact that the form may not be the only
place to supply parameter values - direct specification
in the URL might be, too, and those values won't be
validated.
The best way to pass a date is through a parameterized
query or stored procedure, in which case you don't have
to use any format - you just pass it as a datetime value,
such as a SqlDateTime.
If you must pass your date as a string (and it's unlikely you
must, but it's a quicker, if more reckless, fix from what
you have now than to address the security and use parameters),
use the one date-only string format that SQL Server will
interpret consistently across culture settings: 'yyyymmdd'.
Be sure it's the string 'yyyymmdd', and not the integer yyyymmdd.
The only other safe format is the full datetime format
'YYYY-MM-DDTHH:MM:SS.mmm' (including the T character).
Vulnerable code like this is all too common, and I want
to say something whenever I see it.
Steve Kass
Drew University
http://www.stevekass.com
JediDanny@.discussions.microsoft.com wrote:
> This post has been edited either by the author or a moderator in the
> Microsoft Forums: http://forums.microsoft.com
>
> Hey there,
>
> I'm trying to add rows to a table and pass a date value through. I use
> Format(Date.now, "dd MMM yyyy") however when I check in SQL Server
> Managment Studio it has swapped the day and month round (e.g. input of
> "7/9/2006" will be "9/7/2006" on the actual table. Actually the output
> is 2006-07-09 00:00:00:00.) I've put the full source code below.
>
> Can anyone help with this?
>
> Cheers,
>
> Danny
>
>
>
> Imports System.Data
>
> Imports System.Data.SqlClient
>
> Public Class Form1
>
> Dim dtdate As Date
>
> Dim cn As New SqlConnection("server=*********;database=********;USER
> ID=*********;password=*********")
>
> Dim objCommand As New SqlCommand("", cn)
>
> Dim ws As New wrtRefID.Service
>
> Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
> System.EventArgs) Handles Button1.Click
>
> dtdate = Now
>
> AddHistory("ActivityID", 1, "AccountID", "AccountName", "ContactName",
> "Category", Microsoft.VisualBasic.Format(dtdate, "dd MMM yyyy"), 2,
> "Description", "UserName", Microsoft.VisualBasic.Format(dtdate, "dd MMM
> yyyy"), "CreateUser", "Notes", "LongNotes")
>
> MessageBox.Show("DONE")
>
> End Sub
>
> Private Sub AddHistory(ByVal ActivityID As String, ByVal Type As
> Integer, ByVal AccountID As String, _
>
> ByVal AccountName As String, ByVal ContactName As String, ByVal Category
> As String, ByVal StartDate As Date, _
>
> ByVal Duration As Integer, ByVal Description As String, ByVal UserName
> As String, _
>
> ByVal CreateDate As Date, ByVal CreateUser As String, ByVal Notes As
> String, ByVal LongNotes As String)
>
> cn.Open()
>
> objCommand.CommandText = "INSERT INTO HISTORY (HISTORYID, ACTIVITYID,
> Type, ACCOUNTID, ACCOUNTNAME, CONTACTNAME, " & _
>
> "CATEGORY, STARTDATE, DURATION, DESCRIPTION, USERNAME, CREATEDATE,
> CREATEUSER, NOTES, LONGNOTES) " & _
>
> "VALUES ('" & ws.MCS2_SLXID("HISTORY") & "', " & "'" & ActivityID & "',
> '" & Type & "', '" & AccountID & "', '" & AccountName & "', '" &
> ContactName & _
>
> "', '" & Category & "', '" & StartDate & "', '" & Duration & "', '" &
> Description & "', '" & UserName & _
>
> "', '" & CreateDate & "', '" & CreateUser & "', '" & Notes & "', '" &
> LongNotes & "')"
>
> ' MessageBox.Show(Format(dtdate, "dd MMM yyyy"))
>
> TextBox1.Text = objCommand.CommandText
>
> objCommand.ExecuteNonQuery()
>
> cn.Close()
>
> End Sub
>
> End Class
>
>
