Skip to content

Instantly share code, notes, and snippets.

@Krule

Krule/mysql_stored_functions.rb

Last active August 29, 2015 14:19
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save Krule/9703de31e6bbc8538b85 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save Krule/9703de31e6bbc8538b85 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. Krule revised this gist Apr 24, 2015. 1 changed file with 2 additions and 2 deletions.
    4 changes: 2 additions & 2 deletions mysql_stored_functions.rb
    View file
    Original file line number Diff line number Diff line change
    @@ -1,10 +1,10 @@
    module MysqlStoredFunctions
    def store_urlencode_function
    # Thanks to Jeremy Thomerson
    # http://jeremythomerson.com/2013/05/30/urlencoder-function-for-mysql/
    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
  2. Krule revised this gist Apr 24, 2015. 1 changed file with 2 additions and 0 deletions.
    2 changes: 2 additions & 0 deletions mysql_stored_functions.rb
    View file
    Original file line number Diff line number Diff line change
    @@ -1,5 +1,7 @@
    module MysqlStoredFunctions
    def store_urlencode_function
    # Thanks to Jeremy Thomerson
    # http://jeremythomerson.com/2013/05/30/urlencoder-function-for-mysql/
    sql = <<-_SQL
    DROP FUNCTION IF EXISTS urlencode;
    ^
  3. Krule created this gist Apr 24, 2015.
    58 changes: 58 additions & 0 deletions mysql_stored_functions.rb
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,58 @@
    module MysqlStoredFunctions
    def store_urlencode_function
    sql = <<-_SQL
    DROP FUNCTION IF EXISTS urlencode;
    ^
    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