3 * Sql functions/classes for OpenEMR.
5 * Includes classes and functions that OpenEMR uses
6 * to interact with SQL.
9 * @link http://www.open-emr.org
10 * @author Brady Miller <brady.g.miller@gmail.com>
11 * @author Kevin Yeh <kevin.y@integralemr.com>
12 * @copyright Copyright (c) 2019 Brady Miller <brady.g.miller@gmail.com>
13 * @copyright Copyright (c) 2013 Kevin Yeh <kevin.y@integralemr.com>
14 * @copyright Copyright (c) 2013 OEMR <www.oemr.org>
15 * @license https://github.com/openemr/openemr/blob/master/LICENSE GNU General Public License 3
19 require_once(dirname(__FILE__) . "/sqlconf.php");
20 require_once(dirname(__FILE__) . "/../vendor/adodb/adodb-php/adodb.inc.php");
21 require_once(dirname(__FILE__) . "/../vendor/adodb/adodb-php/drivers/adodb-mysqli.inc.php");
22 require_once(dirname(__FILE__) . "/ADODB_mysqli_log.php");
24 if (!defined('ADODB_FETCH_ASSOC')) {
25 define('ADODB_FETCH_ASSOC', 2);
28 $database = NewADOConnection("mysqli_log"); // Use the subclassed driver which logs execute events
29 // Below optionFlags flag is telling the mysql connection to ensure local_infile setting,
30 // which is needed to import data in the Administration->Other->External Data Loads feature.
31 // (Note the MYSQLI_READ_DEFAULT_GROUP is just to keep the current setting hard-coded in adodb)
32 $database->optionFlags = array(array(MYSQLI_READ_DEFAULT_GROUP,0), array(MYSQLI_OPT_LOCAL_INFILE,1));
33 // Set mysql to use ssl, if applicable.
34 // Can support basic encryption by including just the mysql-ca pem (this is mandatory for ssl)
35 // Can also support client based certificate if also include mysql-cert and mysql-key (this is optional for ssl)
36 if (file_exists($GLOBALS['OE_SITE_DIR'] . "/documents/certificates/mysql-ca")) {
37 if (defined('MYSQLI_CLIENT_SSL')) {
38 $database->clientFlags = MYSQLI_CLIENT_SSL;
41 $database->port = $port;
42 $database->PConnect($host, $login, $pass, $dbase);
43 $GLOBALS['adodb']['db'] = $database;
44 $GLOBALS['dbh'] = $database->_connectionID;
46 // Modified 5/2009 by BM for UTF-8 project ---------
47 if (!$disable_utf8_flag) {
48 $success_flag = $database->ExecuteNoLog("SET NAMES 'utf8'");
50 error_log("PHP custom error: from openemr library/sql.inc - Unable to set up UTF8 encoding with mysql database: " . errorLogEscape(getSqlLastError()), 0);
54 // Turn off STRICT SQL
55 $sql_strict_set_success = $database->ExecuteNoLog("SET sql_mode = ''");
56 if (!$sql_strict_set_success) {
57 error_log("Unable to set strict sql setting: " . errorLogEscape(getSqlLastError()), 0);
60 // set up associations in adodb calls (not sure why above define
61 // command does not work)
62 $GLOBALS['adodb']['db']->SetFetchMode(ADODB_FETCH_ASSOC);
64 if ($GLOBALS['debug_ssl_mysql_connection']) {
65 error_log("CHECK SSL CIPHER IN MAIN ADODB: " . errorLogEscape(print_r($GLOBALS['adodb']['db']->ExecuteNoLog("SHOW STATUS LIKE 'Ssl_cipher';")->fields, true)));
68 //fmg: This makes the login screen informative when no connection can be made
69 if (!$GLOBALS['dbh']) {
70 //try to be more helpful
71 if ($host == "localhost") {
72 echo "Check that mysqld is running.<p>";
74 echo "Check that you can ping the server " . text($host) . ".<p>";
76 HelpfulDie("Could not connect to server!", getSqlLastError());
81 * Standard sql query in OpenEMR.
83 * Function that will allow use of the adodb binding
84 * feature to prevent sql-injection. Will continue to
85 * be compatible with previous function calls that do
87 * It will return a recordset object.
88 * The sqlFetchArray() function should be used to
89 * utilize the return object.
91 * @param string $statement query
92 * @param array $binds binded variables array (optional)
95 function sqlStatement($statement, $binds = false)
97 // Below line is to avoid a nasty bug in windows.
102 // Use adodb Execute with binding and return a recordset.
103 // Note that the auditSQLEvent function is embedded
104 // in the Execute command.
105 $recordset = $GLOBALS['adodb']['db']->Execute($statement, $binds);
106 if ($recordset === false) {
107 HelpfulDie("query failed: $statement", getSqlLastError());
114 * Specialized sql query in OpenEMR that skips auditing.
116 * Function that will allow use of the adodb binding
117 * feature to prevent sql-injection. Will continue to
118 * be compatible with previous function calls that do
119 * not use binding. It is equivalent to the
120 * sqlStatement() function, EXCEPT it skips the
121 * audit engine. This function should only be used
122 * in very special situations.
123 * It will return a recordset object.
124 * The sqlFetchArray() function should be used to
125 * utilize the return object.
127 * @param string $statement query
128 * @param array $binds binded variables array (optional)
131 function sqlStatementNoLog($statement, $binds = false)
133 // Below line is to avoid a nasty bug in windows.
138 // Use adodb ExecuteNoLog with binding and return a recordset.
139 $recordset = $GLOBALS['adodb']['db']->ExecuteNoLog($statement, $binds);
140 if ($recordset === false) {
141 HelpfulDie("query failed: $statement", getSqlLastError());
148 * sqlStatement() function wrapper for CDR engine in OpenEMR.
149 * Allows option to turn on/off auditing specifically for the
152 * @param string $statement query
153 * @param array $binds binded variables array (optional)
154 * @return recordset/resource
156 function sqlStatementCdrEngine($statement, $binds = false)
158 // Below line is to avoid a nasty bug in windows.
163 if ($GLOBALS['audit_events_cdr']) {
164 return sqlStatement($statement, $binds);
166 return sqlStatementNoLog($statement, $binds);
171 * Returns a row (as an array) from a sql recordset.
173 * Function that will allow use of the adodb binding
174 * feature to prevent sql-injection.
175 * It will act upon the object returned from the
176 * sqlStatement() function (and sqlQ() function).
178 * @param recordset $r
181 function sqlFetchArray($r)
183 //treat as an adodb recordset
192 //ensure it's an object (ie. is set)
193 if (!is_object($r)) {
197 return $r->FetchRow();
202 * Wrapper for ADODB getAssoc
204 * @see http://adodb.org/dokuwiki/doku.php?id=v5:reference:connection:getassoc
207 * @param string[] $bindvars
208 * @param boolean $forceArray
209 * @param boolean $first2Cols
212 function sqlGetAssoc($sql, $bindvars = false, $forceArray = false, $first2Cols = false)
215 return $GLOBALS['adodb']['db']->getAssoc($sql, $bindvars, $forceArray, $first2Cols);
219 * Standard sql insert query in OpenEMR.
220 * Only use this function if you need to have the
221 * id returned. If doing an insert query and do
222 * not need the id returned, then use the
223 * sqlStatement function instead.
225 * Function that will allow use of the adodb binding
226 * feature to prevent sql-injection. This function
227 * is specialized for insert function and will return
228 * the last id generated from the insert.
230 * @param string $statement query
231 * @param array $binds binded variables array (optional)
232 * @return integer Last id generated from the sql insert command
234 function sqlInsert($statement, $binds = false)
236 // Below line is to avoid a nasty bug in windows.
241 //Run a adodb execute
242 // Note the auditSQLEvent function is embedded in the
244 $recordset = $GLOBALS['adodb']['db']->Execute($statement, $binds, true);
245 if ($recordset === false) {
246 HelpfulDie("insert failed: $statement", getSqlLastError());
249 // Return the correct last id generated using function
250 // that is safe with the audit engine.
251 return $GLOBALS['lastidado'] > 0 ? $GLOBALS['lastidado'] : $GLOBALS['adodb']['db']->Insert_ID();
255 * Specialized sql query in OpenEMR that only returns
256 * the first row of query results as an associative array.
258 * Function that will allow use of the adodb binding
259 * feature to prevent sql-injection.
261 * @param string $statement query
262 * @param array $binds binded variables array (optional)
265 function sqlQuery($statement, $binds = false)
267 // Below line is to avoid a nasty bug in windows.
272 $recordset = $GLOBALS['adodb']['db']->Execute($statement, $binds);
274 if ($recordset === false) {
275 HelpfulDie("query failed: $statement", getSqlLastError());
278 if ($recordset->EOF) {
282 $rez = $recordset->FetchRow();
291 * Specialized sql query in OpenEMR that bypasses the auditing engine
292 * and only returns the first row of query results as an associative array.
294 * Function that will allow use of the adodb binding
295 * feature to prevent sql-injection. It is equivalent to the
296 * sqlQuery() function, EXCEPT it skips the
297 * audit engine. This function should only be used
298 * in very special situations.
300 * @param string $statement query
301 * @param array $binds binded variables array (optional)
304 function sqlQueryNoLog($statement, $binds = false)
306 // Below line is to avoid a nasty bug in windows.
311 $recordset = $GLOBALS['adodb']['db']->ExecuteNoLog($statement, $binds);
313 if ($recordset === false) {
314 HelpfulDie("query failed: $statement", getSqlLastError());
317 if ($recordset->EOF) {
321 $rez = $recordset->FetchRow();
330 * Specialized sql query in OpenEMR that ignores sql errors, bypasses the
331 * auditing engine and only returns the first row of query results as an
334 * Function that will allow use of the adodb binding
335 * feature to prevent sql-injection. It is equivalent to the
336 * sqlQuery() function, EXCEPT it skips the
337 * audit engine and ignores erros. This function should only be used
338 * in very special situations.
340 * @param string $statement query
341 * @param array $binds binded variables array (optional)
344 function sqlQueryNoLogIgnoreError($statement, $binds = false)
346 // Below line is to avoid a nasty bug in windows.
351 $recordset = $GLOBALS['adodb']['db']->ExecuteNoLog($statement, $binds);
353 if ($recordset === false) {
354 // ignore the error and return FALSE
358 if ($recordset->EOF) {
362 $rez = $recordset->FetchRow();
371 * sqlQuery() function wrapper for CDR engine in OpenEMR.
372 * Allows option to turn on/off auditing specifically for the
375 * @param string $statement query
376 * @param array $binds binded variables array (optional)
379 function sqlQueryCdrEngine($statement, $binds = false)
381 // Below line is to avoid a nasty bug in windows.
386 if ($GLOBALS['audit_events_cdr']) {
387 return sqlQuery($statement, $binds);
389 return sqlQueryNoLog($statement, $binds);
394 * Specialized sql query in OpenEMR that skips auditing.
396 * This function should only be used in very special situations.
398 * @param string $statement query
400 function sqlInsertClean_audit($statement)
403 $ret = $GLOBALS['adodb']['db']->ExecuteNoLog($statement);
404 if ($ret === false) {
405 HelpfulDie("insert failed: $statement", getSqlLastError());
410 * Function that will safely return the last error,
411 * and accounts for the audit engine.
413 * @param string $mode either adodb(default) or native_mysql
414 * @return string last mysql error
416 function getSqlLastError()
418 return !empty($GLOBALS['last_mysql_error']) ? $GLOBALS['last_mysql_error'] : $GLOBALS['adodb']['db']->ErrorMsg();
422 * Function that will safely return the last error no,
423 * and accounts for the audit engine.
425 * @param string $mode either adodb(default) or native_mysql
426 * @return string last mysql error no
428 function getSqlLastErrorNo()
430 return !empty($GLOBALS['last_mysql_error_no']) ? $GLOBALS['last_mysql_error_no'] : $GLOBALS['adodb']['db']->ErrorNo();
434 * Function that will return an array listing
435 * of columns that exist in a table.
437 * @param string $table sql table
440 function sqlListFields($table)
442 $sql = "SHOW COLUMNS FROM ". add_escape_custom($table);
443 $resource = sqlQ($sql);
444 $field_list = array();
445 while ($row = sqlFetchArray($resource)) {
446 $field_list[] = $row['Field'];
453 * Returns the number of sql rows
455 * @param recordset $r
456 * @return integer Number of rows
458 function sqlNumRows($r)
460 return $r->RecordCount();
464 * Error function for OpenEMR sql functions
466 * @param string $statement
467 * @param string $sqlerr
469 function HelpfulDie($statement, $sqlerr = '')
472 echo "<h2><font color='red'>" . xlt('Query Error') . "</font></h2>";
474 if (!$GLOBALS['sql_string_no_show_screen']) {
475 echo "<p><font color='red'>ERROR:</font> " . text($statement) . "</p>";
478 $logMsg="SQL Error with statement:".$statement;
481 if (!$GLOBALS['sql_string_no_show_screen']) {
482 echo "<p>Error: <font color='red'>" . text($sqlerr) . "</font></p>";
485 $logMsg.="--".$sqlerr;
488 $backtrace = debug_backtrace();
490 if (!$GLOBALS['sql_string_no_show_screen']) {
491 for ($level = 1; $level < count($backtrace); $level++) {
492 $info = $backtrace[$level];
493 echo "<br>" . text($info["file"] . " at " . $info["line"] . ":" . $info["function"]);
495 echo "(" . text(implode(",", $info["args"])) . ")";
500 $logMsg.="==>".$backtrace[1]["file"]." at ".$backtrace[1]["line"].":".$backtrace[1]["function"];
502 error_log(errorLogEscape($logMsg));
508 * @todo document use of the generate_id function
510 function generate_id()
512 $database = $GLOBALS['adodb']['db'];
513 return $database->GenID("sequences");
517 * Deprecated function. Standard sql query in OpenEMR.
519 * Function that will allow use of the adodb binding
520 * feature to prevent sql-injection. Will continue to
521 * be compatible with previous function calls that do
523 * It will return a recordset object.
524 * The sqlFetchArray() function should be used to
525 * utilize the return object.
528 * @param string $statement query
529 * @param array $binds binded variables array (optional)
532 function sqlQ($statement, $binds = false)
534 // Below line is to avoid a nasty bug in windows.
539 $recordset = $GLOBALS['adodb']['db']->Execute($statement, $binds) or
540 HelpfulDie("query failed: $statement", getSqlLastError());
546 * Sql close connection function (deprecated)
548 * No longer needed since PHP does this automatically.
555 //----------Close our mysql connection
556 $closed = $GLOBALS['adodb']['db']->close or
557 HelpfulDie("could not disconnect from mysql server link", getSqlLastError());
562 * Very simple wrapper function and not necessary (deprecated)
571 return $GLOBALS['adodb']['db'];
575 * Generic mysql select db function
576 * Used when converted to mysqli to centralize special circumstances.
577 * @param string $database
579 function generic_sql_select_db($database, $link = null)
581 if (is_null($link)) {
582 $link = $GLOBALS['dbh'];
585 mysqli_select_db($link, $database);
589 * Generic mysql affected rows function
590 * Used when converted to mysqli to centralize special circumstances.
593 function generic_sql_affected_rows()
595 return mysqli_affected_rows($GLOBALS['dbh']);
599 * Generic mysql insert id function
600 * Used when converted to mysqli to centralize special circumstances.
603 function generic_sql_insert_id()
605 return mysqli_insert_id($GLOBALS['dbh']);
610 * Begin a Transaction.
612 function sqlBeginTrans()
614 $GLOBALS['adodb']['db']->BeginTrans();
619 * Commit a transaction
621 function sqlCommitTrans($ok = true)
623 $GLOBALS['adodb']['db']->CommitTrans();
628 * Rollback a transaction
630 function sqlRollbackTrans()
632 $GLOBALS['adodb']['db']->RollbackTrans();
636 * For the 3 functions below:
638 * To support an optional higher level of security, queries that access password
639 * related information use these functions instead of the standard functions
640 * provided by sql.inc.
642 * By default, the privQuery and privStatement calls pass-through to
643 * the existing ADODB instance initialized by sql.inc.
645 * If an additional configuration file is created (secure_sqlconf.php) and saved
646 * in the sites/<sitename> directory (e.g. sites/default). The MySQL login
647 * information defined in that file as $secure_* will be used to create an ADODB
648 * instance specifically for querying privileged information.
650 * By configuring a server in this way, the default MySQL user can be denied access
651 * to sensitive tables (currently only "users_secure" would qualify). Thus
652 * the likelyhood of unintended modification can be reduced (e.g. through SQL Injection).
654 * Details on how to set this up are included in Documentation/privileged_db/priv_db_HOWTO
656 * The trade off for this additional security is extra complexity in configuration and
657 * maintenance of the database, hence it is not enabled at install time and must be
663 if (!isset($GLOBALS['PRIV_DB'])) {
664 $secure_config=$GLOBALS['OE_SITE_DIR'] . "/secure_sqlconf.php";
665 if (file_exists($secure_config)) {
666 require_once($secure_config);
667 $GLOBALS['PRIV_DB']=NewADOConnection("mysqli_log"); // Use the subclassed driver which logs execute events
668 // Below optionFlags flag is telling the mysql connection to ensure local_infile setting,
669 // which is needed to import data in the Administration->Other->External Data Loads feature.
670 // (Note the MYSQLI_READ_DEFAULT_GROUP is just to keep the current setting hard-coded in adodb)
671 $GLOBALS['PRIV_DB']->optionFlags = array(array(MYSQLI_READ_DEFAULT_GROUP,0), array(MYSQLI_OPT_LOCAL_INFILE,1));
672 // Set mysql to use ssl, if applicable.
673 // Can support basic encryption by including just the mysql-ca pem (this is mandatory for ssl)
674 // Can also support client based certificate if also include mysql-cert and mysql-key (this is optional for ssl)
675 if (file_exists($GLOBALS['OE_SITE_DIR'] . "/documents/certificates/mysql-ca")) {
676 if (defined('MYSQLI_CLIENT_SSL')) {
677 $GLOBALS['PRIV_DB']->clientFlags = MYSQLI_CLIENT_SSL;
680 $GLOBALS['PRIV_DB']->port = $port;
681 $GLOBALS['PRIV_DB']->PConnect($secure_host, $secure_login, $secure_pass, $secure_dbase);
682 // set up associations in adodb calls
683 $GLOBALS['PRIV_DB']->SetFetchMode(ADODB_FETCH_ASSOC);
684 // debug hook for ssl stuff
685 if ($GLOBALS['debug_ssl_mysql_connection']) {
686 error_log("CHECK SSL CIPHER IN PRIV_DB ADODB: " . errorLogEscape(print_r($GLOBALS[PRIV_DB]->ExecuteNoLog("SHOW STATUS LIKE 'Ssl_cipher';")->fields), true));
689 $GLOBALS['PRIV_DB']=$GLOBALS['adodb']['db'];
693 return $GLOBALS['PRIV_DB'];
696 * mechanism to use "super user" for SQL queries related to password operations
699 * @param type $params
702 function privStatement($sql, $params = null)
704 if (is_array($params)) {
705 $recordset = getPrivDB()->ExecuteNoLog($sql, $params);
707 $recordset = getPrivDB()->ExecuteNoLog($sql);
710 if ($recordset === false) {
711 // These error messages are explictly NOT run through xl() because we still
712 // need them if there is a database problem.
713 echo "Failure during database access! Check server error log.";
714 $backtrace=debug_backtrace();
716 error_log("Executing as user:" . errorLogEscape(getPrivDB()->user) . " Statement failed:" . errorLogEscape($sql) . ":" . errorLogEscape($GLOBALS['last_mysql_error'])
717 . "==>" . errorLogEscape($backtrace[1]["file"]) . " at " . errorLogEscape($backtrace[1]["line"]) . ":" . errorLogEscape($backtrace[1]["function"]));
725 * Wrapper for privStatement that just returns the first row of a query or FALSE
726 * if there were no results.
729 * @param type $params
732 function privQuery($sql, $params = null)
734 $recordset=privStatement($sql, $params);
735 if ($recordset->EOF) {
739 $rez = $recordset->FetchRow();