SQL string getting cut short 1

[yesti]

I have a string:

strSQL="Update Employees Set Password='"&str&"' Where email='"&uname&"'"

str is the text the user types and run through the following functions:

str=hash(userinput)

Function hash(input)
Dim md5
Set md5=Server.CreateObject("MD5DLL.Crypt&quot
hash=newstr(md5.Encrypt(CStr(newstr(md5.Encrypt(CStr(input))))))
End Function
Function newstr(str)
Dim tempstr
tempstr=Replace(str, "../", "&quot
tempstr=Replace(tempstr, "--", "&quot
tempstr=Replace(tempstr, ";", "&quot
tempstr=Replace(tempstr, CHR(34),""&quot
newstr=Replace(tempstr, "'", "''&quot
End Function

Usually this works fine, but when a user types in certain strings, the double hash function sometimes returns strange values such and the strSQL results in:

Update Employees Set Password=dÉÖ"

and throws an ASP error. If I look at the html source I get:

Update Employees Set Password=dÉÖ"

so I know the newstr function is replacing the quote symbol with the ampersand-quot equivalent. Why is the string being terminated prematurely?

I have tried brackets and double quotation marks, but the string still gets chopped off. Any ideas? Thanks.

 

[yesti]

The browser is changing things:

In the newstr function the 5th line should read:

tempstr=Replace(tempstr, CHR(34),"AMPERSANDQUOT&quot

and looking at the source I get:

Update Employees Set Password=dÉÖAMPERSANDQUOT

 

[vbkris]

i am confused ur sql says:
strSQL="Update Employees Set Password='"&str&"' Where email='"&uname&"'"

bit ur output is:
Update Employees Set Password=dÉÖ"? what happened to the where part? even that is not coming?

Known is handfull, Unknown is worldfull

 

[yesti]

Right! That's the problem, the where clause is completely absent, hence the thread title I know the code isn't buggy per se since it works with most passwords. Just the right magical combination of characters produces weird results.

 

[clarkin]

show us a Response.Write of what's in the variable str at the various stages (before you add to that SQL string).

Seems odd that it swallows up the SQL


Posting code? Wrap it with code tags: [ignore]

[/ignore][code]CodeHere

[ignore][/code][/ignore].

 

[yesti]

OK, The vars str and temp are dimensioned. On form submission, the old password is stored in temp. If temp<>&quot;&quot; Then str=newstr(hash(temp)). The old password is retrieved from the database and compared with this hash. If they match then thenew password is stored in temp. str=hash(temp)

This produces str: dÉÖ&quot; (the quote is AMPERSANDQUOT in html source)

There is a semicolon after the ampquot as a result of the replace function but running it through the hash function runs newstr twice and strips the semicolon. I have tried changing the replace function and taking out the semicolon, but that has no effect.

Also, when I response.write str at this stage, I added a space, three exclamation points and a LESSTHANbrGREATERTHAN to the end of the line and all that is not getting printed either. The md5 algorithm must be putting some invisible character in there after the quote, right?

I then construct the strSQL and print it:

strSQL: Update Employees Set Password='dÉÖ&quot;

Here is where I open the recordset with that string and of course get an error about the quotes being all wrong.

 

[clarkin]

use this to run through your problematic str printing out all the ascii values.. we'll see what's inside.

dim i
for i = 0 to Len(str)
  Response.Write(Asc(str) & ", ") 'prints ASCII code for leftmost char
  str = Right(str, Len(str)-1) 'dump leftmost char
next

Tell us what you get..

Posting code? Wrap it with code tags: [ignore]

[/ignore][code]CodeHere

[ignore][/code][/ignore].

 

[yesti]

Ok pasted the code and here's what I got:

100, 201, 214, 38, 113, 117, 111, 116, 59, 0, 55, 55, 190, 138, 222, 18, 176, 203, 135, 113, 184,

Then I get an ASP error:

Microsoft VBScript runtime (800A0005)
Invalid procedure call or argument: 'Asc'

 

[clarkin]

you can possibly eliminate that error by changing it to:
[tt]for i = 0 to Len(str)-1[/tt]
.. but regardless in your ascii values:
[tt]100,201,214,38,113,117,111,116,59,0,55,55,190,138,222,18...
d & q u o t ;

that value of 0 is (null) in the ascii table, i'd say its what is destroying your string (

http://www.asciitable.com)

perhaps you can check out your md5 algorithm and make sure it only outputs printable characters? Seeing as you have to use them within a SQL string...

good luck

Posting code? Wrap it with code tags: [ignore]

[/ignore][code]CodeHere

[ignore][/code][/ignore].

 

[yesti]

Added this line to my newstr funtion:

tempstr=Replace(tempstr, CHR(0),&quot;&quot

And problem is solved. Thanks for helping me troubleshoot!