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 * Stashing the insert ID into lastidado so it doesn't get clobbered when
39 * we insert into the audit log.
41 * @param string $sql query
42 * @param array $inputarr binded variables array (optional)
43 * @return boolean returns false if error
45 function Execute($sql,$inputarr=false)
47 $retval= parent::Execute($sql,$inputarr);
48 $GLOBALS['lastidado']=$this->Insert_ID();
49 $outcome= ($retval === false) ? false : true;
50 auditSQLEvent($sql,$outcome,$inputarr);
55 * ADODB _insertid function wrapper to ensure proper auditing in OpenEMR.
57 * Need to override this method to prevent infinite recursion with execute
58 * when trying to retrieve the last insert id.
60 * @return boolean returns false if error
64 $rs=$this->ExecuteNoLog("SELECT LAST_INSERT_ID()");
65 $ret=reset($rs->fields);
71 * ADODB Execute function wrapper to skip auditing in OpenEMR.
73 * Bypasses the OpenEMR auditing engine.
75 * @param string $sql query
76 * @param array $inputarr binded variables array (optional)
77 * @return boolean returns false if error
79 function ExecuteNoLog($sql,$inputarr=false)
81 return parent::Execute($sql,$inputarr);
84 if (!defined('ADODB_FETCH_ASSOC')) define('ADODB_FETCH_ASSOC', 2);
85 $database = NewADOConnection("mysql_log"); // Use the subclassed driver which logs execute events
87 $database->PConnect($host, $login, $pass, $dbase);
88 $GLOBALS['adodb']['db'] = $database;
89 $GLOBALS['dbh'] = $database->_connectionID;
91 // Modified 5/2009 by BM for UTF-8 project ---------
92 if (!$disable_utf8_flag) {
93 $success_flag = $database->Execute("SET NAMES 'utf8'");
95 error_log("PHP custom error: from openemr library/sql.inc - Unable to set up UTF8 encoding with mysql database: ".$database->ErrorMsg(), 0);
99 // set up associations in adodb calls (not sure why above define
100 // command does not work)
101 $GLOBALS['adodb']['db']->SetFetchMode(ADODB_FETCH_ASSOC);
103 //fmg: This makes the login screen informative when no connection can be made
104 if (!$GLOBALS['dbh']) {
105 //try to be more helpful
106 if ($host == "localhost") {
107 echo "Check that mysqld is running.<p>";
109 echo "Check that you can ping the server '$host'.<p>";
111 HelpfulDie("Could not connect to server!", mysql_error($GLOBALS['dbh']));
116 * Standard sql query in OpenEMR.
118 * Function that will allow use of the adodb binding
119 * feature to prevent sql-injection. Will continue to
120 * be compatible with previous function calls that do
122 * If use adodb binding, then will return a recordset object.
123 * If do not use binding, then will return a resource object.
124 * The sqlFetchArray() function should be used to
125 * utilize the return object (it will accept both recordset
126 * and resource objects).
128 * @param string $statement query
129 * @param array $binds binded variables array (optional)
130 * @return recordset/resource
132 function sqlStatement($statement, $binds=NULL )
134 if (is_array($binds)) {
135 // Use adodb Execute with binding and return a recordset.
136 // Note that the auditSQLEvent function is embedded
137 // in the Execute command.
138 $recordset = $GLOBALS['adodb']['db']->Execute( $statement, $binds );
139 if ($recordset === FALSE) {
140 HelpfulDie("query failed: $statement", $GLOBALS['adodb']['db']->ErrorMsg());
145 // Use mysql_query and return a resource.
146 $resource = mysql_query($statement, $GLOBALS['dbh']);
147 if ($resource === FALSE) {
148 auditSQLEvent($statement, FALSE, $binds);
149 HelpfulDie("query failed: $statement", mysql_error($GLOBALS['dbh']));
151 auditSQLEvent($statement, TRUE, $binds);
157 * Specialized sql query in OpenEMR that skips auditing.
159 * Function that will allow use of the adodb binding
160 * feature to prevent sql-injection. Will continue to
161 * be compatible with previous function calls that do
162 * not use binding. It is equivalent to the
163 * sqlStatement() function, EXCEPT it skips the
164 * audit engine. This function should only be used
165 * in very special situations.
166 * If use adodb binding, then will return a recordset object.
167 * If do not use binding, then will return a resource object.
168 * The sqlFetchArray() function should be used to
169 * utilize the return object (it will accept both recordset
170 * and resource objects).
172 * @param string $statement query
173 * @param array $binds binded variables array (optional)
174 * @return recordset/resource
176 function sqlStatementNoLog($statement, $binds=NULL )
178 if (is_array($binds)) {
179 // Use adodb ExecuteNoLog with binding and return a recordset.
180 $recordset = $GLOBALS['adodb']['db']->ExecuteNoLog( $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 HelpfulDie("query failed: $statement", mysql_error($GLOBALS['dbh']));
197 * Returns a row (as an array) from a sql recordset or resource object.
199 * Function that will allow use of the adodb binding
200 * feature to prevent sql-injection.
201 * It will act upon the object returned from the
202 * sqlStatement() function (and sqlQ() function).
203 * It will automatically figure out if the input
204 * object is a recordset or a resource.
206 * @param recordset/resource $r
209 function sqlFetchArray($r)
211 if (!is_resource($r)) {
212 //treat as an adodb recordset
217 //ensure it's an object (ie. is set)
220 return $r->FetchRow();
223 //treat as a mysql_query resource
226 return mysql_fetch_array($r, MYSQL_ASSOC);
231 * Standard sql insert query in OpenEMR.
233 * Function that will allow use of the adodb binding
234 * feature to prevent sql-injection. This function
235 * is specialized for insert function and will return
236 * the last id generated from the insert.
238 * @param string $statement query
239 * @param array $binds binded variables array (optional)
240 * @return integer Last id generated from the sql insert command
242 function sqlInsert($statement, $binds=array())
244 //Run a adodb execute
245 // Note the auditSQLEvent function is embedded in the
247 $recordset = $GLOBALS['adodb']['db']->Execute($statement, $binds);
248 if ($recordset === FALSE) {
249 HelpfulDie("insert failed: $statement", $GLOBALS['adodb']['db']->ErrorMsg());
251 // Return the correct last id generated using function
252 // that is safe with the audit engine.
253 return getSqlLastID();
257 * Specialized sql query in OpenEMR that only returns
258 * the first row of query results as an associative array.
260 * Function that will allow use of the adodb binding
261 * feature to prevent sql-injection.
263 * @param string $statement query
264 * @param array $binds binded variables array (optional)
267 function sqlQuery($statement, $binds=NULL)
269 if (is_array($binds)) {
270 $recordset = $GLOBALS['adodb']['db']->Execute( $statement, $binds );
273 $recordset = $GLOBALS['adodb']['db']->Execute( $statement );
275 if ($recordset === FALSE) {
276 HelpfulDie("query failed: $statement", $GLOBALS['adodb']['db']->ErrorMsg());
280 $rez = $recordset->FetchRow();
287 * Specialized sql query in OpenEMR that bypasses the auditing engine
288 * and only returns the first row of query results as an associative array.
290 * Function that will allow use of the adodb binding
291 * feature to prevent sql-injection. It is equivalent to the
292 * sqlQuery() function, EXCEPT it skips the
293 * audit engine. This function should only be used
294 * in very special situations.
296 * @param string $statement query
297 * @param array $binds binded variables array (optional)
300 function sqlQueryNoLog($statement, $binds=NULL)
302 if (is_array($binds)) {
303 $recordset = $GLOBALS['adodb']['db']->ExecuteNoLog( $statement, $binds );
306 $recordset = $GLOBALS['adodb']['db']->ExecuteNoLog( $statement );
308 if ($recordset === FALSE) {
309 HelpfulDie("query failed: $statement", $GLOBALS['adodb']['db']->ErrorMsg());
313 $rez = $recordset->FetchRow();
320 * Specialized sql query in OpenEMR that ignores sql errors, bypasses the
321 * auditing engine and only returns the first row of query results as an
324 * Function that will allow use of the adodb binding
325 * feature to prevent sql-injection. It is equivalent to the
326 * sqlQuery() function, EXCEPT it skips the
327 * audit engine and ignores erros. This function should only be used
328 * in very special situations.
330 * @param string $statement query
331 * @param array $binds binded variables array (optional)
334 function sqlQueryNoLogIgnoreError($statement, $binds=NULL)
336 if (is_array($binds)) {
337 $recordset = $GLOBALS['adodb']['db']->ExecuteNoLog( $statement, $binds );
340 $recordset = $GLOBALS['adodb']['db']->ExecuteNoLog( $statement );
342 if ($recordset === FALSE) {
343 // ignore the error and return FALSE
348 $rez = $recordset->FetchRow();
355 * Specialized sql query in OpenEMR that skips auditing.
357 * This function should only be used in very special situations.
359 * @param string $statement query
361 function sqlInsertClean_audit($statement)
364 $ret = $GLOBALS['adodb']['db']->ExecuteNoLog($statement);
365 if ($ret === FALSE) {
366 HelpfulDie("insert failed: $statement", $GLOBALS['adodb']['db']->ErrorMsg());
371 * Function that will safely return the last ID inserted,
372 * and accounts for the audit engine.
374 * @return integer Last ID that was inserted into sql
376 function getSqlLastID() {
377 if ($GLOBALS['lastidado'] >0) {
378 return $GLOBALS['lastidado'];
381 return $GLOBALS['adodb']['db']->Insert_ID();
386 * Function that will return an array listing
387 * of columns that exist in a table.
389 * @param string $table sql table
392 function sqlListFields($table) {
393 $sql = "SHOW COLUMNS FROM ". mysql_real_escape_string($table);
394 $resource = sqlQ($sql);
395 $field_list = array();
396 while($row = mysql_fetch_array($resource)) {
397 $field_list[] = $row['Field'];
403 * Returns the number of sql rows
405 * Function that will allow use of the adodb binding
406 * feature to prevent sql-injection.
407 * It will act upon the object returned from the
408 * sqlStatement() function (and sqlQ() function).
409 * It will automatically figure out if the input
410 * object is a recordset or a resource.
412 * @param recordset/resource $r
413 * @return integer Number of rows
415 function sqlNumRows($r)
417 if (!is_resource($r)) {
418 //treat as an adodb recordset
419 return $r->RecordCount();
422 //treat as a mysql_query resource
423 return mysql_num_rows($r);
428 * Error function for OpenEMR sql functions
430 * @param string $statement
431 * @param string $sqlerr
433 function HelpfulDie ($statement, $sqlerr='')
435 echo "<p><p><font color='red'>ERROR:</font> $statement<p>";
437 echo "Error: <font color='red'>$sqlerr</font><p>";
443 * @todo document use of the generate_id function
445 function generate_id () {
446 $database = $GLOBALS['adodb']['db'];
447 return $database->GenID("sequences");
451 * Specialized sql query in OpenEMR with limited functionality
453 * Does not fully incorporate the audit engine, so
454 * recommend not using this function (if bind is set,
455 * then will get logged, however if bind is not set,
456 * then will not get logged).
457 * Function that will allow use of the adodb binding
458 * feature to prevent sql-injection. Will continue to
459 * be compatible with previous function calls that do
461 * If use adodb binding, then will return a recordset object.
462 * If do not use binding, then will return a resource object.
463 * The sqlFetchArray() function should be used to
464 * utilize the return object (it will accept both recordset
465 * and resource objects).
468 * @param string $statement query
469 * @param array $binds binded variables array (optional)
470 * @return recordset/resource
472 function sqlQ($statement, $binds=NULL )
474 if (is_array($binds)) {
475 $recordset = $GLOBALS['adodb']['db']->Execute( $statement, $binds ) or
476 HelpfulDie("query failed: $statement", $GLOBALS['adodb']['db']->ErrorMsg());
480 $resource = mysql_query($statement, $GLOBALS['dbh']) or
481 HelpfulDie("query failed: $statement", mysql_error($GLOBALS['dbh']));
487 * Simple wrapper for sqlInsert() function (deprecated).
489 * Function that will allow use of the adodb binding feature
490 * to prevent sql-injection.
493 * @param string $statement query
494 * @param array $binds binded variables array (optional)
495 * @return integer Last id generated from the sql insert command
497 function idSqlStatement($statement , $binds=NULL )
499 return sqlInsert($statement, $binds);
503 * Simple wrapper for sqlInsert() function (deprecated).
505 * Function that will allow use of the adodb binding feature
506 * to prevent sql-injection.
509 * @param string $statement query
510 * @param array $binds binded variables array (optional)
511 * @return integer Last id generated from the sql insert command
513 function sqlInsertClean($statement, $binds=NULL )
515 return sqlInsert($statement, $binds);
519 * Sql connection function (deprecated)
524 * @param string $login
525 * @param string $pass
526 * @param string $dbase
527 * @param string $host
528 * @param string $port
531 function sqlConnect($login,$pass,$dbase,$host,$port = '3306')
533 $GLOBALS['dbh'] = $database->_connectionID;
534 return $GLOBALS['dbh'];
538 * Sql close connection function (deprecated)
540 * No longer needed since PHP does this automatically.
547 //----------Close our mysql connection
548 $closed = $GLOBALS['adodb']['db']->close or
549 HelpfulDie("could not disconnect from mysql server link", $GLOBALS['adodb']['db']->ErrorMsg());
554 * Very simple wrapper function and not necessary (deprecated)
562 return $GLOBALS['adodb']['db'];