def create_course(self, name, category_id):
category = site.find_category_by_id(category_id)
name = f'{name}, {category.name}'
course = CourseFactory.create(name, category_id)
course.create_object(course)
self.courses = session.query(CourseBase).all()
return course
def place_queen(N, queens_positions, current_row):
if current_row == N:
solution = str(queens_positions).strip('[]')
solution = solution.replace(' ', '')
if not solution_exists(N):
global first_time
first_time = True
queen_case = QueenCase(n=N, number_of_solutions=0)
session.add(queen_case)
session.commit()
if first_time:
query = session.query(QueenCase).filter_by(n=N)
for x in query:
queen_case_id = x.id
sol_obj = Solution(queen_case_id=queen_case_id, solution=solution)
session.add(sol_obj)
session.commit()
global sol
sol += 1
else:
for column in range(0, N):
if verify(queens_positions, current_row, column):
queens_positions[current_row] = column
place_queen(N, queens_positions, current_row + 1)
def calculate(N):
global sol, first_time
sol = 0
first_time = False
queens_positions = [-1] * N
place_queen(N, queens_positions, 0)
if first_time:
queen_case = session.query(QueenCase).filter_by(n=N).first()
queen_case.number_of_solutions = sol
session.commit()
elif not solution_exists(N):
queen_case = QueenCase(n=N, number_of_solutions=sol)
session.add(queen_case)
session.commit()
return sol
def annotate(result):
if not result.data:
return
if "resolved" in result.data:
return
digest = result.data["chain"][0][1]
key = session.query(SSLKey).filter(SSLKey.key == digest).first()
if key is None:
print "Missing key for digest %s" % digest
return
cert = key.cert
pprint(result.data)
pprint(key)
resolved = []
while cert:
resolved.append((cert.subject, cert.data_hash()))
if cert == cert.issuer:
break
cert = cert.issuer
result.data["resolved"] = resolved
def getcountry(threeletter="PAK"):
print threeletter
baseurl = "http://api.worldbank.org/datafiles/%s_Country_MetaData_en_EXCEL.xls"
value = {'dsID': 'World Bank',
'region': threeletter,
'source': baseurl % threeletter,
'is_number': True}
fh = dl.grab(baseurl % threeletter, [404])
if not fh:
return
messy = messytables.excel.XLSTableSet(fh)
table = xypath.Table.from_messy(list(messy.tables)[0])
indicators = table.filter(is_in(indicator_list))
indname = indicators.shift(x=-1)
if not len(indname) == len(indicator_list):
print "missing indicators", [x.value for x in indname]
code = table.filter(equal_to('Indicator Code'))
years = code.fill(xypath.RIGHT)
junction = indname.junction(years)
for ind_cell, year_cell, value_cell in junction:
vdict = dict(value)
vdict['indID'] = ind_cell.value
vdict['period'] = year_cell.value
vdict['value'] = value_cell.value
indicator = {'indID': vdict['indID']}
nameunits = re.search('(.*)\((.*)\)', vdict['indID'])
if nameunits:
(indicator['name'], indicator['units']) = nameunits.groups()
else:
indicator['name'] = vdict['indID']
indicator['units'] = 'uno'
Indicator(**indicator).save()
v = Value(**vdict)
if not v.is_blank():
v.save()
print len(session.query(Value).filter(Value.dsID == 'World Bank').all())
session.commit()
def add_cert(cert, is_anchor=False):
# print(cert)
print(cert.get_subject(), '%08x' % cert.get_subject().hash(), fingerprint(cert.get_subject().der(), hashlib.md5))
# print(cert.get_subject().get_components())
# print(cert.digest("sha1"))
# print(hashlib.sha1(crypto.dump_certificate(crypto.FILETYPE_ASN1, cert)).hexdigest())
subject = repr(cert.get_subject().get_components())
subject_der = cert.get_subject().der()
data_der = crypto.dump_certificate(crypto.FILETYPE_ASN1, cert)
result = session.query(SSLCert).filter_by(subject_der=subject_der, data_der=data_der).first()
if result:
print "already have %s: %s" % (subject, result)
return result
x = SSLCert()
x.is_anchor = is_anchor
x.subject = subject
x.subject_der = subject_der
x.data_der = data_der
session.add(x)
return x
def update_user(cls):
ob = session.query(Products).get(1)
ob.nombre = "Cambio!!"
session.commit()
def get_product(cls):
ob = session.query(Products).get(1)
ob_all = session.query(Products).all()
first = session.query(Products).first()
print(ob)
#!/usr/bin/python
import requests
import json
import urlparse
def fetch(url, timeout=10):
assert(isinstance(url, basestring))
url = urlparse.urlparse(url)
page = requests.get(url.geturl(), timeout=timeout)
try:
return json.loads(page.content)
except ValueError:
return {}
from orm import session, JSONAPI, Result
import sqlalchemy
if __name__ == "__main__":
for x in session.query(JSONAPI):
x.results.append(Result("jsonapi", fetch(x.name)))
session.commit()
def __init__(self):
self.categories = session.query(CategoryBase).all()
self.courses = session.query(CourseBase).all()
self.user_types = UserFactory.user_types
self.users = session.query(UserBase).all()
def create_course(self, name, category_id):
course = CourseFactory.create(name, category_id)
course.create_object(course)
self.courses = session.query(CourseBase).all()
return course
def user_remove(self, user_id):
UserBase.delete_object(user_id)
self.users = session.query(UserBase).all()
return result
def query(address):
address = IPAddress(address)
print(address)
p = subprocess.Popen(CMD + [str(address)], stdout=subprocess.PIPE, stderr=subprocess.STDOUT)
result = p.communicate()[0]
print(result)
result = result.decode('ISO-8859-1').strip()
if p.returncode != 0:
print(result)
raise Exception("whois failed with %i" % p.returncode)
result = result.split('\n')
return result
from orm import session, Node, Result
if __name__ == "__main__":
for x in session.query(Node).filter((Node.type == "ip4address") | (Node.type == "ip6address")).all():
if not x.is_expired(NAME, age=24 * 60 * 60):
continue
pprint(x)
address = x.name
data = {}
data["raw"] = query(address)
data["parsed"] = parse(data["raw"])
pprint(data)
x.results.append(Result(NAME, data))
session.commit()
from datetime import time
from orm import subscribe_crawl, session
url = ""
# new_data = SubscribeCrawl(
# id=int(time.time()),
# url=url.replace(" ", ""),
# type=1,
# is_closed=False,
# interval=3600,
# next_time=0,
# )
# session.add(new_data)
session.query(subscribe_crawl).filter(subscribe_crawl.id == 1571721122).update(
{subscribe_crawl.rule: {
"need_proxy": True
}})
session.commit()
# -*- coding: utf-8 -*-
"""
Created on Mon Jan 05 22:03:38 2015
@author: Jeffrey
"""
from orm import session, Symbol, Override
from sqlalchemy.sql.expression import insert, update, delete, select
from sqlalchemy import func
from sqlalchemy.sql import and_
syms = session.query(Symbol).filter(Symbol.name.like("oil%")).all()
eu_oil = syms[1]
df = eu_oil.cache()
#s = select([Override.dt_ind,Override.value]).where(Override.symname == 'oil_CONT').execute()
#gb = session.query(Override.dt_ind,func.max(Override.dt_log).label('max_dt_log')).group_by(Override.dt_ind).subquery()
#ors = session.query(Override).join((gb, and_(Override.dt_ind == gb.c.dt_ind, Override.dt_log == gb.c.max_dt_log))).all()
#s = select([])
#res = session.query(Override).filter(Override.symname == 'oil_CONT').all()
#print eu_oil.data()
def find_sshfp(hostname):
result = {"sshfp": []}
try:
for x in query(hostname, "SSHFP"):
result["sshfp"].append((x.algorithm, x.fp_type, x.fingerprint.encode("hex-codec")))
except dns.exception.DNSException:
pass
except socket.error:
pass
return result
from orm import session, HostName, Result
if __name__ == "__main__":
for x in session.query(HostName):
if not x.is_expired(NAME, age=60 * 60):
continue
hostname = x.get_hostname()
if is_ipaddr(hostname):
continue
data = {}
data.update(find_address(hostname))
data.update(find_cname(hostname))
data.update(find_soa(hostname))
data.update(find_sshfp(hostname))
x.results.append(Result(NAME, data))
pprint({hostname: data})
session.commit()
return {"dns-sd": result}
def show(hostname):
data = {}
data.update(find_basic(hostname))
data.update(find_srv(hostname))
data.update(find_dnssd(hostname))
pprint({hostname: data})
# show("dns-sd.org")
# show("google.com")
# show("stratum0.org")
# show("stratum0blalssdf.net")
# show("stratum0.net")
# show("totalueberwachung.de")
# show("sipgate.de")
from orm import session, DomainName, Result
if __name__ == "__main__":
for x in session.query(DomainName):
hostname = x.name
data = {}
data.update(find_basic(hostname))
data.update(find_srv(hostname))
data.update(find_dnssd(hostname))
x.results.append(Result("domainname", data))
session.commit()
pprint({hostname: data})
# -*- coding: utf-8 -*-
"""
Created on Mon Jan 05 22:03:38 2015
@author: Jeffrey
"""
from orm import session, Symbol, Override
from sqlalchemy.sql.expression import insert, update, delete, select
from sqlalchemy import func
from sqlalchemy.sql import and_
syms = session.query(Symbol).filter(Symbol.name.like("oil%")).all()
eu_oil = syms[1]
df = eu_oil.cache()
#s = select([Override.dt_ind,Override.value]).where(Override.symname == 'oil_CONT').execute()
#gb = session.query(Override.dt_ind,func.max(Override.dt_log).label('max_dt_log')).group_by(Override.dt_ind).subquery()
#ors = session.query(Override).join((gb, and_(Override.dt_ind == gb.c.dt_ind, Override.dt_log == gb.c.max_dt_log))).all()
#s = select([])
return {}
except requests.exceptions.Timeout as e:
print e
return {}
except requests.exceptions.ConnectionError:
return {}
try:
return json.loads(page.content)
except ValueError:
pass
if 'text/html' in page.headers['content-type']:
dom = BeautifulSoup(page.text)
meta = dom.find("link", rel="space-api")
if meta:
url = urlparse.urlparse(meta['href'])
if url.scheme == "":
url = urlparse.urlparse("%s://%s%s" % (baseurl.scheme, baseurl.netloc, meta['href']))
return fetch(url.geturl())
# print pretty_json(fetch("https://hickerspace.org"))
# print pretty_json(fetch("https://stratum0.org"))
from orm import session, SpaceAPI, Result
if __name__ == "__main__":
for x in session.query(SpaceAPI):
if not x.is_expired(NAME):
continue
x.results.append(Result(NAME, fetch(x.name)))
session.commit()
#!/usr/bin/env python
# -*- coding: utf-8 -*-
from orm import session, Student, Class
sStudents = session.query(Student).join(Class).filter(Class.level == 3).all()
for student in sStudents:
print(student.name)
def discover(node, data):
print node
c = node.children["services"]
existing = set((x.__class__, x.name) for x in c)
print existing
current = set()
for name, proto, port in data.get("mx", []):
current.add((MailServer, "smtp://%s:%i" % (name, port)))
for name, proto, port in data.get("ns", []):
current.add((NameServer, name))
for name, proto, port in data.get("xmpp-client", []):
current.add((XMPPServer, "xmpp-client://%s:%i" % (name, port)))
for name, proto, port in data.get("xmpp-server", []):
current.add((XMPPServer, "xmpp-server://%s:%i" % (name, port)))
new = current - existing
for x in new:
c.append(x[0](name=x[1]))
from orm import session, DomainName, Result
from orm import MailServer, NameServer, XMPPServer
if __name__ == "__main__":
for node in session.query(DomainName):
result = node.results.filter(Result.method == "domainname").first()
if not result or not result.data:
print "no domainname result for %s" % node
continue
discover(node, result.data)
session.commit()
def course_remove(self, course_id):
CourseBase.delete_object(course_id)
self.courses = session.query(CourseBase).all()
code = table.filter(equal_to('Indicator Code'))
years = code.fill(xypath.RIGHT)
junction = indname.junction(years)
for ind_cell, year_cell, value_cell in junction:
vdict = dict(value)
vdict['indID'] = ind_cell.value
vdict['period'] = year_cell.value
vdict['value'] = value_cell.value
indicator = {'indID': vdict['indID']}
nameunits = re.search('(.*)\((.*)\)', vdict['indID'])
if nameunits:
(indicator['name'], indicator['units']) = nameunits.groups()
else:
indicator['name'] = vdict['indID']
indicator['units'] = 'uno'
Indicator(**indicator).save()
v = Value(**vdict)
if not v.is_blank():
v.save()
print len(session.query(Value).filter(Value.dsID == 'World Bank').all())
session.commit()
for country in getcountrylist():
try:
getcountry(country)
except Exception, e:
print country, e
raise
def create_category(self, name):
category = Category(name)
category.create_object(category)
self.categories = session.query(CategoryBase).all()
return category
def getcountrylist():
for value in session.query(Value).filter(Value.indID == "CG060").all():
yield value.region
def create_user(self, name, user_type):
user = UserFactory.create(name, user_type)
user.create_object(user)
self.users = session.query(UserBase).all()
return user
keys.add("sub/md5/%s" % hashlib.md5(subject_der).hexdigest())
keys.add("sub/sha1/%s" % hashlib.sha1(subject_der).hexdigest())
spki = get_spki(cert.data_der)
digest = hashlib.sha1(spki).digest()
# keys.add("hpkp/fp/sha1/%s" % digest.encode("hex_codec"))
# keys.add("hpkp/pin/sha1/%s" % digest.encode("base64_codec").strip())
digest = hashlib.sha256(spki).digest()
keys.add("hpkp/fp/sha256/%s" % digest.encode("hex_codec"))
keys.add("hpkp/pin/sha256/%s" % digest.encode("base64_codec").strip())
print keys
for key in keys:
if not key in existing:
cert.keys.append(SSLKey(key))
for k, v in existing.items():
if not k in keys:
cert.keys.remove(v)
from orm import engine, session, SSLCert, SSLKey
if __name__ == "__main__":
# engine.echo = False
for cert in session.query(SSLCert):
print cert
rekey(cert)
session.commit()
#!/usr/bin/python
from urlparse import urlparse
def discover(node, data):
print node
c = node.children["dns"]
existing = set((x.__class__, x.name) for x in c)
print existing
current = set()
for name in data.get("cname", []):
current.add((HostName, name))
name = data.get("soa")
if name:
current.add((DomainName, name))
new = current - existing
for x in new:
c.append(x[0](name=x[1]))
from orm import session, HostName, DomainName, Result
if __name__ == "__main__":
for node in session.query(HostName):
result = node.results.filter(Result.method == "hostname").first()
if not result or not result.data:
print "no hostname result for %s" % node
continue
discover(node, result.data)
session.commit()
#!/usr/bin/python
from urlparse import urlparse
def discover(node, data):
print node
c = node.children["spaceapidirectory"]
existing = set(x.name for x in c if isinstance(x, SpaceAPI))
current = set(x for x in data.values())
new = current - existing
for x in new:
c.append(SpaceAPI(name=x))
from orm import session, JSONAPI, SpaceAPI, Result
if __name__ == "__main__":
for node in session.query(JSONAPI):
if not node.conf["discover"] == "spaceapidirectory":
continue
result = node.results.filter(Result.method == "jsonapi").first()
if result is None:
print "no jsonapi result for %s" % node
continue
discover(node, result.data)
session.commit()
crypto.verify(cacert, sig, der_cert, algo)
print "Certificate looks good"
except crypto.Error as e:
print "Sorry. Nope."
# except ValueError as e: # algo "ecdsa-with-SHA384" seems to be unsupported
# print e
from ssl_ca import pin_to_fingerprint, fingerprint_to_pin, fingerprint, load_cert
from orm import engine, session, SSLCert
if __name__ == "__main__":
engine.echo = False
for cert in session.query(SSLCert):
cert_x509 = crypto.load_certificate(crypto.FILETYPE_ASN1, cert.data_der)
subject_name = repr(cert_x509.get_subject().get_components())
subject_der = cert_x509.get_subject().der()
issuer_name = repr(cert_x509.get_issuer().get_components())
issuer_der = cert_x509.get_issuer().der()
if cert.is_anchor and subject_der == issuer_der:
print "Skipping self signed root cert"
cert.issuer_id = cert.id
continue
results = session.query(SSLCert).filter_by(subject_der=issuer_der).all()
if not results:
cert.issuer_id = None
if cert.is_anchor:
continue
print "Issuer not found"
from orm import engine, session, Result, SSLCert, SSLKey
def annotate(result):
if not result.data:
return
if "resolved" in result.data:
return
digest = result.data["chain"][0][1]
key = session.query(SSLKey).filter(SSLKey.key == digest).first()
if key is None:
print "Missing key for digest %s" % digest
return
cert = key.cert
pprint(result.data)
pprint(key)
resolved = []
while cert:
resolved.append((cert.subject, cert.data_hash()))
if cert == cert.issuer:
break
cert = cert.issuer
result.data["resolved"] = resolved
if __name__ == "__main__":
# engine.echo = False
# for result in session.query(Result).filter(Result.method == 'ssl').group_by(Result.node_id).order_by(Result.created.desc()):
for result in session.query(Result).filter(Result.method == 'ssl').order_by(Result.created.desc()):
annotate(result)
session.commit()
def delete_user(cls):
ob = session.query(Products).get(2)
session.delete(ob)
session.commit()
def solution_exists(N):
exist = session.query(QueenCase).filter_by(n=N).count()
if exist == 0:
return False
return True
def getcountrylist():
for value in session.query(Value).filter(Value.indID == "CG060").all():
yield value.region
def find_tlsa(hostname, port):
result = {"tcp": [], "udp": []}
try:
for x in query("_%i._tcp.%s" % (port, hostname), "TLSA"):
result["tcp"].append((x.usage, x.selector, x.mtype, x.cert.encode("hex-codec")))
except dns.exception.DNSException:
pass
try:
for x in query("_%i._udp.%s" % (port, hostname), "TLSA"):
result["udp"].append((x.usage, x.selector, x.mtype, x.cert.encode("hex-codec")))
except dns.exception.DNSException:
pass
return result
from orm import engine, session, Node, Result
if __name__ == "__main__":
engine.echo = False
for x in session.query(Node):
if not x.is_expired(NAME, 60 * 60):
continue
url = urlparse.urlsplit(x.name)
if not url.hostname or not url.port:
continue
if url.scheme in ["http"]:
continue
data = find_tlsa(url.hostname, url.port)
x.results.append(Result(NAME, data))
pprint({x.name: data})
session.commit()
#!/usr/bin/env python
# -*- coding: utf-8 -*-
from orm import session, Student, Class
for student_name, in session.query(Student.name). \
join(Class, Class.address == Student.address).filter(Class.level==3).all():
print student_name
def category_remove(self, category_id):
CategoryBase.delete_object(category_id)
self.categories = session.query(CategoryBase).all()
self.courses = session.query(CourseBase).all()
#!/usr/bin/env python
# -*- coding: utf-8 -*-
from orm import session, Student, Class
for student, class_ in session.query(Student, Class).join(Class).filter(Class.level==3).all():
print student.name, class_.name
def discover(node, data):
print node
c = node.children["spaceapi"]
while c:
c.pop()
c = set()
if "url" in data:
c.add(HTTPService(name=data["url"]))
host = host_from_url(data["url"])
if not host is None:
c.add(HostName(name=host))
if "feeds" in data:
discover_feeds(c, data["feeds"])
if "contact" in data:
discover_contacts(c, data["contact"])
for n in c:
node.children["spaceapi"].append(n)
from orm import session, SpaceAPI, Result
from orm import HTTPService, HostName, Feed, EMailAddress
if __name__ == "__main__":
for node in session.query(SpaceAPI):
result = node.results.filter(Result.method == "spaceapi").first()
if not result or not result.data:
print "no spaceapi result for %s" % node
continue
discover(node, result.data)
session.commit()