如何使用特定值更新 varbinary 字段? [英] How to update a varbinary field with a specific value?

问题描述

基本上我想给用户一个特定的密码，这样我就可以在系统上测试一些功能，因为我只有我们的管理员帐户，我不能玩，我只是选择一个随机帐户，所以我可以做我的测试.所以这是我尝试更新:

Basically I am trying to give a user a certain password so I can test some functionality on a system, as I only have our admin account and I can't play with that I am just picking a random account so I can do my testing. So here is my attempt at an update:

UPDATE dbo.Login
SET
  Salt=CAST('bPftidzyAQik' AS VARBINARY),
  Password=CAST('0x2B89C2954E18E15759545A421D243E251784FA009E46F7A163926247FDB945F85F095DBB1FFF5B2B43A6ADAE27B8C46E176902412C4F8943E39528FF94E0DD5B' AS VARBINARY)
WHERE LoginID=10947

它运行良好，但是数据库中的代码一个看起来是日语，另一个的语法看起来不错，但它不是我输入的值，我希望它使用我输入的确切值，以便我可以签名在.我该怎么做?我尝试了几种不同的转换和转换解决方案，但都没有运气.

It runs fine however the code in the database looks japanese for one and the syntax for the other looks fine but its not the value i'm putting in, I want it to use the exact value I put in so I can sign in. How do I do this? I've tried several different cast and convert solutions with no luck.

推荐答案

试试这个:

UPDATE dbo.Login
SET
  Salt=CAST('bPftidzyAQik' AS VARBINARY),
  Password=0x2B89C2954E18E15759545A421D243E251784FA009E46F7A163926247FDB945F85F095DBB1FFF5B2B43A6ADAE27B8C46E176902412C4F8943E39528FF94E0DD5B
WHERE LoginID=10947

(您不需要将存储十六进制值的字符串转换为 varbinary - 默认情况下就是这样，没有引号)

(you don't need to cast a string storing a hex value into varbinary - that's just what it is by default, without the quotes)

问题是，您如何生成该密码十六进制值，并且您是否使用与读取时相同的编码来生成它?如果您打算将该数据转换回字符串，则需要一些代码来执行此操作.这是我编写的一个函数:

The question is, how are you generating that password hex value, and are you using the same encoding to generate it as you are to read it? If you are planning on converting that data back into a string, you'll need some code to do so. Here's a function I wrote that does this:

CREATE FUNCTION ConvertBinary
(  
    @value AS varbinary(max)
) RETURNS VARCHAR(MAX) AS BEGIN  

    DECLARE @result AS varbinary(max),
            @result2 AS varchar(max),
            @idx AS bigint;

    IF @value IS null
        return null;

    SELECT @result = @value;  
    SELECT @result2 = '';
    SELECT @idx = 1;

    WHILE substring(@result, @idx, 1) != 0 AND @idx < len(@result)
        BEGIN
            SET @result2 = @result2 + cast(substring(@result,@idx,1) as char(1));  
            SET @idx = @idx + 1;
        END

    RETURN @result2;  

END 

不过，我不知道这对您有多大帮助，因为很可能使用这些字段的任何应用程序处理值的方式与此函数预期的不同.作为记录，此函数采用原始 utf-8 字符串的 varbinary 值，并返回该字符串的 varchar 值.祝你好运！

I don't know how helpful this will be for you, though, since it's very possible that whatever app is using these fields is handling the values differently than this function expects. For the record, this function takes a varbinary value which was originally utf-8 string, and returns the varchar value of that string. Good luck!

这篇关于如何使用特定值更新 varbinary 字段?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！