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
129 $database->PConnect($host, $login, $pass, $dbase);
130 $GLOBALS['adodb']['db'] = $database;
131 $GLOBALS['dbh'] = $database->_connectionID;
133 // Modified 5/2009 by BM for UTF-8 project ---------
134 if (!$disable_utf8_flag) {
135 $success_flag = $database->Execute("SET NAMES 'utf8'");
136 if (!$success_flag) {
137 error_log("PHP custom error: from openemr library/sql.inc - Unable to set up UTF8 encoding with mysql database: ".$database->ErrorMsg(), 0);
141 // set up associations in adodb calls (not sure why above define
142 // command does not work)
143 $GLOBALS['adodb']['db']->SetFetchMode(ADODB_FETCH_ASSOC);
145 //fmg: This makes the login screen informative when no connection can be made
146 if (!$GLOBALS['dbh']) {
147 //try to be more helpful
148 if ($host == "localhost") {
149 echo "Check that mysqld is running.<p>";
151 echo "Check that you can ping the server '$host'.<p>";
153 HelpfulDie("Could not connect to server!", mysql_error($GLOBALS['dbh']));
158 * Standard sql query in OpenEMR.
160 * Function that will allow use of the adodb binding
161 * feature to prevent sql-injection. Will continue to
162 * be compatible with previous function calls that do
164 * If use adodb binding, then will return a recordset object.
165 * If do not use binding, then will return a resource object.
166 * The sqlFetchArray() function should be used to
167 * utilize the return object (it will accept both recordset
168 * and resource objects).
170 * @param string $statement query
171 * @param array $binds binded variables array (optional)
172 * @return recordset/resource
174 function sqlStatement($statement, $binds=NULL )
176 if (is_array($binds)) {
177 // Use adodb Execute with binding and return a recordset.
178 // Note that the auditSQLEvent function is embedded
179 // in the Execute command.
180 $recordset = $GLOBALS['adodb']['db']->Execute( $statement, $binds );
181 if ($recordset === FALSE) {
182 HelpfulDie("query failed: $statement", $GLOBALS['adodb']['db']->ErrorMsg());
187 // Use mysql_query and return a resource.
188 $resource = mysql_query($statement, $GLOBALS['dbh']);
189 if ($resource === FALSE) {
190 auditSQLEvent($statement, FALSE, $binds);
191 HelpfulDie("query failed: $statement", mysql_error($GLOBALS['dbh']));
193 auditSQLEvent($statement, TRUE, $binds);
199 * Specialized sql query in OpenEMR that skips auditing.
201 * Function that will allow use of the adodb binding
202 * feature to prevent sql-injection. Will continue to
203 * be compatible with previous function calls that do
204 * not use binding. It is equivalent to the
205 * sqlStatement() function, EXCEPT it skips the
206 * audit engine. This function should only be used
207 * in very special situations.
208 * If use adodb binding, then will return a recordset object.
209 * If do not use binding, then will return a resource object.
210 * The sqlFetchArray() function should be used to
211 * utilize the return object (it will accept both recordset
212 * and resource objects).
214 * @param string $statement query
215 * @param array $binds binded variables array (optional)
216 * @return recordset/resource
218 function sqlStatementNoLog($statement, $binds=NULL )
220 if (is_array($binds)) {
221 // Use adodb ExecuteNoLog with binding and return a recordset.
222 $recordset = $GLOBALS['adodb']['db']->ExecuteNoLog( $statement, $binds );
223 if ($recordset === FALSE) {
224 HelpfulDie("query failed: $statement", $GLOBALS['adodb']['db']->ErrorMsg());
229 // Use mysql_query and return a resource.
230 $resource = mysql_query($statement, $GLOBALS['dbh']);
231 if ($resource === FALSE) {
232 HelpfulDie("query failed: $statement", mysql_error($GLOBALS['dbh']));
239 * Returns a row (as an array) from a sql recordset or resource object.
241 * Function that will allow use of the adodb binding
242 * feature to prevent sql-injection.
243 * It will act upon the object returned from the
244 * sqlStatement() function (and sqlQ() function).
245 * It will automatically figure out if the input
246 * object is a recordset or a resource.
248 * @param recordset/resource $r
251 function sqlFetchArray($r)
253 if (!is_resource($r)) {
254 //treat as an adodb recordset
259 //ensure it's an object (ie. is set)
262 return $r->FetchRow();
265 //treat as a mysql_query resource
268 return mysql_fetch_array($r, MYSQL_ASSOC);
273 * Standard sql insert query in OpenEMR.
275 * Function that will allow use of the adodb binding
276 * feature to prevent sql-injection. This function
277 * is specialized for insert function and will return
278 * the last id generated from the insert.
280 * @param string $statement query
281 * @param array $binds binded variables array (optional)
282 * @return integer Last id generated from the sql insert command
284 function sqlInsert($statement, $binds=array())
286 //Run a adodb execute
287 // Note the auditSQLEvent function is embedded in the
289 $recordset = $GLOBALS['adodb']['db']->Execute($statement, $binds);
290 if ($recordset === FALSE) {
291 HelpfulDie("insert failed: $statement", $GLOBALS['adodb']['db']->ErrorMsg());
293 // Return the correct last id generated using function
294 // that is safe with the audit engine.
295 return getSqlLastID();
299 * Specialized sql query in OpenEMR that only returns
300 * the first row of query results as an associative array.
302 * Function that will allow use of the adodb binding
303 * feature to prevent sql-injection.
305 * @param string $statement query
306 * @param array $binds binded variables array (optional)
309 function sqlQuery($statement, $binds=NULL)
311 if (is_array($binds)) {
312 $recordset = $GLOBALS['adodb']['db']->Execute( $statement, $binds );
315 $recordset = $GLOBALS['adodb']['db']->Execute( $statement );
317 if ($recordset === FALSE) {
318 HelpfulDie("query failed: $statement", $GLOBALS['adodb']['db']->ErrorMsg());
322 $rez = $recordset->FetchRow();
329 * Specialized sql query in OpenEMR that bypasses the auditing engine
330 * and only returns the first row of query results as an associative array.
332 * Function that will allow use of the adodb binding
333 * feature to prevent sql-injection. It is equivalent to the
334 * sqlQuery() function, EXCEPT it skips the
335 * audit engine. This function should only be used
336 * in very special situations.
338 * @param string $statement query
339 * @param array $binds binded variables array (optional)
342 function sqlQueryNoLog($statement, $binds=NULL)
344 if (is_array($binds)) {
345 $recordset = $GLOBALS['adodb']['db']->ExecuteNoLog( $statement, $binds );
348 $recordset = $GLOBALS['adodb']['db']->ExecuteNoLog( $statement );
350 if ($recordset === FALSE) {
351 HelpfulDie("query failed: $statement", $GLOBALS['adodb']['db']->ErrorMsg());
355 $rez = $recordset->FetchRow();
362 * Specialized sql query in OpenEMR that ignores sql errors, bypasses the
363 * auditing engine and only returns the first row of query results as an
366 * Function that will allow use of the adodb binding
367 * feature to prevent sql-injection. It is equivalent to the
368 * sqlQuery() function, EXCEPT it skips the
369 * audit engine and ignores erros. This function should only be used
370 * in very special situations.
372 * @param string $statement query
373 * @param array $binds binded variables array (optional)
376 function sqlQueryNoLogIgnoreError($statement, $binds=NULL)
378 if (is_array($binds)) {
379 $recordset = $GLOBALS['adodb']['db']->ExecuteNoLog( $statement, $binds );
382 $recordset = $GLOBALS['adodb']['db']->ExecuteNoLog( $statement );
384 if ($recordset === FALSE) {
385 // ignore the error and return FALSE
390 $rez = $recordset->FetchRow();
397 * Specialized sql query in OpenEMR that skips auditing.
399 * This function should only be used in very special situations.
401 * @param string $statement query
403 function sqlInsertClean_audit($statement)
406 $ret = $GLOBALS['adodb']['db']->ExecuteNoLog($statement);
407 if ($ret === FALSE) {
408 HelpfulDie("insert failed: $statement", $GLOBALS['adodb']['db']->ErrorMsg());
413 * Function that will safely return the last ID inserted,
414 * and accounts for the audit engine.
416 * @return integer Last ID that was inserted into sql
418 function getSqlLastID() {
419 if ($GLOBALS['lastidado'] >0) {
420 return $GLOBALS['lastidado'];
423 return $GLOBALS['adodb']['db']->Insert_ID();
428 * Function that will return an array listing
429 * of columns that exist in a table.
431 * @param string $table sql table
434 function sqlListFields($table) {
435 $sql = "SHOW COLUMNS FROM ". mysql_real_escape_string($table);
436 $resource = sqlQ($sql);
437 $field_list = array();
438 while($row = mysql_fetch_array($resource)) {
439 $field_list[] = $row['Field'];
445 * Returns the number of sql rows
447 * Function that will allow use of the adodb binding
448 * feature to prevent sql-injection.
449 * It will act upon the object returned from the
450 * sqlStatement() function (and sqlQ() function).
451 * It will automatically figure out if the input
452 * object is a recordset or a resource.
454 * @param recordset/resource $r
455 * @return integer Number of rows
457 function sqlNumRows($r)
459 if (!is_resource($r)) {
460 //treat as an adodb recordset
461 return $r->RecordCount();
464 //treat as a mysql_query resource
465 return mysql_num_rows($r);
470 * Error function for OpenEMR sql functions
472 * @param string $statement
473 * @param string $sqlerr
475 function HelpfulDie ($statement, $sqlerr='')
477 echo "<p><p><font color='red'>ERROR:</font> $statement<p>";
479 echo "Error: <font color='red'>$sqlerr</font><p>";
485 * @todo document use of the generate_id function
487 function generate_id () {
488 $database = $GLOBALS['adodb']['db'];
489 return $database->GenID("sequences");
493 * Specialized sql query in OpenEMR with limited functionality
495 * Does not fully incorporate the audit engine, so
496 * recommend not using this function (if bind is set,
497 * then will get logged, however if bind is not set,
498 * then will not get logged).
499 * Function that will allow use of the adodb binding
500 * feature to prevent sql-injection. Will continue to
501 * be compatible with previous function calls that do
503 * If use adodb binding, then will return a recordset object.
504 * If do not use binding, then will return a resource object.
505 * The sqlFetchArray() function should be used to
506 * utilize the return object (it will accept both recordset
507 * and resource objects).
510 * @param string $statement query
511 * @param array $binds binded variables array (optional)
512 * @return recordset/resource
514 function sqlQ($statement, $binds=NULL )
516 if (is_array($binds)) {
517 $recordset = $GLOBALS['adodb']['db']->Execute( $statement, $binds ) or
518 HelpfulDie("query failed: $statement", $GLOBALS['adodb']['db']->ErrorMsg());
522 $resource = mysql_query($statement, $GLOBALS['dbh']) or
523 HelpfulDie("query failed: $statement", mysql_error($GLOBALS['dbh']));
529 * Simple wrapper for sqlInsert() function (deprecated).
531 * Function that will allow use of the adodb binding feature
532 * to prevent sql-injection.
535 * @param string $statement query
536 * @param array $binds binded variables array (optional)
537 * @return integer Last id generated from the sql insert command
539 function idSqlStatement($statement , $binds=NULL )
541 return sqlInsert($statement, $binds);
545 * Simple wrapper for sqlInsert() function (deprecated).
547 * Function that will allow use of the adodb binding feature
548 * to prevent sql-injection.
551 * @param string $statement query
552 * @param array $binds binded variables array (optional)
553 * @return integer Last id generated from the sql insert command
555 function sqlInsertClean($statement, $binds=NULL )
557 return sqlInsert($statement, $binds);
561 * Sql connection function (deprecated)
566 * @param string $login
567 * @param string $pass
568 * @param string $dbase
569 * @param string $host
570 * @param string $port
573 function sqlConnect($login,$pass,$dbase,$host,$port = '3306')
575 $GLOBALS['dbh'] = $database->_connectionID;
576 return $GLOBALS['dbh'];
580 * Sql close connection function (deprecated)
582 * No longer needed since PHP does this automatically.
589 //----------Close our mysql connection
590 $closed = $GLOBALS['adodb']['db']->close or
591 HelpfulDie("could not disconnect from mysql server link", $GLOBALS['adodb']['db']->ErrorMsg());
596 * Very simple wrapper function and not necessary (deprecated)
604 return $GLOBALS['adodb']['db'];