Select & Update at the same table at same time

[andri.wardhana]

Hi Guys,

I have a problem with my ASP file. since I'm all new in ASP, i found
that the error statement generated by ASP is confusing. basically what
I want to do in this script is ability to change password. Currently
this script's running well in the PHP version ( I rewrote the entire
PHP app to ASP app):
1. The form contains these fields : old password, new passwd, confirm
new passwd. users have to input old passwd in order to change their
passwd.
2. then the ASP script check old password against tbl_users for that
userid. if found, the script then update password for that user.

I thought this would be easy, since I have no problem running it on
PHP. but when I did it for ASP, I had this following error :
Syntax error in update statement

This is my (horribly) simple code :

oldpasswd = Request.Form("oldpasswd")
newpasswd = Request.Form("newpasswd")
userid = SESSION("userid")
query = "SELECT password FROM tbl_users WHERE userid='" & userid & "'"
set RSusers = dataConn.Execute(query)
referer = Request.Form("referer")
storedpwd = RSusers("password")
if sha256(oldpasswd) = storedpwd then
newencrypted = sha256(newpasswd)
kueri_update = "UPDATE tbl_users SET password='" & newencrypted & "'
WHERE NPK=" & SESSION("npk")
dataConn.Execute(kueri_update)
dataConn.close
Response.Redirect(referer & ".asp")
else
Response.Redirect(referer & ".asp" & "?page=changepasswd&err=on")
END IF


Thank you for your helps.

 

[AlanM]

"UPDATE tbl_users SET password='" & newencrypted & "'
WHERE NPK=" & SESSION("npk")

my guess is that NPK is not a numeric field in your database, so you need to
use quotes.

or one of the variables used is empty at time of execution

 

[andri.wardhana]

Thanks Alan for the quick response.

I've checked that possibility and none of variables are empty at the
time of execution. Here's the output of SQL query when I response.write
it :
UPDATE tbl_users SET
password='9834876dcfb05cb167a5c24953eba58c4ac89b1adf57f28f2f9d09af107ee8f0'
WHERE NPK=989
the NPK field is already a number type in db (I use access). Is it
possible that there is a lock mechanism in Access that prevent updating
table when other query read the same table in the same page?

Thanks again

 

[Bob Barrows [MVP]]

Thanks Alan for the quick response.

I've checked that possibility and none of variables are empty at the
time of execution. Here's the output of SQL query when I
response.write
it :
UPDATE tbl_users SET
password='9834876dcfb05cb167a5c24953eba58c4ac89b1adf57f28f2f9d09af107ee8f0'
WHERE NPK=989
the NPK field is already a number type in db (I use access). Is it
possible that there is a lock mechanism in Access that prevent
updating table when other query read the same table in the same page?

No, especially when you use the Execute method to open the recordset: this
will default to a read-only, forward-only cursor, so, no locks.

You are more probably facing a "reserved keyword" problem. If you look here:
http://www.aspfaq.com/show.asp?id=2080
You will see that "password" is a reserved keyword wihich should be avoided
for your db object names.

My suggestion would be to change the name of the field, but, if you can't
for some reason, you will need to remember to enclose it in brackets []
whenever you use it in a query run via ADO.

With that out of the way, you need to be aware that by using dynamic sql
(using concatenation to build sql statements) you are leaving your database
and site vulnerable to hackers using sql injection:
http://mvp.unixwiz.net/techtips/sql-injection.html
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23

The best way to defeat sql injection is to use parameters, either via saved
parameter queries (my preferred technique):
http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&[email protected]

http://groups.google.com/groups?hl=...=1&[email protected]

http://groups-beta.google.com/group/microsoft.public.inetserver.asp.db/msg/b3d322b882a604bd

or by using a Command object to pass parameters to a string containing ODBC
parameter markers:
http://groups-beta.google.com/group/microsoft.public.inetserver.asp.db/msg/72e36562fee7804e

 

[andri.wardhana]

It is indeed the problem. I've change the field name and everything
goes fine . pheww, thanks Bob, really appreciate it, and also thanks
for the advice.

but it is funny to think that the PHP version (also use Access thru
ODBC), didn't encounter the same problem.

Thanks again

 

[Bob Barrows [MVP]]

It is indeed the problem. I've change the field name and everything
goes fine . pheww, thanks Bob, really appreciate it, and also thanks
for the advice.

but it is funny to think that the PHP version (also use Access thru
ODBC), didn't encounter the same problem.

Does PHP use ADO?

 

[andri.wardhana]

I guess not. PHP connects to Access via ODBC, so I think it lacks of
ADO functionality. (pardon me, I'm not so knowledgeable on ADO thingy)

So, maybe because of that PHP can use ADO's reseved words as field's
name, although it uses Access as database. Right?

Thanks for shed some lights here. (and sorry for top posting, won't
happen again )

 

[Bob Barrows [MVP]]

I guess not. PHP connects to Access via ODBC, so I think it lacks of
ADO functionality. (pardon me, I'm not so knowledgeable on ADO thingy)

You can find the ADO API Reference at msdn.microsoft.com/library

So, maybe because of that PHP can use ADO's reseved words as field's
name, although it uses Access as database. Right?

Sort of. ADO enforces a different set of reserved keywords than the set of
keywords enforced by ODBC on its own.

Thanks for shed some lights here. (and sorry for top posting, won't
happen again )

Not a problem for me.