4 * Sql functions/classes for OpenEMR.
6 * Includes classes and functions that OpenEMR uses
7 * to interact with SQL.
10 * @link http://www.open-emr.org
11 * @author Brady Miller <brady.g.miller@gmail.com>
12 * @author Kevin Yeh <kevin.y@integralemr.com>
13 * @copyright Copyright (c) 2019 Brady Miller <brady.g.miller@gmail.com>
14 * @copyright Copyright (c) 2013 Kevin Yeh <kevin.y@integralemr.com>
15 * @copyright Copyright (c) 2013 OEMR <www.oemr.org>
16 * @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->setConnectionParameter(MYSQLI_READ_DEFAULT_GROUP, 0);
33 $database->setConnectionParameter(MYSQLI_OPT_LOCAL_INFILE, 1);
34 // Set mysql to use ssl, if applicable.
35 // Can support basic encryption by including just the mysql-ca pem (this is mandatory for ssl)
36 // Can also support client based certificate if also include mysql-cert and mysql-key (this is optional for ssl)
37 if (file_exists($GLOBALS['OE_SITE_DIR'] . "/documents/certificates/mysql-ca")) {
38 if (defined('MYSQLI_CLIENT_SSL')) {
40 file_exists($GLOBALS['OE_SITE_DIR'] . "/documents/certificates/mysql-key") &&
41 file_exists($GLOBALS['OE_SITE_DIR'] . "/documents/certificates/mysql-cert")
43 // with client side certificate/key
44 $database->ssl_key = "${GLOBALS['OE_SITE_DIR']}/documents/certificates/mysql-key";
45 $database->ssl_cert = "${GLOBALS['OE_SITE_DIR']}/documents/certificates/mysql-cert";
46 $database->ssl_ca = "${GLOBALS['OE_SITE_DIR']}/documents/certificates/mysql-ca";
48 // without client side certificate/key
49 $database->ssl_ca = "${GLOBALS['OE_SITE_DIR']}/documents/certificates/mysql-ca";
51 $database->clientFlags = MYSQLI_CLIENT_SSL;
54 $database->port = $port;
55 if ((!empty($GLOBALS["enable_database_connection_pooling"]) || !empty($_SESSION["enable_database_connection_pooling"])) && empty($GLOBALS['connection_pooling_off'])) {
56 $database->PConnect($host, $login, $pass, $dbase);
58 $database->connect($host, $login, $pass, $dbase);
60 $GLOBALS['adodb']['db'] = $database;
61 $GLOBALS['dbh'] = $database->_connectionID;
63 // Modified 5/2009 by BM for UTF-8 project ---------
64 if (!$disable_utf8_flag) {
65 if (!empty($sqlconf["db_encoding"]) && ($sqlconf["db_encoding"] == "utf8mb4")) {
66 $success_flag = $database->ExecuteNoLog("SET NAMES 'utf8mb4'");
68 error_log("PHP custom error: from openemr library/sql.inc - Unable to set up UTF8MB4 encoding with mysql database: " . errorLogEscape(getSqlLastError()), 0);
71 $success_flag = $database->ExecuteNoLog("SET NAMES 'utf8'");
73 error_log("PHP custom error: from openemr library/sql.inc - Unable to set up UTF8 encoding with mysql database: " . errorLogEscape(getSqlLastError()), 0);
78 // Turn off STRICT SQL
79 $sql_strict_set_success = $database->ExecuteNoLog("SET sql_mode = ''");
80 if (!$sql_strict_set_success) {
81 error_log("Unable to set strict sql setting: " . errorLogEscape(getSqlLastError()), 0);
84 // set up associations in adodb calls (not sure why above define
85 // command does not work)
86 $GLOBALS['adodb']['db']->SetFetchMode(ADODB_FETCH_ASSOC);
88 if (!empty($GLOBALS['debug_ssl_mysql_connection'])) {
89 error_log("CHECK SSL CIPHER IN MAIN ADODB: " . errorLogEscape(print_r($GLOBALS['adodb']['db']->ExecuteNoLog("SHOW STATUS LIKE 'Ssl_cipher';")->fields, true)));
92 //fmg: This makes the login screen informative when no connection can be made
93 if (!$GLOBALS['dbh']) {
94 //try to be more helpful
95 if ($host == "localhost") {
96 echo "Check that mysqld is running.<p>";
98 echo "Check that you can ping the server " . text($host) . ".<p>";
100 HelpfulDie("Could not connect to server!", getSqlLastError());
105 * Standard sql query in OpenEMR.
107 * Function that will allow use of the adodb binding
108 * feature to prevent sql-injection. Will continue to
109 * be compatible with previous function calls that do
111 * It will return a recordset object.
112 * The sqlFetchArray() function should be used to
113 * utilize the return object.
115 * @param string $statement query
116 * @param array $binds binded variables array (optional)
119 function sqlStatement($statement, $binds = false)
121 // Below line is to avoid a nasty bug in windows.
126 // Use adodb Execute with binding and return a recordset.
127 // Note that the auditSQLEvent function is embedded
128 // in the Execute command.
129 $recordset = $GLOBALS['adodb']['db']->Execute($statement, $binds);
130 if ($recordset === false) {
131 HelpfulDie("query failed: $statement", getSqlLastError());
138 * Standard sql query in OpenEMR.
140 * Function that will allow use of the adodb binding
141 * feature to prevent sql-injection. Will continue to
142 * be compatible with previous function calls that do
144 * It will return a recordset object.
145 * The sqlFetchArray() function should be used to
146 * utilize the return object.
148 * @param string $statement query
149 * @param array $binds binded variables array (optional)
152 function sqlStatementThrowException($statement, $binds = false)
154 // Below line is to avoid a nasty bug in windows.
159 //Run a adodb execute
160 // Note the auditSQLEvent function is embedded in the
162 $recordset = $GLOBALS['adodb']['db']->Execute($statement, $binds, true);
163 if ($recordset === false) {
164 throw new \OpenEMR\Common\Database\SqlQueryException($statement, "Failed to execute statement. Error: " . getSqlLastError() . " Statement: " . $statement);
170 * Return the last inserted sql id for a query.
173 function sqlGetLastInsertId()
175 // Return the correct last id generated using function
176 // that is safe with the audit engine.
177 return $GLOBALS['lastidado'] > 0 ? $GLOBALS['lastidado'] : $GLOBALS['adodb']['db']->Insert_ID();
181 * Specialized sql query in OpenEMR that skips auditing.
183 * Function that will allow use of the adodb binding
184 * feature to prevent sql-injection. Will continue to
185 * be compatible with previous function calls that do
186 * not use binding. It is equivalent to the
187 * sqlStatement() function, EXCEPT it skips the
188 * audit engine. This function should only be used
189 * in very special situations.
190 * It will return a recordset object.
191 * The sqlFetchArray() function should be used to
192 * utilize the return object.
194 * @param string $statement query
195 * @param array $binds binded variables array (optional)
198 function sqlStatementNoLog($statement, $binds = false)
200 // Below line is to avoid a nasty bug in windows.
205 // Use adodb ExecuteNoLog with binding and return a recordset.
206 $recordset = $GLOBALS['adodb']['db']->ExecuteNoLog($statement, $binds);
207 if ($recordset === false) {
208 HelpfulDie("query failed: $statement", getSqlLastError());
215 * sqlStatement() function wrapper for CDR engine in OpenEMR.
216 * Allows option to turn on/off auditing specifically for the
219 * @param string $statement query
220 * @param array $binds binded variables array (optional)
221 * @return recordset/resource
223 function sqlStatementCdrEngine($statement, $binds = false)
225 // Below line is to avoid a nasty bug in windows.
230 if ($GLOBALS['audit_events_cdr']) {
231 return sqlStatement($statement, $binds);
233 return sqlStatementNoLog($statement, $binds);
238 * Returns a row (as an array) from a sql recordset.
240 * Function that will allow use of the adodb binding
241 * feature to prevent sql-injection.
242 * It will act upon the object returned from the
243 * sqlStatement() function (and sqlQ() function).
245 * @param recordset $r
248 function sqlFetchArray($r)
250 //treat as an adodb recordset
259 //ensure it's an object (ie. is set)
260 if (!is_object($r)) {
264 return $r->FetchRow();
269 * Wrapper for ADODB getAssoc
271 * @see http://adodb.org/dokuwiki/doku.php?id=v5:reference:connection:getassoc
274 * @param string[] $bindvars
275 * @param boolean $forceArray
276 * @param boolean $first2Cols
279 function sqlGetAssoc($sql, $bindvars = false, $forceArray = false, $first2Cols = false)
282 return $GLOBALS['adodb']['db']->getAssoc($sql, $bindvars, $forceArray, $first2Cols);
286 * Standard sql insert query in OpenEMR.
287 * Only use this function if you need to have the
288 * id returned. If doing an insert query and do
289 * not need the id returned, then use the
290 * sqlStatement function instead.
292 * Function that will allow use of the adodb binding
293 * feature to prevent sql-injection. This function
294 * is specialized for insert function and will return
295 * the last id generated from the insert.
297 * @param string $statement query
298 * @param array $binds binded variables array (optional)
299 * @return integer Last id generated from the sql insert command
301 function sqlInsert($statement, $binds = false)
303 // Below line is to avoid a nasty bug in windows.
308 //Run a adodb execute
309 // Note the auditSQLEvent function is embedded in the
311 $recordset = $GLOBALS['adodb']['db']->Execute($statement, $binds, true);
312 if ($recordset === false) {
313 HelpfulDie("insert failed: $statement", getSqlLastError());
316 // Return the correct last id generated using function
317 // that is safe with the audit engine.
318 return $GLOBALS['lastidado'] > 0 ? $GLOBALS['lastidado'] : $GLOBALS['adodb']['db']->Insert_ID();
322 * Specialized sql query in OpenEMR that only returns
323 * the first row of query results as an associative array.
325 * Function that will allow use of the adodb binding
326 * feature to prevent sql-injection.
328 * @param string $statement query
329 * @param array $binds binded variables array (optional)
332 function sqlQuery($statement, $binds = false)
334 // Below line is to avoid a nasty bug in windows.
339 $recordset = $GLOBALS['adodb']['db']->Execute($statement, $binds);
341 if ($recordset === false) {
342 HelpfulDie("query failed: $statement", getSqlLastError());
345 if ($recordset->EOF) {
349 $rez = $recordset->FetchRow();
358 * Specialized sql query in OpenEMR that bypasses the auditing engine
359 * and only returns the first row of query results as an associative array.
361 * Function that will allow use of the adodb binding
362 * feature to prevent sql-injection. It is equivalent to the
363 * sqlQuery() function, EXCEPT it skips the
364 * audit engine. This function should only be used
365 * in very special situations.
367 * Note: If you do an INSERT or UPDATE statement you will get an empty string ("") as a response
369 * @param string $statement query
370 * @param array $binds binded variables array (optional)
371 * @return array|false|""
373 function sqlQueryNoLog($statement, $binds = false)
375 // Below line is to avoid a nasty bug in windows.
380 $recordset = $GLOBALS['adodb']['db']->ExecuteNoLog($statement, $binds);
382 if ($recordset === false) {
383 HelpfulDie("query failed: $statement", getSqlLastError());
386 if ($recordset->EOF) {
390 $rez = $recordset->FetchRow();
399 * Specialized sql query in OpenEMR that ignores sql errors, bypasses the
400 * auditing engine and only returns the first row of query results as an
403 * Function that will allow use of the adodb binding
404 * feature to prevent sql-injection. It is equivalent to the
405 * sqlQuery() function, EXCEPT it skips the
406 * audit engine and ignores erros. This function should only be used
407 * in very special situations.
409 * @param string $statement query
410 * @param array $binds binded variables array (optional)
413 function sqlQueryNoLogIgnoreError($statement, $binds = false)
415 // Below line is to avoid a nasty bug in windows.
420 $recordset = $GLOBALS['adodb']['db']->ExecuteNoLog($statement, $binds);
422 if ($recordset === false) {
423 // ignore the error and return FALSE
427 if ($recordset->EOF) {
431 $rez = $recordset->FetchRow();
440 * sqlQuery() function wrapper for CDR engine in OpenEMR.
441 * Allows option to turn on/off auditing specifically for the
444 * @param string $statement query
445 * @param array $binds binded variables array (optional)
448 function sqlQueryCdrEngine($statement, $binds = false)
450 // Below line is to avoid a nasty bug in windows.
455 if ($GLOBALS['audit_events_cdr']) {
456 return sqlQuery($statement, $binds);
458 return sqlQueryNoLog($statement, $binds);
463 * Specialized sql query in OpenEMR that skips auditing.
465 * This function should only be used in very special situations.
467 * @param string $statement query
469 function sqlInsertClean_audit($statement, $binds = false)
471 // Below line is to avoid a nasty bug in windows.
476 $ret = $GLOBALS['adodb']['db']->ExecuteNoLog($statement, $binds);
477 if ($ret === false) {
478 HelpfulDie("insert failed: $statement", getSqlLastError());
483 * Function that will safely return the last error,
484 * and accounts for the audit engine.
486 * @param string $mode either adodb(default) or native_mysql
487 * @return string last mysql error
489 function getSqlLastError()
491 return !empty($GLOBALS['last_mysql_error']) ? $GLOBALS['last_mysql_error'] : $GLOBALS['adodb']['db']->ErrorMsg();
495 * Function that will safely return the last error no,
496 * and accounts for the audit engine.
498 * @param string $mode either adodb(default) or native_mysql
499 * @return string last mysql error no
501 function getSqlLastErrorNo()
503 return !empty($GLOBALS['last_mysql_error_no']) ? $GLOBALS['last_mysql_error_no'] : $GLOBALS['adodb']['db']->ErrorNo();
507 * Function that will return an array listing
508 * of columns that exist in a table.
510 * @param string $table sql table
513 function sqlListFields($table)
515 $sql = "SHOW COLUMNS FROM " . add_escape_custom($table);
516 $resource = sqlStatementNoLog($sql);
517 $field_list = array();
518 while ($row = sqlFetchArray($resource)) {
519 $field_list[] = $row['Field'];
526 * Returns the number of sql rows
528 * @param recordset $r
529 * @return integer Number of rows
531 function sqlNumRows($r)
533 return $r->RecordCount();
537 * Error function for OpenEMR sql functions
539 * @param string $statement
540 * @param string $sqlerr
542 function HelpfulDie($statement, $sqlerr = '')
545 echo "<h2><font color='red'>" . xlt('Query Error') . "</font></h2>";
547 if (!$GLOBALS['sql_string_no_show_screen']) {
548 echo "<p><font color='red'>ERROR:</font> " . text($statement) . "</p>";
551 $logMsg = "SQL Error with statement:" . $statement;
554 if (!$GLOBALS['sql_string_no_show_screen']) {
555 echo "<p>Error: <font color='red'>" . text($sqlerr) . "</font></p>";
558 $logMsg .= "--" . $sqlerr;
561 $backtrace = debug_backtrace();
563 if (!$GLOBALS['sql_string_no_show_screen']) {
564 for ($level = 1; $level < count($backtrace); $level++) {
565 $info = $backtrace[$level];
566 echo "<br />" . text($info["file"] . " at " . $info["line"] . ":" . $info["function"]);
568 echo "(" . text(implode(",", $info["args"])) . ")";
573 $logMsg .= "==>" . $backtrace[1]["file"] . " at " . $backtrace[1]["line"] . ":" . $backtrace[1]["function"];
575 error_log(errorLogEscape($logMsg));
581 * @todo document use of the generate_id function
583 function generate_id()
585 $database = $GLOBALS['adodb']['db'];
586 return $database->GenID("sequences");
590 * Deprecated function. Standard sql query in OpenEMR.
592 * Function that will allow use of the adodb binding
593 * feature to prevent sql-injection. Will continue to
594 * be compatible with previous function calls that do
596 * It will return a recordset object.
597 * The sqlFetchArray() function should be used to
598 * utilize the return object.
601 * @param string $statement query
602 * @param array $binds binded variables array (optional)
605 function sqlQ($statement, $binds = false)
607 // Below line is to avoid a nasty bug in windows.
612 $recordset = $GLOBALS['adodb']['db']->Execute($statement, $binds) or
613 HelpfulDie("query failed: $statement", getSqlLastError());
619 * Sql close connection function (deprecated)
621 * No longer needed since PHP does this automatically.
628 //----------Close our mysql connection
629 $closed = $GLOBALS['adodb']['db']->close or
630 HelpfulDie("could not disconnect from mysql server link", getSqlLastError());
635 * Very simple wrapper function and not necessary (deprecated)
644 return $GLOBALS['adodb']['db'];
648 * Generic mysql select db function
649 * Used when converted to mysqli to centralize special circumstances.
650 * @param string $database
652 function generic_sql_select_db($database, $link = null)
654 if (is_null($link)) {
655 $link = $GLOBALS['dbh'];
658 mysqli_select_db($link, $database);
662 * Generic mysql affected rows function
663 * Used when converted to mysqli to centralize special circumstances.
666 function generic_sql_affected_rows()
668 return mysqli_affected_rows($GLOBALS['dbh']);
672 * Generic mysql insert id function
673 * Used when converted to mysqli to centralize special circumstances.
676 function generic_sql_insert_id()
678 return mysqli_insert_id($GLOBALS['dbh']);
683 * Begin a Transaction.
685 function sqlBeginTrans()
687 $GLOBALS['adodb']['db']->BeginTrans();
692 * Commit a transaction
694 function sqlCommitTrans($ok = true)
696 $GLOBALS['adodb']['db']->CommitTrans();
701 * Rollback a transaction
703 function sqlRollbackTrans()
705 $GLOBALS['adodb']['db']->RollbackTrans();
709 * For the 3 functions below:
711 * To support an optional higher level of security, queries that access password
712 * related information use these functions instead of the standard functions
713 * provided by sql.inc.
715 * By default, the privQuery and privStatement calls pass-through to
716 * the existing ADODB instance initialized by sql.inc.
718 * If an additional configuration file is created (secure_sqlconf.php) and saved
719 * in the sites/<sitename> directory (e.g. sites/default). The MySQL login
720 * information defined in that file as $secure_* will be used to create an ADODB
721 * instance specifically for querying privileged information.
723 * By configuring a server in this way, the default MySQL user can be denied access
724 * to sensitive tables (currently only "users_secure" would qualify). Thus
725 * the likelyhood of unintended modification can be reduced (e.g. through SQL Injection).
727 * Details on how to set this up are included in Documentation/privileged_db/priv_db_HOWTO
729 * The trade off for this additional security is extra complexity in configuration and
730 * maintenance of the database, hence it is not enabled at install time and must be
736 if (!isset($GLOBALS['PRIV_DB'])) {
737 $secure_config = $GLOBALS['OE_SITE_DIR'] . "/secure_sqlconf.php";
738 if (file_exists($secure_config)) {
739 require_once($secure_config);
740 $GLOBALS['PRIV_DB'] = NewADOConnection("mysqli_log"); // Use the subclassed driver which logs execute events
741 // Below optionFlags flag is telling the mysql connection to ensure local_infile setting,
742 // which is needed to import data in the Administration->Other->External Data Loads feature.
743 // (Note the MYSQLI_READ_DEFAULT_GROUP is just to keep the current setting hard-coded in adodb)
744 $GLOBALS['PRIV_DB']->setConnectionParameter(MYSQLI_READ_DEFAULT_GROUP, 0);
745 $GLOBALS['PRIV_DB']->setConnectionParameter(MYSQLI_OPT_LOCAL_INFILE, 1);
746 // Set mysql to use ssl, if applicable.
747 // Can support basic encryption by including just the mysql-ca pem (this is mandatory for ssl)
748 // Can also support client based certificate if also include mysql-cert and mysql-key (this is optional for ssl)
749 if (file_exists($GLOBALS['OE_SITE_DIR'] . "/documents/certificates/mysql-ca")) {
750 if (defined('MYSQLI_CLIENT_SSL')) {
752 file_exists($GLOBALS['OE_SITE_DIR'] . "/documents/certificates/mysql-key") &&
753 file_exists($GLOBALS['OE_SITE_DIR'] . "/documents/certificates/mysql-cert")
755 // with client side certificate/key
756 $GLOBALS['PRIV_DB']->ssl_key = "${GLOBALS['OE_SITE_DIR']}/documents/certificates/mysql-key";
757 $GLOBALS['PRIV_DB']->ssl_cert = "${GLOBALS['OE_SITE_DIR']}/documents/certificates/mysql-cert";
758 $GLOBALS['PRIV_DB']->ssl_ca = "${GLOBALS['OE_SITE_DIR']}/documents/certificates/mysql-ca";
760 // without client side certificate/key
761 $GLOBALS['PRIV_DB']->ssl_ca = "${GLOBALS['OE_SITE_DIR']}/documents/certificates/mysql-ca";
763 $GLOBALS['PRIV_DB']->clientFlags = MYSQLI_CLIENT_SSL;
766 $GLOBALS['PRIV_DB']->port = $port;
767 if ((!empty($GLOBALS["enable_database_connection_pooling"]) || !empty($_SESSION["enable_database_connection_pooling"])) && empty($GLOBALS['connection_pooling_off'])) {
768 $GLOBALS['PRIV_DB']->PConnect($secure_host, $secure_login, $secure_pass, $secure_dbase);
770 $GLOBALS['PRIV_DB']->connect($secure_host, $secure_login, $secure_pass, $secure_dbase);
772 // set up associations in adodb calls
773 $GLOBALS['PRIV_DB']->SetFetchMode(ADODB_FETCH_ASSOC);
774 // debug hook for ssl stuff
775 if (!empty($GLOBALS['debug_ssl_mysql_connection'])) {
776 error_log("CHECK SSL CIPHER IN PRIV_DB ADODB: " . errorLogEscape(print_r($GLOBALS[PRIV_DB]->ExecuteNoLog("SHOW STATUS LIKE 'Ssl_cipher';")->fields), true));
779 $GLOBALS['PRIV_DB'] = $GLOBALS['adodb']['db'];
783 return $GLOBALS['PRIV_DB'];
786 * mechanism to use "super user" for SQL queries related to password operations
789 * @param type $params
792 function privStatement($sql, $params = null)
794 if (is_array($params)) {
795 $recordset = getPrivDB()->ExecuteNoLog($sql, $params);
797 $recordset = getPrivDB()->ExecuteNoLog($sql);
800 if ($recordset === false) {
801 // These error messages are explictly NOT run through xl() because we still
802 // need them if there is a database problem.
803 echo "Failure during database access! Check server error log.";
804 $backtrace = debug_backtrace();
806 error_log("Executing as user:" . errorLogEscape(getPrivDB()->user) . " Statement failed:" . errorLogEscape($sql) . ":" . errorLogEscape($GLOBALS['last_mysql_error'])
807 . "==>" . errorLogEscape($backtrace[1]["file"]) . " at " . errorLogEscape($backtrace[1]["line"]) . ":" . errorLogEscape($backtrace[1]["function"]));
815 * Wrapper for privStatement that just returns the first row of a query or FALSE
816 * if there were no results.
819 * @param type $params
822 function privQuery($sql, $params = null)
824 $recordset = privStatement($sql, $params);
825 if ($recordset->EOF) {
829 $rez = $recordset->FetchRow();