postgresql에서의 Unicode 시퀀스 처리

postgresql에서의 Unicode 시퀀스 처리

일부 JSON 데이터가 postgresql 데이터베이스(9.4.1)의 JSON(JSONB가 아님) 열에 저장되어 있습니다.이러한 JSON 구조 중 일부는 속성 값에 Unicode 시퀀스를 포함합니다.예를 들어 다음과 같습니다.

{"client_id": 1, "device_name": "FooBar\ufffd\u0000\ufffd\u000f\ufffd" }

컬럼을 할때(JSON에 않는 에도)device_nameattribute) 、 ) 、 음 、 음 )

이스케이프 : "Unicode" 입니다.
세::\u0000텍스트로 변환할 수 없습니다.

postgresql 서버에서 다음 명령을 실행하여 이 오류를 재현할 수 있습니다.

select '{"client_id": 1, "device_name": "FooBar\ufffd\u0000\ufffd\u000f\ufffd" }'::json->>'client_id'

가 됩니다. 즉, 을 나타내는 방법은 를 나타내는 방법은 없습니다.유니코드입니다.NULL본문 결과로

착신 데이터에 대해 「관리」를 실시하지 않고, 같은 JSON 데이터를 문의할 수 있는 방법이 있습니까?Atribute(「JSON」)를 스캔 합니다.device_name이 경우)는 유사한 데이터를 보유할 수 있는 다른 속성이 있을 수 있으므로 좋은 솔루션이 아닙니다.

---

몇 가지 더 조사한 결과 이 동작은 changelog에 기재되어 있는 버전9.4.1에서는 새로운 동작인 것 같습니다.

...그러므로\u0000이제 디컴포넌트 폼으로의 변환이 필요한 경우에도 json 값으로 거부됩니다.으로 인해 저장 .\u0000Json...

이게 진짜 의도였나요?여기서 9.4.1 이전으로의 다운그레이드는 실행 가능한 옵션입니까?

---

_{참고로 이 속성은 클라이언트의 모바일 장치 이름에서 가져온 것입니다. 이 텍스트는 장치에 입력한 사용자입니다.도대체 어떻게 사용자가 값을 삽입했을까?!}

\u0000는 문자열 내에서 유효하지 않은1개의 Unicode 코드 포인트입니다.끈을 소독하는 것 말고는 다른 방법이 없네요.

★★json는 특정 형식의 문자열에 불과하기 때문에 JSON 구조에 신경 쓰지 않고 표준 문자열 함수를 사용할 수 있습니다.코드 포인트를 삭제하는 한 줄의 세정제는 다음과 같습니다.

SELECT (regexp_replace(the_string::text, '\\u0000', '', 'g'))::json;

그러나 원하는 문자를 삽입할 수도 있습니다. 제로 코드 포인트를 구분 기호로 사용할 경우 유용합니다.

또한 데이터베이스에 저장된 내용과 사용자에게 표시되는 방법 간의 미묘한 차이도 유의하십시오.문자열에 할 수 을 JSON 문자열로 .jsondata type 입니다.

패트릭의 해결책은 내게는 처음부터 통하지 않았다.그럼에도 불구하고 항상 오류가 발생했습니다.그 후 조금 더 조사하여 문제를 해결하는 작은 커스텀 함수를 작성할 수 있었습니다.

처음에 다음과 같이 써서 오류를 재현할 수 있었습니다.

select json '{ "a":  "null \u0000 escape" }' ->> 'a' as fails

그런 다음 쿼리에서 사용한 커스텀 함수를 추가했습니다.

CREATE OR REPLACE FUNCTION null_if_invalid_string(json_input JSON, record_id UUID)
  RETURNS JSON AS $$
DECLARE json_value JSON DEFAULT NULL;
BEGIN
  BEGIN
    json_value := json_input ->> 'location';
    EXCEPTION WHEN OTHERS
    THEN
      RAISE NOTICE 'Invalid json value: "%".  Returning NULL.', record_id;
      RETURN NULL;
  END;
  RETURN json_input;
END;
$$ LANGUAGE plpgsql;

함수를 호출하려면 이 작업을 수행합니다.에러는 표시되지 않습니다.

select null_if_invalid_string('{ "a":  "null \u0000 escape" }', id) from my_table

이렇게 하면 예상대로 json이 반환됩니다.

select null_if_invalid_string('{ "a":  "null" }', id) from my_table

SQL을 사용하여 다음과 같이 모든 항목을 수정할 수 있습니다.

update ___MY_TABLE___ 
set settings = REPLACE(settings::text, '\u0000', '' )::json
where settings::text like '%\u0000%'

나에게 맞는 솔루션을 찾았습니다.

SELECT (regexp_replace(the_string::text, '(?<!\\)\\u0000', '', 'g'))::json;

일치 패턴 「(?<!\)\u0000」에 주목합니다.

여기에 접속한 웹 검색어를 위한 것입니다.

이는 정확한 질문에 대한 해결책은 아니지만, 유사한 경우에 따라서는 데이터셋에 nullbyte가 포함되어 있지 않은 경우 해결 방법입니다.추가:

AND json NOT LIKE '%\u0000%'

WHERE 스테이트먼트에 기재되어 있습니다.

REPLACE SQL-syntax를 사용하여 데이터를 삭제할 수도 있습니다.

REPLACE(source_field, '\u0000', '' );

언급URL : https://stackoverflow.com/questions/31671634/handling-unicode-sequences-in-postgresql