Início / dba / Perguntas / 278585
Accepted
kenorb
Asked: 2020-10-24 11:10:57 +0800 CST

Como extrair atributos de certificado de uma única coluna e dividir em muitas?

  • 772

Na minha tabela eu tenho um campo (digamos cert_attr) que armazena Certificate X.509 Attributes .

Aqui está o exemplo de 3 linhas (cada linha corresponde a um campo):

"CN=User1, OU=Eng, O=Company Ltd, L=D4, S=Dublin, C=IE"
"CN=User2, OU=Eng, O=Company Ltd, L=D2, S=Dublin, C=IE"
"OU=Eng, O=Company Ltd"

E estou tentando dividir o valor de um campo em colunas separadas usando SELECTda seguinte maneira:

SELECT
SUBSTRING_INDEX(SUBSTRING_INDEX(cert_attr, "CN=", -1), ", ", 1) as CN,
SUBSTRING_INDEX(SUBSTRING_INDEX(cert_attr, "OU=", -1), ", ", 1) as OU,
SUBSTRING_INDEX(SUBSTRING_INDEX(cert_attr, "O=", -1), ", ", 1) as O,
SUBSTRING_INDEX(SUBSTRING_INDEX(cert_attr, "L=", -1), ", ", 1) as L,
SUBSTRING_INDEX(SUBSTRING_INDEX(cert_attr, "ST=", -1), ", ", 1) as ST,
SUBSTRING_INDEX(SUBSTRING_INDEX(cert_attr, "C=", -1), ", ", 1) as C
FROM mytable

que funciona, no entanto, há um problema para as linhas que estão faltando alguns atributos.

Portanto, no caso em que o atributo está ausente na string do campo, espero que a coluna esteja vazia, mas ela retorna a string inteira.

Os dois primeiros exemplos de linha estão funcionando conforme o esperado, o que retorna as seguintes colunas corretamente:

| CN    | OU  | O           | L  | S.     | C  |
| ----- | --- | ----------- | -- | ------ | -- |
| User1 | Eng | Company Ltd | D4 | Dublin | IE |
| User2 | Eng | Company Ltd | D2 | Dublin | IE |

O problema está no exemplo da 3ª linha, que espero retornar uma string vazia quando o padrão de substring não for encontrado:

| CN         | OU  | O           | L          | S.         | C          |
| ---------- | --- | ----------- | ---------- | ---------- | ---------- |
| OU=Eng,... | Eng | Company Ltd | OU=Eng,... | OU=Eng,... | OU=Eng,... |

mas em vez disso, a string inteira é retornada.

Pergunta:

Existe alguma maneira de retornar uma string vazia quando SUBSTRING_INDEX()não consegue encontrar a substring? Ou talvez haja alguma outra função (como uma expressão regular) ou outra solução alternativa?

Meu objetivo é extrair os dados para o arquivo TSV tendo esses atributos em colunas separadas com valores válidos:

mysql mytable < query.sql > cert_attributes.tsv
mysql regex

1 respostas

  1. Best Answer

    A primeira parte é apenas para mostrar que o procedimento funciona

    CREATE tABLE tablecertificate  (
stationery_name varchar(500) 

    INSERT INTO tablecertificate values 
("CN=User1, OU=Eng, O=Company Ltd, L=D4, S=Dublin, C=IE"),
("CN=User2, OU=Eng, O=Company Ltd, L=D2, S=Dublin, C=IE"),
("OU=Eng, O=Company Ltd")

    CREATE PROCEDURE `splitcertifcate`()
BEGIN
 DECLARE current_pos INT DEFAULT 1;
 DECLARE delim CHAR DEFAULT ',';
 DECLARE current VARCHAR(100) DEFAULT '';
 DECLARE CN VARCHAR(100) DEFAULT '';
 DECLARE OU VARCHAR(100) DEFAULT '';
 DECLARE O VARCHAR(100) DEFAULT '';
 DECLARE L VARCHAR(100) DEFAULT '';
 DECLARE S VARCHAR(100) DEFAULT '';
 DECLARE C VARCHAR(100) DEFAULT '';
 DECLARE rest_cert_part VARCHAR(100) DEFAULT '';
 DECLARE current_cert_part  VARCHAR(100) DEFAULT '';
  DECLARE finished INTEGER DEFAULT 0;
  DECLARE certificate varchar(500) DEFAULT "";
  DEClARE curcertificate 
      CURSOR FOR 
          SELECT stationery_name FROM tablecertificate;

  -- declare NOT FOUND handler
  DECLARE CONTINUE HANDLER 
        FOR NOT FOUND SET finished = 1;
        
        
  #Temporary Table that holds the splittet part
   DROP TEMPORARY TABLE IF EXISTS mycertificate;
  CREATE TEMPORARY TABLE mycertificate(CN VARCHAR(100), OU VARCHAR(100), O VARCHAR(100) , L  VARCHAR(100), S VARCHAR(100), C VARCHAR(100));

  OPEN curcertificate;
    getcertificate: LOOP
      # get first row elemnt
      FETCH curcertificate INTO certificate;
      IF finished = 1 THEN 
          #Last element reached
          LEAVE getcertificate;
      END IF;
        SET CN = '';
        SET OU = '';
        SET O = '';
        SET L = '';
        SET S = '';
        SET C = '';
      SET current_pos =  LOCATE(delim,certificate);
        SET current_cert_part = SUBSTRING(certificate,1,current_pos-1);
      SET rest_cert_part = SUBSTRING(certificate from current_pos+1);
        IF length(trim(current_cert_part)) = 0   THEN
          SET current_cert_part = rest_cert_part;
      END IF;
      #Examine first element of the string
        CASE 
          WHEN INSTR(current_cert_part, "CN=")  > 0 THEN SET CN = TRIM(current_cert_part);
          WHEN INSTR(current_cert_part, "OU=")  > 0 THEN SET OU = TRIM(current_cert_part);
          WHEN INSTR(current_cert_part, "O=")  > 0 THEN SET O = TRIM(current_cert_part);
          WHEN INSTR(current_cert_part, "L=")  > 0 THEN SET L = TRIM(current_cert_part);
          WHEN INSTR(current_cert_part, "S=")  > 0 THEN SET S = TRIM(current_cert_part);
          WHEN INSTR(current_cert_part, "C=")  > 0 THEN SET C = TRIM(current_cert_part);
        END CASE;
        WHILE current_pos <> 0 DO
          #loop throuigh the string
          set  current_pos = LOCATE(delim,rest_cert_part);
          set current_cert_part = SUBSTRING(rest_cert_part,1,current_pos-1);
          set rest_cert_part = SUBSTRING(rest_cert_part from current_pos+1);

          if length(trim(current_cert_part)) = 0   then
              #ÖLast elemnt in string
              set current_cert_part = rest_cert_part;
          end if;    
          CASE 
              WHEN INSTR(current_cert_part, "CN=")  > 0 THEN SET CN = TRIM(current_cert_part);
              WHEN INSTR(current_cert_part, "OU=")  > 0 THEN SET OU = TRIM(current_cert_part);
              WHEN INSTR(current_cert_part, "O=")  > 0 THEN SET O = TRIM(current_cert_part);
              WHEN INSTR(current_cert_part, "L=")  > 0 THEN SET L = TRIM(current_cert_part);
              WHEN INSTR(current_cert_part, "S=")  > 0 THEN SET S = TRIM(current_cert_part);
              WHEN INSTR(current_cert_part, "C=")  > 0 THEN SET C = TRIM(current_cert_part);
          END CASE;
      END WHILE;
        #In sert splittet strung  row in temp table
       INSERT INTO mycertificate VALUES (  CN,OU,O,L,S,C);


  END LOOP getcertificate;
  CLOSE curcertificate;
SELECT * FROM mycertificate;
END

    cALL splitcertifcate();

    NC | UO | O | L | S | C   
:------- | :----- | :------------ | :--- | :------- | :---
CN=Usuário1 | OU=Eng | O=Empresa Ltda | L=D4 | S=Dublim | C=IE
CN=Usuário2 | OU=Eng | O=Empresa Ltda | L=D2 | S=Dublim | C=IE
         | OU=Eng | O=Empresa Ltda | | |     

✓

    db<>fique aqui

    O procedimento armazenado completo, incluindo a produção do arquivo tsv

    DELIMITER //
CREATE DEFINER=`root`@`%` PROCEDURE `splitcertifcate`()
BEGIN
 DECLARE current_pos INT DEFAULT 1;
 DECLARE delim CHAR DEFAULT ',';
 DECLARE current VARCHAR(100) DEFAULT '';
 DECLARE CN VARCHAR(100) DEFAULT '';
 DECLARE OU VARCHAR(100) DEFAULT '';
 DECLARE O VARCHAR(100) DEFAULT '';
 DECLARE L VARCHAR(100) DEFAULT '';
 DECLARE S VARCHAR(100) DEFAULT '';
 DECLARE C VARCHAR(100) DEFAULT '';
 DECLARE rest_cert_part VARCHAR(100) DEFAULT '';
 DECLARE current_cert_part  VARCHAR(100) DEFAULT '';
    DECLARE finished INTEGER DEFAULT 0;
    DECLARE certificate varchar(500) DEFAULT "";
    DEClARE curcertificate 
        CURSOR FOR 
            SELECT stationery_name FROM tablecertificate;

    -- declare NOT FOUND handler
    DECLARE CONTINUE HANDLER 
        FOR NOT FOUND SET finished = 1;
        
    #Temporary Table that holds the splittet part
     DROP TEMPORARY TABLE IF EXISTS mycertificate;
    CREATE TEMPORARY TABLE mycertificate(CN VARCHAR(100), OU VARCHAR(100), O VARCHAR(100) , L  VARCHAR(100), S VARCHAR(100), C VARCHAR(100));

    OPEN curcertificate;
    getcertificate: LOOP
        # get first row elemnt
        FETCH curcertificate INTO certificate;
        IF finished = 1 THEN 
            #Last element reached
            LEAVE getcertificate;
        END IF;
        SET CN = '';
        SET OU = '';
        SET O = '';
        SET L = '';
        SET S = '';
        SET C = '';
        SET current_pos =  LOCATE(delim,certificate);
        SET current_cert_part = SUBSTRING(certificate,1,current_pos-1);
        SET rest_cert_part = SUBSTRING(certificate from current_pos+1);
        IF length(trim(current_cert_part)) = 0   THEN
            SET current_cert_part = rest_cert_part;
        END IF;
        #Examine first element of the string
        CASE 
            WHEN INSTR(current_cert_part, "CN=")  > 0 THEN SET CN = TRIM(current_cert_part);
            WHEN INSTR(current_cert_part, "OU=")  > 0 THEN SET OU = TRIM(current_cert_part);
            WHEN INSTR(current_cert_part, "O=")  > 0 THEN SET O = TRIM(current_cert_part);
            WHEN INSTR(current_cert_part, "L=")  > 0 THEN SET L = TRIM(current_cert_part);
            WHEN INSTR(current_cert_part, "S=")  > 0 THEN SET S = TRIM(current_cert_part);
            WHEN INSTR(current_cert_part, "C=")  > 0 THEN SET C = TRIM(current_cert_part);
        END CASE;
        WHILE current_pos <> 0 DO
            #loop throuigh the string
            set  current_pos = LOCATE(delim,rest_cert_part);
            set current_cert_part = SUBSTRING(rest_cert_part,1,current_pos-1);
            set rest_cert_part = SUBSTRING(rest_cert_part from current_pos+1);

            if length(trim(current_cert_part)) = 0   then
                #ÖLast elemnt in string
                set current_cert_part = rest_cert_part;
            end if;    
            CASE 
                WHEN INSTR(current_cert_part, "CN=")  > 0 THEN SET CN = TRIM(current_cert_part);
                WHEN INSTR(current_cert_part, "OU=")  > 0 THEN SET OU = TRIM(current_cert_part);
                WHEN INSTR(current_cert_part, "O=")  > 0 THEN SET O = TRIM(current_cert_part);
                WHEN INSTR(current_cert_part, "L=")  > 0 THEN SET L = TRIM(current_cert_part);
                WHEN INSTR(current_cert_part, "S=")  > 0 THEN SET S = TRIM(current_cert_part);
                WHEN INSTR(current_cert_part, "C=")  > 0 THEN SET C = TRIM(current_cert_part);
            END CASE;
        END WHILE;
        #In sert splittet strung  row in temp table
       INSERT INTO mycertificate VALUES (  CN,OU,O,L,S,C);


    END LOOP getcertificate;
    CLOSE curcertificate;

    SELECT * FROM mycertificate INTO OUTFILE "C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/cert.tsv"
    FIELDS TERMINATED BY '\t'
    ENCLOSED BY '"'
    LINES TERMINATED BY '\n';
END//
DELIMITER ;

    Arquivo tsv de resultado

    "CN=User1"  "OU=Eng"    "O=Company Ltd" "L=D4"  "S=Dublin"  "C=IE"
"CN=User2"  "OU=Eng"    "O=Company Ltd" "L=D2"  "S=Dublin"  "C=IE"
""          "OU=Eng"    "O=Company Ltd"  ""       ""        ""

    Você deve verificar a pasta INTO OUTFILE, que deve corresponder à entrada no arquivo my.ini/cnf

    secure-file-priv='C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/'
    • 1

relate perguntas