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>";
}
}
}
