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 * Returns a row (as an array) from a sql recordset or resource object.
246 * Function that will allow use of the adodb binding
247 * feature to prevent sql-injection.
248 * It will act upon the object returned from the
249 * sqlStatement() function (and sqlQ() function).
250 * It will automatically figure out if the input
251 * object is a recordset or a resource.
253 * @param recordset/resource $r
256 function sqlFetchArray($r)
258 if (!is_resource($r)) {
259 //treat as an adodb recordset
264 //ensure it's an object (ie. is set)
267 return $r->FetchRow();
270 //treat as a mysql_query resource
273 return mysql_fetch_array($r, MYSQL_ASSOC);
278 * Standard sql insert query in OpenEMR.
280 * Function that will allow use of the adodb binding
281 * feature to prevent sql-injection. This function
282 * is specialized for insert function and will return
283 * the last id generated from the insert.
285 * @param string $statement query
286 * @param array $binds binded variables array (optional)
287 * @return integer Last id generated from the sql insert command
289 function sqlInsert($statement, $binds=array())
291 //Run a adodb execute
292 // Note the auditSQLEvent function is embedded in the
294 $recordset = $GLOBALS['adodb']['db']->Execute($statement, $binds);
295 if ($recordset === FALSE) {
296 HelpfulDie("insert failed: $statement", $GLOBALS['adodb']['db']->ErrorMsg());
298 // Return the correct last id generated using function
299 // that is safe with the audit engine.
300 return getSqlLastID();
304 * Specialized sql query in OpenEMR that only returns
305 * the first row of query results as an associative array.
307 * Function that will allow use of the adodb binding
308 * feature to prevent sql-injection.
310 * @param string $statement query
311 * @param array $binds binded variables array (optional)
314 function sqlQuery($statement, $binds=NULL)
316 if (is_array($binds)) {
317 $recordset = $GLOBALS['adodb']['db']->Execute( $statement, $binds );
320 $recordset = $GLOBALS['adodb']['db']->Execute( $statement );
322 if ($recordset === FALSE) {
323 HelpfulDie("query failed: $statement", $GLOBALS['adodb']['db']->ErrorMsg());
327 $rez = $recordset->FetchRow();
334 * Specialized sql query in OpenEMR that bypasses the auditing engine
335 * and only returns the first row of query results as an associative array.
337 * Function that will allow use of the adodb binding
338 * feature to prevent sql-injection. It is equivalent to the
339 * sqlQuery() function, EXCEPT it skips the
340 * audit engine. This function should only be used
341 * in very special situations.
343 * @param string $statement query
344 * @param array $binds binded variables array (optional)
347 function sqlQueryNoLog($statement, $binds=NULL)
349 if (is_array($binds)) {
350 $recordset = $GLOBALS['adodb']['db']->ExecuteNoLog( $statement, $binds );
353 $recordset = $GLOBALS['adodb']['db']->ExecuteNoLog( $statement );
355 if ($recordset === FALSE) {
356 HelpfulDie("query failed: $statement", $GLOBALS['adodb']['db']->ErrorMsg());
360 $rez = $recordset->FetchRow();
367 * Specialized sql query in OpenEMR that ignores sql errors, bypasses the
368 * auditing engine and only returns the first row of query results as an
371 * Function that will allow use of the adodb binding
372 * feature to prevent sql-injection. It is equivalent to the
373 * sqlQuery() function, EXCEPT it skips the
374 * audit engine and ignores erros. This function should only be used
375 * in very special situations.
377 * @param string $statement query
378 * @param array $binds binded variables array (optional)
381 function sqlQueryNoLogIgnoreError($statement, $binds=NULL)
383 if (is_array($binds)) {
384 $recordset = $GLOBALS['adodb']['db']->ExecuteNoLog( $statement, $binds );
387 $recordset = $GLOBALS['adodb']['db']->ExecuteNoLog( $statement );
389 if ($recordset === FALSE) {
390 // ignore the error and return FALSE
395 $rez = $recordset->FetchRow();
402 * Specialized sql query in OpenEMR that skips auditing.
404 * This function should only be used in very special situations.
406 * @param string $statement query
408 function sqlInsertClean_audit($statement)
411 $ret = $GLOBALS['adodb']['db']->ExecuteNoLog($statement);
412 if ($ret === FALSE) {
413 HelpfulDie("insert failed: $statement", $GLOBALS['adodb']['db']->ErrorMsg());
418 * Function that will safely return the last ID inserted,
419 * and accounts for the audit engine.
421 * @return integer Last ID that was inserted into sql
423 function getSqlLastID() {
424 if ($GLOBALS['lastidado'] >0) {
425 return $GLOBALS['lastidado'];
428 return $GLOBALS['adodb']['db']->Insert_ID();
433 * Function that will return an array listing
434 * of columns that exist in a table.
436 * @param string $table sql table
439 function sqlListFields($table) {
440 $sql = "SHOW COLUMNS FROM ". mysql_real_escape_string($table);
441 $resource = sqlQ($sql);
442 $field_list = array();
443 while($row = mysql_fetch_array($resource)) {
444 $field_list[] = $row['Field'];
450 * Returns the number of sql rows
452 * Function that will allow use of the adodb binding
453 * feature to prevent sql-injection.
454 * It will act upon the object returned from the
455 * sqlStatement() function (and sqlQ() function).
456 * It will automatically figure out if the input
457 * object is a recordset or a resource.
459 * @param recordset/resource $r
460 * @return integer Number of rows
462 function sqlNumRows($r)
464 if (!is_resource($r)) {
465 //treat as an adodb recordset
466 return $r->RecordCount();
469 //treat as a mysql_query resource
470 return mysql_num_rows($r);
475 * Error function for OpenEMR sql functions
477 * @param string $statement
478 * @param string $sqlerr
480 function HelpfulDie ($statement, $sqlerr='')
482 echo "<p><p><font color='red'>ERROR:</font> $statement<p>";
484 echo "Error: <font color='red'>$sqlerr</font><p>";
490 * @todo document use of the generate_id function
492 function generate_id () {
493 $database = $GLOBALS['adodb']['db'];
494 return $database->GenID("sequences");
498 * Specialized sql query in OpenEMR with limited functionality
500 * Does not fully incorporate the audit engine, so
501 * recommend not using this function (if bind is set,
502 * then will get logged, however if bind is not set,
503 * then will not get logged).
504 * Function that will allow use of the adodb binding
505 * feature to prevent sql-injection. Will continue to
506 * be compatible with previous function calls that do
508 * If use adodb binding, then will return a recordset object.
509 * If do not use binding, then will return a resource object.
510 * The sqlFetchArray() function should be used to
511 * utilize the return object (it will accept both recordset
512 * and resource objects).
515 * @param string $statement query
516 * @param array $binds binded variables array (optional)
517 * @return recordset/resource
519 function sqlQ($statement, $binds=NULL )
521 if (is_array($binds)) {
522 $recordset = $GLOBALS['adodb']['db']->Execute( $statement, $binds ) or
523 HelpfulDie("query failed: $statement", $GLOBALS['adodb']['db']->ErrorMsg());
527 $resource = mysql_query($statement, $GLOBALS['dbh']) or
528 HelpfulDie("query failed: $statement", mysql_error($GLOBALS['dbh']));
534 * Simple wrapper for sqlInsert() function (deprecated).
536 * Function that will allow use of the adodb binding feature
537 * to prevent sql-injection.
540 * @param string $statement query
541 * @param array $binds binded variables array (optional)
542 * @return integer Last id generated from the sql insert command
544 function idSqlStatement($statement , $binds=NULL )
546 return sqlInsert($statement, $binds);
550 * Simple wrapper for sqlInsert() function (deprecated).
552 * Function that will allow use of the adodb binding feature
553 * to prevent sql-injection.
556 * @param string $statement query
557 * @param array $binds binded variables array (optional)
558 * @return integer Last id generated from the sql insert command
560 function sqlInsertClean($statement, $binds=NULL )
562 return sqlInsert($statement, $binds);
566 * Sql connection function (deprecated)
571 * @param string $login
572 * @param string $pass
573 * @param string $dbase
574 * @param string $host
575 * @param string $port
578 function sqlConnect($login,$pass,$dbase,$host,$port = '3306')
580 $GLOBALS['dbh'] = $database->_connectionID;
581 return $GLOBALS['dbh'];
585 * Sql close connection function (deprecated)
587 * No longer needed since PHP does this automatically.
594 //----------Close our mysql connection
595 $closed = $GLOBALS['adodb']['db']->close or
596 HelpfulDie("could not disconnect from mysql server link", $GLOBALS['adodb']['db']->ErrorMsg());
601 * Very simple wrapper function and not necessary (deprecated)
609 return $GLOBALS['adodb']['db'];