web stats
Mirth Community - View Single Post - Mirth Tools: User defined functions
View Single Post
  #28  
Old 12-09-2013, 03:24 PM
mankowitz mankowitz is offline
Mirth Newb
 
Join Date: Oct 2013
Posts: 8
mankowitz is on a distinguished road
Smile javascript escape for sql, mysql

In some of my channels, I need to use javascript to apply changes to a MySQL database. The problem is that data needs to be esacaped. I have used the below functions which have saved me time.

Code:
function mq_escape(s) {
    if (s==null) return null;
	var str = s.toString() + "";
    return str.replace(/[\0\x08\x09\x1a\n\r"'\\\%]/g, function (char) {
        switch (char) {
            case "\0":
                return "\\0";
            case "\x08":
                return "\\b";
            case "\x09":
                return "\\t";
            case "\x1a":
                return "\\z";
            case "\n":
                return "\\n";
            case "\r":
                return "\\r";
            case "\"":
            case "'":
            case "\\":
            case "%":
                return "\\"+char; // prepends a backslash to backslash, percent,
                                  // and double/single quotes
        }
    });
}


function escaper(match, p) {
 // match is the whole string, p is the first parenthesized text
 // in this case, p is the text between the % signs.
 if ($(p)=="" || $(p)=="null" || $(p)==null) return "null";
 else return "'" + mq_escape($(p)) + "'"; 
}

function mq(db, sql) {
sql = sql.replace(/%([^%]+)%/g, escaper);
db.executeUpdate(sql); 
}
Which then allows you to do something like this:

Code:
var dbConn = DatabaseConnectionFactory.createDatabaseConnection('com.mysql.jdbc.Driver','jdbc:mysql://localhost:3306/documenter','hl7user','zzzzzzz');

mq(dbConn, "UPDATE documenter.tblmaster " + 
"SET FirstName = %pt_firstname%, " + 
"LastName = %pt_lastname%, " + 
"MRN = %pt_MRN%, " +
"ChiefComplaint = %visit_reason%, " + 
"MD = %PV1_md_id%, PMD = %PD1_pmd_id%, DOB = %pt_DOB%, " + 
"Sex = %pt_Sex%, " + 
"Weight = COALESCE(Weight, %weightKg%) " + 
"WHERE account = %pt_account%");
Reply With Quote