Session or Database or WT...E??

[Commodore640]

Hi everyone!

I got a really strange error and I have no clue how to fix it, so any advice would be a lifesaver.

I have created a system where users need to log in. I got complaints that it kicks them out too early. I extended the session length as much as I could but that still was not enough. so I figured if I put encrypted passwords into more permanent cookies like this:

Response.Cookies("passw") = "jambalaya"

Then if they got kicked out, I can just read the encrypted password and id from their computer and relogin them back. I guess this is something like Facebook use as well.

Here is what happens. If I wait long enough (a bunch of hours) and refresh the screen that can be visible only when you are logged in I got the following error:

ODBC Drivers error '80040e14'

it happens in the line where I want to launch a search for the user using their id and pass that I just read out from the cookie.
All variables have the information that is needed.
The weirdest thing when I try to print the query onto the screen it is cut right after the first variable.

strQuery = "SELECT * FROM adminstaff WHERE passw='" & passw & "' AND userid='" & userid & "' AND Aktiv=1;"
Response.Write strQuery & "iamhere"
Set objRS = objConn.Execute(strQuery)

This is what I see on the screen when I print the quesry before it launches:

SELECT * FROM adminstaff WHERE passw='jambalaya
ODBC Drivers error '80040e14'

Even though I put a & "iamhere" after printing of the line, it seems to ignore whatever is after, it cuts itself in half...
However my code is complete and in normal mode, when I relogin again and no timeout on the sessions, everything works fine, no error message...

Any ideas what this could be? Maybe if the user looses its session, a different process is needed to build it up or?

 

[guitarzan]

First, you created two threads for the same problem, probably because you forgot to include the "Set objRS = objConn.Execute(strQuery)" line. You cannot edit posts here, but to avoid confusion, you should just add a new post to your thread with the correction, not create a duplicate thread.

Regarding session length, use "Session.Timeout=xxx". Regarding storing passwords, you should not store them in a cookie, even encrypted... you should use Session variables. The former stores the password (encrypted) on the client browser. Even though it's encrypted, it is fully visible to the client, and can be manipulated. The latter stores only a pointer on the client browser, which points to the contents stored on the server... the actual informtation is not visible to the client, and disappears once the session is lost. See below for a brief explanation of both:

http://www.w3schools.com/ASP/asp_sessions.asp

Finally, the select statement you show is:
SELECT * FROM adminstaff WHERE passw='jambalaya

This is clearly missing a closing quote, and your code shows that you are passing whatever is in "passw" directly into the query without escaping single-quotes with Replace(passw, "'", "''"). You should look into using parameterized queries, otherwise you will be vulnerable to SQL Injection.

 

[Commodore640]

Thank you for replying.

First I know I did, but I did not know how to delete it, so the second one that you replied on was the real one.

It is obvious for me that the second MySQL statement is missing the second half of it, but can you explain when you have this (I put in the line numbers so you can see it):

34 strQuery = "SELECT * FROM adminstaff WHERE passw='" & passw & "' AND userid='" & userid & "' AND Aktiv=1;"
35 Response.Write strQuery & "iamhere"
36 Set objRS = objConn.Execute(strQuery)

in the code, why do you get this on screen?

SELECT * FROM adminstaff WHERE passw='jambalaya
...ODBC Drivers error '80040e14' in line 36

So it shows only the half of what it suppose to print on screen. It should show me the ENTIRE query, not just a half of it. The execution goes after the screen print. And it stops with the error. If the error is caused by the missing part after the password what would be a clear and obvious syntactical error, why on Earth ignores the program it when there is & "' AND userid...." there that suppose to be added to the variable??

 

[guitarzan]

I see your point. Does the password contain odd ascii values? like, ascii 0 (null) for example?

 

[guitarzan]

btw, there is no way to delete a thread here, although you can "red flag" yourself with an explanation if you really want to delete a post

 

[Commodore640]

Nope. It produces this error regardless what the password is...

Sometimes I loose my sessions earlier than what I set up in the website session limit. I suspect it is because the server is a bit overloaded at times. I am running 90 websites on one box so you can imagine...
It happens when someone is filling out a long form while he or she is logged in, they loose their session in the meantime and then loosing whatever they typed in when trying to submit their form.
That is why I put their login into a cookie, so if their session is killed, I try to rebuild their login and let their form values go thorough the system. But when their session is killed, the reenter process fails as I described above... When I go back to the login page everything works fine... so I am totally confused...

or can you suggest a better method to trap their querystring/form information and save it, then submit it back to the system? Is there a "best practice" for that?

 

[Commodore640]

Here is the full error message:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[MySQL][ODBC 3.51 Driver][mysqld-4.1.21-community-nt]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'jambalaya at line 1

/kapu/inc/alogin.asp, line 32

the page itself is called hirek.asp, and this is the list of file includes that the page uses (these are the first lines):

<!--#include virtual="/kapu/inc/serverglobals.asp"-->
<!--#include virtual="/kapu/inc/alogin.asp"-->
<!--#include virtual="/kapu/inc/sqltools.asp"-->
<!--#include virtual="/kapu/inc/general.asp"-->
<!--#include virtual="/kapu/inc/menu.asp"-->
<%
'---------- A few globals for hirek.asp
Dim PageMode, FinalPath, Tree, DBConn, NyelvID, NyelvIDKod
Dim Statikus, Tablazat....


In "serverglobals.asp" I have a call to a database, reading values and even session variable reads, but that goes flawless. It stops by alogin.asp at the database call that would read the visitor's login information...

 

[guitarzan]

I'm not an expert, others may chime in with other options or opinions, but I believe the best practice is to use session variables and increase the session timeout to an appropriate limit. I have noticed situations where a users' session is lost sooner than expected, but it was sporadic and I've never been able to figure out what caused it. What is your Session.Timeout set to?

Regarding the error, I can't think of what's causing it. I still vote for an unexpected character. If you want, this code will show each character in the password and it's ascii code.

Dim x, ch, passw, userid
For x = 1 to len(passw)
ch = mid(passw, x, 1)
response.write x & ": " & ch & " : " & asc(ch) & "</br>"
Next

 

[Commodore640]

Thanks guitarzan, but I have found no special character in there... what is really strange, when I use your loop, the entire string is visible character by character, however when I want to print the string all at once, it breaks... it makes absolutely no sense to me.

I suspect this has to be something with the session connection. I have two session reads in "/inc/serverglobals.asp"

DefaultPortal=Session("DefaultPortal")
DL=Session("DLanguage")

These pass just fine, but as the program gets to the second include, alogin.asp, then it has problems. Here is the first part of the code so you can see it more thoroughly:


<%
Function LoginUser(Loc)
Dim objConn, strConnection, strQuery, objRS, LoginCount, Szamla
Dim passw, id, Tpassw, Tid, Tdefa

id=Request("id")
Call SQLShield(id)
passw=Request("passw")
Call SQLShield(passw)

If id="" and passw="" Then
id = Session("id")
passw= Session("passw")
If id="" AND passw="" Then
id = Request.Cookies("SESSID")
passw = Request.Cookies("SESSPASS")
If id<>"" AND passw<>"" Then
id = DeCryptString(id)
passw = DeCryptString(passw)
End If
End If
End If

Set objConn = Server.CreateObject("ADODB.Connection")
strConnection = ConnectionString(acms)
objConn.Open strConnection
Set objRS = objConn.Execute("SET NAMES '" & CharCode & "';")

Szamla=0

'here is your script
'Dim x, ch, passw, userid
'For x = 1 to len(passw)
'ch = mid(passw, x, 1)
'response.write x & ": " & ch & " : " & asc(ch) & "</br>"
'Next

'------------ This is where it breaks:
Set objRS = objConn.Execute("SELECT * FROM administrators WHERE passw='" & passw & "' AND id='" & id & "'")

While not objRS.EOF
...


and if I go back to the start page, display a new login form, everything is fine...

 

[Commodore640]

Dude, you are the MAN!

It put me a bug in my ears when you said you still think that is a character issue... Now I went back to my code and checked a len(passw) before I built up my query. And guess what happened? It was one character longer than before... so you were right, it got an invisible character somehow that resulted the string chopped in half. I still do not know why, because the asc() did not show me any value at all, but since I cut one character from the right side of both the password and the id, my code WORKS!

Thanks again for your advice!

 

[guitarzan]

I got burned by something similar a while ago, very frustrating. I'm surprised the asc() trick didn't expose it, but at least you found the source. Trim(passw) may also fix it.

 

[Commodore640]

Good idea, I try with trim() as well. left(passw,len(passw)-1) still makes me feeling a bit hesitant even though it works (what if they got two of those invisible devils)...

yeah, the asc() does not show anything... maybe the decryption process adds something weird to them, I have to double check that.

 

[Commodore640]

I have tried trim() but unfortunately that does not work. I had to use the good old "left(passw,len(passw)-1)" method...

 

[guitarzan]

Now you have me curious. What does Asc(Right(passw, 1)) give you?

 

[Commodore640]

It says: 0

Maybe it is not even a whole byte... Frankly the encryption/decryption process is not my invention so it can do something I do not understand fully, however I have not tried to store the passwords in raw text and just read them out directly. That way it would show if that extra character is created by the encryption process, or it was added when I read it out from the cookie...

 

[guitarzan]

It says: 0
See my post on 30 Aug 11 16:03 ;-)

In that case, Replace(passw, Chr(0), "") should work, and would be safer than blindly truncating the last character. Even this might be better:
If Asc(Right(passw, 1)) = 0 then passw = Left(passw,Len(passw)-1)

But yes, it seems that the decryption is not quite doing it's job.

 

[Commodore640]

So you think it will take care of the 0 the same way it would do with anything else. OKay, it worth a try... let's see...

 

[guitarzan]

I tested it, the Replace works for me.

 

[Commodore640]

Yeah, it does what needs to be done. Thanks for your help!
Now it is time to go thorough the encryption process for bugs...