3 * Functions to globally validate and prepare data for sql database insertion.
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
16 * Escape a parameter to prepare for a sql query.
18 * @param string $s Parameter to be escaped.
19 * @return string Escaped parameter.
21 function add_escape_custom($s)
23 //prepare for safe mysql insertion
24 $s = mysqli_real_escape_string($GLOBALS['dbh'], $s);
29 * Escape a sql limit variable to prepare for a sql query.
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).
37 * @param string $s Limit variable to be escaped.
38 * @return string Escaped limit variable.
40 function escape_limit($s)
42 //prepare for safe mysql insertion
48 * Escape/sanitize a sql sort order keyword variable to prepare for a sql query.
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
54 * @param string $s Sort order keyword variable to be escaped/sanitized.
55 * @return string Escaped sort order keyword variable.
57 function escape_sort_order($s)
59 return escape_identifier(strtolower($s), array("asc","desc"));
63 * If parameter string contains comma(,) delimeter
64 * Splits parameter string into an array, using comma(,) as delimeter
65 * else it returns original string
67 * @param string $s string to be processed
68 * @return array $columns an array formed by spliting $s with comma(,) delimeter
71 function process_cols_escape($s)
73 //returns an array of columns
74 $columns = explode(",", $s);
75 if (count($columns) > 1) {
83 * Escape/sanitize a table sql column name for a sql query..
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)
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.
96 function escape_sql_column_name($s, $tables, $long = false)
98 // If $s is asterisk return asterisk to select all columns
103 // If $s is an array process then use recursion to check each column
105 $multiple_columns = [];
106 foreach ($s as $column) {
107 $multiple_columns[] = escape_sql_column_name(trim($column), $tables);
109 return implode(", ", $multiple_columns);
112 // If the $tables is empty, then process them all
113 if (empty($tables)) {
114 $res = sqlStatementNoLog("SHOW TABLES");
116 while ($row=sqlFetchArray($res)) {
117 $keys_return = array_keys($row);
118 $tables[]=$row[$keys_return[0]];
122 // First need to escape the $tables
123 $tables_escaped = array();
124 foreach ($tables as $table) {
125 $tables_escaped[] = escape_table_name($table);
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)) {
134 $columns_options[]=$table_escaped.".".$row['Field'];
136 $columns_options[]=$row['Field'];
141 // Now can escape(via whitelisting) the sql column name
142 return escape_identifier($s, $columns_options, true);
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.
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.
165 function escape_table_name($s)
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]];
174 // Now can escape(via whitelisting) the sql table name
175 return escape_identifier($s, $tables_array, true, false);
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.
183 * @param string $s sql table name variable to be escaped/sanitized.
184 * @return string Escaped table name variable.
186 function mitigateSqlTableUpperCase($s)
188 return escape_table_name($s);
192 * Escape/sanitize a sql identifier variable to prepare for a sql query.
194 * This will escape/sanitize a sql identifier. There are two options provided by this
196 * The first option is done by whitelisting ($whitelist_items is array) 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 checking against a regex expression, which would use as a string
204 * in $whitelist_items (for example, 'a-zA-Z0-9_'). If $die_if_no_match is true, then will die
205 * if any illegal characters are found. If $die_if_no_match is false, then will remove the illegal
206 * characters and send back string of only the legal characters.
207 * The first option is ideal if all the possible identifiers are known, however we realize this
208 * may not always be the case.
210 * @param string $s Sql identifier variable to be escaped/sanitized.
211 * @param array/string $whitelist_items Items used in whitelisting method (See function description for details of whitelisting method).
212 * Standard use is to use a array. If use a string, then should be regex expression of allowed
213 * characters (for example 'a-zA-Z0-9_').
214 * @param boolean $die_if_no_match If there is no match in the whitelist, then die and echo an error to screen and log.
215 * @param boolean $case_sens_match Use case sensitive match (this is default).
216 * @return string Escaped/sanitized sql identifier variable.
218 function escape_identifier($s, $whitelist_items, $die_if_no_match = false, $case_sens_match = true)
220 if (is_array($whitelist_items)) {
221 // Only return an item within the whitelist_items
222 $ok = $whitelist_items;
223 // First, search for case sensitive match
224 $key = array_search($s, $ok);
225 if ($key === false) {
227 if (!$case_sens_match) {
228 // Attempt a case insensitive match
229 $ok_UPPER = array_map("strtoupper", $ok);
230 $key = array_search(strtoupper($s), $ok_UPPER);
233 if ($key === false) {
235 if ($die_if_no_match) {
236 // No match and $die_if_no_match is set, so die() and send error messages to screen and log
237 error_Log("ERROR: OpenEMR SQL Escaping ERROR of the following string: " . errorLogEscape($s), 0);
238 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 // Return first token since no match
248 if ($die_if_no_match) {
249 if (preg_match('/[^' . $whitelist_items . ']/', $s)) {
250 // Contains illegal character and $die_if_no_match is set, so die() and send error messages to screen and log
251 error_Log("ERROR: OpenEMR SQL Escaping ERROR of the following string: " . errorLogEscape($s), 0);
252 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 // Contains all legal characters, so return the legal string
258 // Since not using $die_if_no_match, then will remove the illegal characters and send back a legal string
259 return preg_replace('/[^' . $whitelist_items . ']/', '', $s);
265 * (Note this function is deprecated for new scripts and is only utilized to support legacy scripts)
266 * Function to manage POST, GET, and REQUEST variables.
268 * @param string $name name of the variable requested.
269 * @param string $type 'P', 'G' for post or get data, otherwise uses request.
270 * @param bool $istrim whether to use trim() on the data.
271 * @return string variable requested, or empty string
273 function formData($name, $type = 'P', $isTrim = false)
276 $s = isset($_POST[$name]) ?
$_POST[$name] : '';
277 } else if ($type == 'G') {
278 $s = isset($_GET[$name]) ?
$_GET[$name] : '';
280 $s = isset($_REQUEST[$name]) ?
$_REQUEST[$name] : '';
283 return formDataCore($s, $isTrim);
287 * (Note this function is deprecated for new scripts and is only utilized to support legacy scripts)
288 * NEED TO KEEP THIS FUNCTION TO ENSURE LEGACY FORMS ARE SUPPORTED
289 * Core function that will be called by formData.
290 * Note it can also be called directly if preparing
291 * normal variables (not GET,POST, or REQUEST)
294 * @param bool $istrim whether to use trim() on the data.
297 function formDataCore($s, $isTrim = false)
304 //add escapes for safe database insertion
305 $s = add_escape_custom($s);