(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.

Note: This example requires Chilkat v11.0.0 or greater.

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
    -- Important: Do not use nvarchar(max).  See the warning about using nvarchar(max).
    DECLARE @sTmp0 nvarchar(4000)
    DECLARE @success int
    SELECT @success = 0

    -- This example assumes the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

    DECLARE @rsa int
    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 @pubKeyXml nvarchar(4000)
    SELECT @pubKeyXml = '<RSAPublicKey><Modulus>of3im3mRgd2NLXIGoK6uYLg6jj0Ug2b42rnqa5Tbwz2ieFqMJqt+++x2oqLYGurlz49nt+7/785g3XYWqoka4u9c9zul6YubIjnBM72dQy7rEkEfbUxgjcxqXyjZFx+FpaxFUecLu688XEu+9UA42VKiCgcl+E7TrqnfeeYpNXc=</Modulus><Exponent>AQAB</Exponent></RSAPublicKey>'

    DECLARE @pubKey int
    EXEC @hr = sp_OACreate 'Chilkat.PublicKey', @pubKey OUT

    EXEC sp_OAMethod @pubKey, 'LoadFromString', @success OUT, @pubKeyXml
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @pubKey, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @rsa
        EXEC @hr = sp_OADestroy @pubKey
        RETURN
      END

    EXEC sp_OAMethod @rsa, 'UsePublicKey', @success OUT, @pubKey
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @rsa, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @rsa
        EXEC @hr = sp_OADestroy @pubKey
        RETURN
      END

    -- 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
    EXEC @hr = sp_OADestroy @pubKey


END
GO