def test_good_file(self, request_mock):
with full_rig_with_s3() as (app, engine):
upload_id = self.do_upload(app, request_mock)
# okay, here's what we really want to test.
# call the merge endpoint
response = app.post(
'/api/upload/merge_file?uploadId={}'.format(upload_id))
response_data = json.loads(response.get_data().decode('utf-8'))
assert response_data['status'] == 'success'
# make sure that there is a new merged file on s3
expected_s3_path = 's3://test-bucket/boone/hmis_service_stays/merged'
with open_sesame(expected_s3_path, 'rb') as expected_s3_file:
reader = csv.reader(expected_s3_file)
assert len([row for row in reader]) == ROWS_IN_GOOD_HMIS_FILE
# and make sure that the merge log has a record of this
assert db_session.query(MergeLog).filter(
MergeLog.upload_id == '123-456').one
# make sure that the master table has been bootstrapped
master_table = generate_master_table_name('boone',
'hmis_service_stays')
total_rows = db_session.query(
'count(*) from {}'.format(master_table)).one()
assert total_rows == (ROWS_IN_GOOD_HMIS_FILE - 1, )
# make sure that we filled in some matched ids
total_rows = db_session.query(
'count(matched_id is not null) from {}'.format(
master_table)).one()
assert total_rows == (ROWS_IN_GOOD_HMIS_FILE - 1, )
def detail_article(article_id):
blog = Blog.query.get(article_id)
db_session.query(Blog).filter(Blog.id==article_id).\
update({Blog.count_scan: Blog.count_scan+1})
db_session.commit()
category = get_blog_category()
return render_template('articledetail.html',
article=blog,
categorys=category)
def test_file_storage(self, request_mock):
with TemporaryDirectory() as temp_dir:
root_dir = os.getcwd()
s3_less_config = {
'raw_uploads_path':
os.path.join(
temp_dir,
'{jurisdiction}-{event_type}-uploaded-{date}-{upload_id}'),
'merged_uploads_path':
os.path.join(temp_dir, '{jurisdiction}-{event_type}-merged')
}
with full_rig_without_s3() as (app, engine):
with patch.dict('webapp.utils.app_config', s3_less_config):
upload_id = self.do_upload(app, request_mock)
# okay, here's what we really want to test.
# call the merge endpoint
response = app.post(
'/api/upload/merge_file?uploadId={}'.format(upload_id))
response_data = json.loads(
response.get_data().decode('utf-8'))
assert response_data['status'] == 'success'
# make sure that there is a new merged file on the FS
expected_path = os.path.join(temp_dir,
'boone-jail_bookings-merged')
with open(expected_path, 'rb') as expected_file:
reader = csv.reader(expected_file)
assert len([row for row in reader]) == 11
# and make sure that the merge log has a record of this
assert db_session.query(MergeLog).filter(
MergeLog.upload_id == '123-456').one
def update_release(release_codename, **kwargs):
release = (db_session.query(Release).filter(
Release.codename == release_codename).one_or_none())
if not release:
return make_response(
jsonify({"message": f"Release {release_codename} doesn't exist"}),
404,
)
release_data = request.json
release.version = release_data["version"]
release.name = release_data["name"]
release.development = release_data["development"]
release.lts = release_data["lts"]
release.release_date = release_data["release_date"]
release.esm_expires = release_data["esm_expires"]
release.support_expires = release_data["support_expires"]
db_session.add(release)
try:
db_session.commit()
except IntegrityError as error:
return make_response(
jsonify({
"message": "Failed updating release",
"error": error.orig.args[0],
}),
422,
)
return make_response(jsonify({"message": "Release updated"}), 200)
def can_access_file(upload_id):
upload = db_session.query(Upload).get(upload_id)
if not upload:
raise ValueError('upload_id: %s not present in metadata database',
upload_id)
logger.info('Found jurisdiction %s and event type %s for upload id %s',
upload.jurisdiction_slug, upload.event_type_slug, upload_id)
return can_upload_file(upload.jurisdiction_slug, upload.event_type_slug)
def _update_statuses(cve, data, packages):
statuses = cve.packages
statuses_to_check = (db_session.query(Status).filter(
Status.cve_id == cve.id).all())
statuses_to_delete = {
f"{v.package_name}||{v.release_codename}": v
for v in statuses_to_check
}
for package_data in data.get("packages", []):
name = package_data["name"]
if packages.get(name) is None:
package = Package(name=name)
package.source = package_data["source"]
package.ubuntu = package_data["ubuntu"]
package.debian = package_data["debian"]
packages[name] = package
db_session.add(package)
for status_data in package_data["statuses"]:
update_status = False
codename = status_data["release_codename"]
status = statuses[name].get(codename)
if status is None:
update_status = True
status = Status(cve_id=cve.id,
package_name=name,
release_codename=codename)
elif f"{name}||{codename}" in statuses_to_delete:
del statuses_to_delete[f"{name}||{codename}"]
if status.status != status_data["status"]:
update_status = True
status.status = status_data["status"]
if status.description != status_data["description"]:
update_status = True
status.description = status_data["description"]
if status.component != status_data.get("component"):
update_status = True
status.component = status_data.get("component")
if status.pocket != status_data.get("pocket"):
update_status = True
status.pocket = status_data.get("pocket")
if update_status:
statuses[name][codename] = status
db_session.add(status)
for key in statuses_to_delete:
db_session.delete(statuses_to_delete[key])
def login():
if request.method == 'POST':
username = request.json.get('username')
password = request.json.get('password')
agent = session.query(Agents).filter_by(username=username).first()
if agent.verify_password(password):
flask_login.login_user(agent)
return jsonify({'login': '******'})
else:
return abort(401)
def _deserialize(self, value, attr, data, **kwargs):
exists = False
if "package" in inspector.get_table_names():
exists = (db_session.query(
Package.name).filter_by(name=value).one_or_none() is not None)
if not exists:
raise self.make_error("unrecognised_package_name", input=value)
return super()._deserialize(value, attr, data, **kwargs)
def _deserialize(self, value, attr, data, **kwargs):
exists = False
if "notice" in inspector.get_table_names():
exists = (db_session.query(Notice).filter(
Notice.id == value).one_or_none() is not None)
if exists:
raise self.make_error("notice_id_exists", input=value)
return super()._deserialize(value, attr, data, **kwargs)
def _deserialize(self, value, attr, data, **kwargs):
exists = False
if "release" in inspector.get_table_names():
exists = (db_session.query(Release).filter(
Release.codename == value).one_or_none() is not None)
if exists:
raise self.make_error("release_codename_exists", input=value)
return super()._deserialize(value, attr, data, **kwargs)
def get_release(release_codename):
release = (db_session.query(Release).filter(
Release.codename == release_codename).one_or_none())
if not release:
return make_response(
jsonify({"message": f"Release {release_codename} doesn't exist"}),
404,
)
return release
def merge_file():
upload_id = request.args.get('uploadId', None)
if not upload_id:
return jsonify(status='invalid', reason='uploadId not present')
has_access = False
try:
has_access = can_access_file(upload_id)
if has_access:
upload_log = db_session.query(Upload).get(upload_id)
logger.info('Retrieved upload log, merging raw table to master')
raw_table_name = 'raw_{}'.format(upload_id)
logger.info('Merging raw table to master')
merge_id = upsert_raw_table_to_master(raw_table_name,
upload_log.jurisdiction_slug,
upload_log.event_type_slug,
upload_id, db_session)
logger.info('Syncing merged file to s3')
bootstrap_master_tables(upload_log.jurisdiction_slug, db_session)
sync_merged_file_to_storage(upload_log.jurisdiction_slug,
upload_log.event_type_slug,
db_session.get_bind())
merge_log = db_session.query(MergeLog).get(merge_id)
try:
logger.info('Merge succeeded. Now querying matcher')
notify_matcher(upload_log.jurisdiction_slug, upload_id)
except Exception as e:
logger.error('Error matching: ', e)
db_session.rollback()
return make_response(jsonify(status='error'), 500)
db_session.commit()
return jsonify(status='success',
new_unique_rows=merge_log.new_unique_rows,
total_unique_rows=merge_log.total_unique_rows)
else:
return jsonify(status='not authorized')
except ValueError as e:
logger.error('Error merging: ', e)
db_session.rollback()
return make_response(jsonify(status='error'), 500)
def _get_releases(versions):
releases_query = db_session.query(Release).order_by(Release.release_date)
if versions and not any(a in ["", "current"] for a in versions):
releases_query = releases_query.filter(Release.codename.in_(versions))
else:
releases_query = releases_query.filter(
or_(
Release.support_expires > datetime.now(),
Release.esm_expires > datetime.now(),
)).filter(Release.codename != "upstream")
return releases_query.all()
def delete_cve(cve_id):
cve = db_session.query(CVE).filter(CVE.id == cve_id.upper()).one_or_none()
if not cve:
return make_response(
jsonify({"message": f"CVE {cve_id} doesn't exist"}),
404,
)
db_session.delete(cve)
db_session.commit()
return make_response(
jsonify({"message": f"CVE with id '{cve_id}' was deleted"}), 200)
def get_cve(cve_id, **kwargs):
show_hidden = kwargs.get("show_hidden", False)
cve = db_session.query(CVE).filter(CVE.id == cve_id.upper()).one_or_none()
if not cve:
return make_response(
jsonify({"message": f"CVE with id '{cve_id}' does not exist"}),
404,
)
cve.notices = cve.get_filtered_notices(show_hidden)
return cve
def register():
if request.method == 'POST':
username = request.json.get('username')
password = request.json.get('password')
hostname = request.json.get('hostname')
windows_user = request.json.get('windows_user')
if session.query(Agents).filter_by(username=username).first():
return jsonify({'error': 'username already taken'}), 400
new_agent = Agents(username=username,
hostname=hostname,
windows_user=windows_user)
new_agent.hash_password(password)
session.add(new_agent)
session.commit()
return jsonify({'username': username})
def _update_notice_object(notice, data):
"""
Set fields on a Notice model object
"""
notice.title = data["title"]
notice.summary = data["summary"]
notice.details = data["description"]
notice.release_packages = data["release_packages"]
notice.published = data["published"]
notice.references = data["references"]
notice.instructions = data["instructions"]
notice.is_hidden = data.get("is_hidden", False)
notice.releases = [
db_session.query(Release).get(codename)
for codename in data["release_packages"].keys()
]
notice.cves.clear()
for cve_id in set(data["cves"]):
notice.cves.append(db_session.query(CVE).get(cve_id) or CVE(id=cve_id))
return notice
def delete_notice(notice_id):
notice = (db_session.query(Notice).filter(
Notice.id == notice_id).one_or_none())
if not notice:
return make_response(
jsonify({"message": f"Notice {notice_id} doesn't exist"}),
404,
)
db_session.delete(notice)
db_session.commit()
return make_response(jsonify({"message": f"Notice {notice_id} deleted"}),
200)
def get_notice(notice_id, **kwargs):
notice_query = db_session.query(Notice)
if not kwargs.get("show_hidden", False):
notice_query = notice_query.filter(Notice.is_hidden == "False")
notice = notice_query.filter(Notice.id == notice_id.upper()).one_or_none()
if not notice:
return make_response(
jsonify(
{"message": f"Notice with id '{notice_id}' does not exist"}),
404,
)
return notice
def update_notice(notice_id, **kwargs):
notice = (db_session.query(Notice).filter(
Notice.id == notice_id).one_or_none())
if not notice:
return make_response(
jsonify({"message": f"Notice '{notice_id}' doesn't exist"}),
404,
)
notice = _update_notice_object(notice, request.json)
db_session.add(notice)
db_session.commit()
return make_response(jsonify({"message": "Notice updated"}), 200)
def test_good_file(self):
with full_rig_with_s3() as (app, engine):
response = app.post(
'/api/upload/upload_file?jurisdiction=boone&eventType=hmis_service_stays',
content_type='multipart/form-data',
data={
'file_field': (open(GOOD_HMIS_FILE, 'rb'), 'myfile.csv')
})
response_data = json.loads(response.get_data().decode('utf-8'))
assert response_data['status'] == 'validating'
assert 'jobKey' in response_data
assert 'message' in response_data
job_key = response_data['jobKey']
# get validation result and upload to s3
response = app.get('/api/upload/validated_result/' + job_key)
response_data = json.loads(response.get_data().decode('utf-8'))
assert 'validation' in response_data
assert response_data['validation']['status'] == 'valid'
assert response_data['validation']['jobKey'] == job_key
assert 'upload_result' in response_data
assert 'rowCount' in response_data['upload_result']
assert 'exampleRows' in response_data['upload_result']
assert 'uploadId' in response_data['upload_result']
assert 'fieldOrder' in response_data['upload_result']
current_date = date.today().isoformat()
expected_s3_path = 's3://test-bucket/boone/hmis_service_stays/uploaded/{}/{}'.format(
current_date, response_data['upload_result']['uploadId'])
with open_sesame(expected_s3_path) as expected_s3_file:
with open_sesame(GOOD_HMIS_FILE) as source_file:
# we do not expect the file on s3 to be the same as the
# uploaded source file - missing columns should be filled in
s3_df = pd.read_csv(expected_s3_file)
source_df = pd.read_csv(source_file, sep='|')
assert source_df.equals(s3_df[source_df.columns.tolist()])
assert db_session.query(Upload).filter(
Upload.id == response_data['upload_result']['uploadId']).one
def get_notices(**kwargs):
details = kwargs.get("details")
cve_id = kwargs.get("cve_id")
release = kwargs.get("release")
limit = kwargs.get("limit", 20)
offset = kwargs.get("offset", 0)
order_by = kwargs.get("order")
notices_query = db_session.query(Notice,
func.count("*").over().label("total"))
if not kwargs.get("show_hidden", False):
notices_query = notices_query.filter(Notice.is_hidden == "False")
if cve_id:
notices_query = notices_query.filter(Notice.cves.any(CVE.id == cve_id))
if release:
notices_query = notices_query.join(
Release, Notice.releases).filter(Release.codename == release)
if details:
notices_query = notices_query.filter(
or_(
Notice.id.ilike(f"%{details}%"),
Notice.details.ilike(f"%{details}%"),
Notice.title.ilike(f"%{details}%"),
Notice.cves.any(CVE.id.ilike(f"%{details}%")),
))
sort = asc if order_by == "oldest" else desc
raw_notices = (notices_query.order_by(sort(
Notice.published)).offset(offset).limit(limit).all())
return {
"notices": [raw_notice[0] for raw_notice in raw_notices],
"offset": offset,
"limit": limit,
"total_results": raw_notices[0][1] if raw_notices else 0,
}
def test_good_file(self, request_mock):
with full_rig_with_s3() as (app, engine):
upload_id = self.do_upload(app, request_mock)
# okay, here's what we really want to test.
# call the merge endpoint
response = app.post(
'/api/upload/merge_file?uploadId={}'.format(upload_id))
response_data = json.loads(response.get_data().decode('utf-8'))
assert response_data['status'] == 'success'
# make sure that there is a new merged file on s3
expected_s3_path = 's3://test-bucket/boone/jail_bookings/merged'
with open_sesame(expected_s3_path, 'rb') as expected_s3_file:
reader = csv.reader(expected_s3_file)
assert len([row for row in reader]) == 11
# and make sure that the merge log has a record of this
assert db_session.query(MergeLog).filter(
MergeLog.upload_id == '123-456').one
# and make sure that the raw table is no longer there
assert not table_exists(generate_raw_table_name(upload_id),
db_session.bind)
def delete_release(release_codename):
release = (db_session.query(Release).filter(
Release.codename == release_codename).one_or_none())
if not release:
return make_response(
jsonify({"message": f"Release {release_codename} doesn't exist"}),
404,
)
if len(release.statuses) > 0:
return (
jsonify({
"message": (f"Cannot delete '{release_codename}' release. "
f"Release already in use")
}),
400,
)
db_session.delete(release)
db_session.commit()
return make_response(
jsonify({"message": f"Release {release_codename} deleted"}), 200)
def get_category_child(self):
return db_session.query(Blog_category).filter(
Blog_category.category_parent == self.id).all()
def load_user(id):
return db_session.query(Agents).get(int(id))
def bulk_upsert_cve(*args, **kwargs):
cves_data = request.json
if len(cves_data) > 50:
return make_response(
jsonify({
"message": ("Please only submit up to 50 CVEs at a time. "
f"({len(cves_data)} submitted)")
}),
413,
)
packages = {}
for package in db_session.query(Package).all():
packages[package.name] = package
for data in cves_data:
update_cve = False
cve = db_session.query(CVE).get(data["id"].upper())
if cve is None:
update_cve = True
cve = CVE(id=data["id"])
if cve.status != data.get("status"):
update_cve = True
cve.status = data.get("status")
published_date = (cve.published.strftime("%Y-%B-%d")
if cve.published else None)
data_published_date = (data.get("published").strftime("%Y-%B-%d")
if data.get("published") else None)
if published_date != data_published_date:
update_cve = True
cve.published = data.get("published")
if cve.priority != data.get("priority"):
update_cve = True
cve.priority = data.get("priority")
if cve.cvss3 != data.get("cvss3"):
update_cve = True
cve.cvss3 = data.get("cvss3")
if cve.description != data.get("description"):
update_cve = True
cve.description = data.get("description")
if cve.ubuntu_description != data.get("ubuntu_description"):
update_cve = True
cve.ubuntu_description = data.get("ubuntu_description")
if cve.notes != data.get("notes"):
update_cve = True
cve.notes = data.get("notes")
if cve.references != data.get("references"):
update_cve = True
cve.references = data.get("references")
if cve.bugs != data.get("bugs"):
update_cve = True
cve.bugs = data.get("bugs")
if cve.patches != data.get("patches"):
update_cve = True
cve.patches = data.get("patches")
if cve.tags != data.get("tags"):
update_cve = True
cve.tags = data.get("tags")
if cve.mitigation != data.get("mitigation"):
update_cve = True
cve.mitigation = data.get("mitigation")
if update_cve:
db_session.add(cve)
_update_statuses(cve, data, packages)
created = defaultdict(lambda: 0)
updated = defaultdict(lambda: 0)
deleted = defaultdict(lambda: 0)
for item in db_session.new:
created[type(item).__name__] += 1
for item in db_session.dirty:
updated[type(item).__name__] += 1
for item in db_session.deleted:
deleted[type(item).__name__] += 1
try:
db_session.commit()
except DataError as error:
return make_response(
jsonify({
"message": "Failed bulk upserting session",
"error": error.orig.args[0],
}),
400,
)
return make_response(
jsonify({
"created": created,
"updated": updated,
"deleted": deleted
}),
200,
)
def get_cves(**kwargs):
query = kwargs.get("q", "").strip()
priority = kwargs.get("priority")
package = kwargs.get("package")
limit = kwargs.get("limit", 20)
offset = kwargs.get("offset", 0)
component = kwargs.get("component")
versions = kwargs.get("version")
statuses = kwargs.get("status")
order_by = kwargs.get("order")
show_hidden = kwargs.get("show_hidden", False)
clean_versions = _get_clean_versions(statuses, versions)
clean_statuses = _get_clean_statuses(statuses, versions)
# query cves by filters
cves_query = db_session.query(
CVE,
func.count("*").over().label("total")).filter(CVE.status == "active")
# filter by priority
if priority:
cves_query = cves_query.filter(CVE.priority == priority)
# filter by description or CVE id
if query:
cves_query = cves_query.filter(
or_(
CVE.id.ilike(f"%{query}%"),
CVE.description.ilike(f"%{query}%"),
CVE.ubuntu_description.ilike(f"%{query}%"),
))
# build CVE statuses filter parameters
parameters = []
# filter by package name
if package:
parameters.append(Status.package_name == package)
# filter by component
if component:
parameters.append(Status.component == component)
# filter by status and version
if not _should_filter_by_version_and_status(statuses, versions):
# by default we look for CVEs with active statuses
parameters.append(Status.status.in_(Status.active_statuses))
else:
# make initial filter for cves.statuses by status-version criteria
conditions = []
for key, version in enumerate(clean_versions):
conditions.append(
and_(
Status.release_codename.in_(version),
Status.status.in_(clean_statuses[key]),
))
parameters.append(or_(*[condition for condition in conditions]))
# filter for cve.statuses by status-version including package/component
conditions = []
for key, version in enumerate(clean_versions):
sub_conditions = [
Status.release_codename.in_(version),
Status.status.in_(clean_statuses[key]),
CVE.id == Status.cve_id,
]
if package:
sub_conditions.append(Status.package_name == package)
if component:
sub_conditions.append(Status.component == component)
condition = Package.statuses.any(
and_(*[sub_condition for sub_condition in sub_conditions]))
conditions.append(condition)
parameters.append(
Status.package.has(and_(*[condition for condition in conditions])))
# apply CVE statuses filter parameters
if len(parameters) > 0:
cves_query = cves_query.filter(
CVE.statuses.any(and_(*[p for p in parameters])))
sort = asc if order_by == "oldest" else desc
cves_query = (cves_query.group_by(CVE.id).order_by(
case(
[(CVE.published.is_(None), 1)],
else_=0,
),
sort(CVE.published),
).limit(limit).offset(offset).from_self().join(CVE.statuses).options(
contains_eager(CVE.statuses)))
# get filtered cves
raw_cves = cves_query.all()
cves = []
# filter cve.packages by parameters
for raw_cve in raw_cves:
cve = raw_cve[0]
packages = cve.packages
# filter by package name
if package:
packages = {
package_name: package_statuses
for package_name, package_statuses in packages.items()
if package_name == package
}
# filter by component
if component:
packages = {
package_name: package_statuses
for package_name, package_statuses in packages.items()
if any(status.component == component
for status in package_statuses.values())
}
# filter by status and version
if _should_filter_by_version_and_status(statuses, versions):
packages = {
package_name: package_statuses
for package_name, package_statuses in packages.items() if all(
any(package_status.release_codename in version
and package_status.status in clean_statuses[key]
for package_status in package_statuses.values())
for key, version in enumerate(clean_versions))
}
# refresh cve.statuses after cve.packages filter
for package_name in packages:
statuses = []
for release, status in packages[package_name].items():
statuses.append(status)
cve.statuses = statuses
cve.notices = cve.get_filtered_notices(show_hidden)
cves.append(cve)
return {
"cves": cves,
"offset": offset,
"limit": limit,
"total_results": raw_cves[0][1] if cves else 0,
}
