如何转换T-SQL列类型和数据就地？ [英] How to convert a T-SQL column type and data in-place?

问题描述

我有一个网站的SQL Server 2005数据库，以明文存储用户密码，我想散列和盐。我知道如何使用 HashBytes 函数来获取和比较哈希，但我不知道转换现有密码列数据的最佳方式。它目前存储为 varchar（50）列，我想使用 binary（20）规划使用SHA-1。

I have a SQL Server 2005 database for a web site that stores user passwords in plaintext, and I would like to hash and salt them. I know how to use the HashBytes function to get and compare hashes, but I don't know the best way to go about converting the existing password column data. It's currently stored as a varchar(50) column and I would like to use binary(20) since I'm planning on using SHA-1.

我在考虑 SELECT INTO 一个临时表， ALTER 现有列类型，然后 INSERT 返回用户ID匹配的哈希和salted密码。这是一个有效的方法吗？在没有临时表的情况下有没有办法在原地进行？

I was thinking about SELECT INTO a temporary table, ALTER the existing column type, then INSERT the hashed and salted passwords back where the user ID's match. Is this a valid approach? Is there a way to do it in-place without a temp table?

谢谢！

推荐答案

您只需将二进制信息存储为十六进制字符串。这有一些好处：

You could just store the binary info as a hex string. This has some benefits:

• 允许在原地进行UPDATE查询


• 不需要临时表


• 无需更改表格结构（您的最终结果是一个字符串）

我建议写函数来帮助你进行盐化/散列/十六进制转换（反之亦然）。

I suggest writing functions to help you with the salting/hashing/hex-conversion (and vice versa.)

这篇关于如何转换T-SQL列类型和数据就地？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！