library/formdata.inc.php

   1 <?php
   2
   3 /**
   4  * Functions to globally validate and prepare data for sql database insertion.
   5  *
   6  * @package   OpenEMR
   7  * @link      https://www.open-emr.org
   8  * @author    Rod Roark <rod@sunsetsystems.com>
   9  * @author    Brady Miller <brady.g.miller@gmail.com>
  10  * @copyright Copyright (c) 2009 Rod Roark <rod@sunsetsystems.com>
  11  * @copyright Copyright (c) 2018 Brady Miller <brady.g.miller@gmail.com>
  12  * @license   https://github.com/openemr/openemr/blob/master/LICENSE GNU General Public License 3
  13  */
  14
  15
  16 /**
  17  * Escape a parameter to prepare for a sql query.
  18  *
  19  * @param   string $s  Parameter to be escaped.
  20  * @return  string     Escaped parameter.
  21  */
  22 function add_escape_custom($s)
  23 {
  24     //prepare for safe mysql insertion
  25     $s = mysqli_real_escape_string($GLOBALS['dbh'], $s);
  26     return $s;
  27 }
  28
  29 /**
  30  * Escape a sql limit variable to prepare for a sql query.
  31  *
  32  * This will escape integers within the LIMIT ?, ? part of a sql query.
  33  * Note that there is a maximum value to these numbers, which is why
  34  * should only use for the LIMIT ? , ? part of the sql query and why
  35  * this is centralized to a function (in case need to upgrade this
  36  * function to support larger numbers in the future).
  37  *
  38  * @param   string $s  Limit variable to be escaped.
  39  * @return  string     Escaped limit variable.
  40  */
  41 function escape_limit($s)
  42 {
  43     //prepare for safe mysql insertion
  44     $s = (int)$s;
  45     return $s;
  46 }
  47
  48 /**
  49  * Escape/sanitize a sql sort order keyword variable to prepare for a sql query.
  50  *
  51  * This will escape/sanitize the sort order keyword. It is done by whitelisting
  52  * only certain keywords(asc,desc). If the keyword is illegal, then will default
  53  * to asc.
  54  *
  55  * @param   string $s  Sort order keyword variable to be escaped/sanitized.
  56  * @return  string     Escaped sort order keyword variable.
  57  */
  58 function escape_sort_order($s)
  59 {
  60     return escape_identifier(strtolower($s), array("asc","desc"));
  61 }
  62
  63 /**
  64  * If parameter string contains comma(,) delimeter
  65  * Splits parameter string into an array, using comma(,) as delimeter
  66  * else it returns original string
  67  *
  68  * @param   string       $s  string to be processed
  69  * @return  array        $columns   an array formed by spliting $s with comma(,) delimeter
  70  */
  71
  72 function process_cols_escape($s)
  73 {
  74     //returns an array of columns
  75     $columns = explode(",", $s);
  76     if (count($columns) > 1) {
  77         return $columns;
  78     }
  79
  80     return $s;
  81 }
  82
  83 /**
  84  * Escape/sanitize a table sql column name for a sql query..
  85  *
  86  * This will escape/sanitize the sql column name for a sql query. It is done by whitelisting
  87  * all of the current sql column names in the openemr database from a table(s). Note that if
  88  * there is no match, then it will die() and a error message will be sent to the screen and
  89  * the error log. This function should not be used for escaping tables outside the openemr
  90  * database (should use escape_identifier() function below for that scenario)
  91  *
  92  * @param   string|array        $s       sql column name(s) variable to be escaped/sanitized.
  93  * @param   array         $tables  The table(s) that the sql columns is from (in an array).
  94  * @param   boolean       $long    Use long form (ie. table.colname) vs short form (ie. colname).
  95  * @return  string                 Escaped table name variable.
  96  */
  97 function escape_sql_column_name($s, $tables, $long = false)
  98 {
  99     // If $s is asterisk return asterisk to select all columns
 100     if ($s === "*") {
 101         return "*";
 102     }
 103
 104      // If $s is an array process then use recursion to check each column
 105     if (is_array($s)) {
 106         $multiple_columns = [];
 107         foreach ($s as $column) {
 108             $multiple_columns[] = escape_sql_column_name(trim($column), $tables);
 109         }
 110         return implode(", ", $multiple_columns);
 111     }
 112
 113     // If the $tables is empty, then process them all
 114     if (empty($tables)) {
 115         $res = sqlStatementNoLog("SHOW TABLES");
 116         $tables = array();
 117         while ($row = sqlFetchArray($res)) {
 118             $keys_return = array_keys($row);
 119             $tables[] = $row[$keys_return[0]];
 120         }
 121     }
 122
 123     // First need to escape the $tables
 124     $tables_escaped = array();
 125     foreach ($tables as $table) {
 126         $tables_escaped[] = escape_table_name($table);
 127     }
 128
 129     // Collect all the possible sql columns from the tables
 130     $columns_options = array();
 131     foreach ($tables_escaped as $table_escaped) {
 132         $res = sqlStatementNoLog("SHOW COLUMNS FROM " . $table_escaped);
 133         while ($row = sqlFetchArray($res)) {
 134             if ($long) {
 135                 $columns_options[] = $table_escaped . "." . $row['Field'];
 136             } else {
 137                 $columns_options[] = $row['Field'];
 138             }
 139         }
 140     }
 141
 142     // Now can escape(via whitelisting) the sql column name
 143     return escape_identifier($s, $columns_options, true);
 144 }
 145
 146 /**
 147  * Escape/sanitize a table name for a sql query. This function can also can be used to
 148  * process tables that contain any upper case letters.
 149  *
 150  * This will escape/sanitize the table name for a sql query. It is done by whitelisting
 151  * all of the current tables in the openemr database. The matching is not case sensitive,
 152  * although it will attempt a case sensitive match before proceeding to a case insensitive
 153  * match (see below escape_identifier() function for more details on this). Note that if
 154  * there is no match, then it will die() and a error message will be sent to the screen
 155  * and the error log. This function should not be used for escaping tables outside the
 156  * openemr database (should use escape_identifier() function below for that scenario).
 157  * Another use of this function is to deal with casing issues that arise in tables that
 158  * contain upper case letter(s) (these tables can be huge issues when transferring databases
 159  * from Windows to Linux and vice versa); this function can avoid this issues if run the
 160  * table name through this function (To avoid confusion, there is a wrapper function
 161  * entitled mitigateSqlTableUpperCase() that is used when just need to mitigate casing
 162  * for table names that contain any uppercase letters).
 163  * @param   string $s  sql table name variable to be escaped/sanitized.
 164  * @return  string     Escaped table name variable.
 165  */
 166 function escape_table_name($s)
 167 {
 168     $res = sqlStatementNoLog("SHOW TABLES");
 169     $tables_array = array();
 170     while ($row = sqlFetchArray($res)) {
 171         $keys_return = array_keys($row);
 172         $tables_array[] = $row[$keys_return[0]];
 173     }
 174
 175     // Now can escape(via whitelisting) the sql table name
 176     return escape_identifier($s, $tables_array, true, false);
 177 }
 178
 179 /**
 180  * Process tables that contain any upper case letters; this is simple a wrapper function of
 181  * escape_table_name() above when using it for the sole purpose of mitigating sql table names
 182  * that contain upper case letters.
 183  *
 184  * @param   string $s  sql table name variable to be escaped/sanitized.
 185  * @return  string     Escaped table name variable.
 186  */
 187 function mitigateSqlTableUpperCase($s)
 188 {
 189     return escape_table_name($s);
 190 }
 191
 192 /**
 193  * Escape/sanitize a sql identifier variable to prepare for a sql query.
 194  *
 195  * This will escape/sanitize a sql identifier. There are two options provided by this
 196  *  function.
 197  * The first option is done by whitelisting ($whitelist_items is array) and in this case
 198  *  only certain identifiers (listed in the $whitelist_items array) can be used; if
 199  *  there is no match, then it will either default to the first item in the $whitelist_items
 200  *  (if $die_if_no_match is FALSE) or it will die() and send an error message to the screen
 201  *  and log (if $die_if_no_match is TRUE). Note there is an option to allow case insensitive
 202  *  matching; if this option is chosen, it will first attempt a case sensitive match and if this
 203  *  fails, then attempt a case insensitive match.
 204  * The second option is done by checking against a regex expression, which would use as a string
 205  *  in $whitelist_items (for example, 'a-zA-Z0-9_'). If $die_if_no_match is true, then will die
 206  *  if any illegal characters are found. If $die_if_no_match is false, then will remove the illegal
 207  *  characters and send back string of only the legal characters.
 208  * The first option is ideal if all the possible identifiers are known, however we realize this
 209  *  may not always be the case.
 210  *
 211  * @param   string       $s                Sql identifier variable to be escaped/sanitized.
 212  * @param   array/string $whitelist_items  Items used in whitelisting method (See function description for details of whitelisting method).
 213  *                                          Standard use is to use a array. If use a string, then should be regex expression of allowed
 214  *                                          characters (for example 'a-zA-Z0-9_').
 215  * @param   boolean      $die_if_no_match  If there is no match in the whitelist, then die and echo an error to screen and log.
 216  * @param   boolean      $case_sens_match  Use case sensitive match (this is default).
 217  * @return  string                         Escaped/sanitized sql identifier variable.
 218  */
 219 function escape_identifier($s, $whitelist_items, $die_if_no_match = false, $case_sens_match = true)
 220 {
 221     if (is_array($whitelist_items)) {
 222         // Only return an item within the whitelist_items
 223         $ok = $whitelist_items;
 224         // First, search for case sensitive match
 225         $key = array_search($s, $ok);
 226         if ($key === false) {
 227             // No match
 228             if (!$case_sens_match) {
 229                 // Attempt a case insensitive match
 230                 $ok_UPPER = array_map("strtoupper", $ok);
 231                 $key = array_search(strtoupper($s), $ok_UPPER);
 232             }
 233
 234             if ($key === false) {
 235                 // Still no match
 236                 if ($die_if_no_match) {
 237                     // No match and $die_if_no_match is set, so die() and send error messages to screen and log
 238                     error_Log("ERROR: OpenEMR SQL Escaping ERROR of the following string: " . errorLogEscape($s), 0);
 239                     die("<br /><span style='color:red;font-weight:bold;'>" . xlt("There was an OpenEMR SQL Escaping ERROR of the following string") . " " . text($s) . "</span><br />");
 240                 } else {
 241                     // Return first token since no match
 242                     $key = 0;
 243                 }
 244             }
 245         }
 246
 247         return $ok[$key];
 248     } else {
 249         if ($die_if_no_match) {
 250             if (preg_match('/[^' . $whitelist_items . ']/', $s)) {
 251                 // Contains illegal character and $die_if_no_match is set, so die() and send error messages to screen and log
 252                 error_Log("ERROR: OpenEMR SQL Escaping ERROR of the following string: " . errorLogEscape($s), 0);
 253                 die("<br /><span style='color:red;font-weight:bold;'>" . xlt("There was an OpenEMR SQL Escaping ERROR of the following string") . " " . text($s) . "</span><br />");
 254             } else {
 255                 // Contains all legal characters, so return the legal string
 256                 return $s;
 257             }
 258         } else {
 259             // Since not using $die_if_no_match, then will remove the illegal characters and send back a legal string
 260             return preg_replace('/[^' . $whitelist_items . ']/', '', $s);
 261         }
 262     }
 263 }
 264
 265 /**
 266  * (Note this function is deprecated for new scripts and is only utilized to support legacy scripts)
 267  * Function to manage POST, GET, and REQUEST variables.
 268  *
 269  * @param string $name name of the variable requested.
 270  * @param string $type 'P', 'G' for post or get data, otherwise uses request.
 271  * @param bool $istrim whether to use trim() on the data.
 272  * @return string variable requested, or empty string
 273  */
 274 function formData($name, $type = 'P', $isTrim = false)
 275 {
 276     if ($type == 'P') {
 277         $s = isset($_POST[$name]) ? $_POST[$name] : '';
 278     } elseif ($type == 'G') {
 279         $s = isset($_GET[$name]) ? $_GET[$name] : '';
 280     } else {
 281         $s = isset($_REQUEST[$name]) ? $_REQUEST[$name] : '';
 282     }
 283
 284     return formDataCore($s, $isTrim);
 285 }
 286
 287 /**
 288  * (Note this function is deprecated for new scripts and is only utilized to support legacy scripts)
 289  * NEED TO KEEP THIS FUNCTION TO ENSURE LEGACY FORMS ARE SUPPORTED
 290  * Core function that will be called by formData.
 291  * Note it can also be called directly if preparing
 292  * normal variables (not GET,POST, or REQUEST)
 293  *
 294  * @param string $s
 295  * @param bool $istrim whether to use trim() on the data.
 296  * @return string
 297  */
 298 function formDataCore($s, $isTrim = false)
 299 {
 300     //trim if selected
 301     if ($isTrim) {
 302         $s = trim($s);
 303     }
 304
 305     //add escapes for safe database insertion
 306     $s = add_escape_custom($s);
 307     return $s;
 308 }