处理存储为varbinary在SQL Server和传统的ASP散列密码 [英] Handling hashed passwords stored as varbinary in SQL Server and classic ASP
问题描述
所有，
提前对不起 - 我在下面的大多数主题（SQL，ASP）新手。反正...
Sorry in advance - I'm a novice in most of the topics below (SQL, ASP). Anyway...
我有一个要求用户使用用户名和密码来登录pretty简单的Web应用程序。
I've got a pretty simple web app that requires users to log in with a user name and password.
前端创建密码的盐渍SHA1哈希值，然后发布（用户名）复制到ASP页。
The front end creates a salted SHA1 hash of the password, and posts it (along with the user's name) to an ASP page.
这是ASP页面获取数据，调用SQL Server数据库的存储过程，并通过用户名和哈希密码;存储过程中写入信息，以用户表。
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.
密码列在表的类型是VARBINARY。
The password column's type in the table is varbinary.
据我所知道的，当ASP获取密码（password =的Request.Form（密码）），这是一个字符串。
As far as I can tell, when the ASP gets the password (password = Request.Form("password")), it's a String.
我可以'绝招'的SQL Server将处理它作为varbinary如果我创建查询是这样的：
I can 'trick' SQL Server into handling it as a varbinary if I create the query this way:
查询=EXEC sp_save_user @用户名='＆放大器;用户名和放大器; '，@密码= 0X＆放大器;密码
query = "EXEC sp_save_user @username='" & username & "', @password=0x" & password
IOW - 我是prepending的0X的口令字符串
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:
例如：SET objParam = objCommand.CreateParameter（@密码，204，1，40，密码）
E.g.: SET objParam = objCommand.CreateParameter("@password",204, 1, 40, password)
然而，该失败，因为参数应该是二进制（204），但密码是一个字符串。
However, this fails because the parameter is supposed to be binary (204), but password is a string.
所以，我怎么样4a5e6a8d521ed487b81c91e131cf27e8dae9b783的字符串转换为ASP二进制？
So, how do I convert a string like "4a5e6a8d521ed487b81c91e131cf27e8dae9b783" to a binary in ASP?
提前感谢！
推荐答案
我记得我还是来砸我的头对这种事情的日子。我建议你升级到ASP.Net，但在平均时间如下code（VBScript）的应该做你想要什么：
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>
这篇关于处理存储为varbinary在SQL Server和传统的ASP散列密码的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
