library/formdata.inc.php

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