def query_db ( connection, query ):
'''
Summary line.
Connect to database and attempt to xecute a query from the users input.
Parameters
----------
query : sql query
SQL query to attempt
cursor : method of a connection object:
connection : MySqlConnection class.
Returns
-------
cursor.fetchall()
Description of return value
The method fetches all (or all remaining) rows of a query result
set and returns a list of tuples. If no more rows are available,
it returns an empty list
'''
# creates an arg variable, makes args attributes available within function.
args = argparser.input_args ( )
try:
# Execute a query
cursor = connection.cursor ( )
# calls execute_query function
cursor = execute_query ( query, cursor, connection )
result = cursor.fetchall ( )
return (result)
except:
if args.debug == True:
print ( "Unable to execute the query" )
return (1)
def main ():
# gets user input paramaters
args = argparser.input_args ( )
# prompt user to populate database or skip to query the database.
ask_populate = input ( "Do you want to initialize the database ? (yes/no)\n" )
# depending upon response if yes, then being querying and populating database.
if ask_populate == 'yes':
print ( "Populating the database. This operation may take a while." )
# passes input paramter -f to populate function
populate ( args.filename )
print ( "Population complete" )
# if no then skip popludating databae and allow user to query for ip information.
else:
print ( "Skipping the population phase" )
# pass along SQL query to db and return result.
connection_db = query_db.connect_to_database ( )
# monitor user input
query_loop ( connection_db )
def main ():
# creates an arg variable, makes args attributes available within function.
args = argparser.input_args ( )
# connects to database
connection = connect_to_database ( )
# asks user which table to query
what_db = input ( "What table do you want to query ? (type geo_ip or rdap)\n" )
# describe info about table
describe_table = "DESCRIBE " + what_db + ";"
# try to query table for info if not tell user unable
try:
result = query_db ( connection, describe_table )
print ( result )
except:
print ( "Could not execute the query, check the name of table" )
query = input (
"Type in the MySQL query (be careful to end the command with a ; which is mandatoty for MySQL syntax): \n" )
# pass along the query and connection to database
query_db ( connection, query )
# close the connection
connection.close ( )
def connect_to_database ( host='localhost', user='******', password='******', db='geordap' ):
'''
Summary line.
Establish a connection to the sql database.
Parameters
----------
host : str
SQL query to attempt sql injection.
user : str
Which user to use in sql database.
password : str
Users's password to access sql database.
db : str
Which database to use.
Returns
-------
connection : MySQLCursor() object or System Exit Code 1
Description of return value
This is the connection to the sql database.
If not connection is made then, return a system exit code of 1.
'''
# creates an arg variable, makes args attributes available within function.
args = argparser.input_args ( )
# try to connect to db
try:
connection = pymysql.connect ( host,
user,
password,
db,
charset = 'utf8mb4',
cursorclass = pymysql.cursors.DictCursor )
if args.debug == True:
print ( "Successfuly connected to", db, "database" )
return (connection)
# if not able to connect, let the user now.
except pymysql.err.OperationalError:
print ( "Unable to make a connection to the", db, "database, invalid credentials or server unreachable" )
return (1)
def execute_query ( query, cursor, connection ):
'''
Summary line.
Execute a query from the users input and pass
it along to the database.
Parameters
----------
query : sql query
SQL query to attempt.
cursor : method of a connection object:
connection : MySqlConnection class.
connection to the database
Returns
-------
cursor : mysql cursor or system exit code of 1.
Description of return value
Create a new cursor to execute queries with.
If no commit is made then, return a system exit code of 1.
'''
# creates an arg variable, makes args attributes available within function.
args = argparser.input_args ( )
if args.debug == True:
print ( "Execute query :", query )
# try to execute query
try:
# Execute a query
cursor.execute ( query )
# Commit changes to stable storage.
connection.commit ( )
# return cursor as output from function.
return (cursor)
except:
if args.debug == True:
print ( "Unable to execute the query, check syntax or your connection to the database" )
return (1)
def populate ( file_name="list_of_ips.txt" ):
'''
Summary line.
Populates the mysql database with queries returned from geo and rdap queries.
Extended description of function.
The first step is to fetch the IP addresses inside the file and store them in an array.
The second step is to execute the lookups on each IP of the array. We also need to
connect to the database to insert the values as we perform the geo ip lookups
Parameters
----------
file_name : str
This is the filename of the text file.
Returns
-------
None
Description of return value
'''
# creates an arg variable, makes args attributes available within function.
args = argparser.input_args ( )
# get current working directory
current_dir = os.getcwd ( )
# joins together string absolute path with string filename
file_name = os.path.join ( current_dir, "data", file_name )
# checks if debug is enabled.
if args.debug == True:
print ( "Opening file [", file_name, "]" )
# creates variable ip_text, holds data from input file
ip_text = parsing.file_to_text ( file_name )
# creates a list,list_of_ips, holds ip addresses.
list_of_ips = parsing.parse ( ip_text )
# makes a connection to the sql database
connection_db = query_db.connect_to_database ( )
# tells sql database to use geordap db.
query_db.execute_query ( "USE geordap;", connection_db.cursor ( ), connection_db )
# grabs length of list_of)ips
len_of_list = len ( list_of_ips )
# iterates through each ip address, gathering geo and rdap information for each ip address.
# Also shows the progress of the database being populated.
for i in tqdm ( range ( len_of_list ) ):
ip = list_of_ips [ i ]
# starts geo query for ip address
geo_info = geo_ip_lookup.geo_ip_query ( ip )
# inserts values into database, if geo query returns no results, populate with empty strings.
if geo_info != None:
geo_ip_insert_query = "INSERT INTO geo_ip(ip_address, country_code, country_name, region_code, region_name, city, zip_code, time_zone, latitude, longitude, metro_code) VALUES('" \
+ geo_info [ 'ip' ] + "', '" \
+ geo_info [ 'country_code' ] + "', '" \
+ geo_info [ 'country_name' ] + "', '" \
+ geo_info [ 'region_code' ] + "', '" \
+ geo_info [ 'region_name' ] + "', '" \
+ geo_info [ 'city' ] + "', '" \
+ geo_info [ 'zip_code' ] + "', '" \
+ geo_info [ 'time_zone' ] + "', '" \
+ str ( geo_info [ 'latitude' ] ) + "', '" \
+ str ( geo_info [ 'longitude' ] ) + "', '" \
+ str ( geo_info [ 'metro_code' ] ) + "');"
# inserts values into database from geo ip query.
else:
geo_ip_insert_query = "INSERT INTO geo_ip(ip_address, country_code, country_name, region_code, region_name, city, zip_code, time_zone, latitude, longitude, metro_code) VALUES('" \
+ geo_info [ 'ip' ] + "','','','','','','','','','','' );"
# init. and starts process of querying for geolocation of ip address.
query_db.execute_query ( geo_ip_insert_query, connection_db.cursor ( ), connection_db )
# starts rdap query for ip address
rdap_info = rdap_lookup.rdap_query ( ip )
# extracts selected attributes from query respsone
rdap_extract = rdap_lookup.rdap_extract ( rdap_info )
# inserts values into database, if rdap query returns no results, populate with empty strings.
if rdap_extract != None:
rdap_insert_query = "INSERT INTO rdap(ip_address, start_address, end_address, company_name, company_address) VALUES('" \
+ ip + "', '" \
+ rdap_extract [ 'startAddress' ] + "', '" \
+ rdap_extract [ 'endAddress' ] + "', '" \
+ rdap_extract [ 'company_name' ] + "', '" \
+ rdap_extract [ 'company_address' ].replace ( "\n", " " ) + "');"
else:
# inserts values into database from rdap query.
rdap_insert_query = "INSERT INTO rdap(ip_address, start_address, end_address, company_name, company_address) VALUES('" \
+ ip + "','','','','');"
# init. and starts process of querying for geolocation of ip address.
query_db.execute_query ( rdap_insert_query, connection_db.cursor ( ), connection_db )
# closes database connection
connection_db.close ( )
def rdap_query ( ip_address ):
'''
Summary line.
Gets rdap information about an ip address.
by the user.
Parameters
----------
ip_address : str
Ip address represented as a string.
Returns
-------
query_response : dict
None : None
Description of return value
Attempts to return quuery from https://rdap.arin.net/registry/ip/<ip address>.
If query returns nothing, then returns none.
'''
# creates an arg variable, makes args attributes available within function.
args = argparser.input_args ( )
# go to this url
api_url = "https://rdap.arin.net/registry/ip/{}".format ( ip_address )
# pass these headers
headers = {
'accept': "application/json",
'content-type': "application/json"}
# response from server with url and headers
response = requests.get ( api_url, headers = headers )
# below checks for varying responses from the server, to handle errors.
# More information is displayed if the flag -d is passed.
if response.status_code >= 500:
if args.debug == True:
print ( '[!] [{0}] Server Error'.format ( response.status_code ) )
return None
elif response.status_code >= 429:
if args.debug == True:
print ( '[!] [{0}] Too Many Requests'.format ( response.status_code ) )
print ( "Using Alternative RDAP API Site" )
alt_api_url = "https://www.rdap.net/ip/{}".format ( ip_address )
alt_response = requests.get ( alt_api_url, headers = headers )
rdap_query = json.loads ( response.content.decode ( 'utf-8' ) )
return rdap_query
elif response.status_code == 404:
if args.debug == True:
print ( '[!] [{0}] URL not found: [{1}]'.format ( response.status_code, api_url ) )
return None
elif response.status_code == 401:
if args.debug == True:
print ( '[!] [{0}] Authentication Failed'.format ( response.status_code ) )
return None
elif response.status_code >= 400:
if args.debug == True:
print ( '[!] [{0}] Bad Request'.format ( response.status_code ) )
return None
elif response.status_code >= 300:
if args.debug == True:
print ( '[!] [{0}] Unexpected redirect.'.format ( response.status_code ) )
return None
elif response.status_code == 200:
rdap_query = json.loads ( response.content.decode ( 'utf-8' ) )
return rdap_query
else:
if args.debug == True:
print ( '[?] Unexpected Error: [HTTP {0}]: Content: {1}'.format ( response.status_code, response.content ) )
return None