My colleague just installed apex-sert, unfortunately it fell over on one of our apps with an invalid number error.
I tracked this down to
select collection_sql from sv_sert_220200.sv_sec_score_collections where collection_name = 'SV_URL_MIS_BC';
specifically the line in collection_sql
AND b.page_id = p.page_id
b.page_id is varchar2 and in our case contained '&VAR_NAME.' and p.page_id is a number.
The following update allowed everything to run, although I appreciate that anything with a variable for the page_id won't match and be checked:
`update sv_sert_220200.sv_sec_score_collections
set collection_sql =
'INSERT INTO sv_sec_collection_data
(
collection_name,
collection_id,
category_key,
application_id,
attribute_id,
page_id,
component_id,
last_updated_by,
last_updated_on,
component_signature,
edit,
link_page,
link_req,
link_cc,
link,
link_desc,
c001,
c002,
c003,
c004,
result,
val,
checksum,
component_name,
column_name
)
SELECT
''#COLLECTION_NAME#'',
COLLECTION_ID#,
(SELECT category_key FROM sv_sec_categories WHERE category_id =
(SELECT category_id FROM sv_sec_attributes WHERE attribute_key = ''SV_URL_MIS_BC''))
category_key,
p.application_id,
(SELECT attribute_id FROM sv_sec_attributes WHERE attribute_key = ''SV_URL_MIS_BC'')
attribute_id,
p.page_id,
b.breadcrumb_entry_id,
b.last_updated_by,
b.last_updated_on,
b.component_signature,
''Edit'' edit,
''104'' link_page,
NULL link_req,
''RP,104'' link_cc,
''P104_ID,P104_MENU_ID:'' || breadcrumb_entry_id || '',''
|| breadcrumb_id link,
''URL Tampering - Inconsistent Breadcrumb Entries'' link_desc,
b.breadcrumb_name,
b.entry_label,
b.bc_auth_scheme,
p.authorization_scheme,
CASE
WHEN b.bc_auth_scheme_id != TO_CHAR(p.authorization_scheme_id)
AND b.bc_auth_scheme_id IS NOT NULL
AND TO_CHAR(p.authorization_scheme_id) IS NOT NULL THEN ''FAIL''
WHEN b.bc_auth_scheme_id IS NOT NULL
AND TO_CHAR(p.authorization_scheme_id) IS NULL THEN ''FAIL''
ELSE ''PASS''
END result,
NULL val,
NULL checksum,
b.breadcrumb_name,
b.entry_label
FROM
(
SELECT
bc.application_id,
bc.breadcrumb_id,
bc.breadcrumb_name,
bce.component_signature,
bce.breadcrumb_entry_id,
bce.last_updated_on,
bce.last_updated_by,
bce.authorization_scheme bc_auth_scheme,
TO_CHAR(bce.authorization_scheme_id) bc_auth_scheme_id,
bce.entry_label,
TO_CHAR(SUBSTR
(
url,
INSTR(url, '':'')+1,
INSTR(SUBSTR(url, INSTR(url, '':'')+1), '':'', 1)-1
)) page_id,
TO_CHAR(bce.authorization_scheme_id) authorization_scheme_id
FROM
apex_application_bc_entries bce,
apex_application_breadcrumbs bc
WHERE
bc.breadcrumb_id = bce.breadcrumb_id
AND bc.application_id = #APPLICATION_ID#
) b,
apex_application_pages p
WHERE
p.application_id = b.application_id
AND b.page_id = to_char(p.page_id)'
where collection_name = 'SV_URL_MIS_BC';`
My colleague just installed apex-sert, unfortunately it fell over on one of our apps with an invalid number error. I tracked this down to
select collection_sql from sv_sert_220200.sv_sec_score_collections where collection_name = 'SV_URL_MIS_BC';
specifically the line in collection_sql
AND b.page_id = p.page_id
b.page_id is varchar2 and in our case contained '&VAR_NAME.' and p.page_id is a number.The following update allowed everything to run, although I appreciate that anything with a variable for the page_id won't match and be checked: `update sv_sert_220200.sv_sec_score_collections set collection_sql = 'INSERT INTO sv_sec_collection_data ( collection_name, collection_id, category_key, application_id, attribute_id, page_id, component_id, last_updated_by, last_updated_on, component_signature, edit, link_page, link_req, link_cc, link, link_desc, c001, c002, c003, c004, result, val, checksum, component_name, column_name ) SELECT ''#COLLECTION_NAME#'',
COLLECTION_ID#,
(SELECT category_key FROM sv_sec_categories WHERE category_id = (SELECT category_id FROM sv_sec_attributes WHERE attribute_key = ''SV_URL_MIS_BC'')) category_key, p.application_id, (SELECT attribute_id FROM sv_sec_attributes WHERE attribute_key = ''SV_URL_MIS_BC'') attribute_id, p.page_id, b.breadcrumb_entry_id, b.last_updated_by, b.last_updated_on, b.component_signature, ''Edit'' edit, ''104'' link_page, NULL link_req, ''RP,104'' link_cc, ''P104_ID,P104_MENU_ID:'' || breadcrumb_entry_id || '','' || breadcrumb_id link, ''URL Tampering - Inconsistent Breadcrumb Entries'' link_desc, b.breadcrumb_name, b.entry_label, b.bc_auth_scheme, p.authorization_scheme, CASE WHEN b.bc_auth_scheme_id != TO_CHAR(p.authorization_scheme_id) AND b.bc_auth_scheme_id IS NOT NULL AND TO_CHAR(p.authorization_scheme_id) IS NOT NULL THEN ''FAIL'' WHEN b.bc_auth_scheme_id IS NOT NULL AND TO_CHAR(p.authorization_scheme_id) IS NULL THEN ''FAIL'' ELSE ''PASS'' END result, NULL val, NULL checksum, b.breadcrumb_name, b.entry_label FROM ( SELECT bc.application_id, bc.breadcrumb_id, bc.breadcrumb_name, bce.component_signature, bce.breadcrumb_entry_id, bce.last_updated_on, bce.last_updated_by, bce.authorization_scheme bc_auth_scheme, TO_CHAR(bce.authorization_scheme_id) bc_auth_scheme_id, bce.entry_label, TO_CHAR(SUBSTR ( url, INSTR(url, '':'')+1, INSTR(SUBSTR(url, INSTR(url, '':'')+1), '':'', 1)-1 )) page_id, TO_CHAR(bce.authorization_scheme_id) authorization_scheme_id FROM apex_application_bc_entries bce, apex_application_breadcrumbs bc WHERE bc.breadcrumb_id = bce.breadcrumb_id AND bc.application_id = #APPLICATION_ID# ) b, apex_application_pages p WHERE p.application_id = b.application_id AND b.page_id = to_char(p.page_id)' where collection_name = 'SV_URL_MIS_BC';`