Generic Brute Force SQL Search - Groovy Style

DBBFSearch.groovy

#!/usr/bin/env groovy
/*
 * deas - http://www.contentreich.de/generic-brute-force-sql-search-groovy-style
 *
 * Created on Mar 15, 2012
 * 
 * Licensed to the Apache Software Foundation (ASF) under one or more
 * contributor license agreements.  See the NOTICE file distributed with
 * this work for additional information regarding copyright ownership.
 * The ASF licenses this file to You under the Apache License, Version 2.0
 * (the "License"); you may not use this file except in compliance with
 * the License.  You may obtain a copy of the License at
 *
 *     http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */
import groovy.sql.Sql

def collectRs(resultSet, clojure) { // I know
	def resList = []
	try {
		while (resultSet.next()) {
			def res = clojure(resultSet.toRowResult())
			if (res)  resList << res
		}
	} finally {
		resultSet.close()
	}
	resList
}

def createStmt (table, cnt, cols, dbProdName) {
	def clojure = cnt ? { "(select count(*) from ${table.name} where " + escapeColName(it, dbProdName) + " like ?1) as '${it}'" } : { "t."+ it}
	def sel = cols.collect(clojure).join(",")
	def escCol = table.columns.collect { escapeColName(it, dbProdName) }
	def colfn = fn ? escCol.collect { "${fn}(t.${it})" } : escCol.collect { "t.${it}" }
	def where = colfn.collect { "$it like ?1"}.join(" OR ")
	"select $sel " + (!cnt ? "from ${table.name} t where $where" : "")
}

// Some people are asking for trouble - really !
def escapeColName(col, prod) {
	def colVal = col
	if (col.indexOf(".") > -1) {
		if (prod.startsWith("microsoft")) {
			colVal = "[${col}]"
		} else if (prod.startsWith("mysql")) {
			colVal = "`${col}`"
		}
	}
	colVal
} 

def sc = getClass().name
def cli = new CliBuilder( usage: "$sc -h -u userid -p password -v value -U url -d driver -j jar -f function -m -q -s",
	 footer:"""
Example:
$sc -v findme \\
-U 'jdbc:mysql://localhost/db?user=user&password=pass' \\
-j mysql:mysql-connector-java:5.1.6 -s -f lower
""")
cli.h(longOpt:'help', 'help')
cli.u(argName:'userid', longOpt:'userid', args:1, required:false, type:GString, 'Userid')
cli.p(argName:'password', longOpt:'password', args:1, required:false, type:GString, 'Password')
cli.v(argName:'value', longOpt:'value', args:1, required:true, type:GString, 'Value to search for')
cli.U(argName:'url', longOpt:'url', args:1, required:true, type:GString, 'Database URL')
cli.d(argName:'driver', longOpt:'driver', args:1, required:false, type:GString, 'Driver class')
cli.j(argName:'jar', longOpt:'jar', args:1, required:false, type:GString, 'Jar file containing driver')
cli.f(argName:'function', longOpt:'function', args:1, required:false, type:GString, 'Column function')
cli.m(longOpt:'matches', 'Show row matches, default is count only')
cli.q(longOpt:'query', 'Show SQL query')
cli.s(longOpt:'substring', 'Substring search')

def opt = cli.parse(args)
if (!opt) return
if (opt.h) cli.usage()
url = opt.U
userid = opt.u
password = opt.p
driver = opt.d
fn = opt.f
def value = opt.v
def grab = null
if (opt.j) {
	def vals = opt.j.split(":")
	grabParams = (0..2).collectEntries { index -> [["group","module","version"][index], vals[index]] }
	grabParams["classLoader"] = this.class.classLoader.rootLoader
	groovy.grape.Grape.grab(grabParams)
}


def sql = Sql.newInstance([url:url, user:userid, password:password, driver:driver].findAll { it.value })
def dmd = sql.connection.metaData
def tables = collectRs dmd.getTables(null, null, null, ["TABLE"] as String[]), { it.TABLE_NAME }

def tableColumns = tables.collect { table ->
	def cols = collectRs dmd.getColumns(null, null, table, null), {
		["longtext","text", "varchar","nvarchar"].contains(it.TYPE_NAME.toLowerCase()) ? it.COLUMN_NAME : null 
	}
	[name:table, columns:cols]
}

tableColumns.each { table ->
	val = opt.s ? "%${value}%" : value
	print "${table.name} [${table.columns.size()}] "
	if (table.columns) {
		def pstmt = createStmt(table, !opt.m, table.columns, dmd.databaseProductName.toLowerCase())
		def rsClojure = null
		def rowClojure = null
		if (opt.m) {
			println()
			rowClojure = { row -> println "   $row" }
		} else {
			rowClojure = { row -> def res = row.toRowResult().findAll { ent -> ent.value  } ; if (res) print res; }
		}
		sql.eachRow pstmt, [ val as String ] , rowClojure
		if (!opt.m)
			println()
		if (opt.q) {
			println createStmt(table, false, table.columns, dmd.databaseProductName.toLowerCase())
		}
	} else {
		println()
	}
}