sql validator

by ecropolis

HTML

<div>
    <h3>Test String</h3>
    <p id="testexp"></p>
    <h3>Parsed String</h3>
    <p id="output"></p>
    <p id="result"></p>
</div>
<form>
    <label>INPUT</label>
    <textarea></textarea>
</form>
<h3>Test Expressions</h3>
<ul>
    <li>ORIGINALBORROWERBALANCE >= 1000 AND (ORIGINALBORROWERBALANCE <= 90000000) AND (OBLIGORNAME = 'IVPCohort5') AND (CURRENTSERVICINGBALANCE <> 0)</li>
    <li>CLIENTINSTRUMENTID = 'BETWEEN'</li>
    <li>CLIENTINSTRUMENTID = ' BETWEEN '</li>
    <li>CLIENTINSTRUMENTID = '  BETWEEN   '</li>
    <li>CLIENTINSTRUMENTID = 'ABC  BETWEEN   XYZ '</li>
    <li>ORIGINALBORROWERBALANCE >= 1000 AND (ORIGINALBORROWERBALANCE <= 90000000) AND (OBLIGORNAME = 'IVPCohort5') AND (CURRENTSERVICINGBALANCE <> 0)</li>
    <li>RISK_FICORANGE IN ('451-500','501-550','551-600','601-650','651-700','701-750') AND (OBLIGORNAME = 'RmmCohortOne')  AND (CURRENTAPPRAISEDVALUE BETWEEN 1000 AND 100000) AND (CURRENTBORROWERBALANCE > 1000) AND (CURRENTBORROWERBALANCE < 90000000) and (CURRENTAPPRAISALDATE BETWEEN '2000-01-01' AND '2011-12-31')</li>
    <li>(OBLIGORNAME = 'IVPCohort8' OR CLIENTINSTRUMENTID IN ('LossCurveShapingLOANID001','LossCurveShapingLOANID002','LossCurveShapingLOANID003','LossCurveShapingLOANID005')) AND (ORIGINALBORROWERBALANCE > 10) AND (ORIGINALBORROWERBALANCE < 90000000)</li>
    <li>(OBLIGORNAME = 'IVPCohort8' OR CLIENTINSTRUMENTID IN ('LossCurveShapingLOANID001','LossCurveShapingLOANID002','LossCurveShapingLOANID003','LossCurveShapingLOANID005')) AND (ORIGINALBORROWERBALANCE > 10) AND (ORIGINALBORROWERBALANCE < 90000000)</li>
    <li>(vala> 5.3 AND vala <.10) AND (plumb < .012 AND ( valb < 10 OR this <> 2,000 ) AND this in ('blue','red) and that in ('greenyellow') and green between 4.5 and 'purple')</li>
</ul>

CSS

body {
    font-size:12px;
    font-family:arial, sans-serif;
}
em {
    text-transform:uppercase;
    font-weight: bold;
}
.valid {
    color: green
}
.invalid {
    color:red;
}
p {
    display: block;
    padding: 2px;
    background: rgb(247, 242, 235);
    border: 1px solid #d5d5d5;
    min-height: 14px;
}
#result { font-weight: bold; }
form { margin-top: 40px; }
form textarea { width: 98%; margin: 12px auto;  outline: none; height: 60px; }
ul li { border-bottom: 1px solid #d5d5d5; padding: 5px 0; cursor: pointer; }

JavaScript

var $editor = {};
$editor.typeinput = $('form textarea');
$editor.typeinput.keyup(function(event) {
    event.stopPropagation(); 
    //console.log('key:'+event.which); // (event.which == 190) || // Shift key
    isvalid();
});
var isvalid = function() {
    var bordercolor = 'red';
    var result = 'invalid';
    if(validateExp($editor.typeinput.val())){
        bordercolor = 'green';
        result = 'valid';
    }
    $editor.typeinput.css('border','1px solid ' + bordercolor);
    $('#testexp').html($editor.typeinput.val());
    $('#result').html('RESULT: <em class="' + result + '">' + result + '</em>');   
}

var validateExp = function (exp,showparsed) {
    var po = (exp.match(/\(/g) || []).length;
    var pc = (exp.match(/\)/g) || []).length;
    exp = exp.replace(/\'([^\']*)\'/gi,"TEST");
    exp = exp.replace(/\(/g,' ( ').replace(/\)/g,' ) ');
    var quotes = (exp.match(/\'/g) || []).length;
    //console.log('quotes:' + (quotes % 2))
    var result = true;
    //console.log(exp.match(/^[\s\da-zA-Z><=&()+%*$,.\\-]*$/g));
    if (po != pc || (quotes % 2) != 0) {
        result = false;
    } else {
        exp = exp.replace(/\'/g,'');
        if(exp.match(/\sin\s/gi)) {
            console.log('expression has an IN');
            var rx_ins = /(\w+\s*)(IN\s*\()(.+?)\)\s*/gi;
            if(exp.match(rx_ins) != null){
                exp = exp.replace(rx_ins, function(match, text, urlId){
                   return ' 1 = 1 ';
                });
            } else {
                result = false;
            }
            //var ins = (exp.match(rx_ins));
            //exp = exp.replace(rx_ins, ' 1 = 1 ');
        }
        if(exp.match(/between/i)) {
            //console.log('expression has a BETWEEN');
            var rx_bet = /([+\-]?((?:[1-9]\d*|0)(?:\.\d*)?|\.\d+)([eE][+-]?\d+)?|\w+)(?=\s*BETWEEN)(\s*BETWEEN\s*)(.+?\w+.+?\s*)(AND\s*)(.+?\w+.+?\s*)/gi;
            var bets = (exp.match(rx_bet));
            exp = exp.replace(rx_bet, ' 1 = 1 ');
       ...