SitePoint Sponsor

User Tag List

Results 1 to 16 of 16
  1. #1
    SitePoint Evangelist
    Join Date
    Jun 2010
    Location
    Israel
    Posts
    523
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    problem in update statement

    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...

    Code:
    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)
    Code:
    Microsoft JET Database Engine error '80040e14' 
    
    Syntax error in UPDATE statement.
    thanks for the help

  2. #2
    SitePoint Wizard siteguru's Avatar
    Join Date
    Oct 2002
    Location
    Scotland
    Posts
    3,631
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Is your table really called table?
    Ian Anderson
    www.siteguru.co.uk

  3. #3
    SitePoint Evangelist
    Join Date
    Jun 2010
    Location
    Israel
    Posts
    523
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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
    Code:
    DB = "DRIVER=Microsoft Access Driver (*.mdb);DBQ=" & Server.MapPath("path/DB.mdb")
    to
    Code:
    DB= "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=path/DB.mdb; Persist Security Info=False"

  4. #4
    SitePoint Guru
    Join Date
    Jun 2007
    Posts
    691
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    write out your ssql statement to verify its content and also verify id is numeric

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

    or

    id = cint(id)

  5. #5
    SitePoint Evangelist
    Join Date
    Jun 2010
    Location
    Israel
    Posts
    523
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    it works just fine,

    response.write gives:
    Code:
    UPDATE table SET password='123456' WHERE ID=1
    pretty strange uh

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    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...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Wizard siteguru's Avatar
    Join Date
    Oct 2002
    Location
    Scotland
    Posts
    3,631
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by ulthane View Post
    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).
    Ian Anderson
    www.siteguru.co.uk

  8. #8
    SitePoint Evangelist
    Join Date
    Jun 2010
    Location
    Israel
    Posts
    523
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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

  9. #9
    SitePoint Wizard siteguru's Avatar
    Join Date
    Oct 2002
    Location
    Scotland
    Posts
    3,631
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    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).
    Code:
      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
    Ian Anderson
    www.siteguru.co.uk

  10. #10
    SitePoint Evangelist
    Join Date
    Jun 2010
    Location
    Israel
    Posts
    523
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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

  11. #11
    SitePoint Wizard siteguru's Avatar
    Join Date
    Oct 2002
    Location
    Scotland
    Posts
    3,631
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    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.
    Ian Anderson
    www.siteguru.co.uk

  12. #12
    SitePoint Evangelist
    Join Date
    Jun 2010
    Location
    Israel
    Posts
    523
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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

  13. #13
    SitePoint Wizard siteguru's Avatar
    Join Date
    Oct 2002
    Location
    Scotland
    Posts
    3,631
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Easiest way to get the path is to create a page with the following in it and put this in the webroot folder.
    Code:
    <% 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).
    Ian Anderson
    www.siteguru.co.uk

  14. #14
    SitePoint Evangelist
    Join Date
    Jun 2010
    Location
    Israel
    Posts
    523
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    i got it, but what do i do with it? where to write it?

  15. #15
    SitePoint Wizard siteguru's Avatar
    Join Date
    Oct 2002
    Location
    Scotland
    Posts
    3,631
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Code:
      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
    Ian Anderson
    www.siteguru.co.uk

  16. #16
    SitePoint Evangelist
    Join Date
    Jun 2010
    Location
    Israel
    Posts
    523
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    it worked, thanks


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •