Regex functionality is not documented in README

[bean5]

Well, there are a few references, but builder.js has some references in

/* Example datatable querystring ....

Regex wasn't working for me. We were using this for SQLite. I realized we'd need to filter based on regex after running the full query. I was going to submit a pull request, but it turns out I modified a previous version of the code. Here is what we use:

/*!
 * node-datatable
 * https://github.com/jpravetz/node-datatable
 * Copyright(c) 2012-2013 Jim Pravetz <[email protected]>
 * node-datatable may be freely distributed under the MIT license.
 * 
 * Note: We are not compatible with newer versions of this since 
 * the newer version doesn't seem to support RegEx. In face,
 * we modified the default regex support functionality such that it
 * was supported after the query ran. In other words, the filtering
 * occurs after the query runs.
 */

// var _u = require('underscore');

var DEFAULT_LIMIT = 5000;

/**
 * Constructor
 * @param options Refer to README.md for a list of properties
 * @return {Object}
 */
// module.exports = function (options) {
var QueryBuilder = function ( options ) {

    var self = {
        sTableName: options.sTableName,
        sCountTableName: options.sCountTableName,   // Name of table to use when counting total number of rows. Defaults to sCountColumnName
        sCountColumnName: options.sCountColumnName,   // Name of column to use when counting total number of rows. Defaults to options.sCountColumnName
        sDatabaseOrSchema: options.sDatabaseOrSchema,           // If set, then do "USE useDatabase;" prior to query
        aSearchColumns: options.aSearchColumns || [],     // Used to determine names of columns to search
        sSelectSql: options.sSelectSql,           // alternate select statement
        sFromSql: options.sFromSql,           // alternate select statement
        sWhereAndSql: options.sWhereAndSql,           // Custom caller SQL, added as AND where to add date range or other checks (caller must write the SQL)
        sGroupBySql: options.sGroupBySql || [],   // Custom caller SQL, added as GROUP BY statement
        sDateColumnName: options.sDateColumnName,   // If set then only get entries within the range (can use sWhereSql instead)
        dateFrom: options.dateFrom,                 // Only retrieve content from before this date. sDateColumnName must be set.
        dateTo: options.dateTo,                     // Only retrieve content from after this date. sDateColumnName must be set.
        oRequestQuery: options.oRequestQuery,           // Usually passed in with buildQuery
        sAjaxDataProp: 'data',           // The name of the data prop to set on the return value

        dbType: options.dbType,                     // "postgres" or "oracle", defaults to MySQL syntax

        buildQuery: buildQuery,
        parseResponse: parseResponse,
        filteredResult: filteredResult
    };

    /**
     * (private) Build an optional "USE sDatabaseOrSchema" for MySQL / Postgres or
     * "ALTER SESSION SET CURRENT_SCHEMA = sDatabaseOrSchema" statement for Oracle if sDatabaseOrSchema is set.
     * @return {string|undefined} The SQL statement or undefined
     */
    function buildSetDatabaseOrSchemaStatement() {
        if (self.sDatabaseOrSchema){
            if (self.dbType === 'oracle'){
                return 'ALTER SESSION SET CURRENT_SCHEMA = ' + self.sDatabaseOrSchema;
            }
            else{
                return "USE " + self.sDatabaseOrSchema;
            }
        }
    }

    /**
     * (private) Build the date partial that is used in a WHERE clause
     * @return {*}
     */
    function buildDatePartial() {
        if (self.sDateColumnName && self.dateFrom || self.dateTo) {
            // console.log( "DateFrom %s to %s", self.dateFrom, self.dateTo );
            if (self.dateFrom && self.dateTo) {
                return self.sDateColumnName + " BETWEEN '" + self.dateFrom.toISOString() + "' AND '" + self.dateTo.toISOString() + "'";
            } else if (self.dateFrom) {
                return self.sDateColumnName + " >= '" + self.dateFrom.toISOString() + "'";
            } else if (self.dateTo) {
                return self.sDateColumnName + " <= '" + self.dateTo.toISOString() + "'";
            }
        }
        return undefined;
    }

    /**
     * (private) Build a complete SELECT statement that counts the number of entries.
     * @param searchString If specified then produces a statement to count the filtered list of records.
     * Otherwise the statement counts the unfiltered list of records.
     * @return {String} A complete SELECT statement
     */
    function buildCountStatement(requestQuery, countType) {
        var dateSql = buildDatePartial();
        var result;
        var countTable;

        if(self.sCountTableName) {
            countTable = self.sCountTableName;
        } else {
            countTable = self.sTableName;
        }

        if(self.sGroupBySql.length) {
            result = "SELECT COUNT(*) " + countType + " FROM (";
            result += "SELECT COUNT(" + self.sGroupBySql[0] + ") as " + countType + " FROM ";
            result += self.sFromSql ? self.sFromSql : countTable;
            result += buildWherePartial(requestQuery);
            result += buildGroupByPartial();
            result += ") temp";
        } else {
            result = "SELECT COUNT(";
            result += self.sSelectSql ? "*" : (self.sCountColumnName ? self.sCountColumnName : "id");
            result += ") AS " + countType + " FROM ";
            result += self.sFromSql ? self.sFromSql : countTable;
            result += buildWherePartial(requestQuery);
        }
//        var sSearchQuery = buildSearchPartial( sSearchString );
//        var sWheres = sSearchQuery ? [ sSearchQuery ] : [];
//        if( self.sWhereAndSql )
//            sWheres.push( self.sWhereAndSql )
//        if( dateSql )
//            sWheres.push( dateSql );
//        if( sWheres.length )
//            result += " WHERE (" + sWheres.join( ") AND (" ) + ")";
        return result;
    }

    /**
     * (private) Build the WHERE clause
     * otherwise uses aoColumnDef mData property.
     * @param searchString
     * @return {String}
     */
    function buildWherePartial(requestQuery) {
        var sWheres = [];
        var searchQuery = buildSearchPartial(requestQuery);
        if (searchQuery)
            sWheres.push(searchQuery);
        if (self.sWhereAndSql)
            sWheres.push(self.sWhereAndSql);
        var dateSql = buildDatePartial();
        if (dateSql)
            sWheres.push(dateSql);
        if (sWheres.length)
            return " WHERE (" + sWheres.join(") AND (") + ")";
        return "";
    }

    /**
     * (private) Build the GROUP BY clause
     * @return {String}
     */
    function buildGroupByPartial() {
        if (self.sGroupBySql.length)
            return " GROUP BY " + self.sGroupBySql.join(',') + " ";
        return "";
    }

    /**
     * (private)  Builds the search portion of the WHERE clause using LIKE (or ILIKE for PostgreSQL).
     * @param {Object} requestQuery The datatable parameters that are generated by the client
     * @return {String} A portion of a WHERE clause that does a search on all searchable row entries.
     */
    function buildSearchPartial(requestQuery) {
        var query = [];
        var globalQuery = [];
        var queryString;
        var globalString = "";
        var returnValue;
        for (var sdx = 0; sdx < requestQuery.columns.length; ++sdx) {
            var globalSearch = null,
                columnSearch = null,
                column = requestQuery.columns[sdx],
                colName = self.aSearchColumns[sdx] || column.data;

            if (column.searchable === true){
                if (requestQuery.search.value){
                    globalSearch = self.dbType === 'postgres' ?
                                   buildILIKESearch(colName, requestQuery.search.value) :
                                   buildLIKESearch(colName, requestQuery.search.value);
                }

                if (column.search.value){
                    if( self.dbType === 'postgres' ) {
                       columnSearch = buildILIKESearch(colName, column.search.value);
                    } else {
                        if( column.search.regex ) {
                            // Regex is not cross-browser supported in SQLite, so it will be handled after the query runs
                            // columnSearch = buildREGEXPSearch(colName, column.search.value);
                        } else {
                            columnSearch = buildLIKESearch(colName, column.search.value);
                        }
                    }
                }

                if (globalSearch && columnSearch){
                    query.push(columnSearch);
                    globalQuery.push(globalSearch);
                } else if (globalSearch){
                    globalQuery.push(globalSearch);
                }
                else if (columnSearch){
                    query.push(columnSearch);
                }
            }
        }

        if( query.length ) {
            queryString = "(" + query.join(" AND ") + ")";
        }

        if( globalQuery.length ) {
            globalString = "(" + globalQuery.join(" OR ") + ")";
        }

        if(query.length && globalQuery.length) {
            returnValue = queryString + " AND " + globalString;
        } else if (query.length) {
            returnValue = queryString;
        } else if (globalString.length) {
            returnValue = globalString;
        } else {
            returnValue = undefined;
        }

        return returnValue;
    }

    /**
     * (private) Builds the search portion of the WHERE clause using ILIKE
     * @param {string} colName The column to search
     * @param {string} searchVal The value to search for
     * @returns {string} An ILIKE statement to be added to the where clause
     */
    function buildILIKESearch(colName, searchVal) {
        return "CAST(" + colName + " as text)" + " ILIKE '%" + searchVal + "%'";
    }

    /**
     * (private) Builds the search portion of the WHERE clause using LIKE
     * @param {string} colName The column to search
     * @param {string} searchVal The value to search for
     * @returns {string} A LIKE statement to be added to the where clause
     */
    function buildLIKESearch(colName, searchVal) {
        return colName + " LIKE '%" + searchVal + "%'";
    }

    /**
     * (private) Builds the search portion of the WHERE clause using LIKE
     * @param {string} colName The column to search
     * @param {string} searchVal The value to search for
     * @returns {string} A LIKE statement to be added to the where clause
     */
    function buildREGEXPSearch(colName, searchVal) {
        return colName + " REGEXP '" + searchVal + "'";
    }

    /**
     * (private) Adds an ORDER clause
     * @param requestQuery The Datatable query string (we look at sort direction and sort columns)
     * @return {String} The ORDER clause
     */
    function buildOrderingPartial(requestQuery) {
        var query = [];
        for (var fdx = 0; fdx < requestQuery.order.length; ++fdx) {
            var order = requestQuery.order[fdx],
                column = requestQuery.columns[order.column];

            if (column.orderable === true && column.data) {
                query.push(column.data + " " + order.dir);
            }
        }
        if (query.length)
            return " ORDER BY " + query.join(", ");
        return "";
    }

    /**
     * Build a LIMIT clause
     * @param requestQuery The Datatable query string (we look at length and start)
     * @return {String} The LIMIT clause
     */
    function buildLimitPartial(requestQuery) {
        var sLimit = "";
        if (requestQuery && requestQuery.start !== undefined && self.dbType !== 'oracle') {
            var start = parseInt(requestQuery.start, 10);
            if (start >= 0) {
                var len = parseInt(requestQuery.length, 10);
                sLimit = (self.dbType === 'postgres') ? " OFFSET " + String(start) + " LIMIT " : " LIMIT " + String(start) + ", ";
                sLimit += ( len > 0 ) ? String(len) : String(DEFAULT_LIMIT);
            }
        }
        return sLimit;
    }

    /**
     * Build the base SELECT statement.
     * @return {String} The SELECT partial
     */
    function buildSelectPartial() {
        var query = "SELECT ";
        query += self.sSelectSql ? self.sSelectSql : "*";
        query += " FROM ";
        query += self.sFromSql ? self.sFromSql : self.sTableName;
        return query;
    }

    /**
     * Build an array of query strings based on the Datatable parameters
     * @param requestQuery The datatable parameters that are generated by the client
     * @return {Object} An array of query strings, each including a terminating semicolon.
     */
    function buildQuery(requestQuery) {
        var queries = {};
        if (typeof requestQuery !== 'object')
            return queries;
        var searchString = sanitize(requestQuery.search.value);
        self.oRequestQuery = requestQuery;
        var useStmt = buildSetDatabaseOrSchemaStatement();
        if (useStmt) {
            queries.use = useStmt;
        }
        queries.recordsTotal = buildCountStatement(requestQuery, "recordsTotal");
        if (searchString) {
            queries.recordsFiltered = buildCountStatement(requestQuery, "recordsFiltered");
        }
        var query = buildSelectPartial();
        query += buildWherePartial(requestQuery);
        query += buildGroupByPartial();
        query += buildOrderingPartial(requestQuery);
        // Due to the fact that regex filtering must occur after the query has run, we can't paginate until after the regex has been applied (which means a potentially large query result set up until pagination)
        // query += buildLimitPartial(requestQuery);
        if (self.dbType === 'oracle'){
            var start = parseInt(requestQuery.start, 10);
            var len = parseInt(requestQuery.length, 10);
            if (len >= 0 && start >= 0) {
                query = 'SELECT * FROM (SELECT a.*, ROWNUM rnum FROM (' + query + ') ';
                query += 'a WHERE ROWNUM <= ' + (start + len) + ') WHERE rnum >= ' + start;
            }
        }
        queries.select = query;
        return queries;
    }

    /**
     * Parse the responses from the database and build a Datatable response object.
     * @param queryResult An array of SQL response objects, each of which must, in order, correspond with a query string
     * returned by buildQuery.
     * @return {Object} A Datatable reply that is suitable for sending in a response to the client.
     */
    function parseResponse(queryResult) {
        var oQuery = self.oRequestQuery;
        var result = { recordsFiltered: 0, recordsTotal: 0 };
        if (oQuery && typeof oQuery.draw === 'string') {
            // Cast for security reasons, as per http://datatables.net/usage/server-side
            result.draw = parseInt(oQuery.draw,10);
        } else {
            result.draw = 0;
        }
        if (_u.isObject(queryResult) && _u.keys(queryResult).length > 1) {
            result.recordsFiltered = result.recordsTotal = extractCount(queryResult.recordsTotal);
            if (queryResult.recordsFiltered) {
                result.recordsFiltered = extractCount(queryResult.recordsFiltered);
            }
            result.data = queryResult.select;
        }
        return result;
    }

    /**
     * (private)
     * @param obj
     * @return {*}
     */
    function extractCount(obj) {
        var values;
        if (obj && obj.length)
            values = _u.values(obj[0]);
        if (values && values.length)
            return values[0];
        return 0;
    }

    /**
     * Debug, reduced size object for display
     * @param obj
     * @return {*}
     */
    function filteredResult(obj, count) {
        if (obj) {
            var result = _u.omit(obj, self.sAjaxDataProp );
            result.aaLength = obj[self.sAjaxDataProp] ? obj[self.sAjaxDataProp].length : 0;
            result[self.sAjaxDataProp] = [];
            var count = count ? Math.min(count, result.aaLength) : result.aaLength;
            for (var idx = 0; idx < count; ++idx) {
                result[self.sAjaxDataProp].push(obj[self.sAjaxDataProp][idx]);
            }
            return result;
        }
        return null;
    }

    return self;
}

/**
 * Sanitize to prevent SQL injections.
 * @param str
 * @return {*}
 */
function sanitize(str, len) {
    len = len || 256;
    if (!str || typeof str === 'string' && str.length < 1)
        return str;
    if (typeof str !== 'string' || str.length > len)
        return null;
    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
        }
    });
}