3 * Sql functions/classes for OpenEMR.
5 * Includes classes and functions that OpenEMR uses
6 * to interact with SQL.
8 * LICENSE: This program is free software: you can redistribute it and/or modify
9 * it under the terms of the GNU General Public License as published by
10 * the Free Software Foundation, either version 3 of the License, or
11 * (at your option) any later version.
12 * This program is distributed in the hope that it will be useful,
13 * but WITHOUT ANY WARRANTY; without even the implied warranty of
14 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
15 * GNU General Public License for more details.
16 * You should have received a copy of the GNU General Public License
17 * along with this program. If not, see <http://opensource.org/licenses/gpl-license.php>.
20 * @link http://www.open-emr.org
23 require_once(dirname(__FILE__) . "/sqlconf.php");
24 require_once(dirname(__FILE__) . "/../vendor/adodb/adodb-php/adodb.inc.php");
25 require_once(dirname(__FILE__) . "/../vendor/adodb/adodb-php/drivers/adodb-mysqli.inc.php");
26 require_once(dirname(__FILE__) . "/ADODB_mysqli_log.php");
28 if (!defined('ADODB_FETCH_ASSOC')) {
29 define('ADODB_FETCH_ASSOC', 2);
32 $database = NewADOConnection("mysqli_log"); // Use the subclassed driver which logs execute events
33 // Below optionFlags flag is telling the mysql connection to ensure local_infile setting,
34 // which is needed to import data in the Administration->Other->External Data Loads feature.
35 // (Note the MYSQLI_READ_DEFAULT_GROUP is just to keep the current setting hard-coded in adodb)
36 $database->optionFlags = array(array(MYSQLI_READ_DEFAULT_GROUP,0), array(MYSQLI_OPT_LOCAL_INFILE,1));
37 // Set mysql to use ssl, if applicable.
38 // Can support basic encryption by including just the mysql-ca pem (this is mandatory for ssl)
39 // Can also support client based certificate if also include mysql-cert and mysql-key (this is optional for ssl)
40 if (file_exists($GLOBALS['OE_SITE_DIR'] . "/documents/certificates/mysql-ca")) {
41 if (defined('MYSQLI_CLIENT_SSL')) {
42 $database->clientFlags = MYSQLI_CLIENT_SSL;
45 $database->port = $port;
46 $database->PConnect($host, $login, $pass, $dbase);
47 $GLOBALS['adodb']['db'] = $database;
48 $GLOBALS['dbh'] = $database->_connectionID;
50 // Modified 5/2009 by BM for UTF-8 project ---------
51 if (!$disable_utf8_flag) {
52 $success_flag = $database->Execute("SET NAMES 'utf8'");
54 error_log("PHP custom error: from openemr library/sql.inc - Unable to set up UTF8 encoding with mysql database: ".getSqlLastError(), 0);
58 // Turn off STRICT SQL
59 $sql_strict_set_success = $database->Execute("SET sql_mode = ''");
60 if (!$sql_strict_set_success) {
61 error_log("Unable to set strict sql setting: ".getSqlLastError(), 0);
64 // set up associations in adodb calls (not sure why above define
65 // command does not work)
66 $GLOBALS['adodb']['db']->SetFetchMode(ADODB_FETCH_ASSOC);
68 if ($GLOBALS['debug_ssl_mysql_connection']) {
69 error_log("CHECK SSL CIPHER IN MAIN ADODB: " . print_r($GLOBALS['adodb']['db']->ExecuteNoLog("SHOW STATUS LIKE 'Ssl_cipher';")->fields, true));
72 //fmg: This makes the login screen informative when no connection can be made
73 if (!$GLOBALS['dbh']) {
74 //try to be more helpful
75 if ($host == "localhost") {
76 echo "Check that mysqld is running.<p>";
78 echo "Check that you can ping the server " . text($host) . ".<p>";
80 HelpfulDie("Could not connect to server!", getSqlLastError());
85 * Standard sql query in OpenEMR.
87 * Function that will allow use of the adodb binding
88 * feature to prevent sql-injection. Will continue to
89 * be compatible with previous function calls that do
91 * It will return a recordset object.
92 * The sqlFetchArray() function should be used to
93 * utilize the return object.
95 * @param string $statement query
96 * @param array $binds binded variables array (optional)
99 function sqlStatement($statement, $binds = false)
101 // Below line is to avoid a nasty bug in windows.
106 // Use adodb Execute with binding and return a recordset.
107 // Note that the auditSQLEvent function is embedded
108 // in the Execute command.
109 $recordset = $GLOBALS['adodb']['db']->Execute($statement, $binds);
110 if ($recordset === false) {
111 HelpfulDie("query failed: $statement", getSqlLastError());
118 * Specialized sql query in OpenEMR that skips auditing.
120 * Function that will allow use of the adodb binding
121 * feature to prevent sql-injection. Will continue to
122 * be compatible with previous function calls that do
123 * not use binding. It is equivalent to the
124 * sqlStatement() function, EXCEPT it skips the
125 * audit engine. This function should only be used
126 * in very special situations.
127 * It will return a recordset object.
128 * The sqlFetchArray() function should be used to
129 * utilize the return object.
131 * @param string $statement query
132 * @param array $binds binded variables array (optional)
135 function sqlStatementNoLog($statement, $binds = false)
137 // Below line is to avoid a nasty bug in windows.
142 // Use adodb ExecuteNoLog with binding and return a recordset.
143 $recordset = $GLOBALS['adodb']['db']->ExecuteNoLog($statement, $binds);
144 if ($recordset === false) {
145 HelpfulDie("query failed: $statement", getSqlLastError());
152 * sqlStatement() function wrapper for CDR engine in OpenEMR.
153 * Allows option to turn on/off auditing specifically for the
156 * @param string $statement query
157 * @param array $binds binded variables array (optional)
158 * @return recordset/resource
160 function sqlStatementCdrEngine($statement, $binds = false)
162 // Below line is to avoid a nasty bug in windows.
167 if ($GLOBALS['audit_events_cdr']) {
168 return sqlStatement($statement, $binds);
170 return sqlStatementNoLog($statement, $binds);
175 * Returns a row (as an array) from a sql recordset.
177 * Function that will allow use of the adodb binding
178 * feature to prevent sql-injection.
179 * It will act upon the object returned from the
180 * sqlStatement() function (and sqlQ() function).
182 * @param recordset $r
185 function sqlFetchArray($r)
187 //treat as an adodb recordset
196 //ensure it's an object (ie. is set)
197 if (!is_object($r)) {
201 return $r->FetchRow();
206 * Wrapper for ADODB getAssoc
208 * @see http://adodb.org/dokuwiki/doku.php?id=v5:reference:connection:getassoc
211 * @param string[] $bindvars
212 * @param boolean $forceArray
213 * @param boolean $first2Cols
216 function sqlGetAssoc($sql, $bindvars = false, $forceArray = false, $first2Cols = false)
219 return $GLOBALS['adodb']['db']->getAssoc($sql, $bindvars, $forceArray, $first2Cols);
223 * Standard sql insert query in OpenEMR.
224 * Only use this function if you need to have the
225 * id returned. If doing an insert query and do
226 * not need the id returned, then use the
227 * sqlStatement function instead.
229 * Function that will allow use of the adodb binding
230 * feature to prevent sql-injection. This function
231 * is specialized for insert function and will return
232 * the last id generated from the insert.
234 * @param string $statement query
235 * @param array $binds binded variables array (optional)
236 * @return integer Last id generated from the sql insert command
238 function sqlInsert($statement, $binds = false)
240 // Below line is to avoid a nasty bug in windows.
245 //Run a adodb execute
246 // Note the auditSQLEvent function is embedded in the
248 $recordset = $GLOBALS['adodb']['db']->Execute($statement, $binds, true);
249 if ($recordset === false) {
250 HelpfulDie("insert failed: $statement", getSqlLastError());
253 // Return the correct last id generated using function
254 // that is safe with the audit engine.
255 return $GLOBALS['lastidado'] > 0 ? $GLOBALS['lastidado'] : $GLOBALS['adodb']['db']->Insert_ID();
259 * Specialized sql query in OpenEMR that only returns
260 * the first row of query results as an associative array.
262 * Function that will allow use of the adodb binding
263 * feature to prevent sql-injection.
265 * @param string $statement query
266 * @param array $binds binded variables array (optional)
269 function sqlQuery($statement, $binds = false)
271 // Below line is to avoid a nasty bug in windows.
276 $recordset = $GLOBALS['adodb']['db']->Execute($statement, $binds);
278 if ($recordset === false) {
279 HelpfulDie("query failed: $statement", getSqlLastError());
282 if ($recordset->EOF) {
286 $rez = $recordset->FetchRow();
295 * Specialized sql query in OpenEMR that bypasses the auditing engine
296 * and only returns the first row of query results as an associative array.
298 * Function that will allow use of the adodb binding
299 * feature to prevent sql-injection. It is equivalent to the
300 * sqlQuery() function, EXCEPT it skips the
301 * audit engine. This function should only be used
302 * in very special situations.
304 * @param string $statement query
305 * @param array $binds binded variables array (optional)
308 function sqlQueryNoLog($statement, $binds = false)
310 // Below line is to avoid a nasty bug in windows.
315 $recordset = $GLOBALS['adodb']['db']->ExecuteNoLog($statement, $binds);
317 if ($recordset === false) {
318 HelpfulDie("query failed: $statement", getSqlLastError());
321 if ($recordset->EOF) {
325 $rez = $recordset->FetchRow();
334 * Specialized sql query in OpenEMR that ignores sql errors, bypasses the
335 * auditing engine and only returns the first row of query results as an
338 * Function that will allow use of the adodb binding
339 * feature to prevent sql-injection. It is equivalent to the
340 * sqlQuery() function, EXCEPT it skips the
341 * audit engine and ignores erros. This function should only be used
342 * in very special situations.
344 * @param string $statement query
345 * @param array $binds binded variables array (optional)
348 function sqlQueryNoLogIgnoreError($statement, $binds = false)
350 // Below line is to avoid a nasty bug in windows.
355 $recordset = $GLOBALS['adodb']['db']->ExecuteNoLog($statement, $binds);
357 if ($recordset === false) {
358 // ignore the error and return FALSE
362 if ($recordset->EOF) {
366 $rez = $recordset->FetchRow();
375 * sqlQuery() function wrapper for CDR engine in OpenEMR.
376 * Allows option to turn on/off auditing specifically for the
379 * @param string $statement query
380 * @param array $binds binded variables array (optional)
383 function sqlQueryCdrEngine($statement, $binds = false)
385 // Below line is to avoid a nasty bug in windows.
390 if ($GLOBALS['audit_events_cdr']) {
391 return sqlQuery($statement, $binds);
393 return sqlQueryNoLog($statement, $binds);
398 * Specialized sql query in OpenEMR that skips auditing.
400 * This function should only be used in very special situations.
402 * @param string $statement query
404 function sqlInsertClean_audit($statement)
407 $ret = $GLOBALS['adodb']['db']->ExecuteNoLog($statement);
408 if ($ret === false) {
409 HelpfulDie("insert failed: $statement", getSqlLastError());
414 * Function that will safely return the last error,
415 * and accounts for the audit engine.
417 * @param string $mode either adodb(default) or native_mysql
418 * @return string last mysql error
420 function getSqlLastError()
422 return !empty($GLOBALS['last_mysql_error']) ? $GLOBALS['last_mysql_error'] : $GLOBALS['adodb']['db']->ErrorMsg();
426 * Function that will safely return the last error no,
427 * and accounts for the audit engine.
429 * @param string $mode either adodb(default) or native_mysql
430 * @return string last mysql error no
432 function getSqlLastErrorNo()
434 return !empty($GLOBALS['last_mysql_error_no']) ? $GLOBALS['last_mysql_error_no'] : $GLOBALS['adodb']['db']->ErrorNo();
438 * Function that will return an array listing
439 * of columns that exist in a table.
441 * @param string $table sql table
444 function sqlListFields($table)
446 $sql = "SHOW COLUMNS FROM ". add_escape_custom($table);
447 $resource = sqlQ($sql);
448 $field_list = array();
449 while ($row = sqlFetchArray($resource)) {
450 $field_list[] = $row['Field'];
457 * Returns the number of sql rows
459 * @param recordset $r
460 * @return integer Number of rows
462 function sqlNumRows($r)
464 return $r->RecordCount();
468 * Error function for OpenEMR sql functions
470 * @param string $statement
471 * @param string $sqlerr
473 function HelpfulDie($statement, $sqlerr = '')
476 echo "<h2><font color='red'>" . xlt('Query Error') . "</font></h2>";
478 if (!$GLOBALS['sql_string_no_show_screen']) {
479 echo "<p><font color='red'>ERROR:</font> " . text($statement) . "</p>";
482 $logMsg="SQL Error with statement:".$statement;
485 if (!$GLOBALS['sql_string_no_show_screen']) {
486 echo "<p>Error: <font color='red'>" . text($sqlerr) . "</font></p>";
489 $logMsg.="--".$sqlerr;
492 $backtrace = debug_backtrace();
494 if (!$GLOBALS['sql_string_no_show_screen']) {
495 for ($level = 1; $level < count($backtrace); $level++) {
496 $info = $backtrace[$level];
497 echo "<br>" . text($info["file"] . " at " . $info["line"] . ":" . $info["function"]);
499 echo "(" . text(implode(",", $info["args"])) . ")";
504 $logMsg.="==>".$backtrace[1]["file"]." at ".$backtrace[1]["line"].":".$backtrace[1]["function"];
512 * @todo document use of the generate_id function
514 function generate_id()
516 $database = $GLOBALS['adodb']['db'];
517 return $database->GenID("sequences");
521 * Deprecated function. Standard sql query in OpenEMR.
523 * Function that will allow use of the adodb binding
524 * feature to prevent sql-injection. Will continue to
525 * be compatible with previous function calls that do
527 * It will return a recordset object.
528 * The sqlFetchArray() function should be used to
529 * utilize the return object.
532 * @param string $statement query
533 * @param array $binds binded variables array (optional)
536 function sqlQ($statement, $binds = false)
538 // Below line is to avoid a nasty bug in windows.
543 $recordset = $GLOBALS['adodb']['db']->Execute($statement, $binds) or
544 HelpfulDie("query failed: $statement", getSqlLastError());
550 * Sql close connection function (deprecated)
552 * No longer needed since PHP does this automatically.
559 //----------Close our mysql connection
560 $closed = $GLOBALS['adodb']['db']->close or
561 HelpfulDie("could not disconnect from mysql server link", getSqlLastError());
566 * Very simple wrapper function and not necessary (deprecated)
575 return $GLOBALS['adodb']['db'];
579 * Generic mysql select db function
580 * Used when converted to mysqli to centralize special circumstances.
581 * @param string $database
583 function generic_sql_select_db($database, $link = null)
585 if (is_null($link)) {
586 $link = $GLOBALS['dbh'];
589 mysqli_select_db($link, $database);
593 * Generic mysql affected rows function
594 * Used when converted to mysqli to centralize special circumstances.
597 function generic_sql_affected_rows()
599 return mysqli_affected_rows($GLOBALS['dbh']);
603 * Generic mysql insert id function
604 * Used when converted to mysqli to centralize special circumstances.
607 function generic_sql_insert_id()
609 return mysqli_insert_id($GLOBALS['dbh']);
614 * Begin a Transaction.
616 function sqlBeginTrans()
618 $GLOBALS['adodb']['db']->BeginTrans();
623 * Commit a transaction
625 function sqlCommitTrans($ok = true)
627 $GLOBALS['adodb']['db']->CommitTrans();
632 * Rollback a transaction
634 function sqlRollbackTrans()
636 $GLOBALS['adodb']['db']->RollbackTrans();