function get_foreign_keys_constraints_query($table_name) { /* $q = "SELECT a.table_name, a.column_name, a.constraint_name, c.owner, c.r_owner, c_pk.table_name r_table_name, c_pk.constraint_name r_pk FROM all_cons_columns a JOIN all_constraints c ON a.owner = c.owner AND a.constraint_name = c.constraint_name JOIN all_constraints c_pk ON c.r_owner = c_pk.owner AND c.r_constraint_name = c_pk.constraint_name WHERE c.constraint_type = 'R' AND a.table_name = '".strtoupper(totally_escape($table_name))."'"; */ $q = "SELECT c_list.CONSTRAINT_NAME as NAME,\nsubstr(c_src.COLUMN_NAME, 1, 20) as SRC_COLUMN,\nc_dest.TABLE_NAME as DEST_TABLE,\nsubstr(c_dest.COLUMN_NAME, 1, 20) as DEST_COLUMN\nFROM ALL_CONSTRAINTS c_list, ALL_CONS_COLUMNS c_src, ALL_CONS_COLUMNS c_dest\nWHERE c_list.CONSTRAINT_NAME = c_src.CONSTRAINT_NAME\nAND c_list.R_CONSTRAINT_NAME = c_dest.CONSTRAINT_NAME\nAND c_list.CONSTRAINT_TYPE = 'R'\nAND c_src.TABLE_NAME = '" . strtoupper(totally_escape($table_name)) . "'\nGROUP BY c_list.CONSTRAINT_NAME, c_src.TABLE_NAME,\n c_src.COLUMN_NAME, c_dest.TABLE_NAME, c_dest.COLUMN_NAME;"; return $q; }
FOREIGN KEY ("DT") REFERENCES "MARKS" ("MARK_DATE") */ $fkq = "ALTER TABLE \"" . totally_escape($table_name) . "\" ADD FOREIGN KEY ("; if (isset($_POST["foreign_key_columns"]) || !isset($_POST["foreign_key_columns"][$i])) { $list = ""; foreach ($_POST["foreign_key_columns"][$i] as $cname) { if ($list == "") { $list = "\"" . $cname . "\""; } else { $list .= ", \"" . $cname . "\""; } } $fkq .= $list; } $fkq .= ") REFERENCES \"" . totally_escape($_POST["foreign_key_table"][$i]) . "\" ("; if (isset($_POST["foreign_key_other_columns"]) || !isset($_POST["foreign_key_other_columns"][$i])) { $list = ""; foreach ($_POST["foreign_key_other_columns"][$i] as $cname) { if ($list == "") { $list = "\"" . $cname . "\""; } else { $list .= ", \"" . $cname . "\""; } } $fkq .= $list; } $fkq .= ")"; if (odbc_exec($client->get_connection(), $fkq) === false) { $rollback_needed = true; $rollback_error_message = get_odbc_error();
$list .= ", \"" . $cname . "\""; } } $fkq .= $list; } $fkq .= ")"; if (odbc_exec($client->get_connection(), $fkq) === false) { $rollback_needed = true; $rollback_error_message = $fkq . "\n\n" . get_odbc_error(); break; } } } //check if rollback needed if ($rollback_needed === true) { odbc_exec($client->get_connection(), "DROP TABLE " . totally_escape($table_name)); //we don't care whether it's successful //but as table is not dropped after rollback, that might fix our problem } if ($rollback_needed === true) { if (odbc_exec($client->get_connection(), "ROLLBACK;") === false) { die("Error occurred. Was unable rollback the transaction:\n\n" . $rollback_error_message . "\n\n" . get_odbc_error()); } die("Error occurred. Transaction was rollbacked.\n\n" . $rollback_error_message); } if (odbc_exec($client->get_connection(), "COMMIT;") === false) { $err = get_odbc_error(); if (odbc_exec($client->get_connection(), "ROLLBACK;") === false) { die("Was unable to both commit and rollback the transaction:\n\n" . $err . "\n\n" . get_odbc_error()); } die("Was unable to both commit the transaction. It was rollbacked.\n\n" . $err);
<?php //check auth include_once "../functions/client.php"; include_once "../functions/utils.php"; $client = new client(); if (!$client->logged_in()) { die("false"); } //check POST $table_name = null; $fields_count = 0; $rowid = null; if ($_POST) { $table_name = totally_escape($_POST["table_name"]); $fields_count = $_POST["fields_count"]; $rowid = totally_escape($_POST["rowid"]); } if ($table_name == null) { die("false"); } //TODO check table_name is one word //prepare statement if ($rowid == null) { $query = "INSERT INTO " . $table_name . " VALUES("; for ($i = 1; $i < $fields_count; ++$i) { $query .= "?, "; } $query .= "?);"; } else { $colnames = odbc_exec($client->get_connection(), "SELECT column_name, data_type, data_length FROM ALL_TAB_COLUMNS WHERE table_name = '" . strtoupper($table_name) . "';"); $q2 = "";
$tabs = array("tables" => "Tables", "compose_report" => "Compose report", "execute_query" => "Execute query"); $tab = key($tabs); $action = "list"; $target = ""; $rowid = ""; foreach ($_GET as $k => $v) { $k = totally_escape($k); if ($k === "action") { $action = totally_escape($v); } else { if ($k === "target") { $target = totally_escape($v); } else { if ($k === "rowid") { $rowid = totally_escape($v); } else { foreach ($tabs as $tab_name => $tab_title) { if ($k === $tab_name) { $tab = $k; } } } } } } $found = false; foreach ($tabs as $tab_name => $tab_title) { if ($tab == $tab_name) { $found = true; break;
ID INTEGER NOT NULL PRIMARY KEY, student_ID INTEGER NOT NULL UNIQUE, group_ID INTEGER NOT NULL UNIQUE, policy INTEGER NOT NULL CHECK(policy >= 100000 AND policy <= 999999), FOREIGN KEY (student_ID) REFERENCES students (ID), FOREIGN KEY (group_ID) REFERENCES groups (ID) ); */ ?> <div id="save_message" style="display: none;"></div> <form id='table_form'> <?php echo "<input type='" . ($target == "" ? "text" : "hidden") . "' placeholder='table name' name='table_name' value='" . totally_escape($target) . "' class='create_table_table_name_field'/>"; if ($target != "") { echo "<h1>" . totally_escape($target) . "</h1>"; echo "<input type='hidden' name='mode' value='editing'/>"; } ?> <input type='hidden' name='fields_count' id='fields_count' value='0'/> <input type='hidden' name='foreign_keys_count' id='foreign_keys_count' value='0'/> <table id="table_columns" class="results_table"> <tr> <th>Column Name</th> <th>Type</th> <th style="max-width: 50pt;">Precision</th> <th style="max-width: 50pt;">Length</th> <th style="min-width: 55pt; max-width: 55pt;">Not NULL</th> <th style="max-width: 50pt;">Unique</th> <th style="max-width: 50pt;">Primary</th>
} $query .= " FROM " . $table_name . " WHERE rownum <= ?;"; //prepare statement $statement = odbc_prepare($client->get_connection(), $query); if ($statement === false) { return $query . "\n\n" . get_odbc_error(); } $items = array(); $items[] = (int) $rownum; $result = odbc_execute($statement, $items); if ($result === false) { return $query . "\n\n" . get_odbc_error(); } return $statement; } $result = get_report($client, totally_escape($_POST["target"]), $_POST["show"], $_POST["rownum"]); if (is_string($result)) { echo "<div class=\"error_message\">" . $result . "</div>"; } else { make_results_table($result, false, null); } } else { //show form ?> <div id="form_message" style="display: none;"></div> <form action="" method="post" class="report_form" id="report_form"> <p>Compose report of <input type='number' name='rownum' value='50' min='1'/> rows from table <select id="report_target" name="target" onchange="show_fields();"> <option value=""><select table></option>
<?php //check auth include_once "../functions/client.php"; include_once "../functions/utils.php"; $client = new client(); if (!$client->logged_in()) { die("false"); } //check POST $table_name = null; if ($_POST) { $table_name = totally_escape($_POST["target"]); } if ($table_name == null) { die("false"); } //TODO check table_name is one word //select column names $colnames = odbc_exec($client->get_connection(), "SELECT column_name, data_type, data_length FROM ALL_TAB_COLUMNS WHERE table_name = '" . strtoupper($table_name) . "';"); if ($colnames === false) { die("false"); } $q2 = ""; while (odbc_fetch_row($colnames)) { if ($q2 == "") { $q2 .= '["' . odbc_result($colnames, 1) . '"'; } else { $q2 .= ', "' . odbc_result($colnames, 1) . '"'; } } $q2 .= "]";