function GenQuery($tbl, $do = 's', $col = '*', $ord = '', $lim = '', $rawin = array(), $rawop = array(), $rawst = array(), $rawco = array(), $jn = '') { global $debug; $tbl = mysql_real_escape_string($tbl); # Mitigate SQL injection $ord = mysql_real_escape_string($ord); $lim = mysql_real_escape_string($lim); $in = array_map('mysql_real_escape_string', $rawin); $op = array_map('mysql_real_escape_string', $rawop); $st = array_map('mysql_real_escape_string', $rawst); $co = array_map('mysql_real_escape_string', $rawco); if ($do == 'i') { $qry = "INSERT INTO {$tbl} (" . implode(',', $in) . ") VALUES ('" . implode("','", $st) . "')"; } elseif ($do == 'u') { if ($in[0]) { $x = 0; foreach ($in as $c) { $o = array_key_exists($x, $op) ? $op[$x] : '='; # Use '=' if no operator is set if ($c) { $s[] = "{$c} {$o} '{$st[$x]}'"; } $x++; } $qry = "UPDATE {$tbl} SET " . implode(',', $s) . " WHERE {$col} {$ord} '{$lim}'"; } } elseif ($do == 'b') { $qry = "SHOW DATABASES {$col} '{$tbl}'"; } elseif ($do == 'p') { $qry = "DROP DATABASE {$tbl}"; } elseif ($do == 'h') { $qry = "SHOW TABLES {$tbl}"; } elseif ($do == 't') { $qry = "TRUNCATE {$tbl}"; } elseif ($do == 'o') { $qry = "OPTIMIZE TABLE {$tbl}"; } elseif ($do == 'c') { $qry = "SHOW COLUMNS FROM {$tbl}"; } elseif ($do == 'r') { $qry = "REPAIR TABLE {$tbl}"; } elseif ($do == 'v') { $qry = "SELECT VERSION()"; } elseif ($do == 'x') { $qry = "SHOW processlist"; } else { $l = $lim ? "LIMIT {$lim}" : ""; if (strstr($ord, 'ifname')) { $desc = strpos($ord, 'desc') ? " desc" : ""; $ord = $desc ? substr($ord, 0, -5) : $ord; # Cut away desc for proper handling below $oar = explode(".", $ord); # Handle table in join queries $icol = ($oar[0] == 'ifname' or $oar[0] == 'nbrifname') ? 'ifname' : "{$oar['0']}.ifname"; $dcol = ($oar[0] == 'ifname' or $oar[0] == 'nbrifname') ? 'device' : "{$oar['0']}.device"; $od = "ORDER BY {$dcol} {$desc},SUBSTRING_INDEX({$icol}, '/', 1), SUBSTRING_INDEX({$icol}, '/', -1)*1+0"; } elseif ($ord) { $od = "ORDER BY {$ord}"; } else { $od = ""; } $w = Condition($in, $op, $st, $co, 2); if (isset($_SESSION['view']) and $_SESSION['view'] and (strstr($jn, 'JOIN devices') or $tbl == 'devices')) { $viewq = explode(' ', $_SESSION['view']); $w = ($w ? "{$w} AND " : "WHERE ") . AdOpVal($viewq[0], $viewq[1], $viewq[2]); } if ($do == 'd') { $qry = "DELETE FROM {$tbl} {$w} {$od} {$l}"; } elseif ($do == 's') { $qry = "SELECT {$col} FROM {$tbl} {$jn} {$w} {$od} {$l}"; } else { $cal = ''; $hav = ''; if (strpos($col, ';')) { $xcol = explode(";", $col); $col = $xcol[0]; if ($xcol[1] != '-') { $cal = ", {$xcol['1']}"; } if (array_key_exists(2, $xcol) and $xcol[2]) { $hav = "having({$xcol['2']})"; } } $qry = "SELECT {$col},count(*) as cnt{$cal} FROM {$tbl} {$jn} {$w} GROUP BY {$col} {$hav} {$od} {$l}"; } } if ($debug) { echo "<div class=\"textpad code warn\" style=\"width:600px\">"; debug_print_backtrace(); echo "<p><a href=\"System-Export.php?act=c&query=" . urlencode($qry) . "\">{$qry}</a></div>\n"; } return $qry; }
function GenQuery($tbl, $do = 's', $col = '*', $ord = '', $lim = '', $rawin = array(), $rawop = array(), $rawst = array(), $rawco = array(), $jn = '') { global $debug; $tbl = pg_escape_string($tbl); # Mitigate SQL injection $ord = pg_escape_string($ord); $lim = pg_escape_string($lim); $in = array_map('pg_escape_string', $rawin); $op = array_map('pg_escape_string', $rawop); $st = array_map('pg_escape_string', $rawst); $co = array_map('pg_escape_string', $rawco); if ($do == 'i') { $qry = "INSERT INTO {$tbl} (" . implode(',', $in) . ") VALUES ('" . implode("','", $st) . "')"; } elseif ($do == 'u') { if ($in[0]) { $x = 0; foreach ($in as $c) { $o = array_key_exists($x, $op) ? $op[$x] : '='; # Use '=' if no operator is set if ($c) { $s[] = "{$c} {$o} '{$st[$x]}'"; } $x++; } $qry = "UPDATE {$tbl} SET " . implode(',', $s) . " WHERE {$col} {$ord} '{$lim}'"; } } elseif ($do == 'b') { $qry = "SELECT datname FROM pg_database WHERE datistemplate = false and datname {$col} '{$tbl}'"; } elseif ($do == 'p') { $qry = "DROP DATABASE {$tbl}"; } elseif ($do == 'h') { $qry = "SELECT relname from pg_stat_user_tables ORDER BY relname"; } elseif ($do == 't') { $qry = "TRUNCATE {$tbl}"; } elseif ($do == 'o') { $qry = "VACUUM {$tbl}"; } elseif ($do == 'c') { $qry = "SELECT column_name,data_type,is_nullable,column_default from INFORMATION_SCHEMA.COLUMNS where table_name = '{$tbl}' ORDER BY ordinal_position"; } elseif ($do == 'r') { $qry = "VACUUM FULL {$tbl}"; } elseif ($do == 'v') { $qry = "SELECT VERSION()"; } elseif ($do == 'x') { $qry = "SELECT procpid,usename,datname FROM pg_stat_activity"; } else { $l = $lim ? "LIMIT {$lim}" : ""; if (strstr($ord, 'ifname')) { $desc = strpos($ord, 'desc') ? " desc" : ""; $ord = $desc ? substr($ord, 0, -5) : $ord; # Cut away desc for proper handling below $oar = explode(".", $ord); # Handle table in join queries $icol = ($oar[0] == 'ifname' or $oar[0] == 'nbrifname') ? 'ifname' : "{$oar['0']}.ifname"; $dcol = ($oar[0] == 'ifname' or $oar[0] == 'nbrifname') ? 'device' : "{$oar['0']}.device"; $od = "ORDER BY {$dcol} {$desc},substring({$icol} from '.*/')"; #TODO rework? GH:$od = "ORDER BY $dcol $desc,SUBSTRING_INDEX($icol, '/', 1), case when SUBSTRING_INDEX($icol, '/', -1) ~ '^\d+$' then cast(SUBSTRING_INDEX($icol, '/', -1) as bigint) else 0 end"; } elseif ($ord) { $od = "ORDER BY {$ord}"; } else { $od = ""; } $w = Condition($in, $op, $st, $co, 2); if (isset($_SESSION['view']) and $_SESSION['view'] and (strstr($jn, 'JOIN devices') or $tbl == 'devices')) { $viewq = explode(' ', $_SESSION['view']); $w = ($w ? "{$w} AND " : "WHERE ") . AdOpVal($viewq[0], $viewq[1], $viewq[2]); } if ($do == 'd') { $qry = "DELETE FROM {$tbl} WHERE ctid IN (SELECT ctid FROM {$tbl} {$w} {$od} {$l})"; } elseif ($do == 's') { $qry = "SELECT {$col} FROM {$tbl} {$jn} {$w} {$od} {$l}"; } else { $cal = ''; $hav = ''; if (strpos($col, ';')) { $xcol = explode(";", $col); $col = $xcol[0]; if ($xcol[1] != '-') { $cal = ", {$xcol['1']}"; } if (array_key_exists(2, $xcol) and $xcol[2]) { $hav = "having({$xcol['2']})"; } } $qry = "SELECT {$col},count(*) as cnt{$cal} FROM {$tbl} {$jn} {$w} GROUP BY {$col} {$hav} {$od} {$l}"; } } if ($debug) { echo "<div class=\"textpad code warn\" style=\"width:600px\">"; debug_print_backtrace(); echo "<p><a href=\"System-Export.php?act=c&query=" . urlencode($qry) . "\">{$qry}</a></div>\n"; } return $qry; }
function Condition($in, $op, $st, $co, $mod = 0) { global $cols; $h = ''; $w = ''; $comok = 0; if (!count($in)) { return ''; } if (preg_match('/[<>=]/', $co[0])) { # subconditions 1 and 2 compare columns $w .= $in[0] . " {$co['0']} " . $in[1]; $h .= $cols[$in[0]] . " {$co['0']} " . $cols[$in[1]]; $comok = 1; } elseif ($op[0] and !(preg_match('/~|LIKE$/i', $op[0]) and $st[0] === '')) { # process normally unless empty regexp/like in 1 $w .= AdOpVal($in[0], $op[0], $st[0]); $h .= $cols[$in[0]] . " {$op['0']} '" . $st[0] . "'"; if ($co[0] and $op[1] and !(preg_match('/~|LIKE$/i', $op[1]) and $st[1] === '')) { # subcondition 2 unless empty regexp/like $w .= " {$co['0']} " . AdOpVal($in[1], $op[1], $st[1]); $h .= " {$co['0']} " . $cols[$in[1]] . " {$op['1']} '" . $st[1] . "'"; $comok = 1; } } if ($comok and $co[1]) { # Combining subconditions if (preg_match('/[<>=]/', $co[2])) { # subconditions 3 and 4 compares columns $w .= " {$co['1']} " . $in[2] . " {$co['2']} " . $in[3]; $h .= " {$co['1']} " . $cols[$in[2]] . " {$co['2']} " . $cols[$in[3]]; } elseif ($op[2] and !(preg_match('/~|LIKE$/i', $op[2]) and $st[2] === '')) { # process normally unless empty regexp/like in 3 $w2 = AdOpVal($in[2], $op[2], $st[2]); $h2 = $cols[$in[2]] . " {$op['2']} '" . $st[2] . "'"; if ($co[2] and $op[2] and !(preg_match('/~|LIKE$/i', $op[3]) and $st[3] === '')) { # subcondition 4 unless empty regexp/like $w2 .= " {$co['2']} " . AdOpVal($in[3], $op[3], $st[3]); $h2 .= " {$co['2']} " . $cols[$in[3]] . " {$op['3']} '" . $st[3] . "'"; } $w = "({$w}) {$co['1']} ({$w2})"; $h = "({$h}) {$co['1']} ({$h2})"; } } if ($mod == 2) { return $w ? "WHERE {$w}" : ''; } elseif ($mod) { return $h; } else { if ($h) { echo "<h3>{$h}</h3>"; } } }