Skip to content

Instantly share code, notes, and snippets.

@Krule
Last active August 29, 2015 14:19
Show Gist options
  • Save Krule/9703de31e6bbc8538b85 to your computer and use it in GitHub Desktop.
Save Krule/9703de31e6bbc8538b85 to your computer and use it in GitHub Desktop.
Stored functions for string escaping for CSV export
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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment