创建非对称密钥'+@MANV+‘

Question

    CREATE PROC [dbo].[SP_INS_PUBLIC_NHANVIEN](@MANV varchar(20), @HOTEN nvarchar(100), @EMAIL varchar(20), @LUONGCB nvarchar(100), 
@TENDN nvarchar(100), @MK nvarchar(20))
AS

BEGIN

DECLARE @C NVARCHAR(MAX) 
SET @C = 
    'CREATE ASYMMETRIC KEY '+@MANV+'
     WITH ALGORITHM = RSA_512
     ENCRYPTION BY PASSWORD = '+@MK+''

EXEC(@C)

INSERT INTO NHANVIEN (MANV, HOTEN, EMAIL, LUONG, TENDN, MATKHAU, PUBKEY) 
VALUES (@MANV, @HOTEN, @EMAIL, ENCRYPTBYASYMKEY(ASYMKEY_ID(@MANV),@LUONGCB), @TENDN, HASHBYTES('SHA1', @MK), @MANV)

END;

当我使用这个存储过程时，LUONG的值是NULL。有什么问题吗？

它不会在文件非对称密钥中创建RSA，这意味着创建非对称密钥不起作用。为什么？

Answer 1

..feel可以自由改进..

--drop asymmetric key [myasymmetrickey];
--go


--key
declare @asymkeyname sysname, @asymkeypassword nvarchar(128);
-- encryptme
declare @encryptme nvarchar(30) /*better make it 26 if nvarchar or 53 if varchar*/;

select @asymkeyname = N'myasymmetrickey', @asymkeypassword = N'123456?';
select @encryptme = N'encrypt:53byte=max26nchars'; --rsa_512 can encrypt max 53bytes..if datalength(string) > 53 then encrypresult=null

begin
    declare @sql nvarchar(500);

    if nullif(@asymkeyname, '') is null 
    begin
        raiserror('no key name!', 16, 1);
        return; -- -1
    end


    if asymkey_id(@asymkeyname) is null 
    begin 

        if len(isnull(@asymkeypassword, N'') < 6)
        begin
            raiserror('password too short!', 16, 1);
            return; -- -1
        end
        
        begin try
            select @sql = N'CREATE ASYMMETRIC KEY '+QUOTENAME(@asymkeyname)+N' WITH ALGORITHM = RSA_512 ENCRYPTION BY PASSWORD = ''' + replace(@asymkeypassword, '''', '''''') + N'''';
            exec (@sql)
        end try
        begin catch
            select error_message() as error;
            return -- -1
        end catch
    end

    select 
        encryptbyasymkey(asymkey_id(@asymkeyname),@encryptme) as binaryencrypted, 
        hashbytes('SHA1', @asymkeypassword) as asymkeypasswordhashed;
end

Answer 2

试试这个：

CREATE Proc sp_Ins_Public_NHANVIEN (
            @manv    sysname,       @hoten nvarchar(100), @email varchar(20),
            @luongcb nvarchar(100), @tendn nvarchar(100), @mk   nvarchar(20)) as
begin
    declare @c nvarchar(MAX)

    if Asymkey_Id(@manv) is null
    begin
        set @c = 'CREATE ASYMMETRIC KEY '    + quoteName(@manv) + ' ' +
                 'WITH ALGORITHM = RSA_512 ' + 
                 'ENCRYPTION BY PASSWORD = ' + quoteName(@mk , nchar(39))
        exec (@c)
    end

    insert into NHANVIEN (MANV, HOTEN, EMAIL, LUONG, TENDN, MATKHAU, PUBKEY)
    values (@manv, @hoten, @email,
            EncryptByAsymKey(Asymkey_Id(@manv), @luongcb),
            @tendn, hashBytes('SHA1', @mk), 
            (select convert(nvarchar(256), public_key, 2)
               from sys.asymmetric_keys where name = @manv)
            )
end