/**
* Escape/sanitize a table sql column name for a sql query..
*
* This will escape/sanitize the sql column name for a sql query. It is done by whitelisting
* all of the current sql column names in the openemr database from a table(s). Note that if
* there is no match, then it will die() and a error message will be sent to the screen and
* the error log. This function should not be used for escaping tables outside the openemr
* database (should use escape_identifier() function below for that scenario)
*
* @param string $s sql column name variable to be escaped/sanitized.
* @param array $tables The table(s) that the sql columns is from (in an array).
* @param boolean $long Use long form (ie. table.colname) vs short form (ie. colname).
* @return string Escaped table name variable.
*/
function escape_sql_column_name($s, $tables, $long = FALSE)
{
// If the $tables is empty, then process them all
if (empty($tables)) {
$res = sqlStatementNoLog("SHOW TABLES");
$tables = array();
while ($row = sqlFetchArray($res)) {
$keys_return = array_keys($row);
$tables[] = $row[$keys_return[0]];
}
}
// First need to escape the $tables
$tables_escaped = array();
foreach ($tables as $table) {
$tables_escaped[] = escape_table_name($table);
}
// Collect all the possible sql columns from the tables
$columns_options = array();
foreach ($tables_escaped as $table_escaped) {
$res = sqlStatementNoLog("SHOW COLUMNS FROM " . $table_escaped);
while ($row = sqlFetchArray($res)) {
if ($long) {
$columns_options[] = $table_escaped . "." . $row['Field'];
} else {
$columns_options[] = $row['Field'];
}
}
}
// Now can escape(via whitelisting) the sql column name
return escape_identifier($s, $columns_options, TRUE);
}
/**
* Get the data in an array for this form.
*
* First, we check the forms table to get the row id in the
* specific table. Then we get the row of data from the specific
* form_* table.
*
* @see \ESign\SignableIF::getData()
*/
public function getData()
{
// We assume that the formdir is the same as the table suffix,
// but this may not always be the case. TODO In the future,
// create a list in the list_options for formdir => table mapping
$table = "form_" . $this->_formDir;
if ($this->_formDir == 'newpatient') {
$table = "form_encounter";
}
if ($this->_formDir == 'procedure_order') {
$table = "procedure_order";
}
// Get row from forms table
$statement = "SELECT F.id, F.date, F.encounter, F.form_name, F.form_id, F.pid, F.user, F.formdir FROM forms F ";
$statement .= "WHERE F.id = ? LIMIT 1";
$row = sqlQuery($statement, array($this->_formId));
// Get form-specific data
$statement = "SELECT * FROM " . escape_table_name($table) . " ";
if ($this->_formDir == 'procedure_order') {
$statement .= "WHERE procedure_order_id = ? LIMIT 1";
} else {
$statement .= "WHERE id = ? LIMIT 1";
}
$formRow = sqlQuery($statement, array($row['form_id']));
return $formRow;
}
/**
* Process tables that contain any upper case letters; this is simple a wrapper function of
* escape_table_name() above when using it for the sole purpose of mitigating sql table names
* that contain upper case letters.
*
* @param string $s sql table name variable to be escaped/sanitized.
* @return string Escaped table name variable.
*/
function mitigateSqlTableUpperCase($s)
{
return escape_table_name($s);
}
} elseif ($_POST['hidden_mode'] == 'alter') {
$newval = $_POST[$_POST['hidden_selection']];
if ($_POST['hidden_selection'] == 'change_category') {
$to_alter_id = $_POST['hidden_category'];
$to_alter_table = 'form_CAMOS_category';
$to_alter_column = 'category';
} elseif ($_POST['hidden_selection'] == 'change_subcategory') {
$to_alter_id = $_POST['hidden_subcategory'];
$to_alter_table = 'form_CAMOS_subcategory';
$to_alter_column = 'subcategory';
} elseif ($_POST['hidden_selection'] == 'change_item') {
$to_alter_id = $_POST['hidden_item'];
$to_alter_table = 'form_CAMOS_item';
$to_alter_column = 'item';
}
$query = "UPDATE " . escape_table_name($to_alter_table) . " set " . $to_alter_column . " = '" . $newval . "' where id = " . $to_alter_id;
sqlInsert($query);
}
// end handle changes to database
//preselect column items
//either a database change has been made, so the user should be made to feel that they never left the same CAMOS screen
//or, CAMOS has been started freshly, therefore the last entry of the current patient should be selected.
$preselect_mode = '';
if ($preselect_category == '' && !$out_of_encounter) {
$preselect_mode = 'by name';
//at this point, if this variable has not been set, CAMOS must have been start over
//so let's get the most recent values from form_CAMOS for this patient's pid
$tmp = sqlQuery("SELECT max(id) AS max FROM " . mitigateSqlTableUpperCase("form_CAMOS") . " WHERE " . "pid = '" . $_SESSION['pid'] . "'");
$maxid = $tmp['max'] ? $tmp['max'] : 0;
$query = "SELECT category, subcategory, item FROM " . mitigateSqlTableUpperCase("form_CAMOS") . " WHERE id = {$maxid}";
$statement = sqlStatement($query);
/**
* Get the data in an array for this form.
*
* First, we check the forms table to get the row id in the
* specific table. Then we get the row of data from the specific
* form_* table.
*
* @see \ESign\SignableIF::getData()
*/
public function getData()
{
// Use default standards based on formdir value
// Exceptions are specified in formdir_keys list
$row = sqlQuery("SELECT title FROM list_options WHERE list_id = ? AND option_id = ? AND activity = 1", array('formdir_keys', $this->_formDir));
if (isset($row['title'])) {
$excp = json_decode("{" . $row['title'] . "}");
}
$tbl = isset($excp->tbl) ? $excp->tbl : "form_" . $this->_formDir;
$id = isset($excp->id) ? $excp->id : 'id';
$limit = isset($excp->limit) ? $excp->limit : 1;
// Get form data based on key from forms table
$sql = sprintf("SELECT fd.* FROM %s fd\n \t\tINNER JOIN forms f ON fd.%s = f.form_id\n \t\tWHERE f.id = ?", escape_table_name($tbl), escape_sql_column_name($id, array($tbl)));
if ($limit != '*') {
$sql .= ' LIMIT ' . escape_limit($limit);
}
$rs = sqlStatement($sql, array($this->_formId));
if (sqlNumRows($rs) == 1) {
// maintain legacy hash
$frs = sqlFetchArray($rs);
} else {
$frs = array();
while ($fr = sqlFetchArray($rs)) {
array_push($frs, $fr);
}
}
return $frs;
}
private function delete_calendar_external()
{
$sql = "TRUNCATE TABLE " . escape_table_name(self::TABLE_NAME);
$res = sqlStatement($sql);
}
} elseif ($_POST['hidden_mode'] == 'alter') {
$newval = $_POST[$_POST['hidden_selection']];
if ($_POST['hidden_selection'] == 'change_category') {
$to_alter_id = $_POST['hidden_category'];
$to_alter_table = 'form_CAMOS_category';
$to_alter_column = 'category';
} elseif ($_POST['hidden_selection'] == 'change_subcategory') {
$to_alter_id = $_POST['hidden_subcategory'];
$to_alter_table = 'form_CAMOS_subcategory';
$to_alter_column = 'subcategory';
} elseif ($_POST['hidden_selection'] == 'change_item') {
$to_alter_id = $_POST['hidden_item'];
$to_alter_table = 'form_CAMOS_item';
$to_alter_column = 'item';
}
sqlInsert("UPDATE " . escape_table_name($to_alter_table) . " set " . $to_alter_column . " = ? where id = ?", array($newval, $to_alter_id));
}
//preselect column items
//either a database change has been made, so the user should be made to feel that they never left the same CAMOS screen
//or, CAMOS has been started freshly, therefore the last entry of the current patient should be selected.
$preselect_mode = '';
if ($preselect_category == '' && !$out_of_encounter) {
$preselect_mode = 'by name';
//at this point, if this variable has not been set, CAMOS must have been start over
//so let's get the most recent values from form_CAMOS for this patient's pid
$tmp = sqlQuery("SELECT max(id) AS max FROM " . mitigateSqlTableUpperCase("form_CAMOS") . " WHERE " . "pid = '" . $_SESSION['pid'] . "'");
$maxid = $tmp['max'] ? $tmp['max'] : 0;
$query = "SELECT category, subcategory, item FROM " . mitigateSqlTableUpperCase("form_CAMOS") . " WHERE id = {$maxid}";
$statement = sqlStatement($query);
if ($result = sqlFetchArray($statement)) {
$preselect_category = $result['category'];