search

bacen / pix-api

API Pix: a API do Arranjo de Pagamentos Instantâneos Brasileiro, Pix, criado pelo Banco Central do Brasil.
https://bacen.github.io/pix-api
2.31k stars 262 forks source link

Validação do CRC 16 e EMV #189

Open felipecamargo opened 3 years ago

felipecamargo commented 3 years ago

Percebi que muitos QR CODE estão sendo enviados com CRC 16 calculado invalido. E pouquíssimos PSP's pagadores estão validando tanto o CRC16 quanto o padrão do EMV Merchant Presented Mode como um todo.

Quanto a Estrutura EMV, pelo que eu observei não estão sendo validados:

A maioria dos PSPs estão aceitando QR CODEs conforme listado abaixo, provavelmente os único bancos que estão tentando realizar as consistências do Padrão EMV e também dos certificados dos sites QR CODE dinâmico são Itaú e Banco do Brasil.

Exemplo:

  1. Iniciado de forma incorreta >>> 0020101021226990014br.gov.bcb.pix2577qrcode.pix.bancobmg.com.br/qrs1/01mWFiCMHqRMdrkzWFWazsiurgfoqpsJFbuXx3IcpRqfj52040000530398654040.015802BR5917JEFFERSON F SOUZA6009SAO PAULO62290525QRS1-F5CMMUTDLOVWF8UBT7Q4630413DF -> calculado errado deveria ser 2E7D

  2. Mal formatado 000201 2640 0014BR.GOV.BCB.PIX 0114+55149916479660200 52040000 5303986 54030.05 802BR5921JULIO DE SOUZA RUGOLO6009SAO.PAULO6238050050300017BR.GOV.BCB.BRCODE01051.0.06304BD79

  3. CRC 16 invalido 00020126360014BR.GOV.BCB.PIX0114+55339999117485204000053039865802BR5924LEANDRO ALVES PEREIRA 6009SAO.PAULO623450300017BR.GOV.BCB.BRCODE01051.0.063044867 -> deveria ser C3F4.

  4. Mal formatado 000201 010212 2641 0005Cielo< deveria ser indicação de BR CODE -> 14br.gov.bcb.pix 0116001019064975000102087748103252040000530398654120000000031005802BR5921AUTO POSTO BASE AEREA6011CURITIBA PR801010033"https://www.cielo.com.br/qrcode"0116111117082C14677A0212171120183512030420000404000105020106020163043256

  5. Mal formatado 000201 2653 0014BR.GOV.BCB.PIX 0127takeshinho2001@yahoo.com.br 0200 tamanho 00 não é permitido pelo padrão EMV. 52040000530398654030.05802BR5922ANTONIO TAKESHI FUKUDA6009SAO.PAULO6238050050300017BR.GOV.BCB.BRCODE01051.0.063045B1A

  6. Merchant Name 000201 2636 0014br.gov.bcb.pix 0114+5531998925047 52040000 5303986 54071000.00 5802BR 5938SUELI DO CARMO PEREIRA DA SILVA KUSTER -- Limite de 25 caracteres 6008CONTAGEM 6241 0503*** 5030 0017br.gov.bcb.brcode 01051.0.0 63049602

  7. Mal Formatado (nubank) 000201 2662 0014BR.GOV.BCB.PIX 013639b6c11f-8383-4378-87c1-ce62df204ab8 0200 <<< NÃO PODE SER TAMANHO 00 5204000053039865406127.505802BR5925LARISSA JUSTUS RAYMUNDO 36009SAO PAULO61080540900062160512NUomZK5qzGvA630440BB

  8. Mal Formatado (nubank) 000201 2672 0014BR.GOV.BCB.PIX 18 01366ae8a1ab-8a46-49ef-9a14-6b03df474f6a 40 0210Toma lim㯵2 040000 530398654046.005802BR5924F D C HUMMEL EIRELI - ME6009SAO PAULO61080540900062160512NUNFFQwm3oLJ63040974

prjosemaria commented 1 year ago

Boa tarde a todos,

Segue solução que estou utilizando em PL/SQL:

CREATE OR REPLACE FUNCTION CRC16(V_STRING VARCHAR2) RETURN VARCHAR2 IS CURSOR C_BYTES (PIX_STRING VARCHAR2) IS SELECT TO_NUMBER(COLUMN_VALUE) AS BYTES FROM XMLTABLE( SUBSTR( DUMP(PIX_STRING), INSTR( DUMP(PIX_STRING), ':' )+1 ) );

C NUMBER; J NUMBER;

V_ANSWER VARCHAR2(4000) := 'FFFF'; V_POLINOMIO VARCHAR2(4) := '1021';

V_VERIFY NUMBER;

V_ID_CRC16 CHAR(2) := '63'; V_QTD_CRC16 CHAR(2) := '04'; BEGIN FOR R_BYTES IN C_BYTES(V_STRING||V_ID_CRC16||V_QTD_CRC16) LOOP C := R_BYTES.BYTES;

  IF (C_BYTES%ROWCOUNT = 1) THEN
    V_ANSWER := TO_NUMBER(V_ANSWER, 'XXXX');
  END IF;

  J := (C * POWER(2,8));
  V_ANSWER := (J + V_ANSWER) - BITAND(J, V_ANSWER) * 2;

  FOR BITWISE IN 0..7 LOOP
    V_ANSWER := V_ANSWER * POWER(2,1);
    V_VERIFY := BITAND(V_ANSWER, TO_NUMBER('10000', 'XXXXX'));

    IF (V_VERIFY <> 0) THEN
      V_ANSWER := (V_ANSWER + TO_NUMBER(V_POLINOMIO, 'XXXX')) - BITAND(V_ANSWER, TO_NUMBER(V_POLINOMIO, 'XXXX')) * 2;
    END IF;

    V_ANSWER := BITAND(V_ANSWER,  TO_NUMBER('FFFF', 'XXXX'));
  END LOOP;
END LOOP;

RETURN V_ID_CRC16||V_QTD_CRC16||TRIM(TO_CHAR(V_ANSWER, 'XXXX'));

END CRC16;

Estou utilizando Oracle Database 11g Release 11.2.0.4.0 - 64bit Production.

Att,

Olá seria possivel converter essa função para postgresql 9?

Alguém teria ela para postgresql 9+

derzu commented 1 year ago

Segue um exemplo de geração do PIX qr code estático todo em javascript: https://github.com/derzu/pix-qrcode

maicon451 commented 1 year ago

Boa tarde!

Alguém já fez essa função para DataFlex?

baldinifabio commented 11 months ago

Valeu meu querido !! Agradeço muito a ajuda !!

Você conseguiu gerar? se sim para qual linguagem? não estou conseguindo gerar em c# :/

Neste Repositorio tem de várias linguagens - https://gist.github.com/tijnkooijmans/10981093

duduccosta commented 4 months ago

Está aqui a função em SQL (SQL Server) funcionando:

DROP FUNCTION IF EXISTS dbo.CRC16
GO
CREATE FUNCTION dbo.CRC16(
  @Input VARCHAR(max)
) RETURNS nvarchar(4) AS
BEGIN
  DECLARE @Lookup BINARY(512) = -- 256 * 2-byte lookup values = 512 bytes
    0x0000102120423063408450A560C670E781089129A14AB16BC18CD1ADE1CEF1EF +
    0x123102103273225252B5429472F762D693398318B37BA35AD3BDC39CF3FFE3DE +
    0x246234430420140164E674C744A45485A56AB54B85289509E5EEF5CFC5ACD58D +
    0x365326721611063076D766F6569546B4B75BA77A97198738F7DFE7FED79DC7BC +
    0x48C458E5688678A70840186128023823C9CCD9EDE98EF9AF89489969A90AB92B +
    0x5AF54AD47AB76A961A710A503A332A12DBFDCBDCFBBFEB9E9B798B58BB3BAB1A +
    0x6CA67C874CE45CC52C223C030C601C41EDAEFD8FCDECDDCDAD2ABD0B8D689D49 +
    0x7E976EB65ED54EF43E132E321E510E70FF9FEFBEDFDDCFFCBF1BAF3A9F598F78 +
    0x918881A9B1CAA1EBD10CC12DF14EE16F108000A130C220E35004402570466067 +
    0x83B99398A3FBB3DAC33DD31CE37FF35E02B1129022F332D24235521462777256 +
    0xB5EAA5CB95A88589F56EE54FD52CC50D34E224C314A004817466644754244405 +
    0xA7DBB7FA879997B8E75FF77EC71DD73C26D336F2069116B06657767646155634 +
    0xD94CC96DF90EE92F99C889E9B98AA9AB584448657806682718C008E1388228A3 +
    0xCB7DDB5CEB3FFB1E8BF99BD8ABBBBB9A4A755A546A377A160AF11AD02AB33A92 +
    0xFD2EED0FDD6CCD4DBDAAAD8B9DE88DC97C266C075C644C453CA22C831CE00CC1 +
    0xEF1FFF3ECF5DDF7CAF9BBFBA8FD99FF86E177E364E555E742E933EB20ED11EF0;

  DECLARE @CRC INT = 0xFFFF, @Number INT;
  DECLARE csrChecksumEnumerator CURSOR LOCAL STATIC READ_ONLY FORWARD_ONLY FOR
    SELECT [number]
    FROM master.dbo.spt_values
    WHERE [type] = N'P'
    AND [number] BETWEEN 1 AND LEN(@Input)
    ORDER BY [number];
  OPEN csrChecksumEnumerator;
  FETCH NEXT FROM csrChecksumEnumerator INTO @Number;
  WHILE (@@FETCH_STATUS = 0)
  BEGIN
    SELECT @CRC = ((@CRC << 8) ^ CAST(SUBSTRING(@Lookup, ((@CRC >> 8) ^ ASCII(SUBSTRING(@Input, @Number, 1))) * 2 + 1, 2) AS INT)) & 0xFFFF;
    FETCH NEXT FROM csrChecksumEnumerator INTO @Number;
  END
  CLOSE csrChecksumEnumerator;
  DEALLOCATE csrChecksumEnumerator;
  RETURN CONVERT(nvarchar(4), convert(VARBINARY(2), @crc, 1), 2);
END
go

Utilização: SELECT dbo.CRC16('00020101021226900014br.gov.bcb.pix2568pix.santander.com.br/qr/v2/cobv/c25d50c8-5608-4ccd-aaaa-5a3fd68271615204000053039865406349.005802BR5910AAAXXXDDDA6003Jau62070503***6304')

CRC Calculado: 8209 QRCode final: 00020101021226900014br.gov.bcb.pix2568pix.santander.com.br/qr/v2/cobv/c25d50c8-5608-4ccd-aaaa-5a3fd68271615204000053039865406349.005802BR5910AAAXXXDDDA6003Jau62070503*63048209

Valide o resultado em: https://pix.nascent.com.br/ Valeu!

duduccosta commented 4 months ago

E uma função que cria o QRCode a partir do location, value, merchant e city. A função já retira acentos.

drop function if exists dbo.CreateQrCodePix
go
CREATE FUNCTION dbo.CreateQrCodePix(@location VARCHAR(MAX), @value DECIMAL(15, 2), @merchant VARCHAR(25), @city VARCHAR(15))
RETURNS NVARCHAR(MAX)
BEGIN

    -- Removendo Acentos
    SET @merchant = UPPER(@merchant COLLATE SQL_Latin1_General_Cp1251_CS_AS)
    SET @city = UPPER(@city COLLATE SQL_Latin1_General_Cp1251_CS_AS)

    DECLARE
        @lenLocation CHAR(2) = RIGHT(REPLICATE('0',1)+convert(varchar(max), LEN(convert(VARCHAR(MAX),@location))),2),
        @lenValue CHAR(2) = RIGHT(REPLICATE('0',1)+convert(varchar(max), LEN(convert(VARCHAR(MAX),@value))),2),
        @lenMerchant CHAR(2) = RIGHT(REPLICATE('0',1)+convert(varchar(max), LEN(TRIM(convert(VARCHAR(MAX),@merchant)))),2),
        @lenCity CHAR(2) = RIGHT(REPLICATE('0',1)+convert(varchar(max), LEN(TRIM(convert(VARCHAR(MAX),@city)))),2),
        @qrCode NVARCHAR(MAX)

    set @qrCode =
        concat(
            '000201',
            '010212',
            '26', 22 + @lenLocation,
            '0014br.gov.bcb.pix',
            '25', @lenLocation, @location,
            '52040000',
            '5303986',
            '54', @lenValue,
            trim(convert(VARCHAR(MAX),@value)),
            '5802BR',
            '59', @lenMerchant, TRIM(@merchant),
            '60', @lenCity, @city,
            '6207',
            '0503***',
            '6304'
        )

    DECLARE @Lookup BINARY(512) = 
        0x0000102120423063408450A560C670E781089129A14AB16BC18CD1ADE1CEF1EF +
        0x123102103273225252B5429472F762D693398318B37BA35AD3BDC39CF3FFE3DE +
        0x246234430420140164E674C744A45485A56AB54B85289509E5EEF5CFC5ACD58D +
        0x365326721611063076D766F6569546B4B75BA77A97198738F7DFE7FED79DC7BC +
        0x48C458E5688678A70840186128023823C9CCD9EDE98EF9AF89489969A90AB92B +
        0x5AF54AD47AB76A961A710A503A332A12DBFDCBDCFBBFEB9E9B798B58BB3BAB1A +
        0x6CA67C874CE45CC52C223C030C601C41EDAEFD8FCDECDDCDAD2ABD0B8D689D49 +
        0x7E976EB65ED54EF43E132E321E510E70FF9FEFBEDFDDCFFCBF1BAF3A9F598F78 +
        0x918881A9B1CAA1EBD10CC12DF14EE16F108000A130C220E35004402570466067 +
        0x83B99398A3FBB3DAC33DD31CE37FF35E02B1129022F332D24235521462777256 +
        0xB5EAA5CB95A88589F56EE54FD52CC50D34E224C314A004817466644754244405 +
        0xA7DBB7FA879997B8E75FF77EC71DD73C26D336F2069116B06657767646155634 +
        0xD94CC96DF90EE92F99C889E9B98AA9AB584448657806682718C008E1388228A3 +
        0xCB7DDB5CEB3FFB1E8BF99BD8ABBBBB9A4A755A546A377A160AF11AD02AB33A92 +
        0xFD2EED0FDD6CCD4DBDAAAD8B9DE88DC97C266C075C644C453CA22C831CE00CC1 +
        0xEF1FFF3ECF5DDF7CAF9BBFBA8FD99FF86E177E364E555E742E933EB20ED11EF0;

      DECLARE @CRC INT = 0xFFFF, @Number INT;
      DECLARE csrChecksumEnumerator CURSOR LOCAL STATIC READ_ONLY FORWARD_ONLY FOR
        SELECT [number]
        FROM master.dbo.spt_values
        WHERE [type] = N'P'
        AND [number] BETWEEN 1 AND LEN(@qrCode)
        ORDER BY [number];
      OPEN csrChecksumEnumerator;
      FETCH NEXT FROM csrChecksumEnumerator INTO @Number;
      WHILE (@@FETCH_STATUS = 0)
      BEGIN
        SELECT @CRC = ((@CRC << 8) ^ CAST(SUBSTRING(@Lookup, ((@CRC >> 8) ^ ASCII(SUBSTRING(@qrCode, @Number, 1))) * 2 + 1, 2) AS INT)) & 0xFFFF;
        FETCH NEXT FROM csrChecksumEnumerator INTO @Number;
      END
      CLOSE csrChecksumEnumerator;
      DEALLOCATE csrChecksumEnumerator;

    return CONCAT(@qrcode, CONVERT(nvarchar(4), convert(VARBINARY(2), @crc, 1), 2))
END

go

--Testando:
DECLARE
    @location VARCHAR(max) = 'pix.santander.com.br/qr/v2/cobv/595ed985-1234-1234-1234-8b38af03e22a',
    @value DECIMAL(15, 2) = 663.68,
    @merchant NVARCHAR(25) = 'QUALITY SYSTEMS LTDA',
    @city varchar(15) = 'UBERLÂNDIA'

SELECT dbo.CreateQrCodePix(@location, @value, @merchant, @city)

Valeu!!

junior176 commented 1 week ago

E uma função que cria o QRCode a partir do location, value, merchant e city. A função já retira acentos.

drop function if exists dbo.CreateQrCodePix
go
CREATE FUNCTION dbo.CreateQrCodePix(@location VARCHAR(MAX), @value DECIMAL(15, 2), @merchant VARCHAR(25), @city VARCHAR(15))
RETURNS NVARCHAR(MAX)
BEGIN

  -- Removendo Acentos
  SET @merchant = UPPER(@merchant COLLATE SQL_Latin1_General_Cp1251_CS_AS)
  SET @city = UPPER(@city COLLATE SQL_Latin1_General_Cp1251_CS_AS)

  DECLARE
      @lenLocation CHAR(2) = RIGHT(REPLICATE('0',1)+convert(varchar(max), LEN(convert(VARCHAR(MAX),@location))),2),
      @lenValue CHAR(2) = RIGHT(REPLICATE('0',1)+convert(varchar(max), LEN(convert(VARCHAR(MAX),@value))),2),
      @lenMerchant CHAR(2) = RIGHT(REPLICATE('0',1)+convert(varchar(max), LEN(TRIM(convert(VARCHAR(MAX),@merchant)))),2),
      @lenCity CHAR(2) = RIGHT(REPLICATE('0',1)+convert(varchar(max), LEN(TRIM(convert(VARCHAR(MAX),@city)))),2),
      @qrCode NVARCHAR(MAX)

  set @qrCode =
      concat(
          '000201',
          '010212',
          '26', 22 + @lenLocation,
          '0014br.gov.bcb.pix',
          '25', @lenLocation, @location,
          '52040000',
          '5303986',
          '54', @lenValue,
          trim(convert(VARCHAR(MAX),@value)),
          '5802BR',
          '59', @lenMerchant, TRIM(@merchant),
          '60', @lenCity, @city,
          '6207',
          '0503***',
          '6304'
      )

  DECLARE @Lookup BINARY(512) = 
      0x0000102120423063408450A560C670E781089129A14AB16BC18CD1ADE1CEF1EF +
      0x123102103273225252B5429472F762D693398318B37BA35AD3BDC39CF3FFE3DE +
      0x246234430420140164E674C744A45485A56AB54B85289509E5EEF5CFC5ACD58D +
      0x365326721611063076D766F6569546B4B75BA77A97198738F7DFE7FED79DC7BC +
      0x48C458E5688678A70840186128023823C9CCD9EDE98EF9AF89489969A90AB92B +
      0x5AF54AD47AB76A961A710A503A332A12DBFDCBDCFBBFEB9E9B798B58BB3BAB1A +
      0x6CA67C874CE45CC52C223C030C601C41EDAEFD8FCDECDDCDAD2ABD0B8D689D49 +
      0x7E976EB65ED54EF43E132E321E510E70FF9FEFBEDFDDCFFCBF1BAF3A9F598F78 +
      0x918881A9B1CAA1EBD10CC12DF14EE16F108000A130C220E35004402570466067 +
      0x83B99398A3FBB3DAC33DD31CE37FF35E02B1129022F332D24235521462777256 +
      0xB5EAA5CB95A88589F56EE54FD52CC50D34E224C314A004817466644754244405 +
      0xA7DBB7FA879997B8E75FF77EC71DD73C26D336F2069116B06657767646155634 +
      0xD94CC96DF90EE92F99C889E9B98AA9AB584448657806682718C008E1388228A3 +
      0xCB7DDB5CEB3FFB1E8BF99BD8ABBBBB9A4A755A546A377A160AF11AD02AB33A92 +
      0xFD2EED0FDD6CCD4DBDAAAD8B9DE88DC97C266C075C644C453CA22C831CE00CC1 +
      0xEF1FFF3ECF5DDF7CAF9BBFBA8FD99FF86E177E364E555E742E933EB20ED11EF0;

    DECLARE @CRC INT = 0xFFFF, @Number INT;
    DECLARE csrChecksumEnumerator CURSOR LOCAL STATIC READ_ONLY FORWARD_ONLY FOR
      SELECT [number]
      FROM master.dbo.spt_values
      WHERE [type] = N'P'
      AND [number] BETWEEN 1 AND LEN(@qrCode)
      ORDER BY [number];
    OPEN csrChecksumEnumerator;
    FETCH NEXT FROM csrChecksumEnumerator INTO @Number;
    WHILE (@@FETCH_STATUS = 0)
    BEGIN
      SELECT @CRC = ((@CRC << 8) ^ CAST(SUBSTRING(@Lookup, ((@CRC >> 8) ^ ASCII(SUBSTRING(@qrCode, @Number, 1))) * 2 + 1, 2) AS INT)) & 0xFFFF;
      FETCH NEXT FROM csrChecksumEnumerator INTO @Number;
    END
    CLOSE csrChecksumEnumerator;
    DEALLOCATE csrChecksumEnumerator;

  return CONCAT(@qrcode, CONVERT(nvarchar(4), convert(VARBINARY(2), @crc, 1), 2))
END

go

--Testando:
DECLARE
  @location VARCHAR(max) = 'pix.santander.com.br/qr/v2/cobv/595ed985-1234-1234-1234-8b38af03e22a',
  @value DECIMAL(15, 2) = 663.68,
  @merchant NVARCHAR(25) = 'QUALITY SYSTEMS LTDA',
  @city varchar(15) = 'UBERLÂNDIA'

SELECT dbo.CreateQrCodePix(@location, @value, @merchant, @city)

Valeu!!

Complementando a resposta do duducosta O operador << e >> só existem para o SQL Server 2022 e eu precisava que rodasse no 2008 Dei uma simplificada no terço final:

   DECLARE @CRC INT = 0xFFFF, @I INT = 1;

WHILE @I <= LEN(@qrCode)
BEGIN
    SELECT @CRC = (@CRC*256 ^ CAST(SUBSTRING(@LOOKUP, (@CRC/256 ^ ASCII(SUBSTRING(@qrCode, @i, 1))) * 2 + 1, 2) AS INT)) & 0xFFFF;
    SET @I = @I + 1;
END;

RETURN CONCAT(@qrCode, CONVERT(NVARCHAR(4), CONVERT(VARBINARY(2), @CRC, 1), 2))