Stored functions for string escaping for CSV export

mysql_stored_functions.rb

module MysqlStoredFunctions
  def store_urlencode_function
    sql = <<-_SQL
DROP FUNCTION IF EXISTS urlencode;
^
-- Thank Jeremy Thomerson for this one
-- http://jeremythomerson.com/2013/05/30/urlencoder-function-for-mysql/
CREATE FUNCTION URLENCODE(str VARCHAR(4096) CHARSET utf8) RETURNS VARCHAR(4096) CHARSET utf8
DETERMINISTIC
CONTAINS SQL
BEGIN
   DECLARE sub VARCHAR(1) CHARSET utf8;
   DECLARE val BIGINT DEFAULT 0;
   DECLARE ind INT DEFAULT 1;
   DECLARE oct INT DEFAULT 0;
   DECLARE ret VARCHAR(4096) DEFAULT '';
   DECLARE octind INT DEFAULT 0;

   IF ISNULL(str) THEN
      RETURN NULL;
   ELSE
      SET ret = '';
      WHILE ind <= CHAR_LENGTH(str) DO
         SET sub = MID(str, ind, 1);
         SET val = ORD(sub);
         IF NOT (val BETWEEN 48 AND 57 OR
                 val BETWEEN 65 AND 90 OR
                 val BETWEEN 97 AND 122 OR
                 val IN (45, 46, 95, 126)) THEN
            SET octind = OCTET_LENGTH(sub);
            WHILE octind > 0 DO
               SET oct = (val >> (8 * (octind - 1)));
               SET ret = CONCAT(ret, '%', LPAD(HEX(oct), 2, 0));
               SET val = (val & (POWER(256, (octind - 1)) - 1));
               SET octind = (octind - 1);
            END WHILE;
         ELSE
            SET ret = CONCAT(ret, sub);
         END IF;
         SET ind = (ind + 1);
      END WHILE;
   END IF;
   RETURN ret;
END;
^
DROP FUNCTION IF EXISTS html_encode;
^
CREATE FUNCTION html_encode(str VARCHAR(4096) CHARSET utf8) RETURNS VARCHAR(4096) CHARSET utf8
DETERMINISTIC
CONTAINS SQL
BEGIN
   RETURN REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(str,'\e',''),'\r',''),'\f',''),'\v',''),'\n',''),'\t',''),'\b',''),'\a',''),'&','&amp;'),'>','&gt;'),'<','&lt;'),"'",'&#39;'),'"','&quot;');
END;

_SQL
    sql.split('^').each do |stmt|
      ActiveRecord::Base.connection.execute(stmt) if (stmt.strip! && stmt.length > 0)
    end
  end
end