SQL Server
SQL Server
Sign Mexico Pedimento
See more Misc Examples
Add a signature to a Mexico pedimento file.Chilkat SQL Server Downloads
-- 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 requires the Chilkat API to have been previously unlocked.
-- See Global Unlock Sample for sample code.
-- 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
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
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
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
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
EXEC @hr = sp_OACreate 'Chilkat.Rsa', @rsa OUT
EXEC sp_OAMethod @rsa, 'UsePrivateKey', @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
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, 'SignRawBd', @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
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
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