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      http://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  * Escape/sanitize a table sql column name for a sql query..
  64  *
  65  * This will escape/sanitize the sql column name for a sql query. It is done by whitelisting
  66  * all of the current sql column names in the openemr database from a table(s). Note that if
  67  * there is no match, then it will die() and a error message will be sent to the screen and
  68  * the error log. This function should not be used for escaping tables outside the openemr
  69  * database (should use escape_identifier() function below for that scenario)
  70  *
  71  * @param   string        $s       sql column name variable to be escaped/sanitized.
  72  * @param   array         $tables  The table(s) that the sql columns is from (in an array).
  73  * @param   boolean       $long    Use long form (ie. table.colname) vs short form (ie. colname).
  74  * @return  string                 Escaped table name variable.
  75  */
  76 function escape_sql_column_name($s, $tables, $long = false)
  77 {
  78
  79       // If the $tables is empty, then process them all
  80     if (empty($tables)) {
  81         $res = sqlStatementNoLog("SHOW TABLES");
  82         $tables = array();
  83         while ($row=sqlFetchArray($res)) {
  84             $keys_return = array_keys($row);
  85             $tables[]=$row[$keys_return[0]];
  86         }
  87     }
  88
  89       // First need to escape the $tables
  90       $tables_escaped = array();
  91     foreach ($tables as $table) {
  92         $tables_escaped[] = escape_table_name($table);
  93     }
  94
  95       // Collect all the possible sql columns from the tables
  96       $columns_options = array();
  97     foreach ($tables_escaped as $table_escaped) {
  98         $res = sqlStatementNoLog("SHOW COLUMNS FROM ".$table_escaped);
  99         while ($row=sqlFetchArray($res)) {
 100             if ($long) {
 101                 $columns_options[]=$table_escaped.".".$row['Field'];
 102             } else {
 103                 $columns_options[]=$row['Field'];
 104             }
 105         }
 106     }
 107
 108       // Now can escape(via whitelisting) the sql column name
 109       return escape_identifier($s, $columns_options, true);
 110 }
 111
 112 /**
 113  * Escape/sanitize a table name for a sql query. This function can also can be used to
 114  * process tables that contain any upper case letters.
 115  *
 116  * This will escape/sanitize the table name for a sql query. It is done by whitelisting
 117  * all of the current tables in the openemr database. The matching is not case sensitive,
 118  * although it will attempt a case sensitive match before proceeding to a case insensitive
 119  * match (see below escape_identifier() function for more details on this). Note that if
 120  * there is no match, then it will die() and a error message will be sent to the screen
 121  * and the error log. This function should not be used for escaping tables outside the
 122  * openemr database (should use escape_identifier() function below for that scenario).
 123  * Another use of this function is to deal with casing issues that arise in tables that
 124  * contain upper case letter(s) (these tables can be huge issues when transferring databases
 125  * from Windows to Linux and vice versa); this function can avoid this issues if run the
 126  * table name through this function (To avoid confusion, there is a wrapper function
 127  * entitled mitigateSqlTableUpperCase() that is used when just need to mitigate casing
 128  * for table names that contain any uppercase letters).
 129  * @param   string $s  sql table name variable to be escaped/sanitized.
 130  * @return  string     Escaped table name variable.
 131  */
 132 function escape_table_name($s)
 133 {
 134       $res = sqlStatementNoLog("SHOW TABLES");
 135       $tables_array = array();
 136     while ($row=sqlFetchArray($res)) {
 137         $keys_return = array_keys($row);
 138         $tables_array[]=$row[$keys_return[0]];
 139     }
 140
 141       // Now can escape(via whitelisting) the sql table name
 142       return escape_identifier($s, $tables_array, true, false);
 143 }
 144
 145 /**
 146  * Process tables that contain any upper case letters; this is simple a wrapper function of
 147  * escape_table_name() above when using it for the sole purpose of mitigating sql table names
 148  * that contain upper case letters.
 149  *
 150  * @param   string $s  sql table name variable to be escaped/sanitized.
 151  * @return  string     Escaped table name variable.
 152  */
 153 function mitigateSqlTableUpperCase($s)
 154 {
 155     return escape_table_name($s);
 156 }
 157
 158 /**
 159  * Escape/sanitize a sql identifier variable to prepare for a sql query.
 160  *
 161  * This will escape/sanitize a sql identifier. There are two options provided by this
 162  * function.
 163  * The first option is done by whitelisting ($whitelist_items is used) and in this case
 164  * only certain identifiers (listed in the $whitelist_items array) can be used; if
 165  * there is no match, then it will either default to the first item in the $whitelist_items
 166  * (if $die_if_no_match is FALSE) or it will die() and send an error message to the screen
 167  * and log (if $die_if_no_match is TRUE). Note there is an option to allow case insensitive
 168  * matching; if this option is chosen, it will first attempt a case sensitive match and if this
 169  * fails, then attempt a case insensitive match.
 170  * The second option is done by sanitizing ($whitelist_items is not used) and in this case
 171  * only US alphanumeric,'_' and '.' items are kept in the returned string. Note
 172  * the second option is still experimental as we figure out the ideal items to
 173  * filter out of the identifier. The first option is ideal if all the possible identifiers
 174  * are known, however we realize this may not always be the case.
 175  *
 176  * @param   string   $s                Sql identifier variable to be escaped/sanitized.
 177  * @param   array    $whitelist_items  Items used in whitelisting method (See function description for details of whitelisting method).
 178  * @param   boolean  $die_if_no_match  If there is no match in the whitelist, then die and echo an error to screen and log.
 179  * @param   boolean  $case_sens_match  Use case sensitive match (this is default).
 180  * @return  string                     Escaped/sanitized sql identifier variable.
 181  */
 182 function escape_identifier($s, $whitelist_items, $die_if_no_match = false, $case_sens_match = true)
 183 {
 184     if (is_array($whitelist_items)) {
 185         // Only return an item within the whitelist_items
 186         $ok = $whitelist_items;
 187         // First, search for case sensitive match
 188         $key = array_search($s, $ok);
 189         if ($key === false) {
 190             // No match
 191             if (!$case_sens_match) {
 192                 // Attempt a case insensitive match
 193                 $ok_UPPER = array_map("strtoupper", $ok);
 194                 $key = array_search(strtoupper($s), $ok_UPPER);
 195             }
 196
 197             if ($key === false) {
 198                 // Still no match
 199                 if ($die_if_no_match) {
 200                     // No match and $die_if_no_match is set, so die() and send error messages to screen and log
 201                     error_Log("ERROR: OpenEMR SQL Escaping ERROR of the following string: ".$s, 0);
 202                     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>");
 203                 } else {
 204                     // Return first token since no match
 205                     $key = 0;
 206                 }
 207             }
 208         }
 209
 210         return $ok[$key];
 211     } else {
 212         // Return an item that has been "cleaned" up
 213         // (this is currently experimental and goal is to avoid using this)
 214         return preg_replace('/[^a-zA-Z0-9_.]/', '', $s);
 215     }
 216 }
 217
 218 /**
 219  * (Note this function is deprecated for new scripts and is only utilized to support legacy scripts)
 220  * Function to manage POST, GET, and REQUEST variables.
 221  *
 222  * @param string $name name of the variable requested.
 223  * @param string $type 'P', 'G' for post or get data, otherwise uses request.
 224  * @param bool $istrim whether to use trim() on the data.
 225  * @return string variable requested, or empty string
 226  */
 227 function formData($name, $type = 'P', $isTrim = false)
 228 {
 229     if ($type == 'P') {
 230         $s = isset($_POST[$name]) ? $_POST[$name] : '';
 231     } else if ($type == 'G') {
 232         $s = isset($_GET[$name]) ? $_GET[$name] : '';
 233     } else {
 234         $s = isset($_REQUEST[$name]) ? $_REQUEST[$name] : '';
 235     }
 236
 237     return formDataCore($s, $isTrim);
 238 }
 239
 240 /**
 241  * (Note this function is deprecated for new scripts and is only utilized to support legacy scripts)
 242  * NEED TO KEEP THIS FUNCTION TO ENSURE LEGACY FORMS ARE SUPPORTED
 243  * Core function that will be called by formData.
 244  * Note it can also be called directly if preparing
 245  * normal variables (not GET,POST, or REQUEST)
 246  *
 247  * @param string $s
 248  * @param bool $istrim whether to use trim() on the data.
 249  * @return string
 250  */
 251 function formDataCore($s, $isTrim = false)
 252 {
 253       //trim if selected
 254     if ($isTrim) {
 255         $s = trim($s);
 256     }
 257
 258       //add escapes for safe database insertion
 259       $s = add_escape_custom($s);
 260       return $s;
 261 }