All,
Sorry in advance - I'm a novice in most of the topics below (SQL, ASP). Anyway...
I've got a pretty simple web app that requires users to log in with a user name and password.
The front end creates a salted SHA1 hash of the password, and posts it (along with the user's name) to an ASP page.
That ASP page takes the data, calls a stored procedure in the SQL Server database, and passes the users name and hashed password; the stored procedure writes the info to the 'users' table.
The password column's type in the table is varbinary.
As far as I can tell, when the ASP gets the password (password = Request.Form("password")), it's a String.
I can 'trick' SQL Server into handling it as a varbinary if I create the query this way:
query = "EXEC sp_save_user @username='" & username & "', @password=0x" & password
IOW - I'm prepending an "0x" to the password string.
However, I've read that it's a better practice to use a parameterized query:
E.g.: SET objParam = objCommand.CreateParameter("@password",204, 1, 40, password)
However, this fails because the parameter is supposed to be binary (204), but password is a string.
So, how do I convert a string like "4a5e6a8d521ed487b81c91e131cf27e8dae9b783" to a binary in ASP?
Many thanks in advance!
I remember the days when I used to bash my head on this kind of thing. I suggest you get an upgrade to ASP.Net, but in the mean time the following code (VBScript) should do what you want:
<%
Dim result
main()
function main()
Dim userName : userName = "Martin"
Dim data : data = "4a5e6a8d521ed487b81c91e131cf27e8dae9b783"
Dim db: Set db = Server.CreateObject("ADODB.Connection")
db.Open "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Password=password1;Initial Catalog=Test;Data Source=(local)"
Dim cmd : Set cmd = Server.CreateObject("ADODB.Command")
cmd.ActiveConnection = db
cmd.CommandText = "dbo.[sp_save_user]"
cmd.CommandType = 4
cmd.Parameters.Append cmd.CreateParameter("@UserName", 200, 1, 50, userName)
Dim bytes : bytes = stringToBinary(data)
cmd.Parameters.Append cmd.CreateParameter("@Password", 204, 1, LenB(bytes), bytes)
cmd.Execute()
db.Close
result = "done"
end function
function stringToBinary(str)
dim ahex
for i=0 to len(str) - 1 step 2
Dim strDigit1
Dim strDigit2
strDigit1 = Ucase(Mid(str, i+1, 1))
strDigit2 = Ucase(Mid(str, i+2, 1))
Dim byteDigit1
Dim byteDigit2
byteDigit1 = InStr("0123456789ABCDEF", strDigit1) - 1
byteDigit2 = InStr("0123456789ABCDEF", strDigit2) - 1
ahex = ahex & ChrB((byteDigit1 * 16) + byteDigit2)
next
stringToBinary = ahex
end function
%>
<html>
<head>
<title>Test</title>
</head>
<body>
<h1><%= result %></h1>
</body>
</html>
I assume you have scope to change the DB... more specifically the Stored Procedure?
Why not just accept the hash as a string and CAST it to varbinary within the SProc?
Reading further, there is no built-in String -> Varbinary function in SQL Server, but this function has been offered as an easy solution,
Maybe a bit unrelated, but I'm wondering, if you are salting the hash on the client, how are you exactly keeping the salt secure?
