We help IT Professionals succeed at work.

ASP.net strange error in Update query

Murray Brown
Murray Brown asked
on
Hi

I get an error in the following code
"Incorrect syntax in UPDATE Statement"
If I do exactly the same query in my Access database it works
Is it the Code that I am using
Sub UpdateNewEmailAndPassword(ByVal sEmail As String, ByVal sPassword As String)

        '// define a connection to the database
        Dim cs As String = ConfigurationManager.ConnectionStrings("WhatEverNameYouWant").ConnectionString

        cs = cs.Replace("App_Data\GC.accdb", Server.MapPath("App_Data\GC.accdb"))

        Dim cn As New OleDbConnection(cs)


        Try


            Dim sSQL As String
            sSQL = "UPDATE MyUsers SET Password = '" & sPassword & "' WHERE Email = '" & sEmail & "'"
            'sSQL = "INSERT INTO MyUsers ( [Email], [Password], [FirstName], [Logins] ) SELECT '" & sEmail & "' AS Expr1, '" & sPassword & "' AS Expr2, '" & sFirstName & "' AS Expr3, '" & "First login " & CStr(System.DateTime.Today) & " Subsequent logins " & "'"

            '// define the sql statement to execute
            Dim cmd As New OleDbCommand(sSQL, cn)

            '    '// open the connection
            cn.Open()

            cmd.ExecuteNonQuery()

        Catch ex As Exception
            Response.Write(ex.Message)
        Finally
            If cn.State <> ConnectionState.Closed Then
                cn.Close()
            End If
        End Try
    End Sub

Open in new window

Comment
Watch Question

Commented:
first off, I would recommend using parameterized SQL. This will prevent SQL injection attacks and probably fix the problem you are having.

For example, pretend I send the following:

email: someone@somewhere.com' or 1=1--
password: abc123

That makes the SQL query being executed:

UPDATE MyUsers SET Password = 'abc123' WHERE Email = 'someone@somewhere.com' or 1=1--'

This will then make EVERY password be abc123 because 1 will always equal 1. Parameterization doesn't require character escaping like some might suggest and it is pretty much full proof at stopping SQL injection. The SQL executed is something like this:

DECLARE @myPassword varchar(50)
DECLARE @myEmail varchar(50)

UPDATE MyUsers SET Password = @myPassword WHERE Email = @myEmail

Commented:
If you do want to go the route of what you currently have and not take the time to stop SQL injection, I would recommend doing a Response.Write(sSQL) and then copying and pasting that directly into you access query tool.
Commented:
I agree that parameters are a godsend and the whole string concatenation thing gets to be confusing.

Your Access syntax error is pretty much about not surrounding the column names in [] square brackets, and/or it may see the column name password as a key word.

I don't mess with access much due to its limitations, and its always been a quirky beast when it comes to sql code.

Anyway if you made the following changes it will likely work fine:

Dim sSQL As String
'sSQL = "UPDATE MyUsers SET Password = '" & sPassword & "' WHERE Email = '" & sEmail & "'"
sSQL = "UPDATE MyUsers SET [Password] = @sPassword WHERE [Email] = @sEmail"

'// define the sql statement to execute
Dim cmd As New OleDbCommand(sSQL, cn)
cmd.Parameters.Add(New OleDbParameter("@sPassword", sPassword))
cmd.Parameters.Add(New OleDbParameter("@sEmail", sEmail))
Murray BrownASP.net/VBA/VSTO Developer

Author

Commented:
thanks very much for the help

Explore More ContentExplore courses, solutions, and other research materials related to this topic.