problem in update statement

[ulthane]

hey guys,
can any1 tell me what is the problem with the following sql? it worked fine when i used the access driver connection string but after changing to the JET SQL driver it stopped working...

DB= "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=path/DB.mdb; Persist Security Info=False"
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open DB
id = request.querystring("update")
password = Request.Form("password")
ssql = "UPDATE table SET pass='" & password & "' WHERE id=" & id
conn.execute (ssql)

Microsoft JET Database Engine error '80040e14' 

Syntax error in UPDATE statement.

thanks for the help

[siteguru]

Is your table really called table?

[ulthane]

no, thats just an example of how my code looks like
the only line i changed is the connection string and it got broken...

From

DB = "DRIVER=Microsoft Access Driver (*.mdb);DBQ=" & Server.MapPath("path/DB.mdb")

to

DB= "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=path/DB.mdb; Persist Security Info=False"

[webber123456]

write out your ssql statement to verify its content and also verify id is numeric

id = request.querystring("update") + 0

or

id = cint(id)

[ulthane]

it works just fine,

response.write gives:

UPDATE table SET password='123456' WHERE ID=1

pretty strange uh

[r937]

hmm... syntax error in update statement...

maybe somebody dropped a single quote into their password on the form?

i don't write perl (or whatever that language is) but it looks to me like you aren't checking Request.Form for sql injection...

[siteguru]

no, thats just an example of how my code looks like

But different engines have different reserved words. So what IS your table name? Unless you can show the actual (real) SQL command then it's pretty much impossible to point you to the error.

And like Rudy says, you should Response.Write the sSQL variable before trying to execute it, to be sure that the SQL statement actually is what you think it is. SQL injection prevention has been discussed in your threads many times.

(We're presuming it is you testing, and not that someone else is reporting an error).

[ulthane]

yeah thats me testing also i got some checks on all of my fields for quotes and illegal words (such as xp_ , -- etc), if there are any other checks i need to have i'd like to know about them

Well, siteguru is right! password is a reserved word and that what made the problem, adding [] solved the problem

just found it out here...
http://sqlserver2000.databases.aspfa...-keywords.html

thanks guys for the help

ps. i got a problem with my DB password, for now i disabled it because it doesn't seem to work out but i'd like to have it working,
the problem is when i set a password and open the DB manually it works fine, but when opening it through the web it always throws an error that the pass is wrong, even when its not, maybe any of u knows what can cause it?
the topic is here:
http://www.sitepoint.com/forums/showthread.php?t=717222

[siteguru]

MS Access and Jet SQL driver strings, including using a password on the database file. (It's the last line of each that defines the password. The Server.Mappath part is based on Brinkster free hosting).

  Dim connstr,pword
  pword = "Your password here"
  'Using the Microsoft Access driver
  connstr = "DRIVER={Microsoft Access Driver (*.mdb)};" _
  & "DBQ=" & Server.MapPath("/yourwebname/db/testdb.mdb") _
  & ";PWD=" & pword & ";"

  'Using the Microsoft JET 4.0 driver
  connstr = "provider=Microsoft.jet.oledb.4.0;data source=" _
  & Server.MapPath("/yourwebname/db/testdb.mdb") _
  & ";Jet OLEDB:Database Password=" & pword

http://www5.brinkster.com/hiflyer/db/dbtest.asp

[ulthane]

yeah, i took it from that link and changed accordinly to what i need, didn't work out. i used the jet 4.0 driver

[siteguru]

All I can say is that I know it works - I'm using the Jet 4.0 driver for the test database at that link.

[ulthane]

well i cant use the excact link, it throws me an error saying a physical path was used, a virtual path is needed or something like that.. (or the opposite, i dont remmember excactly)

well i took the password off, hope i wont need it

[siteguru]

Easiest way to get the path is to create a page with the following in it and put this in the webroot folder.

<% Response.Write Server.Mappath("/") %>

Call this up in a browser, copy the text shown, replace webroot with database, then add \databasename.mdb. Et voilá - you have your physical path to the database file. (The connection string needs a physical path ... Server.Mappath converts a virtual path to a physical one, but you can't Server.Mappath directly to the database folder on a Brinkster paid account because it lies outside the virtual path).

[ulthane]

i got it, but what do i do with it? where to write it?

[siteguru]

  Dim connstr,pword
  pword = "Your password here"
  'Using the Microsoft JET 4.0 driver
  connstr = "provider=Microsoft.jet.oledb.4.0;data source=" _
  & "Whatever result you got following above process put it here" _
  & ";Jet OLEDB:Database Password=" & pword

[ulthane]

it worked, thanks