Image of Hulk

Generic Brute Force SQL-Search – Groovy Style

Assume you are given a rdbms schema you are not familiar with and you are wondering where a piece of information is stored. To me, this happens every now and then. A few days ago, a customer asked me to create a report based on data in his ERP database. Unfortunately it turned out that the schema was hosted in MS SQL Server, fairly big (roughly 100 tables with 30-40 columns on average), and looked meaningless to me (table- and column names). I remembered that ages ago, a colleague mentioned that he had written a tool addressing this kind of developer/admin problem. I never got to see it. Nevertheless, my current requirement made me revisit the idea. I really tried hard finding something ready to download and run – and failed – did not even find something close.

Quick, but not dirty !

To come up with something quickly, I picked Groovy. All of a sudden, an ugly reverse engineering task showed some beautiful aspects. I don’t want to praise the details of Groovy again. It just proved to be AWESOME in this case. Special thanks go to @mrhaki for his inspiring and practical posts. There is a link to the source at the bottom if you are interested. A few final remarks before you blow up your system:


$ ./DBBFSearch.groovy 
error: Missing required options: v, U
usage: DBBFSearch -h -u userid -p password -v value -U url -d driver -j
                  jar -f function -m -q -s
 -d,--driver <driver>       Driver class
 -f,--function <function>   Column function
 -h,--help                  help
 -j,--jar <jar>             Jar file containing driver
 -m,--matches               Show row matches, default is count only
 -p,--password    Password
 -q,--query                 Show SQL query
 -s,--substring             Substring search
 -U,--url <url>             Database URL
 -u,--userid <userid>       Userid
 -v,--value <value>         Value to search for

Example:
DBBFSearch -v findme \
-U 'jdbc:mysql://localhost/db?user=user&password=pass' \
-j mysql:mysql-connector-java:5.1.6 -s -f lower
$

What I personally like most is the fact that you can execute it right away.

Here is a quick demo using the customers ERP my Alfresco schema searching for admin.

$ groovy https://raw.github.com/gist/2170917/a4e2af6cee3022b34fe5ec536d2c2b9ed9891c2a/DBBFSearch.groovy \
-v admin -U 'jdbc:mysql://localhost/alfresco?user=alfresco&password=alfresco' \
-j mysql:mysql-connector-java:5.1.6 \
-f lower | grep '\] \['
alf_authority [1] [authority:1]
alf_child_assoc [2] [qname_localname:5]
alf_node [6] [audit_creator:16, audit_modifier:16]
alf_node_properties [1] [string_value:3]
$

The first line of output says the script looked at one column in the table alf_authority and found one row authority matching admin. The other lines have analogous meanings.

Just copying and pasting this commandline should work if you happen to have Groovy (>= 1.8.3 mostly because of the remote script execution) and the “usual developer alfresco database” – Trust me ! ;)

PS: With mysql I was sometimes executing things like

$ mysqldump --skip-extended-insert --user=alfresco \
--password=alfresco alfresco | \
grep www.alfresco.org >/dev/null && echo "strike"
strike
$

Sure, this approach has limitations. But sometimes it helps you getting at least basic information.

PPS: The Alfresco entity relationship diagram from the older post may be helpful if you really try digging in the Alfresco schema.

References:

Andreas Steffan
Pragmatic ? Scientist and DevOps Mind @ Contentreich. Believes in Open Source, the Open Web and Linux. Freelancing in DevOps-, Cloud-, Kubernetes, JVM- and Contentland and speaks Clojure, Kotlin, Groovy, Go, Python, JavaScript, Java, Alfresco and WordPress. Built infrastructure before it was cool. ❤️ Emacs.

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht.