(SQL Server) Charset Considerations when RSA Encrypting Strings

When encrypting strings, character encoding can be an issue. Encryption algorithms operate on binary data. If for example, your string includes a character such as é, the bytes used to represent it depend on the character encoding. In an "iso" encoding such as iso-8859-1, or in a "windows" encoding, such as "windows-1252", the é is represented by a single byte. In Unicode however, whether it's utf-8 or ucs-2, the é is represented by 2 bytes. When encrypting strings, the RSA component will first convert the string to the character encoding indicated by the Charset property. When decrypting strings, the RSA component converts from the character encoding indicated by the Charset property to the application's expected charset. In VB6 for example, this would be Unicode. In C++, it's ANSI. In Java, it's utf-8.

Chilkat ActiveX Downloads ActiveX for 32-bit and 64-bit Windows

-- Important: See this note about string length limitations for strings returned by sp_OAMethod calls.
--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
    DECLARE @hr int
    -- This example assumes the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

    DECLARE @rsa int
    -- Use "Chilkat_9_5_0.Rsa" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Rsa', @rsa OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    -- RSA public key in XML format:
    DECLARE @publicKey nvarchar(4000)
    SELECT @publicKey = '<RSAPublicKey><Modulus>of3im3mRgd2NLXIGoK6uYLg6jj0Ug2b42rnqa5Tbwz2ieFqMJqt+++x2oqLYGurlz49nt+7/785g3XYWqoka4u9c9zul6YubIjnBM72dQy7rEkEfbUxgjcxqXyjZFx+FpaxFUecLu688XEu+9UA42VKiCgcl+E7TrqnfeeYpNXc=</Modulus><Exponent>AQAB</Exponent></RSAPublicKey>'

    DECLARE @success int
    EXEC sp_OAMethod @rsa, 'ImportPublicKey', @success OUT, @publicKey

    -- Use the utf-8 character encoding.  When encrypting, the
    -- plain text is first converted to utf-8 bytes and then
    -- RSA encrypted.
    EXEC sp_OASetProperty @rsa, 'Charset', 'utf-8'

    -- Encrypt a string and return the encrypted data base64-encoded:
    EXEC sp_OASetProperty @rsa, 'EncodingMode', 'base64'

    DECLARE @plainText nvarchar(4000)
    SELECT @plainText = 'éééé'

    DECLARE @usePrivateKey int
    SELECT @usePrivateKey = 0
    DECLARE @encryptedStr nvarchar(4000)
    EXEC sp_OAMethod @rsa, 'EncryptStringENC', @encryptedStr OUT, @plainText, @usePrivateKey

    PRINT @encryptedStr

    EXEC @hr = sp_OADestroy @rsa


END
GO