parameter value passed to ora_hash 1

[XTnCIS]

When I create a tableadapter in VS2008 which calls the ora_hash function (oracle 10g db) AND if I pass the password string as a parameter password) rather than passing the password as a string ('hrss3k') I get a different hash value than if I execute the same hash statement in oracle.

I get matching hash value from my tableadapter as I get from sql-plus if execute the Tableadapter with a hardcoded password string...

My gut first told me that maybe when I define a parameter password) in my table adapter that it might be internally using this as a variant or something that pads with spaces before executing the TableAdapter... but I can't get around that if that is what is happening.

Does anybody have any gut reactions to this that might help me track down a way to figure this out?

I am going to post this same problem in an Oracle forum but I think I might be lucky to find any help on this issue.

Thanks!!!

 

[RiverGuy]

I get a different hash value than if I execute the same hash statement in oracle.

Are you saying that calling the statement from VB versus the Oracle query tool returns different results?

I don't know much about Oracle, but SQL Server can store unicode and non-unicode strings. And strings in .Net are unicode.

For example, the following code returns different results:

--non-unicode
SELECT CHECKSUM('hello world')

--unicode
SELECT CHECKSUM(N'hello world')

 

[XTnCIS]

If my TableAdapter is:

SELECT ora_hash('hrss3k', 1257981, ss.SALT) AS HASH
FROM HR.SEC_SUPPORT ss
WHERE (ss.USER_ID = 'ibt10')

of if my TableAdapter is:

SELECT ora_hashpassword, 1257981, ss.SALT) AS HASH
FROM HR.SEC_SUPPORT ss
WHERE (ss.USER_ID = 'ibt10')

Even if I pass in 'hrss3k' to my TableAdapter, I get different values back from my ora_hash function.

The first example above returns the same hash value as if I ran the statement in Oracle. But the second one, where I pass the password into the TableAdapter does not return the same (although it is always consistent with itself).

The unicode might be a clue, I can try to see if I can force this somehow in Oracle. I think that somehow when I pass the assword into the TableAdapter, that VS is padding it, or converting it to a different char-set, or something but I haven't been able to track that down yet.

Thanks for the reply... let me know if you have any other ideas?

 

[RiverGuy]

I just ran a test in SQL Server. The first block of code passes in a string literal to SQL Server which results in the hash of the non-unicode string which I posted above.

The second block of code passes in a parameter which is treated as a unicode string and returns the same result as the second code (unicode) I posted above.

The third block of code uses the parameter, but the SQL casts the unicode string to a non-unicode string and returns the same result as the first example (non-unicode) I posted above.

So I'm betting this is your issue.

        cmd.CommandText = "SELECT CHECKSUM('hello world')"
        cmd.Connection = con
        MessageBox.Show(cmd.ExecuteScalar.ToString)

        cmd.CommandText = "SELECT CHECKSUM(@Val)"
        cmd.Parameters.AddWithValue("@Val", "hello world")
        MessageBox.Show(cmd.ExecuteScalar.ToString)

        cmd.CommandText = "SELECT CHECKSUM(CONVERT(VARCHAR(50), @Val))"
        MessageBox.Show(cmd.ExecuteScalar.ToString)

 

[RiverGuy]

I posted that before I saw your reply. See if you can apply my logic in my third block of code from my post above to your Oracle statement. That is, try casting your assword parameter to VARCHAR in the SQL.

 

[XTnCIS]

Thank you for your replies...

While I was doing my testing I was using the <Query Builder> and <Execute Query> buttons in the Configure Table Adapter window. I would type in my assword string and it would return the result of my query (the hash value) for me to veiw in the query builder.

Based on what you said, I figured I would go a step further and call the table adapter from my login form and just display what was returned from the tableadapter in a textbox... then start trying to cast what I was passing in different ways.

Come to find out, when I call the tableAdapter from within code (rather than using the <Execute Query>) it does return the 'correct' hash value.

I think you are right, unicode might be at the bottom of this. But evidently there is a different in the <Execute Query> and calling the tableAdapter from code?

A mystery I'll have to get back to someday... but it does return the same hash code from code as if I run the statement in Oracle

Thanks again
XTnCIS