시간여행자: urldecode

MySQL에서 URL Decoding 하는 SQL Stored Procedure.

데이터 입력을 encode된걸 넣어서 - 게다가 중간중간 깨진데이터들이나 URL일부가 그대로 들어가있는 경우도 있었고 (물론 매우 소수 일부가 그러했다.)아무튼 그런 이유로 어쩔 수 없이 검증하는 쿼리가 필요했다. (등떠밀려 만듦ㅎ)

아래는 최초 적용한 함수이다.
근데.. 이게 영 이상하게 동작한다. 실패작이다.
(그냥 된다카길래 가져다 썼는데 이거 원;)

DELIMITER $$
DROP FUNCTION IF EXISTS `url_decode` $$
CREATE DEFINER=`root`@`%` FUNCTION `url_decode`(original_text TEXT) RETURNS TEXT CHARSET utf8
BEGIN
DECLARE new_text TEXT DEFAULT NULL;
DECLARE pointer INT DEFAULT 1;
DECLARE end_pointer INT DEFAULT 1;
DECLARE encoded_text TEXT DEFAULT NULL;
DECLARE result_text TEXT DEFAULT NULL;
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
return NULL;
END;
SET new_text = REPLACE(original_text,'+',' ');
SET new_text = REPLACE(new_text,'%0A','\r\n');
SET pointer = LOCATE("%", new_text);
while pointer <> 0 && pointer < (CHAR_LENGTH(new_text) - 2) DO
SET end_pointer = pointer + 3;
while MID(new_text, end_pointer, 1) = "%" DO
SET end_pointer = end_pointer+3;
END while;
SET encoded_text = MID(new_text, pointer, end_pointer - pointer);
SET result_text = CONVERT(UNHEX(REPLACE(encoded_text, "%", "")) USING utf8);
SET new_text = REPLACE(new_text, encoded_text, result_text);
SET pointer = LOCATE("%", new_text, pointer + CHAR_LENGTH(result_text));
END while;
return new_text;
END $$
DELIMITER ;

그래서 다른 것을 찾아보았다.
그래서 아래와 같은 함수를 발견하게되었고 이번엔 꼼꼼하게 소스를 다 훝어보았다.
먼저 소스보다 훨씬 더 가독성이 좋았고 편리하게 작성되어있어 수정도 편했다.

DROP FUNCTION IF EXISTS urldecode;

DELIMITER |

CREATE FUNCTION urldecode (s VARCHAR(4096)) RETURNS VARCHAR(4096)
DETERMINISTIC 
CONTAINS SQL 
BEGIN
       DECLARE c VARCHAR(4096) DEFAULT '';
       DECLARE pointer INT DEFAULT 1;
       DECLARE s2 VARCHAR(4096) DEFAULT '';
       DECLARE h3 VARCHAR(4096) DEFAULT '';
       DECLARE CONTINUE handler FOR 1366
       BEGIN
       END;

       IF ISNULL(s) THEN
          RETURN NULL;
       ELSE
       SET s2 = '';
       WHILE pointer <= LENGTH(s) DO
          SET c = SUBSTR(s,pointer,1);
          IF c = '+' THEN
             SET h3 = '20';
          ELSEIF c = '%' AND pointer + 2 <= LENGTH(s) THEN
                   SET h3 = UPPER(SUBSTR(s,pointer+1,2));
                   SET pointer = pointer + 2;
          ELSE
                   SET h3 = HEX(c);
          END IF;
          SET s2 = CONCAT(s2,h3);
          SET pointer = pointer + 1;
       END while;
       END IF;
       RETURN UNHEX(s2);
END;

|

DELIMITER ;

헌데, 이넘도 문제가 있다.

encode data가 깨진 경우, 마지막 RETURN UNHEX(s2); 에서 1366 오류가 난다.
이해가 가지 않는다; ㅠㅠ;;;;;;;;;;;;;;;;

일단 작업에 방해가 되면 안되니, 위 소스처럼

DECLARE CONTINUE handler FOR 1366
BEGIN
END;

로서 땜빵.

문제의 진짜 이유를 찾기 위해 테스트해 보았다.

결국, 수십여분 삽질끝에.. 이상한것을 발견했다. 이유는 모른다.

RETURN UNHEX(s2); #1366 ERROR 발생

위 부분을

RETURN s2;

위와 같이 UNHEX 배제 후 함수 호출부분에서 UNHEX하니

SELECT urldecode('%ED%81%AC%EBCsn%3D3%7Chk%3Dbbcac20f524e408ffbd748bce750e921a2aca717'); #1366 ERROR 발생

SELECT UNHEX(urldecode('%ED%81%AC%EBCsn%3D3%7Chk%3Dbbcac20f524e408ffbd748bce750e921a2aca717'));

결과가 나왔다. 응?! 왜지?!
return되기직전에 unhex를 사용하면 안되고 반환반고나서 unhex를 쓰면된다?!

SQL 잘하는 사람한테 물어봐야할라나..?

아는사람은 좀 알려주세요~~~~ ㅠㅠ

2016년 7월 6일 수요일

MySQL에서 URL Decoding 하는 SQL Stored Procedure.