From e78e826e258031b361acebc742791800545f485a Mon Sep 17 00:00:00 2001 From: Federico Razzoli Date: Tue, 17 May 2016 01:14:37 +0200 Subject: [PATCH 2/3] added utility functions * quote_name, quote_fqn2, quote_fqn3 * quote_account * is_valid_name --- functions/is_valid_name.sql | 62 +++++++++++++++++++++++++++++++++++++++++++++ functions/quote_account.sql | 53 ++++++++++++++++++++++++++++++++++++++ functions/quote_fqn2.sql | 53 ++++++++++++++++++++++++++++++++++++++ functions/quote_fqn3.sql | 58 ++++++++++++++++++++++++++++++++++++++++++ functions/quote_name.sql | 45 ++++++++++++++++++++++++++++++++ 5 files changed, 271 insertions(+) create mode 100644 functions/is_valid_name.sql create mode 100644 functions/quote_account.sql create mode 100644 functions/quote_fqn2.sql create mode 100644 functions/quote_fqn3.sql create mode 100644 functions/quote_name.sql diff --git a/functions/is_valid_name.sql b/functions/is_valid_name.sql new file mode 100644 index 0000000..1411049 --- /dev/null +++ b/functions/is_valid_name.sql @@ -0,0 +1,62 @@ +DROP FUNCTION IF EXISTS is_valid_name; + +DELIMITER $$ + +CREATE DEFINER='root'@'localhost' PROCEDURE is_valid_name ( + IN in_name varchar(64) CHARACTER SET UTF8, + OUT out_ret BOOL + ) + COMMENT ' + Description + ----------- + + Returns weather the passed string is a valid name in current MySQL version. + + + Parameters + ----------- + + in_name (varchar(64) CHARACTER SET UTF8): + The string to test. + out_ret (bool) + Return value. + + + Example + -------- + + mysql> CALL sys.is_valid_name(''valid_name'', @x); + Query OK, 0 rows affected (0.00 sec) + + mysql> CALL sys.is_valid_name(''select'', @y); + Query OK, 0 rows affected (0.00 sec) + + mysql> SELECT @x, @y; + +------+------+ + | @x | @y | + +------+------+ + | 1 | 0 | + +------+------+ + 1 row in set (0.00 sec) + + ' + SQL SECURITY INVOKER + NOT DETERMINISTIC + CONTAINS SQL +BEGIN + -- error in query occurs if in_name is not a valid alias + DECLARE EXIT HANDLER + FOR 1064 + BEGIN + SET out_ret = FALSE; + END; + + SET @sql_query = CONCAT('DO (SELECT 0 AS ', `in_name`, ');'); + PREPARE stmt FROM @sql_query; + EXECUTE stmt; + DEALLOCATE PREPARE stmt; + + SET out_ret = TRUE; +END$$ + +DELIMITER ; diff --git a/functions/quote_account.sql b/functions/quote_account.sql new file mode 100644 index 0000000..6ffa9b5 --- /dev/null +++ b/functions/quote_account.sql @@ -0,0 +1,53 @@ +DROP FUNCTION IF EXISTS quote_account; + +DELIMITER $$ + +CREATE DEFINER='root'@'localhost' FUNCTION quote_account ( + in_user varchar(32) CHARACTER SET UTF8, + in_host varchar(60) CHARACTER SET UTF8 + ) + RETURNS VARCHAR(200) + COMMENT ' + Description + ----------- + + Returns a quoted MySQL account, in the form `username`@`host`. + The quote char and the escape char are both ` (ASCII 96). + If one of the parameters is NULL, NULL is returned. + + This function can be used to compose prepared statements when table or column names are not known in advance. + + + Parameters + ----------- + + in_user (varchar(32) CHARACTER SET UTF8): + First part of FQN. + in_host (varchar(60) CHARACTER SET UTF8): + Second part of the FQN. + + + Example + -------- + + mysql> SELECT sys.quote_account(''root'', ''localhost''); + +----------------------------------------+ + | sys.quote_account(''root'', ''localhost'') | + +----------------------------------------+ + | `root`@`localhost` | + +----------------------------------------+ + 1 row in set (0.00 sec) + + ' + SQL SECURITY INVOKER + NOT DETERMINISTIC + CONTAINS SQL +BEGIN + RETURN CONCAT( + '`', REPLACE(in_user, '`', '``'), '`', + '@', + '`', REPLACE(in_host, '`', '``'), '`' + ); +END$$ + +DELIMITER ; diff --git a/functions/quote_fqn2.sql b/functions/quote_fqn2.sql new file mode 100644 index 0000000..29a567d --- /dev/null +++ b/functions/quote_fqn2.sql @@ -0,0 +1,53 @@ +DROP FUNCTION IF EXISTS quote_fqn2; + +DELIMITER $$ + +CREATE DEFINER='root'@'localhost' FUNCTION quote_fqn2 ( + in_name1 varchar(64) CHARACTER SET UTF8, + in_name2 varchar(64) CHARACTER SET UTF8 + ) + RETURNS VARCHAR(200) + COMMENT ' + Description + ----------- + + Returns a quoted MySQL FQN, in the form `db_name`.`tab_name`. + The quote char and the escape char are both ` (ASCII 96). + If one of the parameters is NULL, NULL is returned. + + This function can be used to compose prepared statements when table or column names are not known in advance. + + + Parameters + ----------- + + in_name1 (varchar(64) CHARACTER SET UTF8): + First part of FQN. + in_name2 (varchar(64) CHARACTER SET UTF8): + Second part of the FQN. + + + Example + -------- + + mysql> SELECT sys.quote_fqn2(''my_db'', ''my_table''); + +-------------------------------------+ + | sys.quote_fqn2(''my_db'', ''my_table'') | + +-------------------------------------+ + | `my_db`.`my_table` | + +-------------------------------------+ + 1 row in set (0.00 sec) + + ' + SQL SECURITY INVOKER + NOT DETERMINISTIC + CONTAINS SQL +BEGIN + RETURN CONCAT( + '`', REPLACE(in_name1, '`', '``'), '`', + '.', + '`', REPLACE(in_name2, '`', '``'), '`' + ); +END$$ + +DELIMITER ; diff --git a/functions/quote_fqn3.sql b/functions/quote_fqn3.sql new file mode 100644 index 0000000..15eb36e --- /dev/null +++ b/functions/quote_fqn3.sql @@ -0,0 +1,58 @@ +DROP FUNCTION IF EXISTS quote_fqn3; + +DELIMITER $$ + +CREATE DEFINER='root'@'localhost' FUNCTION quote_fqn3 ( + in_name1 varchar(64) CHARACTER SET UTF8, + in_name2 varchar(64) CHARACTER SET UTF8, + in_name3 varchar(64) CHARACTER SET UTF8 + ) + RETURNS VARCHAR(200) + COMMENT ' + Description + ----------- + + Returns a quoted MySQL FQN, in the form `db_name`.`tab_name`.`col_name`. + The quote char and the escape char are both ` (ASCII 96). + If one of the parameters is NULL, NULL is returned. + + This function can be used to compose prepared statements when table or column names are not known in advance. + + + Parameters + ----------- + + in_name1 (varchar(64) CHARACTER SET UTF8): + First part of FQN. + in_name2 (varchar(64) CHARACTER SET UTF8): + Second part of the FQN. + in_name3 (varchar(64) CHARACTER SET UTF8): + Third part of the FQN. + + + Example + -------- + + mysql> SELECT sys.quote_fqn3(''my_db'', ''my_table'', ''my_col''); + +-----------------------------------------------+ + | sys.quote_fqn3(''my_db'', ''my_table'', ''my_col'') | + +-----------------------------------------------+ + | `my_db`.`my_table`.`my_col` | + +-----------------------------------------------+ + 1 row in set (0.00 sec) + + ' + SQL SECURITY INVOKER + NOT DETERMINISTIC + CONTAINS SQL +BEGIN + RETURN CONCAT( + '`', REPLACE(in_name1, '`', '``'), '`', + '.', + '`', REPLACE(in_name2, '`', '``'), '`', + '.', + '`', REPLACE(in_name3, '`', '``'), '`' + ); +END$$ + +DELIMITER ; diff --git a/functions/quote_name.sql b/functions/quote_name.sql new file mode 100644 index 0000000..10abbdc --- /dev/null +++ b/functions/quote_name.sql @@ -0,0 +1,45 @@ +DROP FUNCTION IF EXISTS quote_name; + +DELIMITER $$ + +CREATE DEFINER='root'@'localhost' FUNCTION quote_name ( + in_name varchar(64) CHARACTER SET UTF8 + ) + RETURNS VARCHAR(70) + COMMENT ' + Description + ----------- + + Returns the given string as a quoted MySQL identifier. + The quote char and the escape char are both ` (ASCII 96). + If in_name is NULL, NULL is returned. + + This function can be used to compose prepared statements when table or column names are not known in advance. + + + Parameters + ----------- + + in_name (varchar(64) CHARACTER SET UTF8): + The name to quote. + + + Example + -------- + + mysql> SELECT sys.quote_name(''my_table''), sys.quote_name(''my`table''); + +----------------------------+----------------------------+ + | sys.quote_name(''my_table'') | sys.quote_name(''my`table'') | + +----------------------------+----------------------------+ + | `my_table` | `my``table` | + +----------------------------+----------------------------+ + 1 row in set (0.00 sec) + ' + SQL SECURITY INVOKER + NOT DETERMINISTIC + CONTAINS SQL +BEGIN + RETURN CONCAT('`', REPLACE(in_name, '`', '``'), '`'); +END$$ + +DELIMITER ; From 7b538df57d05a0c773b184edf32b5e517923da61 Mon Sep 17 00:00:00 2001 From: Federico Razzoli Date: Tue, 17 May 2016 01:16:35 +0200 Subject: [PATCH 3/3] is_valid_name clarification in comment --- functions/is_valid_name.sql | 1 + 1 file changed, 1 insertion(+) diff --git a/functions/is_valid_name.sql b/functions/is_valid_name.sql index 1411049..3f7455f 100644 --- a/functions/is_valid_name.sql +++ b/functions/is_valid_name.sql @@ -11,6 +11,7 @@ CREATE DEFINER='root'@'localhost' PROCEDURE is_valid_name ( ----------- Returns weather the passed string is a valid name in current MySQL version. + A valid name is a string that does not require quoting to be used as an object identifier. Parameters