SQL Syntax Error

UPDATE command

[steelegbr]

When sending the SQL command

UPDATE users SET password='newpass' WHERE username='username'

I receive the error:

Syntax error in UPDATE statement

Could anybody please post the correct version of the command?
Any help appreciated.

[IanTurner]

username and password may well be reserved words, it depends on your database.

[steelegbr]

I can create new entries using username and password...

[Birdman]

Try enclosing the tablename in backticks.

UPDATE `users`...

[digitalv]

Are you using Microsoft SQL? If so, there is nothing wrong with the statement you posted but I'm wondering how you're executing it. If you're executing the statement through ASP and are inserting dynamic variables make sure the apostrophe bug isn't the problem.

Try this:
SQL = "UPDATE Users SET Password = '" & strPassword & "' WHERE Username = '" & strUsername & "'"
response.write SQL
con.execute(SQL)

This way you can see EXACTLY what you're executing before it runs.

[oldskool79]

try putting the field names in brackets, like this:

UPDATE [users] SET [password]='newpass' WHERE [username]='username'

That way even if you use names that are reserved it won't throw an error

[sun818]

Yeah, we had a user name with a login of o'connor that was messing up the queries.

[oldskool79]

Always replace single and double quotes from input boxes otherwise you are opening yourself up to a huge security risk.

[digitalv]

Always replace single and double quotes from input boxes otherwise you are opening yourself up to a huge security risk.

If the original poster is still reading this thread, definitely listen to this. In ASP, always run a REPLACE on anything you're going to pass SQL and take out any apostrophes.

replace(strString,"'","")

Not only will it eliminate that bug you experienced in the future, it also prevents a well known security flaw that would let anyone access. Take a look at your code, if you are authenticating the user with WHERE Username = 'username' AND Password = 'password', take a look at what I'm about to show you. If I entered the line below in your password field:

test' OR 1 = 1

Your SQL statement would now look like this: WHERE Username = 'username' AND Password = 'test' OR 1 = 1

Since 1 will always equal 1, I'm in. I will be logged in as whoever the "first" user in the database is (the top row). Hope that isn't you, with god-like admin powers :)

[john_k]

Always replace single and double quotes from input boxes otherwise you are opening yourself up to a huge security risk.

In ASP, always run a REPLACE on anything you're going to pass SQL and take out any apostrophes.

By single quote, you mean apostrophe?

Apostrophes are escaped in SQL by doubling them up. That is, replace each apostrophe with two apostrophes. So there is no need to remove them, and there is no need to exchange them so that the text is other than what the user entered

Take digitalv's advice, but change the function call to this:
Replace(strString, "'", "''")

Here is a set of functions that go a bit further. I utilize these to clean data values when building SQL statements. They pick up the apostrophe problem, protect against SQL injection, and force consistency by writing empty strings or invalid data as NULL.

  Public Function SqlEncode(sText) 
    sqlEncode = Replace(sText,"'","''") 
  End Function 
   
  Public Function SqlWriteTextWNull(sText) 
    If IsNull(sText) Then 
      SqlWriteTextWNull = "NULL" 
    ElseIf sText = "" Then 
      SqlWriteTextWNull = "NULL" 
    Else 
      SqlWriteTextWNull = "'" & SqlEncode(sText) & "'" 
    End If 
  End Function 
   
  Public Function SqlWriteNumberWNull(vNumber) 
    If IsNull(vNumber) Then 
      SqlWriteNumberWNull = "NULL" 
    ElseIf IsNumeric(vNumber) Then 
      SqlWriteNumberWNull = CStr(vNumber) 
    Else 
      SqlWriteNumberWNull = "NULL" 
    End If 
  End Function 
   
  Public Function SqlWriteDateWNull(vDate) 
    If IsNull(vDate) Then 
      SqlWriteDateWNull = "NULL" 
    ElseIf IsDate(vDate) Then 
      SqlWriteDateWNull = "'" & SqlEncode(CStr(vDate)) & "'" 
    Else 
      SqlWriteDateWNull = "NULL" 
    End If 
  End Function 
  Public Function SqlWriteBooleanWNull(vValue) 
    If IsNull(vValue) Then 
      SqlWriteBooleanWNull = "NULL" 
    ElseIf CBool(vValue) Then 
      SqlWriteBooleanWNull = 1 
    Else 
      SqlWriteBooleanWNull = 0 
    End If 
  End Function 

[steelegbr]

Thanks for all the replys, I never expected so many.

Simple change from username to [username] and password to [password] worked.

For your knowledge, I am using Microsoft SQL (though I can't get into any admin stuff as it is a free web host) and was using a script similar to DigitalV's for debugging.