(SQL Server) Sign Mexico Pedimento

Add a signature to a Mexico pedimento file.

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)
    -- This example requires the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

    DECLARE @success int

    -- This is the contents before signing:

    -- 500|1|3621|4199800|400||
    -- 601|3621|4199800|400|IN|1||EKU9003173C9|EKU9003173C9FRNN09|1||
    -- 507|4199800|IM|2006-7888">
    -- 507|4199800|MS|2">
    -- 800|4199800|1">
    -- 801|M3621037.222|1|5|011|

    -- This is the contents after signing

    -- 500|1|3621|4199800|400||
    -- 601|3621|4199800|400|IN|1||EKU9003173C9|EKU9003173C9FRNN09|1||
    -- 507|4199800|IM|2006-7888">
    -- 507|4199800|MS|2">
    -- 800|4199800|1|fhP2Ker54D2+3+UZch23F0E72 .... 9qNSPIuAqpj524qLZbbA==|30001000000500003416|
    -- 801|M3621037.222|1|5|011|

    -- First create the text to be signed.
    DECLARE @bCRLF int
    SELECT @bCRLF = 1
    DECLARE @sb int
    -- Use "Chilkat_9_5_0.StringBuilder" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sb OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    -- Use CRLF line endings.
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, '500|1|3621|4199800|400||', @bCRLF
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, '601|3621|4199800|400|IN|1||EKU9003173C9|EKU9003173C9FRNN09|1||', @bCRLF
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, '507|4199800|IM|2006-7888">', @bCRLF
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, '507|4199800|MS|2">', @bCRLF

    -- Generate the MD5 hash of what we have so far..
    DECLARE @md5_base64 nvarchar(4000)
    EXEC sp_OAMethod @sb, 'GetHash', @md5_base64 OUT, 'md5', 'base64', 'utf-8'

    PRINT 'MD5 hash = ' + @md5_base64

    -- Complete the original file.
    -- After signing, we'll update the BASE64_SIGNATURE and CERT_SERIAL
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, '800|4199800|1|BASE64_SIGNATURE|CERT_SERIAL|', @bCRLF
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, '801|M3621037.222|1|5|011|', @bCRLF

    -- We're going to sign the MD5 hash using the private key.
    DECLARE @privKey int
    -- Use "Chilkat_9_5_0.PrivateKey" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.PrivateKey', @privKey OUT

    EXEC sp_OAMethod @privKey, 'LoadAnyFormatFile', @success OUT, 'qa_data/certs/mexico_test/Certificados_de_Prueba/Certificados_Pruebas/Personas Morales/EKU9003173C9_20230517223532/CSD_EKU9003173C9_20230517223903/CSD_Sucursal_1_EKU9003173C9_20230517_223850.key', '12345678a'
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @privKey, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @sb
        EXEC @hr = sp_OADestroy @privKey
        RETURN
      END

    -- Generate the ASN.1 to be signed.

    -- <sequence>
    --     <sequence>
    --         <oid>1.2.840.113549.2.5</oid>
    --         <null/>
    --     </sequence>
    --     <octets>SwxHfaJhG+N3pPqay6UzVA==</octets>
    -- </sequence>

    DECLARE @xml int
    -- Use "Chilkat_9_5_0.Xml" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Xml', @xml OUT

    EXEC sp_OASetProperty @xml, 'Tag', 'sequence'
    EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'sequence|oid', '1.2.840.113549.2.5'
    EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'sequence|null', ''
    EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'octets', @md5_base64

    DECLARE @asn int
    -- Use "Chilkat_9_5_0.Asn" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Asn', @asn OUT

    EXEC sp_OAMethod @xml, 'GetXml', @sTmp0 OUT
    EXEC sp_OAMethod @asn, 'LoadAsnXml', @success OUT, @sTmp0

    EXEC sp_OAMethod @asn, 'GetEncodedDer', @sTmp0 OUT, 'base64'
    PRINT 'ASN.1 = ' + @sTmp0

    -- Sign with the private key.
    DECLARE @rsa int
    -- Use "Chilkat_9_5_0.Rsa" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Rsa', @rsa OUT

    EXEC sp_OAMethod @rsa, 'ImportPrivateKeyObj', @success OUT, @privKey
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @rsa, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @sb
        EXEC @hr = sp_OADestroy @privKey
        EXEC @hr = sp_OADestroy @xml
        EXEC @hr = sp_OADestroy @asn
        EXEC @hr = sp_OADestroy @rsa
        RETURN
      END

    -- Create the opaque signature.
    DECLARE @bdSig int
    -- Use "Chilkat_9_5_0.BinData" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.BinData', @bdSig OUT

    EXEC sp_OAMethod @asn, 'GetEncodedDer', @sTmp0 OUT, 'base64'
    EXEC sp_OAMethod @bdSig, 'AppendEncoded', @success OUT, @sTmp0, 'base64'
    EXEC sp_OAMethod @rsa, 'OpenSslSignBd', @success OUT, @bdSig
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @rsa, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @sb
        EXEC @hr = sp_OADestroy @privKey
        EXEC @hr = sp_OADestroy @xml
        EXEC @hr = sp_OADestroy @asn
        EXEC @hr = sp_OADestroy @rsa
        EXEC @hr = sp_OADestroy @bdSig
        RETURN
      END

    -- bd now contains the opaque signature, which embeds the ASN.1, which contains the MD5 hash.
    -- We're going to add this line:
    -- 800|4199800|1|BASE64_SIGNATURE|CERT_SERIAL_NUM|

    DECLARE @cert int
    -- Use "Chilkat_9_5_0.Cert" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Cert', @cert OUT

    EXEC sp_OAMethod @cert, 'LoadFromFile', @success OUT, 'qa_data/certs/mexico_test/Certificados_de_Prueba/Certificados_Pruebas/Personas Morales/EKU9003173C9_20230517223532/CSD_EKU9003173C9_20230517223903/CSD_Sucursal_1_EKU9003173C9_20230517_223850.cer'
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @cert, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @sb
        EXEC @hr = sp_OADestroy @privKey
        EXEC @hr = sp_OADestroy @xml
        EXEC @hr = sp_OADestroy @asn
        EXEC @hr = sp_OADestroy @rsa
        EXEC @hr = sp_OADestroy @bdSig
        EXEC @hr = sp_OADestroy @cert
        RETURN
      END
    DECLARE @serialHex nvarchar(4000)
    EXEC sp_OAGetProperty @cert, 'SerialNumber', @serialHex OUT
    -- The serial in hex form looks like this:   3330303031303030303030353030303033343136
    -- Decode to us-ascii.
    DECLARE @sbSerial int
    -- Use "Chilkat_9_5_0.StringBuilder" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbSerial OUT

    EXEC sp_OAMethod @sbSerial, 'DecodeAndAppend', @success OUT, @serialHex, 'hex', 'us-ascii'

    EXEC sp_OAMethod @sbSerial, 'GetAsString', @sTmp0 OUT
    PRINT 'serial number in us-ascii: ' + @sTmp0

    DECLARE @numReplaced int
    EXEC sp_OAMethod @sbSerial, 'GetAsString', @sTmp0 OUT
    EXEC sp_OAMethod @sb, 'Replace', @numReplaced OUT, 'CERT_SERIAL', @sTmp0
    EXEC sp_OAMethod @bdSig, 'GetEncoded', @sTmp0 OUT, 'base64'
    EXEC sp_OAMethod @sb, 'Replace', @numReplaced OUT, 'BASE64_SIGNATURE', @sTmp0


    PRINT '------------------------------------'

    PRINT 'Result:'
    EXEC sp_OAMethod @sb, 'GetAsString', @sTmp0 OUT
    PRINT @sTmp0

    EXEC @hr = sp_OADestroy @sb
    EXEC @hr = sp_OADestroy @privKey
    EXEC @hr = sp_OADestroy @xml
    EXEC @hr = sp_OADestroy @asn
    EXEC @hr = sp_OADestroy @rsa
    EXEC @hr = sp_OADestroy @bdSig
    EXEC @hr = sp_OADestroy @cert
    EXEC @hr = sp_OADestroy @sbSerial


END
GO