MDEV-25704 Add RANDOM_BYTES function

MySQL 5.6 added the RANDOM_BYTES function.
https://dev.mysql.com/doc/refman/5.6/en/encryption-functions.html#function_random-bytes

This is needed for compatibility purposes.

7 files changed, 227 insertions, 1 deletions

diff --git a/mysql-test/main/func_str.result b/mysql-test/main/func_str.result
index 799fd1933dd..f983110627d 100644
--- a/mysql-test/main/func_str.result
+++ b/mysql-test/main/func_str.result
@@ -5265,3 +5265,59 @@ f
 #
 # End of 10.4 tests
 #
+#
+# MDEV-25704 Function random_bytes
+#
+create table t1 as select random_bytes(100);
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `random_bytes(100)` varbinary(100) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+drop table t1;
+# The sequence starts at 17 so that the probability of test failure is small enough (about 2^(-136))
+select count(*) from seq_17_to_1024 where random_bytes(seq) <=> random_bytes(seq);
+count(*)
+0
+select (count(*) = 1024) from seq_1_to_1024 where length(random_bytes(seq)) = seq;
+(count(*) = 1024)
+1
+#
+# Test NULL output for NULL input
+#
+SELECT random_bytes(NULL);
+random_bytes(NULL)
+NULL
+#
+# Test For values outside range from 1 to 1024, an error occurs
+#
+SELECT random_bytes(0);
+ERROR 22003: length value is out of range in 'random_bytes(0)'
+SELECT random_bytes(-1);
+ERROR 22003: length value is out of range in 'random_bytes(-1)'
+SELECT random_bytes(-100);
+ERROR 22003: length value is out of range in 'random_bytes(-100)'
+SELECT random_bytes(-26);
+ERROR 22003: length value is out of range in 'random_bytes(-26)'
+SELECT random_bytes(-132);
+ERROR 22003: length value is out of range in 'random_bytes(-132)'
+SELECT random_bytes(1025);
+ERROR 22003: length value is out of range in 'random_bytes(1025)'
+SELECT random_bytes(11111);
+ERROR 22003: length value is out of range in 'random_bytes(11111)'
+SELECT random_bytes(2056);
+ERROR 22003: length value is out of range in 'random_bytes(2056)'
+#
+# Test For invalid argument, an error occurs
+#
+SELECT random_bytes('s');
+ERROR 22003: length value is out of range in 'random_bytes('s')'
+SELECT random_bytes('r');
+ERROR 22003: length value is out of range in 'random_bytes('r')'
+SELECT random_bytes('res');
+ERROR 22003: length value is out of range in 'random_bytes('res')'
+SELECT random_bytes('test');
+ERROR 22003: length value is out of range in 'random_bytes('test')'
+#
+# End of 10.10 tests
+#
diff --git a/mysql-test/main/func_str.test b/mysql-test/main/func_str.test
index bbdcead280f..af2e64a02ad 100644
--- a/mysql-test/main/func_str.test
+++ b/mysql-test/main/func_str.test
@@ -1,6 +1,7 @@
 # Description
 # -----------
 # Testing string functions
+--source include/have_sequence.inc
 
 --disable_warnings
 drop table if exists t1,t2;
@@ -2193,3 +2194,59 @@ SELECT GROUP_CONCAT( UpdateXML( '<a>new year</a>', '/a', '2019-01-01 00:00:00' )
 --echo #
 --echo # End of 10.4 tests
 --echo #
+
+--echo #
+--echo # MDEV-25704 Function random_bytes
+--echo #
+
+create table t1 as select random_bytes(100);
+show create table t1;
+drop table t1;
+
+--echo # The sequence starts at 17 so that the probability of test failure is small enough (about 2^(-136))
+select count(*) from seq_17_to_1024 where random_bytes(seq) <=> random_bytes(seq);
+
+select (count(*) = 1024) from seq_1_to_1024 where length(random_bytes(seq)) = seq;
+
+--echo #
+--echo # Test NULL output for NULL input
+--echo #
+
+SELECT random_bytes(NULL);
+
+--echo #
+--echo # Test For values outside range from 1 to 1024, an error occurs
+--echo #
+
+--error 1690
+SELECT random_bytes(0);
+--error 1690
+SELECT random_bytes(-1);
+--error 1690
+SELECT random_bytes(-100);
+--error 1690
+SELECT random_bytes(-26);
+--error 1690
+SELECT random_bytes(-132);
+--error 1690
+SELECT random_bytes(1025);
+--error 1690
+SELECT random_bytes(11111);
+--error 1690
+SELECT random_bytes(2056);
+
+--echo #
+--echo # Test For invalid argument, an error occurs
+--echo #
+--error 1690
+SELECT random_bytes('s');
+--error 1690
+SELECT random_bytes('r');
+--error 1690
+SELECT random_bytes('res');
+--error 1690
+SELECT random_bytes('test');
+
+--echo #
+--echo # End of 10.10 tests
+--echo #
diff --git a/mysql-test/suite/binlog/r/binlog_unsafe.result b/mysql-test/suite/binlog/r/binlog_unsafe.result
index a8143ef086e..0c0b0e77915 100644
--- a/mysql-test/suite/binlog/r/binlog_unsafe.result
+++ b/mysql-test/suite/binlog/r/binlog_unsafe.result
@@ -2609,6 +2609,9 @@ INSERT INTO t1 VALUES (VERSION());
 Warnings:
 Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave
 INSERT INTO t1 VALUES (RAND());
+INSERT INTO t1 VALUES (RANDOM_BYTES(1000));
+Warnings:
+Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave
 DELETE FROM t1;
 SET TIME_ZONE= '+03:00';
 SET TIMESTAMP=1000000;
diff --git a/mysql-test/suite/binlog/t/binlog_unsafe.test b/mysql-test/suite/binlog/t/binlog_unsafe.test
index 673712ff209..8b2a00e174c 100644
--- a/mysql-test/suite/binlog/t/binlog_unsafe.test
+++ b/mysql-test/suite/binlog/t/binlog_unsafe.test
@@ -651,7 +651,7 @@ DROP TABLE t1,t2,t3;
 
 #
 # BUG#47995: Mark user functions as unsafe
-# BUG#49222: Mare RAND() unsafe
+# BUG#49222: Mark RAND() unsafe
 #
 # Test that the system functions that are supposed to be marked unsafe
 # generate a warning.  Each INSERT statement below should generate a
@@ -678,6 +678,7 @@ INSERT INTO t1 VALUES (UUID());               #marked unsafe before BUG#47995
 INSERT INTO t1 VALUES (UUID_SHORT());         #marked unsafe before BUG#47995
 INSERT INTO t1 VALUES (VERSION());            #marked unsafe in BUG#47995
 INSERT INTO t1 VALUES (RAND());               #marked unsafe in BUG#49222
+INSERT INTO t1 VALUES (RANDOM_BYTES(1000));   #marked unsafe in MDEV-25704
 DELETE FROM t1;
 
 # Since we replicate the TIMESTAMP variable, functions affected by the
diff --git a/sql/item_create.cc b/sql/item_create.cc
index c34119e1d9e..bceff7fb43c 100644
--- a/sql/item_create.cc
+++ b/sql/item_create.cc
@@ -1873,6 +1873,19 @@ protected:
 };
 
 
+class Create_func_random_bytes : public Create_func_arg1
+{
+public:
+  virtual Item *create_1_arg(THD *thd, Item *arg1);
+
+  static Create_func_random_bytes s_singleton;
+
+protected:
+  Create_func_random_bytes() {}
+  virtual ~Create_func_random_bytes() {}
+};
+
+
 class Create_func_release_all_locks : public Create_func_arg0
 {
 public:
@@ -4985,6 +4998,15 @@ Create_func_rand::create_native(THD *thd, const LEX_CSTRING *name,
 }
 
 
+Create_func_random_bytes Create_func_random_bytes::s_singleton;
+
+Item *Create_func_random_bytes::create_1_arg(THD *thd, Item *arg1)
+{
+  thd->lex->set_stmt_unsafe(LEX::BINLOG_STMT_UNSAFE_SYSTEM_FUNCTION);
+  return new (thd->mem_root) Item_func_random_bytes(thd, arg1);
+}
+
+
 Create_func_release_all_locks Create_func_release_all_locks::s_singleton;
 
 Item*
@@ -5804,6 +5826,7 @@ Native_func_registry func_array[] =
   { { STRING_WITH_LEN("POW") }, BUILDER(Create_func_pow)},
   { { STRING_WITH_LEN("POWER") }, BUILDER(Create_func_pow)},
   { { STRING_WITH_LEN("QUOTE") }, BUILDER(Create_func_quote)},
+  { { STRING_WITH_LEN("RANDOM_BYTES")}, BUILDER(Create_func_random_bytes)},
   { { STRING_WITH_LEN("REGEXP_INSTR") }, BUILDER(Create_func_regexp_instr)},
   { { STRING_WITH_LEN("REGEXP_REPLACE") }, BUILDER(Create_func_regexp_replace)},
   { { STRING_WITH_LEN("REGEXP_SUBSTR") }, BUILDER(Create_func_regexp_substr)},
diff --git a/sql/item_strfunc.cc b/sql/item_strfunc.cc
index 0291ddcd5b3..f7fee8917bd 100644
--- a/sql/item_strfunc.cc
+++ b/sql/item_strfunc.cc
@@ -1449,6 +1449,72 @@ String *Item_func_sformat::val_str(String *res)
   return null_value ? NULL : res;
 }
 
+#include"my_global.h"
+#include <openssl/rand.h>
+#include <openssl/err.h>
+
+bool Item_func_random_bytes::fix_length_and_dec(THD *thd)
+{
+  used_tables_cache|= RAND_TABLE_BIT;
+  if (args[0]->can_eval_in_optimize())
+  {
+    max_length= MY_MIN((int32) args[0]->val_int(), MAX_BLOB_WIDTH);
+    return false;
+  }
+  max_length= MAX_BLOB_WIDTH;
+  return false;
+}
+
+
+void Item_func_random_bytes::update_used_tables()
+{
+  Item_str_func::update_used_tables();
+  used_tables_cache|= RAND_TABLE_BIT;
+}
+
+
+String *Item_func_random_bytes::val_str(String *str)
+{
+  longlong count= args[0]->val_int();
+
+  if (args[0]->null_value)
+    goto err;
+  null_value= 0;
+
+  if (count < 1 || count > 1024)
+  {
+    char buf[256];
+    String msg(buf, sizeof(buf), system_charset_info);
+    msg.length(0);
+    print(&msg, QT_NO_DATA_EXPANSION);
+    my_error(ER_DATA_OUT_OF_RANGE, MYF(0), "length", msg.c_ptr_safe());
+    return make_empty_result(str);
+  }
+
+  if (str->alloc((uint) count))
+    goto err;
+
+  str->length(count);
+  if (!RAND_bytes((unsigned char *) str->ptr(), (int32) count))
+  {
+    ulong ssl_err;
+    while ((ssl_err= ERR_get_error()))
+    {
+      char buf[256];
+      ERR_error_string_n(ssl_err, buf, sizeof(buf));
+      sql_print_warning("SSL error: %s", buf);
+    }
+    return make_empty_result(str);
+  }
+  
+  return str;
+
+err:
+  null_value= 1;
+  return 0;
+}
+
+
 /*********************************************************************/
 bool Item_func_regexp_replace::fix_length_and_dec(THD *thd)
 {
diff --git a/sql/item_strfunc.h b/sql/item_strfunc.h
index 68b6f954b0a..4725779108c 100644
--- a/sql/item_strfunc.h
+++ b/sql/item_strfunc.h
@@ -384,6 +384,26 @@ public:
   { return get_item_copy<Item_func_concat_ws>(thd, this); }
 };
 
+
+class Item_func_random_bytes : public Item_str_func
+{
+public:
+  Item_func_random_bytes(THD *thd, Item *arg1) : Item_str_func(thd, arg1) {}
+  bool fix_length_and_dec(THD *thd) override;
+  void update_used_tables() override;
+  String *val_str(String *) override;
+  LEX_CSTRING func_name_cstring() const override
+  {
+    static LEX_CSTRING name= {STRING_WITH_LEN("random_bytes")};
+    return name;
+  }
+  Item *get_copy(THD *thd) override
+  {
+    return get_item_copy<Item_func_random_bytes>(thd, this);
+  }
+};
+
+
 class Item_func_reverse :public Item_str_func
 {
   String tmp_value;