• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 555
  • Last Modified:

Mysql query fails only when I use MD5(id)

Hi.
Just moved a site to a new hosting provider. Suddenly all pages that use md5(id) in the query fails with the following error :

Microsoft VBScript runtime  error '800a000d'
Type mismatch
/firebee_test.asp, line 21

Please see my attached script - I can't really make a more simple example.
The script only fails in the MD5 scenario.

Hosting is a Windows 2003, fully patched. IIS6.

Any ideas, hints, tests, well almost anything goes. Please help me out here :-(

Cheers,
Mark


<%
Session.LCID = 1030
dbsetup = "Driver={MySQL ODBC 3.51 Driver}; Server=xxx; Port=3306; Option=131072; Stmt=; Database=xxx;uid=xxx; pwd=xxx;"
Set dbConn = Server.CreateObject("ADODB.Connection")
dbConn.Open dbsetup
 
If request.querystring("md5") = "true" Then
	sql = "SELECT md5(id), varenavn FROM produkter ORDER BY id;"
Else
	sql = "SELECT id, varenavn FROM produkter ORDER BY id;"
End If
 
Set rs = dbConn.Execute( sql )
if not (rs.bof OR rs.eof) Then
	arrVarer = rs.getrows
End if
Response.write "<a href='?md5=false'>UDEN md5 (SQL = SELECT id, varenavn FROM produkter ORDER BY id;)</a><br/><a href='?md5=true'>MED md5 (SQL = SELECT md5(id), varenavn FROM produkter ORDER BY id;)</a><br/><br/>"
Response.write "<table width='300' border='1'><tr><td>ID</td><td>Varenavn</td></tr>"
if UBound(arrVarer,2) > 0 Then
	For i = 0 TO Ubound(arrVarer,2)
		Response.write "<tr><td>"& arrVarer(0,i) &"</td><td>"& arrVarer(1,i) &"</td></tr>"& vbCrLf
	Next
End if
Response.write "</table>"
%>

Open in new window

0
trg_dk
Asked:
trg_dk
  • 13
  • 10
1 Solution
 
Louis01Commented:
Just a guess... Could it be that you are hitting a NULL value? I think MD5(NULL) will give the same error.
0
 
trg_dkAuthor Commented:
Louis01 : Nope - not possible.
And SELECT md5(null) returns NULL - no error.

But thanks for the reply :-)

Mark
0
 
Louis01Commented:
The line (21) that gives you the error. Is it the same one as in your code snippet?
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
trg_dkAuthor Commented:
Yes it is - it is the response.write arrVarer(0,i) that fails - this is the array that holds the md5 value.
0
 
Louis01Commented:
Does changing lines 19 to 23 to the following change anything?
Response.write "<tr>"
For LBound(arrVarer,1) = 0 TO UBound(arrVarer,1)
	For c = LBound(arrVarer,2) TO UBound(arrVarer,2)
		Response.write "<td>" & arrVarer(r,c) & "</td>"
		Response.write "<td>"& arrVarer(r,c) &"</td>"
	Next
Next
Response.write "</tr>"& vbCrLf

Open in new window

0
 
Louis01Commented:
Sorry - my code snippet has errors. Should be:
For r = LBound(arrVarer,1) TO UBound(arrVarer,1)
	Response.write "<tr>"
	For c = LBound(arrVarer,2) TO UBound(arrVarer,2)
		Response.write "<td>" & arrVarer(r,c) & "</td>"
	Next
	Response.write "</tr>"& vbCrLf
Next

Open in new window

0
 
trg_dkAuthor Commented:
Hmm - the code fails in the For loop - havent seen this kind of FOR loop before (in ASP at least)
Error :
Microsoft VBScript compilation  error '800a0410'
 Invalid 'for' loop control variable
 /firebee_test.asp, line 21 For LBound(arrVarer,1) = 0 TO UBound(arrVarer,1)

0
 
trg_dkAuthor Commented:
sorry - didnt see your new code - will test right away
0
 
trg_dkAuthor Commented:
same error:

Microsoft VBScript runtime  error '800a000d'
 Type mismatch
 /firebee_test.asp, line 23

line 23 = Response.write "<td>" & arrVarer(r,c) & "</td>"
0
 
Louis01Commented:
What type of field is [varenavn] and what kind of data does it fail on?
To test - try:
(Check in your DB what is in the [id] & [varenavn] fields for the record it fails on)

on error resume next
Response.write "<tr>"
For LBound(arrVarer,1) = 0 TO UBound(arrVarer,1)
	For c = LBound(arrVarer,2) TO UBound(arrVarer,2)
		Response.write "<td>Row " & r & ", Col " & c & " = " & arrVarer(r,c) &"</td>"
		if err.number <> 0 Response.end
	Next
Next
Response.write "</tr>"& vbCrLf
on error goto 0
Response.write "</tr>"& vbCrLf

Open in new window

0
 
Louis01Commented:
Wrong again. Sorry
on error resume next
For LBound(arrVarer,1) = 0 TO UBound(arrVarer,1)
	Response.write "<tr>"
	For c = LBound(arrVarer,2) TO UBound(arrVarer,2)
		Response.write "<td>Row " & r & ", Col " & c & " = " & arrVarer(r,c) &"</td>"
		if err.number <> 0 Response.end
	Next
	Response.write "</tr>"& vbCrLf
Next
on error goto 0

Open in new window

0
 
trg_dkAuthor Commented:
HI Louis
This doesnt really change anything other than it doesnt show the error

You can test the page her :
http://raskerollinger.dk/firebee_test.asp?md5=true

The querystrign md5=true - change it to false and you'll see the query without md5

the value for varenavn is string
0
 
Louis01Commented:
I think maybe you get 0 rows...
(Carefull using NOT and OR together... Gets very confusing)

<%
Session.LCID = 1030
dbsetup = "Driver={MySQL ODBC 3.51 Driver}; Server=xxx; Port=3306; Option=131072; Stmt=; Database=xxx;uid=xxx; pwd=xxx;"
Set dbConn = Server.CreateObject("ADODB.Connection")
dbConn.Open dbsetup
 
If request.querystring("md5") = "true" Then
	sql = "SELECT md5(id), varenavn FROM produkter ORDER BY id;"
Else
	sql = "SELECT id, varenavn FROM produkter ORDER BY id;"
End If
 
Set rs = dbConn.Execute( sql )
if rs.eof Then
	Response.write "0 rows returned."
else
	arrVarer = rs.getrows
	Response.write "<a href='?md5=false'>UDEN md5 (SQL = SELECT id, varenavn FROM produkter ORDER BY id;)</a><br/><a href='?md5=true'>MED md5 (SQL = SELECT md5(id), varenavn FROM produkter ORDER BY id;)</a><br/><br/>"
	Response.write "<table width='300' border='1'><tr><td>ID</td><td>Varenavn</td></tr>"
	if UBound(arrVarer,2) > 0 Then
		For i = 0 TO Ubound(arrVarer,2)
			Response.write "<tr><td>"& arrVarer(0,i) &"</td><td>"& arrVarer(1,i) &"</td></tr>"& vbCrLf
		Next
	End if
	Response.write "</table>"
End if
rs.close
%>

Open in new window

0
 
trg_dkAuthor Commented:
?? Not sure I follow what you mean - you can see the SQL query's right there

If request.querystring("md5") = "true" Then
        sql = "SELECT md5(id), varenavn FROM produkter ORDER BY id;"
Else
        sql = "SELECT id, varenavn FROM produkter ORDER BY id;"
End If

It is the exactly same query, only difference is the md5 encoding - unless I'm mistaking ?
0
 
Louis01Commented:
If (for example) there is a bug in MySQL ODBC 3.51 Driver that causes 0 rows to be returned when you use the MD5 function in a specific way, we can concentrate on that.
In my last post, I changed the code slightly to skip code if rs returned 0 rows. If we know rs returns 0 rows (no errors), we can concentrate on why the query does not behave as expected.
Maybe a simpler way would be better...
if rs.eof Then
  Response.write "0 rows returned."
else
   Response.write "More than 0 rows returned."
end if
(Next step would be to test the getRows function)
0
 
trg_dkAuthor Commented:
Hi Louis

I updated the page - still get's more than 0 rows. Also - it is only when I try to response.write it

Perhaps we should look at the data type in the array
0
 
trg_dkAuthor Commented:
It seems the value in the md5 is Byte()

Perhaps I can make it into a string ?
0
 
trg_dkAuthor Commented:
Well - I found a byte to char function and tested it :

if UBound(arrVarer,2) > 0 Then
    For i = 0 TO Ubound(arrVarer,2)
        Response.write "<tr><td>"& B2S( arrVarer(0,i) ) &"</td><td>"& arrVarer(1,i) &"</td></tr>"& vbCrLf
    Next
End if
Response.write "</table>"

Function B2S(Binary)
Dim I, S
For I = 1 To LenB(Binary)
S = S & Chr(AscB(MidB(Binary, I, 1)))
Next
B2S = S
End Function

Now it can print that value - but WHY :-( ?

Louis - thanks for your help - if you have anything to add please do - I'll reward you the 500 pts for the effort.

Cheers,
Mark
0
 
Louis01Commented:
MD5 function returns a binary string. It would seem that using the UPPER function might work.
SELECT UPPER(md5(id)), varenavn FROM produkter ORDER BY id;
0
 
trg_dkAuthor Commented:
Nope - fails again ?!
0
 
trg_dkAuthor Commented:
This is for the effort more than for a solution.
0
 
Louis01Commented:
Hi Mark

The problem you experience has something to do with the Character set.

It appears that the way the MD5 function behaves were changed. (The MD5(), SHA1(), and ENCRYPT() functions should return a binary string, but the result sometimes was converted to the character set of the argument. - Bug#20536)

For more info on Binary Strings (the type returned by the MD5 function) see http://dev.mysql.com/doc/refman/4.1/en/binary-varbinary.html

(Someone else experienced the same problem. The workaround they did was to use an external MD5 function. See http://forums.aspfree.com/asp-development-5/md5-hash-57661.html)

I cannot find anything on converting a binary string to a normal string (although I did not try CAST(MD5(id) as ???)

Cheers
0
 
trg_dkAuthor Commented:
Hi Louis

Thanks for your time - I did the same...

Function B2S(Binary)
Dim I, S
For I = 1 To LenB(Binary)
S = S & Chr(AscB(MidB(Binary, I, 1)))
Next
B2S = S
End Function


Cheers,
Mark
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

  • 13
  • 10
Tackle projects and never again get stuck behind a technical roadblock.
Join Now