Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Generic Brute Force SQL Search - Groovy Style
#!/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()
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment