library/formdata.inc.php

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