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 // Stash the insert ID into lastidado so it doesn't get clobbered when
46 // we insert into the audit log.
47 $GLOBALS['lastidado']=$this->Insert_ID();
48 $outcome= ($retval === false) ? false : true;
49 auditSQLEvent($sql,$outcome,$inputarr);
54 * ADODB _insertid function wrapper to ensure proper auditing in OpenEMR.
56 * Need to override this method to prevent infinite recursion with execute
57 * when trying to retrieve the last insert id.
59 * @return boolean returns false if error
63 $rs=$this->ExecuteNoLog("SELECT LAST_INSERT_ID()");
64 $ret=reset($rs->fields);
70 * ADODB Execute function wrapper to skip auditing in OpenEMR.
72 * Bypasses the OpenEMR auditing engine.
74 * @param string $sql query
75 * @param array $inputarr binded variables array (optional)
76 * @return boolean returns false if error
78 function ExecuteNoLog($sql,$inputarr=false)
80 return parent::Execute($sql,$inputarr);
84 * ADODB GenID function wrapper to work with OpenEMR.
86 * Need to override to fix a bug where call to GenID was updating
87 * sequences table but always returning a zero with the OpenEMR audit
88 * engine both on and off. Note this bug only appears to occur in recent
89 * php versions on windows. The fix is to use the ExecuteNoLog() function
90 * rather than the Execute() functions within this function (otherwise,
91 * there are no other changes from the original ADODB GenID function).
93 * @param string $seqname table name containing sequence (default is adodbseq)
94 * @param integer $startID id to start with for a new sequence (default is 1)
95 * @return integer returns the sequence integer
97 function GenID($seqname='adodbseq',$startID=1)
99 // post-nuke sets hasGenID to false
100 if (!$this->hasGenID) return false;
102 $savelog = $this->_logsql;
103 $this->_logsql = false;
104 $getnext = sprintf($this->_genIDSQL,$seqname);
105 $holdtransOK = $this->_transOK; // save the current status
106 $rs = @$this->ExecuteNoLog($getnext);
108 if ($holdtransOK) $this->_transOK = true; //if the status was ok before reset
109 $u = strtoupper($seqname);
110 $this->ExecuteNoLog(sprintf($this->_genSeqSQL,$seqname));
111 $cnt = $this->GetOne(sprintf($this->_genSeqCountSQL,$seqname));
112 if (!$cnt) $this->ExecuteNoLog(sprintf($this->_genSeq2SQL,$seqname,$startID-1));
113 $rs = $this->ExecuteNoLog($getnext);
117 $this->genID = mysql_insert_id($this->_connectionID);
122 $this->_logsql = $savelog;
126 if (!defined('ADODB_FETCH_ASSOC')) define('ADODB_FETCH_ASSOC', 2);
127 $database = NewADOConnection("mysql_log"); // Use the subclassed driver which logs execute events
128 // Below clientFlags flag is telling the mysql connection to allow local_infile setting,
129 // which is needed to import data in the Administration->Other->External Data Loads feature.
130 // Note this is a specific bug to work in Ubuntu 12.04, of which the Data Load feature does not
131 // work and is suspicious for a bug in PHP of that OS; Setting this clientFlags fixes this bug
132 // and appears to not cause problems in other operating systems.
133 $database->clientFlags = 128;
134 $database->PConnect($host, $login, $pass, $dbase);
135 $GLOBALS['adodb']['db'] = $database;
136 $GLOBALS['dbh'] = $database->_connectionID;
138 // Modified 5/2009 by BM for UTF-8 project ---------
139 if (!$disable_utf8_flag) {
140 $success_flag = $database->Execute("SET NAMES 'utf8'");
141 if (!$success_flag) {
142 error_log("PHP custom error: from openemr library/sql.inc - Unable to set up UTF8 encoding with mysql database: ".$database->ErrorMsg(), 0);
146 // set up associations in adodb calls (not sure why above define
147 // command does not work)
148 $GLOBALS['adodb']['db']->SetFetchMode(ADODB_FETCH_ASSOC);
150 //fmg: This makes the login screen informative when no connection can be made
151 if (!$GLOBALS['dbh']) {
152 //try to be more helpful
153 if ($host == "localhost") {
154 echo "Check that mysqld is running.<p>";
156 echo "Check that you can ping the server '$host'.<p>";
158 HelpfulDie("Could not connect to server!", mysql_error($GLOBALS['dbh']));
163 * Standard sql query in OpenEMR.
165 * Function that will allow use of the adodb binding
166 * feature to prevent sql-injection. Will continue to
167 * be compatible with previous function calls that do
169 * If use adodb binding, then will return a recordset object.
170 * If do not use binding, then will return a resource object.
171 * The sqlFetchArray() function should be used to
172 * utilize the return object (it will accept both recordset
173 * and resource objects).
175 * @param string $statement query
176 * @param array $binds binded variables array (optional)
177 * @return recordset/resource
179 function sqlStatement($statement, $binds=NULL )
181 if (is_array($binds)) {
182 // Use adodb Execute with binding and return a recordset.
183 // Note that the auditSQLEvent function is embedded
184 // in the Execute command.
185 $recordset = $GLOBALS['adodb']['db']->Execute( $statement, $binds );
186 if ($recordset === FALSE) {
187 HelpfulDie("query failed: $statement", $GLOBALS['adodb']['db']->ErrorMsg());
192 // Use mysql_query and return a resource.
193 $resource = mysql_query($statement, $GLOBALS['dbh']);
194 if ($resource === FALSE) {
195 auditSQLEvent($statement, FALSE, $binds);
196 HelpfulDie("query failed: $statement", mysql_error($GLOBALS['dbh']));
198 auditSQLEvent($statement, TRUE, $binds);
204 * Specialized sql query in OpenEMR that skips auditing.
206 * Function that will allow use of the adodb binding
207 * feature to prevent sql-injection. Will continue to
208 * be compatible with previous function calls that do
209 * not use binding. It is equivalent to the
210 * sqlStatement() function, EXCEPT it skips the
211 * audit engine. This function should only be used
212 * in very special situations.
213 * If use adodb binding, then will return a recordset object.
214 * If do not use binding, then will return a resource object.
215 * The sqlFetchArray() function should be used to
216 * utilize the return object (it will accept both recordset
217 * and resource objects).
219 * @param string $statement query
220 * @param array $binds binded variables array (optional)
221 * @return recordset/resource
223 function sqlStatementNoLog($statement, $binds=NULL )
225 if (is_array($binds)) {
226 // Use adodb ExecuteNoLog with binding and return a recordset.
227 $recordset = $GLOBALS['adodb']['db']->ExecuteNoLog( $statement, $binds );
228 if ($recordset === FALSE) {
229 HelpfulDie("query failed: $statement", $GLOBALS['adodb']['db']->ErrorMsg());
234 // Use mysql_query and return a resource.
235 $resource = mysql_query($statement, $GLOBALS['dbh']);
236 if ($resource === FALSE) {
237 HelpfulDie("query failed: $statement", mysql_error($GLOBALS['dbh']));
244 * sqlStatement() function wrapper for CDR engine in OpenEMR.
245 * Allows option to turn on/off auditing specifically for the
248 * @param string $statement query
249 * @param array $binds binded variables array (optional)
250 * @return recordset/resource
252 function sqlStatementCdrEngine($statement, $binds=NULL )
254 if ($GLOBALS['audit_events_cdr']) {
255 return sqlStatement($statement,$binds);
258 return sqlStatementNoLog($statement,$binds);
263 * Returns a row (as an array) from a sql recordset or resource object.
265 * Function that will allow use of the adodb binding
266 * feature to prevent sql-injection.
267 * It will act upon the object returned from the
268 * sqlStatement() function (and sqlQ() function).
269 * It will automatically figure out if the input
270 * object is a recordset or a resource.
272 * @param recordset/resource $r
275 function sqlFetchArray($r)
277 if (!is_resource($r)) {
278 //treat as an adodb recordset
283 //ensure it's an object (ie. is set)
286 return $r->FetchRow();
289 //treat as a mysql_query resource
292 return mysql_fetch_array($r, MYSQL_ASSOC);
297 * Standard sql insert query in OpenEMR.
299 * Function that will allow use of the adodb binding
300 * feature to prevent sql-injection. This function
301 * is specialized for insert function and will return
302 * the last id generated from the insert.
304 * @param string $statement query
305 * @param array $binds binded variables array (optional)
306 * @return integer Last id generated from the sql insert command
308 function sqlInsert($statement, $binds=array())
310 //Run a adodb execute
311 // Note the auditSQLEvent function is embedded in the
313 $recordset = $GLOBALS['adodb']['db']->Execute($statement, $binds);
314 if ($recordset === FALSE) {
315 HelpfulDie("insert failed: $statement", $GLOBALS['adodb']['db']->ErrorMsg());
317 // Return the correct last id generated using function
318 // that is safe with the audit engine.
319 return getSqlLastID();
323 * Specialized sql query in OpenEMR that only returns
324 * the first row of query results as an associative array.
326 * Function that will allow use of the adodb binding
327 * feature to prevent sql-injection.
329 * @param string $statement query
330 * @param array $binds binded variables array (optional)
333 function sqlQuery($statement, $binds=NULL)
335 if (is_array($binds)) {
336 $recordset = $GLOBALS['adodb']['db']->Execute( $statement, $binds );
339 $recordset = $GLOBALS['adodb']['db']->Execute( $statement );
341 if ($recordset === FALSE) {
342 HelpfulDie("query failed: $statement", $GLOBALS['adodb']['db']->ErrorMsg());
346 $rez = $recordset->FetchRow();
353 * Specialized sql query in OpenEMR that bypasses the auditing engine
354 * and only returns the first row of query results as an associative array.
356 * Function that will allow use of the adodb binding
357 * feature to prevent sql-injection. It is equivalent to the
358 * sqlQuery() function, EXCEPT it skips the
359 * audit engine. This function should only be used
360 * in very special situations.
362 * @param string $statement query
363 * @param array $binds binded variables array (optional)
366 function sqlQueryNoLog($statement, $binds=NULL)
368 if (is_array($binds)) {
369 $recordset = $GLOBALS['adodb']['db']->ExecuteNoLog( $statement, $binds );
372 $recordset = $GLOBALS['adodb']['db']->ExecuteNoLog( $statement );
374 if ($recordset === FALSE) {
375 HelpfulDie("query failed: $statement", $GLOBALS['adodb']['db']->ErrorMsg());
379 $rez = $recordset->FetchRow();
386 * Specialized sql query in OpenEMR that ignores sql errors, bypasses the
387 * auditing engine and only returns the first row of query results as an
390 * Function that will allow use of the adodb binding
391 * feature to prevent sql-injection. It is equivalent to the
392 * sqlQuery() function, EXCEPT it skips the
393 * audit engine and ignores erros. This function should only be used
394 * in very special situations.
396 * @param string $statement query
397 * @param array $binds binded variables array (optional)
400 function sqlQueryNoLogIgnoreError($statement, $binds=NULL)
402 if (is_array($binds)) {
403 $recordset = $GLOBALS['adodb']['db']->ExecuteNoLog( $statement, $binds );
406 $recordset = $GLOBALS['adodb']['db']->ExecuteNoLog( $statement );
408 if ($recordset === FALSE) {
409 // ignore the error and return FALSE
414 $rez = $recordset->FetchRow();
421 * sqlQuery() function wrapper for CDR engine in OpenEMR.
422 * Allows option to turn on/off auditing specifically for the
425 * @param string $statement query
426 * @param array $binds binded variables array (optional)
429 function sqlQueryCdrEngine($statement, $binds=NULL )
431 if ($GLOBALS['audit_events_cdr']) {
432 return sqlQuery($statement,$binds);
435 return sqlQueryNoLog($statement,$binds);
440 * Specialized sql query in OpenEMR that skips auditing.
442 * This function should only be used in very special situations.
444 * @param string $statement query
446 function sqlInsertClean_audit($statement)
449 $ret = $GLOBALS['adodb']['db']->ExecuteNoLog($statement);
450 if ($ret === FALSE) {
451 HelpfulDie("insert failed: $statement", $GLOBALS['adodb']['db']->ErrorMsg());
456 * Function that will safely return the last ID inserted,
457 * and accounts for the audit engine.
459 * @return integer Last ID that was inserted into sql
461 function getSqlLastID() {
462 if ($GLOBALS['lastidado'] >0) {
463 return $GLOBALS['lastidado'];
466 return $GLOBALS['adodb']['db']->Insert_ID();
471 * Function that will return an array listing
472 * of columns that exist in a table.
474 * @param string $table sql table
477 function sqlListFields($table) {
478 $sql = "SHOW COLUMNS FROM ". mysql_real_escape_string($table);
479 $resource = sqlQ($sql);
480 $field_list = array();
481 while($row = mysql_fetch_array($resource)) {
482 $field_list[] = $row['Field'];
488 * Returns the number of sql rows
490 * Function that will allow use of the adodb binding
491 * feature to prevent sql-injection.
492 * It will act upon the object returned from the
493 * sqlStatement() function (and sqlQ() function).
494 * It will automatically figure out if the input
495 * object is a recordset or a resource.
497 * @param recordset/resource $r
498 * @return integer Number of rows
500 function sqlNumRows($r)
502 if (!is_resource($r)) {
503 //treat as an adodb recordset
504 return $r->RecordCount();
507 //treat as a mysql_query resource
508 return mysql_num_rows($r);
513 * Error function for OpenEMR sql functions
515 * @param string $statement
516 * @param string $sqlerr
518 function HelpfulDie ($statement, $sqlerr='')
520 echo "<p><p><font color='red'>ERROR:</font> $statement<p>";
522 echo "Error: <font color='red'>$sqlerr</font><p>";
528 * @todo document use of the generate_id function
530 function generate_id () {
531 $database = $GLOBALS['adodb']['db'];
532 return $database->GenID("sequences");
536 * Specialized sql query in OpenEMR with limited functionality
538 * Does not fully incorporate the audit engine, so
539 * recommend not using this function (if bind is set,
540 * then will get logged, however if bind is not set,
541 * then will not get logged).
542 * Function that will allow use of the adodb binding
543 * feature to prevent sql-injection. Will continue to
544 * be compatible with previous function calls that do
546 * If use adodb binding, then will return a recordset object.
547 * If do not use binding, then will return a resource object.
548 * The sqlFetchArray() function should be used to
549 * utilize the return object (it will accept both recordset
550 * and resource objects).
553 * @param string $statement query
554 * @param array $binds binded variables array (optional)
555 * @return recordset/resource
557 function sqlQ($statement, $binds=NULL )
559 if (is_array($binds)) {
560 $recordset = $GLOBALS['adodb']['db']->Execute( $statement, $binds ) or
561 HelpfulDie("query failed: $statement", $GLOBALS['adodb']['db']->ErrorMsg());
565 $resource = mysql_query($statement, $GLOBALS['dbh']) or
566 HelpfulDie("query failed: $statement", mysql_error($GLOBALS['dbh']));
572 * Simple wrapper for sqlInsert() function (deprecated).
574 * Function that will allow use of the adodb binding feature
575 * to prevent sql-injection.
578 * @param string $statement query
579 * @param array $binds binded variables array (optional)
580 * @return integer Last id generated from the sql insert command
582 function idSqlStatement($statement , $binds=NULL )
584 return sqlInsert($statement, $binds);
588 * Simple wrapper for sqlInsert() function (deprecated).
590 * Function that will allow use of the adodb binding feature
591 * to prevent sql-injection.
594 * @param string $statement query
595 * @param array $binds binded variables array (optional)
596 * @return integer Last id generated from the sql insert command
598 function sqlInsertClean($statement, $binds=NULL )
600 return sqlInsert($statement, $binds);
604 * Sql connection function (deprecated)
609 * @param string $login
610 * @param string $pass
611 * @param string $dbase
612 * @param string $host
613 * @param string $port
616 function sqlConnect($login,$pass,$dbase,$host,$port = '3306')
618 $GLOBALS['dbh'] = $database->_connectionID;
619 return $GLOBALS['dbh'];
623 * Sql close connection function (deprecated)
625 * No longer needed since PHP does this automatically.
632 //----------Close our mysql connection
633 $closed = $GLOBALS['adodb']['db']->close or
634 HelpfulDie("could not disconnect from mysql server link", $GLOBALS['adodb']['db']->ErrorMsg());
639 * Very simple wrapper function and not necessary (deprecated)
647 return $GLOBALS['adodb']['db'];