mysql sql解析浏览器地址栏(成功)

weixin_39137202

已于 2024-02-01 12:34:31 修改

阅读量961

点赞数 22

文章标签： mysql sql 数据库

于 2024-02-01 09:44:19 首次发布

本文链接：https://blog.csdn.net/weixin_39137202/article/details/135966628

版权

本文详细介绍了在处理包含中文URL的生产综合分析看板中，如何使用UrlEncode进行编码，以及如何在MySQL中进行URL解码，包括一级解码（使用自定义的urldecode函数）和二级解码的过程。还涉及了如何检查和设置MySQL的函数创建功能。

摘要由CSDN通过智能技术生成

1、准备：生产综合分析看板http://localhost:8075/webroot/decision/view/form?viewlet=%25E7%2594%259F%25E4%25BA%25A7%25E7%25BB%25BC%25E5%2590%2588%25E5%2588%2586%25E6%259E%2590%25E7%259C%258B%25E6%259D%25BF.frm

解码网站测试，需要两层解析

UrlEncode编码/UrlDecode解码 - 站长工具为了让包含中文的URL可以使用，您可以使用本工具对中文进行UrlEncode编码。https://tool.chinaz.com/tools/urlencode.aspx

mysql官方：

URLEncoder Function for MySQL · Jeremy Thomerson

MySQL常用函数、聚合函数、MD5加密

https://www.cnblogs.com/XiaoCui-blog/p/14925008.htmlhttps://www.cnblogs.com/XiaoCui-blog/p/14925008.html

2、检查及开启创建函数功能：

 -- 1、查看创建函数是否开启 如果Value处值为OFF，则需将其开启。
show variables like '%func%';
-- 2、开启MySQL函数功能
set global log_bin_trust_function_creators=1;
-- 3、关闭MySQL函数功能
set global log_bin_trust_function_creators=0;
 
-- 4、删除一个函数
drop function IF EXISTS getDistance;
 
-- 删除存储过程、存储函数主要使用drop语句：
-- DROP PROCEDURE IF EXISTS #{存储过程} —— 删除存储过程
-- DROP FUNCTION IF EXISTS #{函数}     —— 删除存储函数

3.一级解码

创建urldecode函数

DELIMITER $$

USE `intl`$$

DROP FUNCTION IF EXISTS `urldecode`$$

CREATE DEFINER=`root`@`localhost` FUNCTION `urldecode`(s VARCHAR(4096)) RETURNS VARCHAR(4096) CHARSET utf8mb4
DETERMINISTIC
BEGIN
DECLARE c VARCHAR(4096) DEFAULT '';
DECLARE pointer INT DEFAULT 1;
DECLARE h CHAR(2);
DECLARE h1 CHAR(1);
DECLARE h2 CHAR(1);
DECLARE s2 VARCHAR(4096) DEFAULT '';

IF ISNULL(s) THEN
RETURN NULL;
ELSE
SET s2 = '';
WHILE pointer <= LENGTH(s) DO
SET c = MID(s,pointer,1);
IF c = '+' THEN
SET c = ' ';
ELSEIF c = '%' AND pointer + 2 <= LENGTH(s) THEN
SET h1 = LOWER(MID(s,pointer+1,1));
SET h2 = LOWER(MID(s,pointer+2,1));
IF (h1 BETWEEN '0' AND '9' OR h1 BETWEEN 'a' AND 'f')
AND
(h2 BETWEEN '0' AND '9' OR h2 BETWEEN 'a' AND 'f')
THEN
SET h = CONCAT(h1,h2);
SET pointer = pointer + 2;
SET c = CHAR(CONV(h,16,10));
END IF;
END IF;
SET s2 = CONCAT(s2,c);
SET pointer = pointer + 1;
END WHILE;
END IF;
RETURN s2;
END$$

DELIMITER ;

调用：

SELECT `urldecode`('http://localhost:8075/webroot/decision/view/form?viewlet=%25E7%2594%259F%25E4%25BA%25A7%25E7%25BB%25BC%25E5%2590%2588%25E5%2588%2586%25E6%259E%2590%25E7%259C%258B%25E6%259D%25BF.frm')

4.二级解码

创建url_decode 函数

DELIMITER $$

USE `intl`$$

DROP FUNCTION IF EXISTS `url_decode`$$

CREATE DEFINER=`root`@`localhost` FUNCTION `url_decode`(original_text TEXT CHARSET utf8mb4) RETURNS TEXT CHARSET utf8mb4
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 rep_text TEXT DEFAULT NULL;  
    DECLARE unhex_text TEXT DEFAULT NULL;  
   
    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 rep_text = REPLACE(encoded_text, '%', '');
		SET unhex_text = UNHEX(rep_text);
        SET result_text = CONVERT(unhex_text USING utf8mb4);  
        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 ;

调用：解析一级解码的结果

SELECT url_decode('http://localhost:8075/webroot/decision/view/form?viewlet=%E7%94%9F%E4%BA%A7%E7%BB%BC%E5%90%88%E5%88%86%E6%9E%90%E7%9C%8B%E6%9D%BF.frm')