<?php // (2023.12.9, 차재복, Cha Jae Bok, http://www.ktword.co.kr)
# 세션 스타트 (매 웹페이지 마다 필요)
session_start();
# db 접속 (dbi)
include_once "../base_utils/db_conn.php";
# (Ajax) 전달 파라미터 및 관련 함수 호출 목록
$script_file = substr($_SERVER['SCRIPT_NAME'],strrpos($_SERVER['SCRIPT_NAME'],"/")+1);
// if($script_file!='naviFetch.php') exit('ajax 형태가 아닌, 임의 외부 php로부터의 호출 의심');
// (type, choice, id, no)
$type = $_REQUEST['type'];
$choice = $_REQUEST['choice'];
$id = $_REQUEST['id'];
if ( isset($_REQUEST['id']) and !empty($id) and !is_numeric($id) or $id<0 ) exit('잘못된 fetch id'); // 해킹방지 (수치>0)
$no = $_REQUEST['no'];
if ( isset($_REQUEST['no']) and !empty($no) and !is_numeric($no) or $no<0 ) exit('잘못된 fetch no'); // 해킹방지 (수치>0)
$no = substr($no,0,10); // 해킹 방지 (글자 수 제한)
if($choice=='treeView' and isset($_REQUEST['id'])) {
if (!isset($_REQUEST['type']) or $type == 'word' or $type == 'multi')
ajaxFetchChildWord ($id, $dbi);
else if ($type == 'source') {
include_once "naviFetch_source.php";
ajaxFetchChildFile ($id, $dbi);
} else if ($type == 'src_tree') {
include_once "naviFetch_source.php";
ajaxFetchChildSrcTree ($id, $dbi);
} else if ($type == 'manual') ajaxFetchChildManual ($id, $dbi);
else if ($type == 'db_table') ajaxFetchChildDB ($id, $dbi);
} else if ($choice == 'ajaxFetchDetailSeparate' and isset($_REQUEST['no'])) {
include_once '../navigation/naviFetch_utils.php';
ajaxFetchDetailSeparate($no, $dbi);
} else if ($choice == 'ajaxFetchWord' and isset($_REQUEST['no'])) {
ajaxFetchWord($no, $dbi);
// 분류 검색
} else if ($choice == 'bunryuSearch' and isset($_REQUEST['sh'])) {
// (해킹방어) 긴 쿼리 스트링이면 무조건 거부
if (strlen($_SERVER['QUERY_STRING'])>120) exit;
// 해킹방어
$sh = $_REQUEST['sh'];
if (strtolower($sh) !== 'c++')
// $sh = urldecode($sh); // url decode
$sh = strip_tags($sh); // tag 제거
$sh = mb_substr(trim($sh),0,40,'utf-8'); // 좌 우 공백 제거 및 글자수 제한
$sh = str_replace(array(' ','\''),array(' ',' '),$sh); // 연속 공백 제거
$sh = str_ireplace('union all',' ',$sh); // union all 제거
// bunryu_search ($sh, $dbi);
bunryu_search_v2 ($sh, $dbi); // sql injection 해킹 공격 방어 (prepare statement)
} else if ($choice == 'ajaxFetchSourceView' and isset($_REQUEST['id'])) {
include_once "naviFetch_source.php";
ajaxFetchSourceView($id, $dbi);
} else if ($choice == 'srcScanDir') {
include_once "naviFetch_source.php";
srcScanDir($dbi);
// 소스 검색
} else if ($choice == 'srcSearch' and isset($_REQUEST['sh'])) {
include_once "naviFetch_source.php";
// (해킹방어) 긴 쿼리 스트링이면 무조건 거부
if (strlen($_SERVER['QUERY_STRING'])>120) exit;
// 해킹방어
$sh = $_REQUEST['sh'];
if (strtolower($sh) !== 'c++')
$sh = urldecode($sh); // url decode
$sh = strip_tags($sh); // tag 제거
$sh = mb_substr(trim($sh),0,40,'utf-8'); // 좌 우 공백 제거 및 글자수 제한
$sh = str_replace(array(' ','\''),array(' ',' '),$sh); // 연속 공백 제거
$sh = str_ireplace('union all',' ',$sh); // union all 제거
srcSearch($sh, $dbi); // sql injection 해킹 공격 방어
// default
} else if ($script_file=='naviFetch.php') {
echo json_encode( array('err_msg'=>'해당되는 선택 사항 없음'), JSON_UNESCAPED_UNICODE);
}
# (Ajax : 용어해설 분류) 용어해설 분류 id에 대해, 해당 관련 no들과 자식 id들을 쿼리하여, JSON 변환시켜 출력
// (대상 테이블 : gubun_tree_v2, book_idx)
function ajaxFetchChildWord ($id, $dbi) {
$query = "(select 'no' as item_type,titlename as name,no as id, 1 as rank, list_ord as ord, '0' as child, yoyak
from book_idx where tree_id=$id)
union all
(select 'id' as item_type,name,id, 2 as rank, sub_seq as ord, child, yoyak
from gubun_tree_v2 where parent=$id)
order by rank,ord,name";
$result = mysqli_query($dbi, $query);
if (mysqli_errno($dbi)) { $err_msg .= mysqli_errno($dbi)." : ".mysqli_error($dbi)."\n"; }
if (!empty($err_msg)){
$return = array('err_msg'=>$err_msg);
} else {
while ( $matched = mysqli_fetch_assoc($result) ) {
$data[] = array('item_type' => $matched['item_type'], 'id'=>$matched['id'], 'name'=>$matched['name'], 'child'=>$matched['child'], 'desc'=>$matched['yoyak'], 'parent' => $id, 'seq' =>$matched['ord'] );
}
if (count($data)<1) $return = array('err_msg'=>'해당 id 없음!');
else $return = array('data' => $data);
}
echo json_encode($return, JSON_UNESCAPED_UNICODE);
}
# (Ajax : 소스 관리 메뉴얼) 메뉴얼(reform) id에 대해, 해당 자식 id들을 쿼리하여, JSON 변환시켜 출력
// (대상 테이블 : reform)
function ajaxFetchChildManual ($id, $dbi) {
$query = "select id,name,child,yoyak from reform where parent=$id order by sub_seq";
$result = mysqli_query($dbi, $query);
if (mysqli_errno($dbi)) { $err_msg .= mysqli_errno($dbi)." : ".mysqli_error($dbi)."\n"; }
if (!empty($err_msg)){
$return = array('err_msg'=>$err_msg);
} else {
while ( $matched = mysqli_fetch_assoc($result) ) {
$data[] = array('id'=>$matched[id], 'name'=>$matched[name], 'child'=>$matched[child], 'desc'=>$matched[yoyak]);
}
if (count($data)<1) $return = array('err_msg'=>'해당 id 없음!');
else $return = array('data' => $data);
}
echo json_encode($return, JSON_UNESCAPED_UNICODE);
}
# (Ajax : db 테이블 및 칼럼) database table을 쿼리하여, JSON 변환시켜 출력
// (대상 테이블 : information_schema.tables)
function ajaxFetchChildDB ($id, $dbi) {
if(!empty($_SESSION['db_name'])) $db_name = $_SESSION['db_name'];
else $db_name = 'test';
// $db_name = 'test';
$query = "select (@row_number:=@row_number + 1) as id,
table_name as name,
table_comment as yoyak,
'1' as child,
'table' as item_type
from (select @row_number:=0) as t, information_schema.tables
where table_schema='{$db_name}'";
$result = mysqli_query($dbi,$query);
if (mysqli_errno($dbi)) { $err_msg .= mysqli_errno($dbi)." : ".mysqli_error($dbi)."\n"; }
if (!empty($err_msg)){
$return = array('err_msg'=>$err_msg);
} else {
while ( $matched = mysqli_fetch_assoc($result) ) {
// db 테이블
if($id == 0) {
$data[] = array('id'=>$matched['id'], 'name'=>$matched['name'], 'child'=>$matched['child'], 'desc'=>$matched['yoyak'], 'item_type' => $matched['item_type']);
// 특정 테이블 내 칼럼
} else if ($id == $matched['id']) {
$query = "select (@row_number:=@row_number + 1) as id,
concat(column_name,' : ',column_type) as name,
column_comment as yoyak,
'0' as child,
'field' as item_type
from (select @row_number:=0) as t, information_schema.columns
where table_name = '".$matched['name']."'
and table_schema = '{$db_name}'
order by name";
$result = mysqli_query($dbi,$query);
if (mysqli_errno($dbi)) { $err_msg .= mysqli_errno($dbi)." : ".mysqli_error($dbi)."\n"; }
while ( $matched = mysqli_fetch_assoc($result) ) {
$data[] = array('id'=>$matched['id'], 'name'=>$matched['name'], 'child'=>$matched['child'], 'desc'=>$matched['yoyak'], 'item_type' => $matched['item_type']);
}
}
}
$return = array('data' => $data);
}
echo json_encode($return, JSON_UNESCAPED_UNICODE);
}
# (PHP included) 해당 no(leaf node)로부터 path 가져오기
// (대상 테이블 : gubun_tree_v2, book_idx)
function phpFetchPath ($no, $dbi) {
if(empty($no)) return;
$query = "select
c.id as leaf_id, c.titlename as leaf_name, c.path2node_v2 as full_path,
d.id as sub_id, d.name as sub_name, d.path2node_v2 as sub_path,
e.no as no_1st, e.list_ord
from
(select a.titlename,a.no,a.list_ord,b.id,b.name,b.path2node_v2 from book_idx a left join gubun_tree_v2 b on a.tree_id=b.id where a.no=$no) c
left join
gubun_tree_v2 d
on find_in_set(d.id,c.path2node_v2)
left join
book_idx e
on d.id=e.tree_id and e.list_ord=1
order by full_path,sub_path,list_ord";
$result=mysqli_query($dbi,$query);
if (mysqli_errno($dbi)) {echo mysqli_errno($dbi)." : ".mysqli_error($dbi)."\n";}
while ( $matched = mysqli_fetch_assoc($result) ) {
// 적용 필드 (5) : leaf_id, leaf_name, sub_id, sub_name, no_1st
$name = ( $matched['sub_id']=='0' ? 'Top' : $matched['sub_name'] );
// $path_lines[$matched['leaf_id']]['upper_name'] = $matched['leaf_name'];
// $path_lines[$matched['leaf_id']]['leaf_id'] = $matched['leaf_id'];
if($matched['leaf_id'] == $matched['sub_id']) {
$path_lines[$matched['leaf_id']]['upper_id'] = $matched['sub_id'];
$path_lines[$matched['leaf_id']]['upper_name'] = $matched['sub_name'];
}
$path_lines[$matched['leaf_id']]['url_str'][$matched['sub_id']] =
"<span class='subMenu' data-id='".$matched['sub_id']."' data-direction='v' style='position:relative;'>
<span class='bullet'>▷</span>
<span class='title'>{$name}</span>
</span>";
}
/*
echo "<pre>";
var_dump($path_lines);
echo "</pre>";
exit;
*/
$path = '';
if(count($path_lines) > 0)
foreach($path_lines as $key => $value) {
$path .= implode('',$value['url_str']);
$path .= '<br>';
}
else
$path .= "해당 id에 대한 path 없음";
return array('output'=>$path,'src_arr'=>$path_lines);
}
// 분류 검색
function bunryu_search ($sh, $dbi) {
if(mb_strlen($sh)<2) {
echo '최소 2자 이상';
return;
}
if(mb_strlen($sh)>15) {
echo '최대 15자 미만';
return;
}
$sh = mysqli_real_escape_string($dbi,$sh);
// 한글 (1),영어 (0) 구분
$lang = ( preg_match("/[\x{ac00}-\x{d7a3}][\x{ac00}-\x{d7a3}]/u", $sh) ? 1 : 0 );
if($lang==1) { // 한글
$sh_nospace = str_replace(' ','',$sh);
$where_phrase = "REPLACE(a.name,' ','') like '%{$sh_nospace}%'
or REPLACE(b.titlename,' ','') like '%{$sh_nospace}%'
or REPLACE(c.word,' ','') like '%{$sh_nospace}%'";
} else { // 영어
if(mb_strlen($sh) > 3) {
$where_phrase = "a.name like '%{$sh}%' or b.titlename like '%{$sh}%' or c.word like '%{$sh}%'";
} else if(mb_strlen($sh) <= 3) {
$where_phrase = "a.name like '{$sh}' or b.titlename like '{$sh}' or c.word like '{$sh}' or c.word like '{$sh} %'";
}
}
$query = "select a.id,a.name,a.yoyak,a.path2node_v2,b.titlename,b.no,
@path:=getpath_v2(a.id),
substring_index(substring_index(@path,'|',2),'|',-1) as pre_ord,
substring_index(substring_index(@path,'|',3),'|',-1) as path_str
from gubun_tree_v2 a left join book_idx b on a.id=b.tree_id left join dict_word_list c on b.no=c.no
where {$where_phrase}
order by pre_ord";
$result=mysqli_query($dbi,$query);
if (mysqli_errno($dbi)) {echo mysqli_errno($dbi)." : ".mysqli_error($dbi)."\n";}
$str = '';
$prev = '';
$i = 0;
while ( $matched = mysqli_fetch_assoc($result) ) {
if($prev_titlename == $matched['titlename']) continue;
if($prev != $matched['name']) {
if($i!=0) {
$str = $str.' . . . ';
$str = $str."</span>";
$str = $str.'<br>';
}
$prev = $matched['name'];
$str = $str."<span class='idPathFocusEvent' data-idpath='{$matched['path2node_v2']}'
style='cursor:pointer;'>";
$str = $str.($j+1).') ';
// $str = $str.$matched['name'];
$str = $str."<span style='text-decoration:underline'>".str_replace('::','>',$matched['path_str'])."</span>";
$str = $str." : ";
$j = $j + 1;
}
if(!empty($matched['titlename'])) {
$str = $str.$matched['titlename'].', ';
$prev_titlename = $matched['titlename'];
}
$i = $i + 1;
}
if(mysqli_num_rows($result) <= 0) {
echo "검색 실패";
} else {
$str = $str.' . . . ';
$str = $str."</span>";
echo $str;
}
}
// 분류 검색 ver.2 (sql injection 해킹 공격 방어)
function bunryu_search_v2 ($sh, $dbi) {
if(mb_strlen($sh)<2) {
echo '최소 2자 이상';
return;
}
if(mb_strlen($sh)>15) {
echo '최대 15자 미만';
return;
}
$sh = mysqli_real_escape_string($dbi,$sh);
// 한글 (1),영어 (0) 구분
$lang = ( preg_match("/[\x{ac00}-\x{d7a3}][\x{ac00}-\x{d7a3}]/u", $sh) ? 1 : 0 );
if($lang==1) { // 한글
$typeStr = 'sss';
$sh_nospace = str_replace(' ','',$sh);
$varStrArr = [$sh_nospace,$sh_nospace,$sh_nospace];
$where_phrase = "REPLACE(a.name,' ','') like concat('%',?,'%')
or REPLACE(b.titlename,' ','') like concat('%',?,'%')
or REPLACE(c.word,' ','') like concat('%',?,'%')";
} else { // 영어
if(mb_strlen($sh) > 3) {
$typeStr = 'sss';
$varStrArr = [$sh,$sh,$sh];
$where_phrase = "a.name like ? or b.titlename like concat('%',?,'%') or c.word like concat('%',?,'%')";
} else if(mb_strlen($sh) <= 3) {
$typeStr = 'ssss';
$varStrArr = [$sh,$sh,$sh,$sh];
$where_phrase = "a.name like ? or b.titlename like ? or c.word like ? or c.word like concat(?,' %')";
}
}
$query = "select a.id,a.name,a.yoyak,a.path2node_v2,b.titlename,b.no,
@path:=getpath_v2(a.id),
substring_index(substring_index(@path,'|',2),'|',-1) as pre_ord,
substring_index(substring_index(@path,'|',3),'|',-1) as path_str
from gubun_tree_v2 a left join book_idx b on a.id=b.tree_id left join dict_word_list c on b.no=c.no
where {$where_phrase}
order by pre_ord";
$stmt = mysqli_prepare($dbi, $query);
// if (mysqli_errno($dbi)) { echo mysqli_errno($dbi)." : ".mysqli_error($dbi)."\n"; }
if (mysqli_errno($dbi)) { $err_msg .= mysqli_errno($dbi)." : ".mysqli_error($dbi)."\n"; }
mysqli_stmt_bind_param($stmt, $typeStr, ...$varStrArr);
// if (mysqli_errno($dbi)) { echo mysqli_errno($dbi)." : ".mysqli_error($dbi)."\n"; }
if (mysqli_errno($dbi)) { $err_msg .= mysqli_errno($dbi)." : ".mysqli_error($dbi)."\n"; }
mysqli_stmt_execute($stmt);
// if (mysqli_errno($dbi)) { echo mysqli_errno($dbi)." : ".mysqli_error($dbi)."\n"; }
if (mysqli_errno($dbi)) { $err_msg .= mysqli_errno($dbi)." : ".mysqli_error($dbi)."\n"; }
$result = mysqli_stmt_get_result($stmt);
// if (mysqli_errno($dbi)) { echo mysqli_errno($dbi)." : ".mysqli_error($dbi)."\n"; }
if (mysqli_errno($dbi)) { $err_msg .= mysqli_errno($dbi)." : ".mysqli_error($dbi)."\n"; }
$str = '';
$prev = '';
$i = 0;
while ( $matched = mysqli_fetch_assoc($result) ) {
if($prev_titlename == $matched['titlename']) continue;
if($prev != $matched['name']) {
if($i!=0) {
$str = $str.' . . . ';
$str = $str."</span>";
$str = $str.'<br>';
}
$prev = $matched['name'];
$str = $str."<span class='idPathFocusEvent' data-idpath='{$matched['path2node_v2']}'
style='cursor:pointer;'>";
$str = $str.($j+1).') ';
// $str = $str.$matched['name'];
$str = $str."<span style='text-decoration:underline'>".str_replace('::','>',$matched['path_str'])."</span>";
$str = $str." : ";
$j = $j + 1;
}
if(!empty($matched['titlename'])) {
$str = $str.$matched['titlename'].', ';
$prev_titlename = $matched['titlename'];
}
$i = $i + 1;
}
//echo $query.'<br>';
mysqli_stmt_close($stmt);
if($i <= 0) {
// echo "검색 실패";
$err_msg .= "검색 실패";
} else {
$str = $str.' . . . ';
$str = $str."</span>";
// echo $str;
}
$return = array('str'=>$str,'err_msg'=>$err_msg);
echo json_encode($return, JSON_UNESCAPED_UNICODE);
}
// 용어 설명 fetch 송출
function ajaxFetchWord($no, $dbi) {
$query = "select abbr from cjb_dict where no={$no} limit 1";
$result = mysqli_query($dbi,$query);
if (mysqli_errno($dbi)) { $err_msg .= mysqli_errno($dbi)." : ".mysqli_error($dbi)."\n"; }
$matched = mysqli_fetch_assoc($result);
if (!empty($err_msg)){
$return = array('err_msg'=>$err_msg);
} else {
$return = array('abbr' => $matched['abbr']);
}
echo json_encode($return, JSON_UNESCAPED_UNICODE);
}
?>