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__) . "/adodb/adodb.inc.php");
25 require_once(dirname(__FILE__) . "/adodb/drivers/adodb-mysql.inc.php");
26 require_once(dirname(__FILE__) . "/log.inc");
29 * ADODB_mysql class wrapper to ensure proper auditing in OpenEMR.
31 * @author Kevin Yeh <kevin.y@integralemr.com>
33 class ADODB_mysql_log extends ADODB_mysql
36 * ADODB Execute function wrapper to ensure proper auditing in OpenEMR.
38 * @param string $sql query
39 * @param array $inputarr binded variables array (optional)
40 * @return boolean returns false if error
42 function Execute($sql,$inputarr=false)
44 $retval= parent::Execute($sql,$inputarr);
45 if ($retval === false) {
47 // Stash the error into last_mysql_error so it doesn't get clobbered when
48 // we insert into the audit log.
49 $GLOBALS['last_mysql_error']=$this->ErrorMsg();
54 // Stash the insert ID into lastidado so it doesn't get clobbered when
55 // we insert into the audit log.
56 $GLOBALS['lastidado']=$this->Insert_ID();
57 auditSQLEvent($sql,$outcome,$inputarr);
62 * ADODB _insertid function wrapper to ensure proper auditing in OpenEMR.
64 * Need to override this method to prevent infinite recursion with execute
65 * when trying to retrieve the last insert id.
67 * @return boolean returns false if error
71 $rs=$this->ExecuteNoLog("SELECT LAST_INSERT_ID()");
72 $ret=reset($rs->fields);
78 * ADODB Execute function wrapper to skip auditing in OpenEMR.
80 * Bypasses the OpenEMR auditing engine.
82 * @param string $sql query
83 * @param array $inputarr binded variables array (optional)
84 * @return boolean returns false if error
86 function ExecuteNoLog($sql,$inputarr=false)
88 return parent::Execute($sql,$inputarr);
92 * ADODB GenID function wrapper to work with OpenEMR.
94 * Need to override to fix a bug where call to GenID was updating
95 * sequences table but always returning a zero with the OpenEMR audit
96 * engine both on and off. Note this bug only appears to occur in recent
97 * php versions on windows. The fix is to use the ExecuteNoLog() function
98 * rather than the Execute() functions within this function (otherwise,
99 * there are no other changes from the original ADODB GenID function).
101 * @param string $seqname table name containing sequence (default is adodbseq)
102 * @param integer $startID id to start with for a new sequence (default is 1)
103 * @return integer returns the sequence integer
105 function GenID($seqname='adodbseq',$startID=1)
107 // post-nuke sets hasGenID to false
108 if (!$this->hasGenID) return false;
110 $savelog = $this->_logsql;
111 $this->_logsql = false;
112 $getnext = sprintf($this->_genIDSQL,$seqname);
113 $holdtransOK = $this->_transOK; // save the current status
114 $rs = @$this->ExecuteNoLog($getnext);
116 if ($holdtransOK) $this->_transOK = true; //if the status was ok before reset
117 $u = strtoupper($seqname);
118 $this->ExecuteNoLog(sprintf($this->_genSeqSQL,$seqname));
119 $cnt = $this->GetOne(sprintf($this->_genSeqCountSQL,$seqname));
120 if (!$cnt) $this->ExecuteNoLog(sprintf($this->_genSeq2SQL,$seqname,$startID-1));
121 $rs = $this->ExecuteNoLog($getnext);
125 $this->genID = mysql_insert_id($this->_connectionID);
130 $this->_logsql = $savelog;
134 if (!defined('ADODB_FETCH_ASSOC')) define('ADODB_FETCH_ASSOC', 2);
135 $database = NewADOConnection("mysql_log"); // Use the subclassed driver which logs execute events
136 // Below clientFlags flag is telling the mysql connection to allow local_infile setting,
137 // which is needed to import data in the Administration->Other->External Data Loads feature.
138 // Note this is a specific bug to work in Ubuntu 12.04, of which the Data Load feature does not
139 // work and is suspicious for a bug in PHP of that OS; Setting this clientFlags fixes this bug
140 // and appears to not cause problems in other operating systems.
141 $database->clientFlags = 128;
142 $database->PConnect($host, $login, $pass, $dbase);
143 $GLOBALS['adodb']['db'] = $database;
144 $GLOBALS['dbh'] = $database->_connectionID;
146 // Modified 5/2009 by BM for UTF-8 project ---------
147 if (!$disable_utf8_flag) {
148 $success_flag = $database->Execute("SET NAMES 'utf8'");
149 if (!$success_flag) {
150 error_log("PHP custom error: from openemr library/sql.inc - Unable to set up UTF8 encoding with mysql database: ".getSqlLastError(), 0);
154 // set up associations in adodb calls (not sure why above define
155 // command does not work)
156 $GLOBALS['adodb']['db']->SetFetchMode(ADODB_FETCH_ASSOC);
158 //fmg: This makes the login screen informative when no connection can be made
159 if (!$GLOBALS['dbh']) {
160 //try to be more helpful
161 if ($host == "localhost") {
162 echo "Check that mysqld is running.<p>";
164 echo "Check that you can ping the server '$host'.<p>";
166 HelpfulDie("Could not connect to server!", getSqlLastError("native_mysql"));
171 * Standard sql query in OpenEMR.
173 * Function that will allow use of the adodb binding
174 * feature to prevent sql-injection. Will continue to
175 * be compatible with previous function calls that do
177 * If use adodb binding, then will return a recordset object.
178 * If do not use binding, then will return a resource object.
179 * The sqlFetchArray() function should be used to
180 * utilize the return object (it will accept both recordset
181 * and resource objects).
183 * @param string $statement query
184 * @param array $binds binded variables array (optional)
185 * @return recordset/resource
187 function sqlStatement($statement, $binds=NULL )
189 if (is_array($binds)) {
190 // Use adodb Execute with binding and return a recordset.
191 // Note that the auditSQLEvent function is embedded
192 // in the Execute command.
193 $recordset = $GLOBALS['adodb']['db']->Execute( $statement, $binds );
194 if ($recordset === FALSE) {
195 HelpfulDie("query failed: $statement", getSqlLastError());
200 // Use mysql_query and return a resource.
201 $resource = mysql_query($statement, $GLOBALS['dbh']);
202 if ($resource === FALSE) {
203 auditSQLEvent($statement, FALSE, $binds);
204 HelpfulDie("query failed: $statement", getSqlLastError("native_mysql"));
206 auditSQLEvent($statement, TRUE, $binds);
212 * Specialized sql query in OpenEMR that skips auditing.
214 * Function that will allow use of the adodb binding
215 * feature to prevent sql-injection. Will continue to
216 * be compatible with previous function calls that do
217 * not use binding. It is equivalent to the
218 * sqlStatement() function, EXCEPT it skips the
219 * audit engine. This function should only be used
220 * in very special situations.
221 * If use adodb binding, then will return a recordset object.
222 * If do not use binding, then will return a resource object.
223 * The sqlFetchArray() function should be used to
224 * utilize the return object (it will accept both recordset
225 * and resource objects).
227 * @param string $statement query
228 * @param array $binds binded variables array (optional)
229 * @return recordset/resource
231 function sqlStatementNoLog($statement, $binds=NULL )
233 if (is_array($binds)) {
234 // Use adodb ExecuteNoLog with binding and return a recordset.
235 $recordset = $GLOBALS['adodb']['db']->ExecuteNoLog( $statement, $binds );
236 if ($recordset === FALSE) {
237 HelpfulDie("query failed: $statement", getSqlLastError());
242 // Use mysql_query and return a resource.
243 $resource = mysql_query($statement, $GLOBALS['dbh']);
244 if ($resource === FALSE) {
245 HelpfulDie("query failed: $statement", getSqlLastError("native_mysql"));
252 * sqlStatement() function wrapper for CDR engine in OpenEMR.
253 * Allows option to turn on/off auditing specifically for the
256 * @param string $statement query
257 * @param array $binds binded variables array (optional)
258 * @return recordset/resource
260 function sqlStatementCdrEngine($statement, $binds=NULL )
262 if ($GLOBALS['audit_events_cdr']) {
263 return sqlStatement($statement,$binds);
266 return sqlStatementNoLog($statement,$binds);
271 * Returns a row (as an array) from a sql recordset or resource object.
273 * Function that will allow use of the adodb binding
274 * feature to prevent sql-injection.
275 * It will act upon the object returned from the
276 * sqlStatement() function (and sqlQ() function).
277 * It will automatically figure out if the input
278 * object is a recordset or a resource.
280 * @param recordset/resource $r
283 function sqlFetchArray($r)
285 if (!is_resource($r)) {
286 //treat as an adodb recordset
291 //ensure it's an object (ie. is set)
294 return $r->FetchRow();
297 //treat as a mysql_query resource
300 return mysql_fetch_array($r, MYSQL_ASSOC);
305 * Standard sql insert query in OpenEMR.
307 * Function that will allow use of the adodb binding
308 * feature to prevent sql-injection. This function
309 * is specialized for insert function and will return
310 * the last id generated from the insert.
312 * @param string $statement query
313 * @param array $binds binded variables array (optional)
314 * @return integer Last id generated from the sql insert command
316 function sqlInsert($statement, $binds=array())
318 //Run a adodb execute
319 // Note the auditSQLEvent function is embedded in the
321 $recordset = $GLOBALS['adodb']['db']->Execute($statement, $binds);
322 if ($recordset === FALSE) {
323 HelpfulDie("insert failed: $statement", getSqlLastError());
325 // Return the correct last id generated using function
326 // that is safe with the audit engine.
327 return getSqlLastID();
331 * Specialized sql query in OpenEMR that only returns
332 * the first row of query results as an associative array.
334 * Function that will allow use of the adodb binding
335 * feature to prevent sql-injection.
337 * @param string $statement query
338 * @param array $binds binded variables array (optional)
341 function sqlQuery($statement, $binds=NULL)
343 if (is_array($binds)) {
344 $recordset = $GLOBALS['adodb']['db']->Execute( $statement, $binds );
347 $recordset = $GLOBALS['adodb']['db']->Execute( $statement );
349 if ($recordset === FALSE) {
350 HelpfulDie("query failed: $statement", getSqlLastError());
354 $rez = $recordset->FetchRow();
361 * Specialized sql query in OpenEMR that bypasses the auditing engine
362 * and only returns the first row of query results as an associative array.
364 * Function that will allow use of the adodb binding
365 * feature to prevent sql-injection. It is equivalent to the
366 * sqlQuery() function, EXCEPT it skips the
367 * audit engine. This function should only be used
368 * in very special situations.
370 * @param string $statement query
371 * @param array $binds binded variables array (optional)
374 function sqlQueryNoLog($statement, $binds=NULL)
376 if (is_array($binds)) {
377 $recordset = $GLOBALS['adodb']['db']->ExecuteNoLog( $statement, $binds );
380 $recordset = $GLOBALS['adodb']['db']->ExecuteNoLog( $statement );
382 if ($recordset === FALSE) {
383 HelpfulDie("query failed: $statement", getSqlLastError());
387 $rez = $recordset->FetchRow();
394 * Specialized sql query in OpenEMR that ignores sql errors, bypasses the
395 * auditing engine and only returns the first row of query results as an
398 * Function that will allow use of the adodb binding
399 * feature to prevent sql-injection. It is equivalent to the
400 * sqlQuery() function, EXCEPT it skips the
401 * audit engine and ignores erros. This function should only be used
402 * in very special situations.
404 * @param string $statement query
405 * @param array $binds binded variables array (optional)
408 function sqlQueryNoLogIgnoreError($statement, $binds=NULL)
410 if (is_array($binds)) {
411 $recordset = $GLOBALS['adodb']['db']->ExecuteNoLog( $statement, $binds );
414 $recordset = $GLOBALS['adodb']['db']->ExecuteNoLog( $statement );
416 if ($recordset === FALSE) {
417 // ignore the error and return FALSE
422 $rez = $recordset->FetchRow();
429 * sqlQuery() function wrapper for CDR engine in OpenEMR.
430 * Allows option to turn on/off auditing specifically for the
433 * @param string $statement query
434 * @param array $binds binded variables array (optional)
437 function sqlQueryCdrEngine($statement, $binds=NULL )
439 if ($GLOBALS['audit_events_cdr']) {
440 return sqlQuery($statement,$binds);
443 return sqlQueryNoLog($statement,$binds);
448 * Specialized sql query in OpenEMR that skips auditing.
450 * This function should only be used in very special situations.
452 * @param string $statement query
454 function sqlInsertClean_audit($statement)
457 $ret = $GLOBALS['adodb']['db']->ExecuteNoLog($statement);
458 if ($ret === FALSE) {
459 HelpfulDie("insert failed: $statement", getSqlLastError());
464 * Function that will safely return the last ID inserted,
465 * and accounts for the audit engine.
467 * @return integer Last ID that was inserted into sql
469 function getSqlLastID() {
470 if ($GLOBALS['lastidado'] >0) {
471 return $GLOBALS['lastidado'];
474 return $GLOBALS['adodb']['db']->Insert_ID();
479 * Function that will safely return the last error,
480 * and accounts for the audit engine.
482 * @param string $mode either adodb(default) or native_mysql
483 * @return string last mysql error
485 function getSqlLastError($mode="adodb") {
486 if (!empty($GLOBALS['last_mysql_error'])) {
487 return $GLOBALS['last_mysql_error'];
490 if ($mode == "adodb") {
491 return $GLOBALS['adodb']['db']->ErrorMsg();
493 else { // $mode = "native_mysql"
494 return mysql_error($GLOBALS['dbh']);
500 * Function that will return an array listing
501 * of columns that exist in a table.
503 * @param string $table sql table
506 function sqlListFields($table) {
507 $sql = "SHOW COLUMNS FROM ". mysql_real_escape_string($table);
508 $resource = sqlQ($sql);
509 $field_list = array();
510 while($row = mysql_fetch_array($resource)) {
511 $field_list[] = $row['Field'];
517 * Returns the number of sql rows
519 * Function that will allow use of the adodb binding
520 * feature to prevent sql-injection.
521 * It will act upon the object returned from the
522 * sqlStatement() function (and sqlQ() function).
523 * It will automatically figure out if the input
524 * object is a recordset or a resource.
526 * @param recordset/resource $r
527 * @return integer Number of rows
529 function sqlNumRows($r)
531 if (!is_resource($r)) {
532 //treat as an adodb recordset
533 return $r->RecordCount();
536 //treat as a mysql_query resource
537 return mysql_num_rows($r);
542 * Error function for OpenEMR sql functions
544 * @param string $statement
545 * @param string $sqlerr
547 function HelpfulDie ($statement, $sqlerr='')
549 echo "<p><p><font color='red'>ERROR:</font> $statement<p>";
551 echo "Error: <font color='red'>$sqlerr</font><p>";
557 * @todo document use of the generate_id function
559 function generate_id () {
560 $database = $GLOBALS['adodb']['db'];
561 return $database->GenID("sequences");
565 * Specialized sql query in OpenEMR with limited functionality
567 * Does not fully incorporate the audit engine, so
568 * recommend not using this function (if bind is set,
569 * then will get logged, however if bind is not set,
570 * then will not get logged).
571 * Function that will allow use of the adodb binding
572 * feature to prevent sql-injection. Will continue to
573 * be compatible with previous function calls that do
575 * If use adodb binding, then will return a recordset object.
576 * If do not use binding, then will return a resource object.
577 * The sqlFetchArray() function should be used to
578 * utilize the return object (it will accept both recordset
579 * and resource objects).
582 * @param string $statement query
583 * @param array $binds binded variables array (optional)
584 * @return recordset/resource
586 function sqlQ($statement, $binds=NULL )
588 if (is_array($binds)) {
589 $recordset = $GLOBALS['adodb']['db']->Execute( $statement, $binds ) or
590 HelpfulDie("query failed: $statement", getSqlLastError());
594 $resource = mysql_query($statement, $GLOBALS['dbh']) or
595 HelpfulDie("query failed: $statement", getSqlLastError("native_mysql"));
601 * Simple wrapper for sqlInsert() function (deprecated).
603 * Function that will allow use of the adodb binding feature
604 * to prevent sql-injection.
607 * @param string $statement query
608 * @param array $binds binded variables array (optional)
609 * @return integer Last id generated from the sql insert command
611 function idSqlStatement($statement , $binds=NULL )
613 return sqlInsert($statement, $binds);
617 * Simple wrapper for sqlInsert() function (deprecated).
619 * Function that will allow use of the adodb binding feature
620 * to prevent sql-injection.
623 * @param string $statement query
624 * @param array $binds binded variables array (optional)
625 * @return integer Last id generated from the sql insert command
627 function sqlInsertClean($statement, $binds=NULL )
629 return sqlInsert($statement, $binds);
633 * Sql connection function (deprecated)
638 * @param string $login
639 * @param string $pass
640 * @param string $dbase
641 * @param string $host
642 * @param string $port
645 function sqlConnect($login,$pass,$dbase,$host,$port = '3306')
647 $GLOBALS['dbh'] = $database->_connectionID;
648 return $GLOBALS['dbh'];
652 * Sql close connection function (deprecated)
654 * No longer needed since PHP does this automatically.
661 //----------Close our mysql connection
662 $closed = $GLOBALS['adodb']['db']->close or
663 HelpfulDie("could not disconnect from mysql server link", getSqlLastError());
668 * Very simple wrapper function and not necessary (deprecated)
676 return $GLOBALS['adodb']['db'];