SitePoint Sponsor |
|
User Tag List
Results 1 to 16 of 16
Thread: problem in update statement
-
Dec 8, 2010, 04:33 #1
- 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.
-
Dec 8, 2010, 09:13 #2
- Join Date
- Oct 2002
- Location
- Scotland
- Posts
- 3,631
- Mentioned
- 2 Post(s)
- Tagged
- 0 Thread(s)
Is your table really called table?
-
Dec 8, 2010, 09:45 #3
- 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")
Code:DB= "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=path/DB.mdb; Persist Security Info=False"
-
Dec 8, 2010, 10:34 #4
- 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)
-
Dec 8, 2010, 10:57 #5
- 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
-
Dec 8, 2010, 11:18 #6
- 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...
-
Dec 8, 2010, 12:43 #7
- Join Date
- Oct 2002
- Location
- Scotland
- Posts
- 3,631
- Mentioned
- 2 Post(s)
- Tagged
- 0 Thread(s)
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).
-
Dec 8, 2010, 12:49 #8
- 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
-
Dec 8, 2010, 13:07 #9
- 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
-
Dec 8, 2010, 13:14 #10
- 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
-
Dec 8, 2010, 13:18 #11
- 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.
-
Dec 8, 2010, 13:27 #12
- 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
-
Dec 8, 2010, 13:43 #13
- 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("/") %>
(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).
-
Dec 8, 2010, 14:21 #14
- 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?
-
Dec 8, 2010, 15:18 #15
- 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
-
Dec 9, 2010, 02:11 #16
- Join Date
- Jun 2010
- Location
- Israel
- Posts
- 523
- Mentioned
- 1 Post(s)
- Tagged
- 0 Thread(s)
it worked, thanks
Bookmarks