SQL Transform Examples

This page provides code examples of transforms that query different types of databases. The page includes examples for querying MySQL, PostgreSQL and MSSQL using an ODBC connector. The transforms are written in Python and PHP using the standard TDS libraries.

Python (TRX) Examples ┬╗    PHP Examples┬╗

 

Python Examples

The Python code examples included on this page use the TRX transform framework and run on an Ubuntu server, for more information on setting up the TRX framework see the documentation available on our documentation page here.
 

MySQL Transform Code - Python

 

Library requirements [Back to Top]
To access a MySQL database from our Python code we first need to install the MySQLdb Python library on our server which can be done by running the following command:

sudo apt-get update
sudo apt-get install python-mysqldb

 

MySQL Transform [Back to Top]
The MySQL transform example below will simply query a sample database containing customer information for a made-up company. The database includes columns for the customer's company name and the location of the customer. The transforms will take the customer's company name in as the input entity, lookup the location for that customer in the MySQL database and then return the location of the customer's company as a location entity in Maltego.


#import required libraries
import MySQLdb
from Maltego import * 

def trx_mysqlExamplTransform(m):
	TRX = MaltegoTransform()
	
	#Read the value of the input entity.
	customerName = m.Value

	#Read database password from file.
	try:
		f = open('dbConfig.txt', 'r')
	except:
		TRX.addUIMessage('Could not find dbConfig file.', UIM_FATAL)
		return TRX.returnOutput()
	
	#Create variables to store database connection details.
	server = "LocalHost"
	username = "root"
	password = f.read()
	database = "classicmodels"	
	
	#Connect to database using the details above.
	try:
		conn = MySQLdb.connect(server,username,password,database)
	except:
		TRX.addUIMessage('Could not access database, please check that the connection details are correct.', UIM_FATAL)
		return TRX.returnOutput()
	
	#Define the cursor for the database connection
	c = conn.cursor()
	
	#Run the execute method using a placeholder for the input and enter its value as the second parameter of the method.
	c.execute("SELECT city, country FROM customers WHERE customerName = %s", customerName)

	rows = c.fetchall()
	
	#Create a new entity for each row that is returned from our SQL query.
	for i,eachRow in enumerate(rows):	
		if eachRow[0] and eachRow[1]:
			TRX.addEntity('maltego.Location',eachRow[0]+', '+eachRow[1])
			
		#Read the slider value and break the loop when the number of entities exceeds the slider value
		if i>=m.Slider:
			break
	
	return TRX.returnOutput()
Running this simple transform on a few customers from our sample database will result with the following graph output:
   
 

PostgreSQL Transform Code - Python

This next Python transform will access data from a PostgreSQL database.  

Library requirements [Back to Top]
To access a PostgreSQL database from Python we will first need to install the library python-psycopg2 which can be done as follows:

sudo apt-get update
sudo apt-get install python-psycopg2

 

PostgreSQL Transform [Back to Top]
This transform example will query another sample database containing information about the domain paterva.com. The database includes columns for the DNS names and the IP addresses related to the domain. The transform example below will query the sample database for DNS names that resolve to the IP address specified as the input entity. This transform also shows how transform settings can be used to specify additional pieces of information when a transform is run. In this case a transform setting is used to specify a pattern that must be matched in the DNS names before it can be returned from the transform.


#import the PostgreSQL python library
import psycopg2

def trx_postgresExampleTransform(m):

	TRX = MaltegoTransform()
	
	#Read the value of the input entity.
	IP = m.Value
	
	#Read transform setting for pattern that needs to be match by returned DNS names.
	DnsPattern = m.getTransformSetting('DnsPattern')
	
	#Read database password from file.
	try:
		f = open('dbConfig.txt', 'r')
	except:
		TRX.addUIMessage('Could not find dbConfig file.', UIM_FATAL)
		return TRX.returnOutput()

	#Create variables to store database connection details.
	server = "LocalHost"
	username = "postgres"
	password = f.read()
	database = "postgres"
	
	#Connect to database using the details from the dbConfig file.
	try:
		conn = psycopg2.connect(database, user, host, password)
	except:
		TRX.addUIMessage('Could not access database, please ensure that your database details are correct.', UIM_FATAL)
		return TRX.returnOutput()
		
	#Define the cursor for the database connection
	c = conn.cursor()
	
	#Run the execute method using a placeholders for the inputs and enter their values in a list in the second parameter of the execute method.
	c.execute("SELECT DNSNAME FROM iptodns WHERE IP = %s AND WHERE DNSNAME = %s;", [IP, '*'+DnsPattern+'*'])

	rows = c.fetchall()
	
	#Create a new entity for each row that is returned.
	for i,eachRow in enumerate(rows):	
		if eachRow[0]:
			TRX.addEntity('maltego.DNSName',eachRow[0])
		#Read the slider value and break the loop when the number of entities exceeds the slider value
		if i>=m.Slider:
			break
	
	return TRX.returnOutput()

Running this transform on the IP address "104.200.18.205" which belongs to Paterva and specifying the pattern to match "paterva" in the transform setting results in the graph on the right below:
   

 

MSSQL [using ODBC] Transform Code - Python

ODBC (Open Database Connectivity) is a standard programming language middleware API for accessing DBMS. In this transform example we will be connecting to a MSSQL database using the FreeTDS ODBC Driver. With the correct ODBC drivers installed on your server this transform could be modified to work with a range of database types by changing the connection string to use the appropriate ODBC driver.  

ODBC and FreeTDS setup [Back to Top]

On our server we first need to install all the pre-requisites for using ODBC with MSSQL and then configure them correctly. First install unixODBC and FreeTDS with the following command:

sudo apt-get update
sudo apt-get install unixodbc unixodbc-dev freetds-bin tdsodbc freetds-dev

Microsoft SQL Server uses Tabular Data Stream (TDS) to transfer data between a database server and a client. Freetds is an open source TDS implementation. Next we need to configure a FreeTDS file to include server details of the SQL server you want to connect to. In the case of this example our transform will be connecting to a MSSQL server hosted on Microsoft Azure. :

/etc/freetds/freetds.conf
[global]
tds version = 7.1

[SERVERNAME]
host = HOSTNAME.database.windows.net
port = 1433
Next we need to configure ODBC to include the details of the FreeTDS driver:

/etc/odbcinst.ini
[FreeTDS]
Description = FreeTDS Driver
Driver = /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so
And then configure the ODBC data sources names (DSN) by adding your SQL server details to the file as shown below:

/etc/odbc.ini
[DATA_SOURCE_NAME]
Driver = FreeTDS
Servername = SERVERNAME # This server name must be the same as the one configured in freetds.conf
Port = 1433
Database = DBNAME
 

Library requirements [Back to Top]

The last thing we need to install is the Python library for working with ODBC connectors, this can be done with the command below:

pip install pyodbc

After following the steps above you will be able to access a MSSQL database using the ODBC connection string from within your Python code with pyodbc.
 

MSSQL [using ODBC] Transform [Back to Top]
The transform in the example below will connect to a sample MSSQL database hosted on Microsoft Azure using the ODBC driver that has been configured above. The database being queried has information from the Bitcoin Blockchain and the transform will take a Bitcoin Address in as the input entity and return addresses that have received Bitcoins from the input address:

import pyodbc
def trx_odbcExampleTransform(m):

	TRX = MaltegoTransform()
	
	#Read the input address
	BTC_Addresses = m.Value
	
	#You choose the ODBC driver using the DRIVER keyword.  The other settings are then separated by semicolons.
	try:
		conn = pyodbc.connect('DRIVER=FreeTDS;SERVER=mdviumqix9.database.windows.net;
		PORT=1433;DATABASE=AdventureWorks;UID=testDB@mdviumqix9;PWD=******;TDS_Version=8.0;')
	except:
		TRX.addUIMessage('Could not access database, please ensure that your database details are correct.', UIM_FATAL)
		return TRX.returnOutput()
		
	c = conn.cursor()
	
	#ODBC supports parameters using a question mark as a place holder in the SQL. This is safer than putting the values into the string because 
	#the parameters are passed to the database separately, protecting against SQL injection attacks.
	c.execute("SELECT SentAddresses FROM BTCTransactions WHERE Address = ?", BTC_Addresses)

	rows = c.fetchall()
	
	#Create a new entity for each row that is returned.
	for i,eachRow in enumerate(rows):	
		if eachRow[0]:
			TRX.addEntity('PR.BtcAddress',eachRow[0])
		if i>=m.Slider:
			break
	
	return TRX.returnOutput()
Running this transform on the Bitcoin address "16Fg2yjwrbtC6fZp61EV9mNVKmwCzGasw5" with the transform slider set to 12 results in the graph below:
   

 

PHP Examples


The next three transform examples are written in PHP and use the Basic PHP TDS transform Library which can be downloaded on the transform libraries page here.
 

MySQL Transform Code - PHP

This transform example performs the same task as the Python MySQL transform written above. The transform simply queries a sample customer database for the customer's location and returns it as a location entity in the Maltego client. The transform will use PHP's built in PDO_MYSQL driver that implements the PHP Data Objects (PDO) interface to enable access from PHP to MySQL databases.
 

MySQL Transform [Back to Top]

<?php
//include our MaltegoTDS class(es)
include_once("Maltego.php");
 
//set return content-type to be XML
header ("content-type: text/xml");
 
$maltegoInput = new MaltegoTransformInput();
$maltegoTransform = new MaltegoTransformResponse();

//Define MySQL data connection details.
$servername = "localhost";
$username = "root";
$password = "******";
$dbname = "classicmodels";

if ($maltegoInput->getEntity())
{
	try {
	//get the details into local variables
	$customerName = $maltegoInput->value;
	
	//Connect to the MySQLdb using database your connection details
	$conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
	$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); 
	
	//Prepare and execute the SQL query to get the country that corresponds to the customer name.
	$stmt = $conn->prepare('SELECT country FROM customers WHERE customerName = :customerName'); 
	$stmt->execute(array('customerName' => $customerName));
	
	$result = $stmt->fetchAll(); 
	
	if ( count($result) ) { 
	
		//Create a new entity for each row that is returned.
		foreach($result as $row) {
			
			//Break the loop if added entities exceeds slider value.
			if($i >= $slider){
				break;
				}
			$maltegoTransform->addEntity("maltego.Location",$row['country']);
		}   
		$conn = null;
		$maltegoTransform->returnOutput();
		
	} else {
		$conn = null;
		$maltegoTransform->addException("No results returned.");
		$maltegoTransform->throwExceptions();
	}
	}
	catch(PDOException $e) {
		$maltegoTransform->addException("Error: " . $e->getMessage());
		$maltegoTransform->throwExceptions();
	}
}
else
{
    $maltegoTransform->addException("No input entity found");
    $maltegoTransform->throwExceptions();
} 

Running this transform on a few company names from our sample customer database results with the following graph:
   
 

PostgreSQL Transform Code - PHP

 

Library requirements [Back to Top]
To run this transform on a Ubuntu server you will first need to install the PostgreSQL PHP library which can be done with the command below:

sudo apt-get update
sudo apt-get install php-pgsql

 

PostgreSQL Transform [Back to Top]
This transform example performs a similar task as the Python PostgreSQL transform written above. This transform example queries a database containing information about the domain paterva.com. The database includes columns for the DNS names and the IP addresses that they resolve to. The transform example below will query the sample database for DNS names that resolve to the IP address specified in the input entity.

<?php
//include our MaltegoTDS class(es)
include_once("Maltego.php");
 
//set return content-type to be XML
header ("content-type: text/xml");
 
$maltegoInput = new MaltegoTransformInput();
$maltegoTransform = new MaltegoTransformResponse();

//get the value of the transform slider
$slider = $maltegoInput->slider;

//Define MySQL data connection details.
$servername = "localhost";
$port = "5432";
$username = "postgres";
$password = "*******";
$dbname = "postgres";

if ($maltegoInput->getEntity())
{
	try {
	//get the details into local variables
	$IP = $maltegoInput->value;

	//Connect to the pgsql using database your connection details
	$conn = new PDO("pgsql:host=$servername;port=$port;dbname=$dbname;user=$username;password=$password");
	
	//Prepare and execute the SQL query for DNSNAMES relating to your input IP address.
	$stmt = $conn->prepare('SELECT DNSNAME FROM iptodns WHERE IP = :IP'); 
	$stmt->execute(array('IP' => $IP));
	
	$result = $stmt->fetchAll(); 

	if (count($result) ) { 
	
		//Create a new entity for each row that is returned.
		foreach($result as $row) {
			$maltegoTransform->addEntity("maltego.DNSName",$row[0]);
		}
		$maltegoTransform->returnOutput();
		
	} else {
		
		$maltegoTransform->addException("No results returned.");
		$maltegoTransform->throwExceptions();
	}
	}
	catch(PDOException $e) {
		$maltegoTransform->addException("Error: " . $e->getMessage());
		$maltegoTransform->throwExceptions();
	}
}
else
{
    $maltegoTransform->addException("No input entity found");
    $maltegoTransform->throwExceptions();
} 
$conn = null;

Running this transform on the IP address belonging to Paterva "104.200.18.205" with the transform slider set to 12 results in the graph below:
   
 

MSSQL [using ODBC] Transform Code - PHP

This transform will demonstrate how a MSSQL database can be queried using an ODBC connection string from PHP. A ODBC driver for MSSQL needs to be installed and configured on the server before running the transform. The instructions in the ODBC Python example above can be followed to install and configure unixODBC and FreeTDS that are needed to run the transform code below.
 

MSSQL Transform [Back to Top]
The transform example below will use PHP's standard ODBC library and connect to a database containing information from the Bitcoin Blockchain using the ODBC connection string below:

'DRIVER=FreeTDS;SERVER=mdviumqix9.database.windows.net;PORT=1433;DATABASE=AdventureWorks;UID=testDB@mdviumqix9;PWD=********;TDS_Version=8.0;

The transform in this example will take a Bitcoin address in as the input entity and return Bitcoin transaction where that address was the sender as the output.

<?php
	
//include our MaltegoTDS class(es)
include_once("Maltego.php");
 
//set return content-type to be XML
header ("content-type: text/xml");
 
$maltegoInput = new MaltegoTransformInput();
$maltegoTransform = new MaltegoTransformResponse();

//get the value of the transform slider
$slider = $maltegoInput->slider;

//Define ODBC connection string to connect to MSSQL database
$dsn = 'DRIVER=FreeTDS;SERVER=mdviumqix9.database.windows.net;PORT=1433;DATABASE=AdventureWorks;UID=testDB@mdviumqix9;PWD=*******;TDS_Version=8.0'
$user = 'testDB'
$password = '********'

if ($maltegoInput->getEntity())
{
	//Get the input entity into local variables
	$BTC_Address = $maltegoInput->value;

	//Connect to the MySQLdb using database your connection string defined above
	$conn = odbc_connect($dsn, $user, $password);
	
	//if connection cant be made to the database return an exception to the Maltego client.
	if(!$conn){
		$maltegoTransform->addException("Error : Unable to open database\n");
		$maltegoTransform->throwExceptions();
	} 

	//Prepare and execute the SQL query to get DNS names related to your input IP address.
	$stmt  = odbc_prepare($conn, 'SELECT transactions FROM BTCTransactions WHERE BTC_Address = ?');
	$sql_result = odbc_execute($stmt, array($BTC_Address));

	//Create a new entity for each row that is returned.
	$i = 0;
	while ($row = odbc_fetch_array($sql_result)) {
		//Break the loop if added entities exceeds slider value.
		if($i >= $slider){
			break;
		}
		$maltegoTransform->addEntity("PR.BTCTransaction",$row[0]);
		$i++;
	}
	//Return results to the Maltego client.
	$maltegoTransform->returnOutput();
}
else
{
    $maltegoTransform->addException("No input entity found");
    $maltegoTransform->throwExceptions();
} 
//close the database connection  
odbc_close($conn);
	
	

Running this transform on the Bitcoin address "16Fg2yjwrbtC6fZp61EV9mNVKmwCzGasw5" with the transform slider set to 12 results in the graph below: