def list_chores(self, uid, available, contracted, finished):
"""Available chores are the chores not yet contracted."""
qstr = 'SELECT chore_status.id, chore_id, uid, reward, contracted, finished, title, description FROM chore_status JOIN chore_descriptions ON chore_id=chore_descriptions.id WHERE uid="%s"' % (str(uid))
if available:
qstr = qstr + ' AND contracted = "-1"'
if contracted:
qstr = qstr + ' AND contracted != "-1" AND finished == "-1"'
if finished:
qstr = qstr + ' AND finished != "-1"'
if available:
qstr = qstr + ' ORDER BY title ASC'
if contracted:
qstr = qstr + ' ORDER BY contracted ASC'
if finished:
qstr = qstr + ' ORDER BY finished DESC'
query = self.chores_db.runQuery(qstr)
block_d = BlockingDeferred(query)
ret = []
try:
qr = block_d.blockOn()
for f in qr :
ret.append([ int(f[0]), int(f[1]), unicode(f[2]), int(f[3]), int(f[4]), int(f[5]), unicode(f[6]), unicode(f[7]) ])
return ret
except:
print "Something goes wrong Listing Chore"
return ret
def remove_chore(self, list_id):
query = self.chores_db.runQuery('DELETE FROM chore_status WHERE id=%s' % int(list_id))
block_d = BlockingDeferred(query)
try:
qr = block_d.blockOn()
return True
except:
print "Something goes wrong Removing Chore"
return False
def remove_custom_filter(self, list_id):
query = self.custom_filters_db.runQuery("delete from customfilters where id=%s" % int(list_id))
block_d = BlockingDeferred(query)
try:
qr = block_d.blockOn()
return True
except:
print "Something goes wrong Removing Custom Filters"
return False
def add_chore(self, chore_id, uid):
sql_query = 'INSERT INTO chore_status ("uid", "chore_id", "contracted", "finished") VALUES ("%s", "%s", "-1", "-1")' % (uid, chore_id)
query = self.chores_db.runQuery(sql_query)
block_d = BlockingDeferred(query)
try:
qr = block_d.blockOn()
return True
except:
print "Something goes wrong Adding Chore"
return False
def remove_chore(self, list_id):
query = self.chores_db.runQuery(
'DELETE FROM chore_status WHERE id=%s' % int(list_id))
block_d = BlockingDeferred(query)
try:
qr = block_d.blockOn()
return True
except:
print "Something goes wrong Removing Chore"
return False
def remove_custom_filter(self, list_id):
query = self.custom_filters_db.runQuery(
'delete from customfilters where id=%s' % int(list_id))
block_d = BlockingDeferred(query)
try:
qr = block_d.blockOn()
return True
except:
print "Something goes wrong Removing Custom Filters"
return False
def add_chore_description(self, title, description, reward):
sql_query = 'INSERT INTO chore_descriptions ("title", "description", "reward") VALUES ("%s", "%s", "%s")' % (title, description, reward)
query = self.chores_db.runQuery(sql_query)
block_d = BlockingDeferred(query)
try:
qr = block_d.blockOn()
return True
except:
print "Something goes wrong Adding Chore Description"
return False
def add_chore_description(self, title, description, reward):
sql_query = 'INSERT INTO chore_descriptions ("title", "description", "reward") VALUES ("%s", "%s", "%s")' % (
title, description, reward)
query = self.chores_db.runQuery(sql_query)
block_d = BlockingDeferred(query)
try:
qr = block_d.blockOn()
return True
except:
print "Something goes wrong Adding Chore Description"
return False
def update_chore_description(self, list_id, title, description, reward):
sql_query = 'UPDATE chore_descriptions SET title="%s", description="%s", reward="%s" WHERE id=%s' % (
title, description, reward, int(list_id))
query = self.chores_db.runQuery(sql_query)
block_d = BlockingDeferred(query)
try:
qr = block_d.blockOn()
return True
except:
print "Something goes wrong Updating Chore Description"
return False
def finish_chore(self, list_id, finished):
sql_query = 'UPDATE chore_status SET finished="%s" WHERE id="%s"' % (
finished, int(list_id))
query = self.chores_db.runQuery(sql_query)
block_d = BlockingDeferred(query)
try:
qr = block_d.blockOn()
return True
except:
print "Something goes wrong Updating Chore"
return False
def add_chore(self, chore_id, uid):
sql_query = 'INSERT INTO chore_status ("uid", "chore_id", "contracted", "finished") VALUES ("%s", "%s", "-1", "-1")' % (
uid, chore_id)
query = self.chores_db.runQuery(sql_query)
block_d = BlockingDeferred(query)
try:
qr = block_d.blockOn()
return True
except:
print "Something goes wrong Adding Chore"
return False
def get_contracted_chores_count(self, uid):
qstr = 'SELECT COUNT(*) FROM chore_status JOIN chore_descriptions ON chore_id=chore_descriptions.id WHERE uid="%s" AND contracted != "-1" AND finished == "-1"' % (str(uid))
query = self.chores_db.runQuery(qstr)
block_d = BlockingDeferred(query)
try:
qr = block_d.blockOn()
return qr[0][0]
except:
print "Something goes wrong getting Contracted Chores count"
return -1
def finish_chore(self, list_id, finished):
sql_query = 'UPDATE chore_status SET finished="%s" WHERE id="%s"' % (
finished,
int(list_id))
query = self.chores_db.runQuery(sql_query)
block_d = BlockingDeferred(query)
try:
qr = block_d.blockOn()
return True
except:
print "Something goes wrong Updating Chore"
return False
def add_custom_filter(self, uid, is_black, name, description, regex):
sql_query = 'insert into customfilters ("uid", "is_black", "name", "description", "regexp") values ("%s", %s, "%s", "%s", "%s")' % (
str(uid), int(is_black), name, description, regex)
print sql_query
query = self.custom_filters_db.runQuery(sql_query)
block_d = BlockingDeferred(query)
try:
qr = block_d.blockOn()
return True
except:
print "Something goes wrong Adding Custom Filters"
return False
def get_contracted_chores_count(self, uid):
qstr = 'SELECT COUNT(*) FROM chore_status JOIN chore_descriptions ON chore_id=chore_descriptions.id WHERE uid="%s" AND contracted != "-1" AND finished == "-1"' % (
str(uid))
query = self.chores_db.runQuery(qstr)
block_d = BlockingDeferred(query)
try:
qr = block_d.blockOn()
return qr[0][0]
except:
print "Something goes wrong getting Contracted Chores count"
return -1
def update_custom_filter(self, list_id, name, description, regex):
sql_query = 'update customfilters set name="%s", description="%s", regexp="%s" where id=%s' % (
name, description, regex, int(list_id))
print sql_query
query = self.custom_filters_db.runQuery(sql_query)
block_d = BlockingDeferred(query)
try:
qr = block_d.blockOn()
return True
except:
print "Something goes wrong Updating Custom Filter"
return False
def update_chore_description(self, list_id, title, description, reward):
sql_query = 'UPDATE chore_descriptions SET title="%s", description="%s", reward="%s" WHERE id=%s' % (title,
description,
reward,
int(list_id))
query = self.chores_db.runQuery(sql_query)
block_d = BlockingDeferred(query)
try:
qr = block_d.blockOn()
return True
except:
print "Something goes wrong Updating Chore Description"
return False
def add_custom_filter(self, uid, is_black, name, description, regex):
sql_query = (
'insert into customfilters ("uid", "is_black", "name", "description", "regexp") values ("%s", %s, "%s", "%s", "%s")'
% (str(uid), int(is_black), name, description, regex)
)
print sql_query
query = self.custom_filters_db.runQuery(sql_query)
block_d = BlockingDeferred(query)
try:
qr = block_d.blockOn()
return True
except:
print "Something goes wrong Adding Custom Filters"
return False
def list_custom_filters(self, uid):
query = self.custom_filters_db.runQuery("select * from customfilters where uid = '%s'" % str(uid))
block_d = BlockingDeferred(query)
ret = []
try:
qr = block_d.blockOn()
for f in qr:
ret.append([int(f[0]), unicode(f[3]), unicode(f[4]), unicode(f[5]), bool(f[2])])
return ret
except:
print "Something goes wrong Listing Custom Filters"
return ret
def update_custom_filter(self, list_id, name, description, regex):
sql_query = 'update customfilters set name="%s", description="%s", regexp="%s" where id=%s' % (
name,
description,
regex,
int(list_id),
)
print sql_query
query = self.custom_filters_db.runQuery(sql_query)
block_d = BlockingDeferred(query)
try:
qr = block_d.blockOn()
return True
except:
print "Something goes wrong Updating Custom Filter"
return False
def contract_chore(self, list_id, uid, contracted):
sql_query = 'UPDATE chore_status SET contracted="%s" WHERE id="%s"' % (
contracted,
int(list_id))
query = self.chores_db.runQuery(sql_query)
block_d = BlockingDeferred(query)
try:
qr = block_d.blockOn()
except:
print "Something goes wrong Contracting Chore"
return False
print "Chore contracted"
self.quarterback.add_available_time(uid, 0, self.get_activated_chore_reward(int(list_id)))
return True
def contract_chore(self, list_id, uid, contracted):
sql_query = 'UPDATE chore_status SET contracted="%s" WHERE id="%s"' % (
contracted, int(list_id))
query = self.chores_db.runQuery(sql_query)
block_d = BlockingDeferred(query)
try:
qr = block_d.blockOn()
except:
print "Something goes wrong Contracting Chore"
return False
print "Chore contracted"
self.quarterback.add_available_time(
uid, 0, self.get_activated_chore_reward(int(list_id)))
return True
def get_activated_chore_reward(self, list_id):
sql_query = 'SELECT reward FROM chore_status JOIN chore_descriptions on chore_descriptions.id=chore_status.chore_id WHERE chore_status.id="%s"' % (
int(list_id))
query = self.chores_db.runQuery(sql_query)
block_d = BlockingDeferred(query)
try:
qr = block_d.blockOn()
for f in qr :
return int(f[0])
print "Something goes wrong finding our reward for the chore (broken database?)"
return -1
except:
print "Something goes wrong finding our reward for the chore"
return -1
def get_activated_chore_reward(self, list_id):
sql_query = 'SELECT reward FROM chore_status JOIN chore_descriptions on chore_descriptions.id=chore_status.chore_id WHERE chore_status.id="%s"' % (
int(list_id))
query = self.chores_db.runQuery(sql_query)
block_d = BlockingDeferred(query)
try:
qr = block_d.blockOn()
for f in qr:
return int(f[0])
print "Something goes wrong finding our reward for the chore (broken database?)"
return -1
except:
print "Something goes wrong finding our reward for the chore"
return -1
def list_chore_descriptions(self, desc_id):
qstr = "SELECT * FROM chore_descriptions"
if desc_id != -1:
qstr = qstr + ' WHERE id = "%s"' % (desc_id)
qstr = qstr + ' ORDER BY title ASC'
query = self.chores_db.runQuery(qstr)
block_d = BlockingDeferred(query)
ret = []
try:
qr = block_d.blockOn()
for f in qr :
ret.append([ int(f[0]), unicode(f[1]), unicode(f[2]), int(f[3]) ])
return ret
except:
print "Something goes wrong Listing Chore Description"
return ret
def _refresh_db_categories_cache(self, pkg_id):
try:
if self.db_pools.has_key(pkg_id) and pkg_id not in self.db_cat_cache.keys():
print "REFRESHING CATEGORIES (%s)" % pkg_id
sql = "SELECT id,name FROM category"
query = self.db_pools[pkg_id].runQuery(sql)
block_d = BlockingDeferred(query)
qr = block_d.blockOn()
self.db_cat_cache[pkg_id] = {}
for id, name in qr:
self.db_cat_cache[pkg_id][int(id)] = name
print "REFRESHED CATEGORIES (%s)" % pkg_id
except:
print "Something goes wrong updating categories"
return False
return True
def _refresh_db_categories_cache(self, pkg_id):
try:
if self.db_pools.has_key(
pkg_id) and pkg_id not in self.db_cat_cache.keys():
print "REFRESHING CATEGORIES (%s)" % pkg_id
sql = "SELECT id,name FROM category"
query = self.db_pools[pkg_id].runQuery(sql)
block_d = BlockingDeferred(query)
qr = block_d.blockOn()
self.db_cat_cache[pkg_id] = {}
for id, name in qr:
self.db_cat_cache[pkg_id][int(id)] = name
print "REFRESHED CATEGORIES (%s)" % pkg_id
except:
print "Something goes wrong updating categories"
return False
return True
def list_chores(self, uid, available, contracted, finished):
"""Available chores are the chores not yet contracted."""
qstr = 'SELECT chore_status.id, chore_id, uid, reward, contracted, finished, title, description FROM chore_status JOIN chore_descriptions ON chore_id=chore_descriptions.id WHERE uid="%s"' % (
str(uid))
if available:
qstr = qstr + ' AND contracted = "-1"'
if contracted:
qstr = qstr + ' AND contracted != "-1" AND finished == "-1"'
if finished:
qstr = qstr + ' AND finished != "-1"'
if available:
qstr = qstr + ' ORDER BY title ASC'
if contracted:
qstr = qstr + ' ORDER BY contracted ASC'
if finished:
qstr = qstr + ' ORDER BY finished DESC'
query = self.chores_db.runQuery(qstr)
block_d = BlockingDeferred(query)
ret = []
try:
qr = block_d.blockOn()
for f in qr:
ret.append([
int(f[0]),
int(f[1]),
unicode(f[2]),
int(f[3]),
int(f[4]),
int(f[5]),
unicode(f[6]),
unicode(f[7])
])
return ret
except:
print "Something goes wrong Listing Chore"
return ret
def list_custom_filters(self, uid):
query = self.custom_filters_db.runQuery(
"select * from customfilters where uid = '%s'" % str(uid))
block_d = BlockingDeferred(query)
ret = []
try:
qr = block_d.blockOn()
for f in qr:
ret.append([
int(f[0]),
unicode(f[3]),
unicode(f[4]),
unicode(f[5]),
bool(f[2])
])
return ret
except:
print "Something goes wrong Listing Custom Filters"
return ret
def list_chore_descriptions(self, desc_id):
qstr = "SELECT * FROM chore_descriptions"
if desc_id != -1:
qstr = qstr + ' WHERE id = "%s"' % (desc_id)
qstr = qstr + ' ORDER BY title ASC'
query = self.chores_db.runQuery(qstr)
block_d = BlockingDeferred(query)
ret = []
try:
qr = block_d.blockOn()
for f in qr:
ret.append(
[int(f[0]),
unicode(f[1]),
unicode(f[2]),
int(f[3])])
return ret
except:
print "Something goes wrong Listing Chore Description"
return ret
def check_domain(self, uid, domain):
print "Check Domain"
idomain = ""
domain_list = domain.split(".")
domain_list.reverse()
for x in domain_list:
idomain = idomain + x + "."
idomain = idomain[:-1]
print "Idomain : %s" % idomain
blacklisted_categories = []
custom_black = False
# Search in customfilters
sub_query = "("
sub_query += ' gregexp( "(.+\.|)" || regexp || ".*" , "%s") ' % (domain)
sub_query += ' or gregexp( "(.+\.|)" || regexp || ".*" , "%s") ' % ("http://" + domain)
sub_query += ")"
sql_query = 'select distinct is_black from customfilters where uid="%s" and %s ' % (uid, sub_query)
query = self.custom_filters_db.runQuery(sql_query)
block_d = BlockingDeferred(query)
try:
qr = block_d.blockOn()
if len(qr) > 0:
for x in qr:
if x[0] == 0:
print "Custom WhiteListed"
return [False, False, []]
if x[0] == 1:
custom_black = True
except:
print "Something goes wrong checking Custom Filters"
return [[False, False], []]
if custom_black == True:
print "Custom BlackListed"
return [[True, False], []]
# Search in blacklists
x = self.__split_url(domain)
if x != (None, None, None, None, None):
b_domain = x[1].split(".")[0]
b_etld = x[1][len(b_domain) + 1 :]
b_subdomain = x[2]
if b_subdomain == None:
b_subdomain = ""
b_path = ""
for db in self.pkg_filters_conf.keys():
self._refresh_db_categories_cache(db)
for db in self.pkg_filters_conf.keys():
if self.pkg_filters_conf[db]["users_info"].has_key(uid):
if len(self.pkg_filters_conf[db]["users_info"][uid]) > 0:
sql = 'SELECT id FROM domain WHERE name="%s"' % b_domain
query = self.db_pools[db].runQuery(sql)
block_d = BlockingDeferred(query)
qr = block_d.blockOn()
if len(qr) == 0:
continue
sql = ""
sql += "SELECT categories_list FROM blacklist WHERE "
sql += 'etld_id = (SELECT id FROM etld WHERE name ="%s") AND ' % b_etld
sql += 'domain_id = (SELECT id FROM domain WHERE name ="%s") AND ' % b_domain
if b_subdomain != "":
sql += "( "
sql += 'subdomain_id = (SELECT id FROM subdomain WHERE name ="") OR '
sql += 'subdomain_id = (SELECT id FROM subdomain WHERE name ="%s") ' % b_subdomain
sql += ") AND "
else:
sql += 'subdomain_id = (SELECT id FROM subdomain WHERE name ="") AND '
sql += 'path_id = (SELECT id FROM path WHERE name = "" ) '
query = self.db_pools[db].runQuery(sql)
block_d = BlockingDeferred(query)
qr = block_d.blockOn()
if len(qr) != 0:
for cats in qr:
exec ("cats_list = [%s]" % cats)
for c in cats_list:
if self.db_cat_cache[db][c] in self.pkg_filters_conf[db]["users_info"][uid]:
if self.db_cat_cache[db][c] not in blacklisted_categories:
blacklisted_categories.append(self.db_cat_cache[db][c])
if "may_url_blocked" in blacklisted_categories:
continue
sql = ""
sql += "SELECT COUNT(id) FROM blacklist WHERE "
sql += 'etld_id = (SELECT id FROM etld WHERE name ="%s") AND ' % b_etld
sql += 'domain_id = (SELECT id FROM domain WHERE name ="%s") AND ' % b_domain
if b_subdomain != "":
sql += "( "
sql += 'subdomain_id = (SELECT id FROM subdomain WHERE name ="") OR '
sql += 'subdomain_id = (SELECT id FROM subdomain WHERE name ="%s") ' % b_subdomain
sql += ")"
else:
sql += 'subdomain_id = (SELECT id FROM subdomain WHERE name ="")'
query = self.db_pools[db].runQuery(sql)
block_d = BlockingDeferred(query)
qr = block_d.blockOn()
if (b_subdomain == "" and int(qr[0][0]) > 1) or (b_subdomain != "" and int(qr[0][0]) > 2):
blacklisted_categories.append("may_url_blocked")
if len(blacklisted_categories) > 0:
if "may_url_blocked" in blacklisted_categories:
blacklisted_categories.pop(blacklisted_categories.index("may_url_blocked"))
if len(blacklisted_categories) > 0:
return [[True, True], blacklisted_categories]
else:
return [[False, True], blacklisted_categories]
else:
return [[True, False], blacklisted_categories]
return [[False, False], []]
def check_url(self, uid, host, port, request, rest, pre_check):
if pre_check[0] == True:
print "Uri Validation stopped because domain is blocked, %s" % (host + request.uri)
return False, request, rest, host, port
# Search in customfilters
sub_query = "("
sub_query += ' gregexp( "(.+\.|)" || regexp || ".*" , "%s") ' % (host + request.uri)
sub_query += ' or gregexp( "(.+\.|)" || regexp || ".*" , "%s") ' % ("http://" + host + request.uri)
sub_query += ")"
sql_query = 'select distinct is_black from customfilters where uid="%s" and %s ' % (uid, sub_query)
query = self.custom_filters_db.runQuery(sql_query)
block_d = BlockingDeferred(query)
try:
qr = block_d.blockOn()
if len(qr) > 0:
for x in qr:
if x[0] == 0:
print "Uri Custom filter Whitelist %s" % (host + request.uri)
return True, request, rest, host, port
if x[0] == 1:
print "Uri Custom filter Blacklist %s" % (host + request.uri)
return False, request, rest, host, port
except:
print "Something goes wrong checking Custom Filters (check_url)"
return True, request, rest, host, port
if pre_check[1] == False:
print "Uri validation verified in pre-check %s" % (host + request.uri)
return True, request, rest, host, port
uri = host + request.uri
is_ok = True
blacklisted_categories = []
x = self.__split_url(domain)
if x != (None, None, None, None, None):
b_domain = x[1].split(".")[0]
b_etld = x[1][len(b_domain) + 1 :]
b_subdomain = x[2]
if b_subdomain == None:
b_subdomain = ""
b_path = ""
if x[3] != None:
b_path = b_path + x[3]
if x[4] != None:
b_path = b_path + x[4]
for db in self.pkg_filters_conf.keys():
self._refresh_db_categories_cache(db)
for db in self.pkg_filters_conf.keys():
if self.pkg_filters_conf[db]["users_info"].has_key(uid):
if len(self.pkg_filters_conf[db]["users_info"][uid]) > 0:
sql = 'SELECT id FROM domain WHERE name="%s"' % b_domain
query = self.db_pools[db].runQuery(sql)
block_d = BlockingDeferred(query)
qr = block_d.blockOn()
if len(qr) == 0:
continue
sql = ""
sql += "SELECT categories_list FROM blacklist WHERE "
sql += 'etld_id = (SELECT id FROM etld WHERE name ="%s") AND ' % b_etld
sql += 'domain_id = (SELECT id FROM domain WHERE name ="%s") AND ' % b_domain
if b_subdomain != "":
sql += "( "
sql += 'subdomain_id = (SELECT id FROM subdomain WHERE name ="") OR '
sql += 'subdomain_id = (SELECT id FROM subdomain WHERE name ="%s") ' % b_subdomain
sql += ") AND "
else:
sql += 'subdomain_id = (SELECT id FROM subdomain WHERE name ="") AND '
sql += "("
sql += 'path_id = (SELECT id FROM path WHERE name = "%s" ) OR ' % b_path
sql += 'path_id = (SELECT id FROM path WHERE "%s" GLOB name || "/*") ' % b_path
sql += ")"
query = self.db_pools[db].runQuery(sql)
block_d = BlockingDeferred(query)
qr = block_d.blockOn()
if len(qr) != 0:
for cats in qr:
exec ("cats_list = [%s]" % cats)
for c in cats_list:
if self.db_cat_cache[db][c] in self.pkg_filters_conf[db]["users_info"][uid]:
if self.db_cat_cache[db][c] not in blacklisted_categories:
blacklisted_categories.append(self.db_cat_cache[db][c])
if len(blacklisted_categories) > 0:
print "Uri validation stopped because is blacklisted %s [%s]" % (host + request.uri, blacklisted_categories)
return False, request, rest, host, port
print "Uri validation passed by default %s" % (host + request.uri)
return True, request, rest, host, port
def check_url(self, uid, host, port, request, rest, pre_check):
if pre_check[0] == True:
print 'Uri Validation stopped because domain is blocked, %s' % (
host + request.uri)
return False, request, rest, host, port
#Search in customfilters
sub_query = '('
sub_query += ' gregexp( "(.+\.|)" || regexp || ".*" , "%s") ' % (
host + request.uri)
sub_query += ' or gregexp( "(.+\.|)" || regexp || ".*" , "%s") ' % (
'http://' + host + request.uri)
sub_query += ')'
sql_query = 'select distinct is_black from customfilters where uid="%s" and %s ' % (
uid, sub_query)
query = self.custom_filters_db.runQuery(sql_query)
block_d = BlockingDeferred(query)
try:
qr = block_d.blockOn()
if len(qr) > 0:
for x in qr:
if x[0] == 0:
print 'Uri Custom filter Whitelist %s' % (host +
request.uri)
return True, request, rest, host, port
if x[0] == 1:
print 'Uri Custom filter Blacklist %s' % (host +
request.uri)
return False, request, rest, host, port
except:
print "Something goes wrong checking Custom Filters (check_url)"
return True, request, rest, host, port
if pre_check[1] == False:
print 'Uri validation verified in pre-check %s' % (host +
request.uri)
return True, request, rest, host, port
uri = host + request.uri
is_ok = True
blacklisted_categories = []
x = self.__split_url(domain)
if x != (None, None, None, None, None):
b_domain = x[1].split(".")[0]
b_etld = x[1][len(b_domain) + 1:]
b_subdomain = x[2]
if b_subdomain == None:
b_subdomain = ''
b_path = ''
if x[3] != None:
b_path = b_path + x[3]
if x[4] != None:
b_path = b_path + x[4]
for db in self.pkg_filters_conf.keys():
self._refresh_db_categories_cache(db)
for db in self.pkg_filters_conf.keys():
if self.pkg_filters_conf[db]["users_info"].has_key(uid):
if len(self.pkg_filters_conf[db]["users_info"][uid]) > 0:
sql = 'SELECT id FROM domain WHERE name="%s"' % b_domain
query = self.db_pools[db].runQuery(sql)
block_d = BlockingDeferred(query)
qr = block_d.blockOn()
if len(qr) == 0:
continue
sql = ''
sql += 'SELECT categories_list FROM blacklist WHERE '
sql += 'etld_id = (SELECT id FROM etld WHERE name ="%s") AND ' % b_etld
sql += 'domain_id = (SELECT id FROM domain WHERE name ="%s") AND ' % b_domain
if b_subdomain != '':
sql += '( '
sql += 'subdomain_id = (SELECT id FROM subdomain WHERE name ="") OR '
sql += 'subdomain_id = (SELECT id FROM subdomain WHERE name ="%s") ' % b_subdomain
sql += ') AND '
else:
sql += 'subdomain_id = (SELECT id FROM subdomain WHERE name ="") AND '
sql += '('
sql += 'path_id = (SELECT id FROM path WHERE name = "%s" ) OR ' % b_path
sql += 'path_id = (SELECT id FROM path WHERE "%s" GLOB name || "/*") ' % b_path
sql += ')'
query = self.db_pools[db].runQuery(sql)
block_d = BlockingDeferred(query)
qr = block_d.blockOn()
if len(qr) != 0:
for cats in qr:
exec("cats_list = [%s]" % cats)
for c in cats_list:
if self.db_cat_cache[db][
c] in self.pkg_filters_conf[db][
"users_info"][uid]:
if self.db_cat_cache[db][
c] not in blacklisted_categories:
blacklisted_categories.append(
self.db_cat_cache[db][c])
if len(blacklisted_categories) > 0:
print 'Uri validation stopped because is blacklisted %s [%s]' % (
host + request.uri, blacklisted_categories)
return False, request, rest, host, port
print 'Uri validation passed by default %s' % (host + request.uri)
return True, request, rest, host, port
def check_domain(self, uid, domain):
print "Check Domain"
idomain = ''
domain_list = domain.split(".")
domain_list.reverse()
for x in domain_list:
idomain = idomain + x + "."
idomain = idomain[:-1]
print "Idomain : %s" % idomain
blacklisted_categories = []
custom_black = False
#Search in customfilters
sub_query = '('
sub_query += ' gregexp( "(.+\.|)" || regexp || ".*" , "%s") ' % (
domain)
sub_query += ' or gregexp( "(.+\.|)" || regexp || ".*" , "%s") ' % (
'http://' + domain)
sub_query += ')'
sql_query = 'select distinct is_black from customfilters where uid="%s" and %s ' % (
uid, sub_query)
query = self.custom_filters_db.runQuery(sql_query)
block_d = BlockingDeferred(query)
try:
qr = block_d.blockOn()
if len(qr) > 0:
for x in qr:
if x[0] == 0:
print "Custom WhiteListed"
return [False, False, []]
if x[0] == 1:
custom_black = True
except:
print "Something goes wrong checking Custom Filters"
return [[False, False], []]
if custom_black == True:
print "Custom BlackListed"
return [[True, False], []]
#Search in blacklists
x = self.__split_url(domain)
if x != (None, None, None, None, None):
b_domain = x[1].split(".")[0]
b_etld = x[1][len(b_domain) + 1:]
b_subdomain = x[2]
if b_subdomain == None:
b_subdomain = ''
b_path = ''
for db in self.pkg_filters_conf.keys():
self._refresh_db_categories_cache(db)
for db in self.pkg_filters_conf.keys():
if self.pkg_filters_conf[db]["users_info"].has_key(uid):
if len(self.pkg_filters_conf[db]["users_info"][uid]) > 0:
sql = 'SELECT id FROM domain WHERE name="%s"' % b_domain
query = self.db_pools[db].runQuery(sql)
block_d = BlockingDeferred(query)
qr = block_d.blockOn()
if len(qr) == 0:
continue
sql = ''
sql += 'SELECT categories_list FROM blacklist WHERE '
sql += 'etld_id = (SELECT id FROM etld WHERE name ="%s") AND ' % b_etld
sql += 'domain_id = (SELECT id FROM domain WHERE name ="%s") AND ' % b_domain
if b_subdomain != '':
sql += '( '
sql += 'subdomain_id = (SELECT id FROM subdomain WHERE name ="") OR '
sql += 'subdomain_id = (SELECT id FROM subdomain WHERE name ="%s") ' % b_subdomain
sql += ') AND '
else:
sql += 'subdomain_id = (SELECT id FROM subdomain WHERE name ="") AND '
sql += 'path_id = (SELECT id FROM path WHERE name = "" ) '
query = self.db_pools[db].runQuery(sql)
block_d = BlockingDeferred(query)
qr = block_d.blockOn()
if len(qr) != 0:
for cats in qr:
exec("cats_list = [%s]" % cats)
for c in cats_list:
if self.db_cat_cache[db][
c] in self.pkg_filters_conf[db][
"users_info"][uid]:
if self.db_cat_cache[db][
c] not in blacklisted_categories:
blacklisted_categories.append(
self.db_cat_cache[db][c])
if "may_url_blocked" in blacklisted_categories:
continue
sql = ''
sql += 'SELECT COUNT(id) FROM blacklist WHERE '
sql += 'etld_id = (SELECT id FROM etld WHERE name ="%s") AND ' % b_etld
sql += 'domain_id = (SELECT id FROM domain WHERE name ="%s") AND ' % b_domain
if b_subdomain != '':
sql += '( '
sql += 'subdomain_id = (SELECT id FROM subdomain WHERE name ="") OR '
sql += 'subdomain_id = (SELECT id FROM subdomain WHERE name ="%s") ' % b_subdomain
sql += ')'
else:
sql += 'subdomain_id = (SELECT id FROM subdomain WHERE name ="")'
query = self.db_pools[db].runQuery(sql)
block_d = BlockingDeferred(query)
qr = block_d.blockOn()
if (b_subdomain == '' and int(qr[0][0]) > 1) or (
b_subdomain != '' and int(qr[0][0]) > 2):
blacklisted_categories.append("may_url_blocked")
if len(blacklisted_categories) > 0:
if "may_url_blocked" in blacklisted_categories:
blacklisted_categories.pop(
blacklisted_categories.index("may_url_blocked"))
if len(blacklisted_categories) > 0:
return [[True, True], blacklisted_categories]
else:
return [[False, True], blacklisted_categories]
else:
return [[True, False], blacklisted_categories]
return [[False, False], []]
