package body ted_access_log is
procedure update_bots is
cursor c_monperrus is
select jt.*
,lua.created_by
from ted_access_log_user_agents lua
,json_table(lua.list_clob, '$[*]' columns(pattern path '$.pattern')) jt
where lua.created_by = 'monperrus'
and lua.active_yn = 'Y';
begin
for r_monperrus in c_monperrus
loop
begin
insert into ted_access_log_bots bots
(bot_type
,info_source
,ip_from
,ip_to
,user_agent_regex)
values
('Web Crawler'
,r_monperrus.created_by
,null
,null
,r_monperrus.pattern);
dbms_output.put_line('inserted: ' || r_monperrus.pattern);
exception
when dup_val_on_index then
null;
end;
end loop;
end update_bots;
function ip_to_integer(p_ip_address in varchar2) return number deterministic is
l_ip_address varchar2(100) := p_ip_address;
l_result number;
begin
if instr(l_ip_address, ',') > 0 then
l_ip_address := substr(l_ip_address, 1, instr(l_ip_address, ',') - 1);
end if;
l_result := 16777216 * to_number(substr(l_ip_address,1,instr(l_ip_address,'.',1,1)-1))
+ 65536 * to_number(substr(l_ip_address,instr(l_ip_address,'.',1,1)+1,instr(l_ip_address,'.',1,2)-instr(l_ip_address,'.',1,1)-1))
+ 256 * to_number(substr(l_ip_address,instr(l_ip_address,'.',1,2)+1,instr(l_ip_address,'.',1,3)-instr(l_ip_address,'.',1,2)-1))
+ to_number(substr(l_ip_address,instr(l_ip_address,'.',1,3)+1))
;
return l_result;
exception
when others then
return null;
end ip_to_integer;
function bot_pk_id
(
p_ip_address in varchar2
,p_user_agent in varchar2
) return ted_access_log_bots.pk_id%type is
l_result ted_access_log_bots.pk_id%type;
cursor c_bots is
select min(bots.pk_id) as bots_pk_id
from ted_access_log_bots bots
where ip_to_integer(p_ip_address) between ip_to_integer(bots.ip_from) and ip_to_integer(coalesce(bots.ip_to, bots.ip_from))
or regexp_substr(p_user_agent, bots.user_agent_regex) is not null
or (p_user_agent is null and bots.user_agent_regex is null and bots.ip_from is null);
begin
open c_bots;
fetch c_bots
into l_result;
close c_bots;
return l_result;
exception
when others then
raise_application_error(-20000, sqlerrm);
end bot_pk_id;
function hash(p_unhashed in varchar2) return raw is
v_hash_raw raw(256);
v_charset varchar2(100);
begin
if p_unhashed is null then
return null;
end if;
select value
into v_charset
from nls_database_parameters
where parameter = 'NLS_CHARACTERSET';
v_hash_raw := dbms_crypto.mac(src => utl_i18n.string_to_raw(data => p_unhashed, dst_charset => v_charset)
,typ => dbms_crypto.hash_sh256
,key => utl_i18n.string_to_raw(ted_secret_stuff.c_salt));
return v_hash_raw;
exception
when others then
raise_application_error(-20000, sqlerrm);
end hash;
function hash_check
(
p_unhashed in varchar2
,p_hashed in raw
) return boolean is
begin
if p_hashed = hash(p_unhashed => p_unhashed) then
return true;
end if;
return false;
exception
when others then
raise_application_error(-20000, sqlerrm);
end hash_check;
procedure log_insert is
pragma autonomous_transaction;
l_scope logger_logs.scope%type := $$plsql_unit || '.' || 'log_insert';
l_params logger.tab_param;
l_access_logs_row ted_access_logs%rowtype;
l_ip_address varchar2(100);
l_ip_address_hashed raw(256);
begin
l_ip_address := nvl(owa_util.get_cgi_env('X-FORWARDED-FOR'), owa_util.get_cgi_env('REMOTE_ADDR'));
l_ip_address_hashed := hash(p_unhashed => l_ip_address);
l_access_logs_row.date_inserted := trunc(sysdate);
l_access_logs_row.app_id := v('APP_ID');
l_access_logs_row.page_id := v('APP_PAGE_ID');
l_access_logs_row.ip_address_hashed := l_ip_address_hashed;
l_access_logs_row.app_session := v('APP_SESSION');
l_access_logs_row.n := 1;
l_access_logs_row.bot_pk_id := bot_pk_id(p_ip_address => l_ip_address, p_user_agent => owa_util.get_cgi_env('HTTP_USER_AGENT'));
begin
insert into ted_access_logs
values l_access_logs_row;
exception
when dup_val_on_index then
update ted_access_logs a
set a.n = a.n + 1
,a.bot_pk_id = l_access_logs_row.bot_pk_id
where a.date_inserted = l_access_logs_row.date_inserted
and a.app_id = l_access_logs_row.app_id
and a.page_id = l_access_logs_row.page_id
and a.ip_address_hashed = l_access_logs_row.ip_address_hashed
and a.app_session = l_access_logs_row.app_session;
end;
commit;
exception
when others then
logger.log_error(sqlerrm, l_scope, null, l_params);
end log_insert;
end ted_access_log;