> Do you see any major hangups or screwups on first glance?
Yes.
There is so much wrong with this I don't even know where to begin...
> This function takes 22 parameters: #1 is the table name,
> #2-21 are the row
> names and the values, and #22 is the "integar string".
Dude. Seriously? TWENTY TWO parameters.
Use this for variable number of parameters:
http://us2.php.net/manual/en/function.func-get-args.php
Or how about using an array/hash as your second parameter with the
field=>value pairs.
Which is astonishing since you have the concept of an array with this hack:
$valuesArray = array($value1, $value2, $value3, $value4, $value5,
$value6, $value7, $value8, $value9,
$value10);
foreach ($valuesArray as $key => $value)
The word you're looking for is "INTEGER" not "INTEGAR".
> And is my fear of trying this out on my database unfounded?
No. Don't use it.
> Does this even seem that useful?
No.
Your function is so very limited in scope and use. You're better off writing
a wrapper around the SQL functions and submit direct SQL as the string
parameter to the function. See attached db.inc.php.
You would also be better served using a method/function such as my
base.class.php::sync() which will insert or update a row.
The attached code is about a year old or so and has since been refined
further, but this should give you a good place to start.
http://daevid.com
<?php
#-------------------------------------------------------------------
#
# Confidential - Property of Lockdown Networks, Inc.
# Do not copy or distribute.
# Copyright 2002-2008 Lockdown Networks, Inc. All rights reserved.
#
#-------------------------------------------------------------------
require_once('global.inc.php');
require_once('error.class.php');
class baseClass
{
protected $db = 'V2_Data';
protected $table = NULL;
protected $id = NULL;
protected $created_on = NULL;
protected $_stamp = NULL;
protected $enabled = TRUE;
//we use generic __call __get and __set, but this is a special case.
function get_stamp() { return $this->_stamp; }
function set_stamp($stamp) { $this->_stamp = $stamp; }
/**
* Constructor
*
* @access public
* @return object
* @param mixed $id the ID of the object to load from the
database (this could be a string or usually an integer)
* @author Daevid Vincent [dae...@]
* @version 1.2
* @date 09/20/07
*/
function __construct($id = NULL)
{
if ($_SESSION['companydb']) $this->db = $_SESSION['companydb'];
//this follows the Ruby way for ease of porting/sharring,
please stick with the convention.
if (is_null($this->table) && preg_match( '/y$/',
$this->getClassname() ) > 0)
$this->table = strtolower(preg_replace( '/y$/', 'ies',
$this->getClassName() ));
elseif( is_null( $this->table ) )
$this->table = strtolower($this->getClassName()).'s';
if (!is_null($id)) $this->load($id);
}
/**
* generate a key/value pair from the class' variables.
*
* @access public
* @return array
* @author Daevid Vincent [dae...@]
* @version 1.0
* @date 08/13/07
*/
public function get_array()
{
$row = array();
foreach($this as $key => $value)
$row[$key] = $value;
$row['enabled'] = ($this->enabled) ? 1 : 0;
return $row;
}
/**
* set the class' values based upon a SQL query.
*
* Note: Usually this is called by an extension class,
* which in turn calls the parent::load_from_sql()
* which generates an array and then calls
load_from_array()
*
* @access public
* @return array or false
* @param int $id ID of the object to load
* @author Daevid Vincent [dae...@]
* @version 1.0
* @date 08/20/07
* @see load_from_array()
*/
function load($id = null)
{
if (intval($id) < 1) return false;
$sql = "SELECT *
FROM ".$this->db.".".$this->table."
WHERE id = '".SQL_ESCAPE($id)."'";
$result = $this->load_from_sql($sql); //LIMIT 1 is appended by
base class
if ($result)
return $result;
else
throw new Exception(translate('%1$s threw an exception
trying to load object #%2$s', __CLASS__, $id));
}
/**
* set the class' values based upon a SQL table which is converted to an
array of column(key) value pairs and passed to load_from_array().
*
* @access public
* @return array or false
* @param string $sql SQL schema columns to use as array keys
* @author Daevid Vincent [dae...@]
* @version 1.0
* @date 08/13/07
* @see load_from_array()
*/
public function load_from_sql($sql = null)
{
if (is_null($sql)) return false;
$result = SQL_QUERY($sql." LIMIT 1");
if($result && $row = SQL_ASSOC_ARRAY($result))
{
return $this->load_from_array($row);
}
else return false;
}
/**
* set the class' values based upon an array.
*
* @access public
* @return boolean
* @param array $row class or SQL schema column/value pairs
* @param array $force force loading of value pairs
* @author Daevid Vincent [dae...@]
* @version 1.1
* @date 12/17/07
* @see load_from_sql()
*/
public function load_from_array($row, $force = false)
{
if (!$force && intval($row['id']) < 1) return false;
foreach($row as $key => $value)
$this->$key = $value;
$this->enabled = ($row['enabled'] == '1') ? true : false;
//$this->iterateVisible();
return true;
}
/**
* INSERT or UPDATE an object's Database row.
*
* Pass in an array of column name/value pairs to INSERT/UPDATE those
specifically, using schema defaults for the rest.
*
* @access public
* @return boolean false on error, true on UPDATE, record ID on
INSERT
* @param array $row SQL schema column/value pairs
* @param boolean $auto_escape (true) will wrap all values in
SQL_ESCAPE()
* @param boolean $show_errors toggle SQL errors, use
SQL_ERROR_NUM() or SQL_ERROR() to handle yourself.
* @author Daevid Vincent [dae...@]
* @version 1.2
* @date 10/11/07
*/
public function sync($row = null, $auto_escape = true, $show_errors =
true)
{
if (is_null($this->table)) return false;
if (is_null($row)) $row = $this->get_array();
if (count($row) < 1) return;
//[dv] this is a handy way to shortcut and update a record with
the passed in array key/vals.
if ($row['id'] < 1) unset($row['id']);
if (!$this->id && $row['id'] > 0) $this->id = $row['id'];
//[dv] I thought about scrubbing the $row array of empty
values,
// but that causes a problem if you actually DO want to
wipe out some values.
$row['enabled'] = ($row['enabled']) ? 1 : 0;
if (intval($this->id) < 1)
{
$cols = "`".implode("`, `", array_keys($row))."`";
$temp = array();
foreach ( $row as $val )
{
if (!is_null($val))
{
$value = trim($val);
$temp[] = ($auto_escape === true) ?
"'".SQL_ESCAPE( $val )."'" : $val;
}
else
$temp[] = "NULL";
}
$values = implode(', ',$temp);
$sql = "INSERT INTO ".$this->table." (created_on,
".$cols.") VALUES (NOW(), ".$values.")";
}
else
{
$sql = "UPDATE ".$this->table." SET ";
unset($row['id']);
$temp = array();
foreach ($row as $col => $value)
{
if (!is_null($value))
{
$value = trim($value);
$temp[] = $col." = ".(($auto_escape ===
true) ? "'".SQL_ESCAPE( $value )."'" : $value);
}
else
$temp[] = $col." = NULL";
}
$sql .= implode(', ', $temp);
$sql .= " WHERE id = '".$this->id."' LIMIT 1";
}
if ($sth = SQL_QUERY($sql, false, $show_errors))
{
if (intval($this->id) < 1)
{
$this->id = SQL_INSERT_ID();
$row['id'] = $this->id; //or load_from_array
will fail
$this->load_from_array($row); //[dv] TODO: not
sure if this is needed
AddUserLog('Action', MakeUserLog('Added %1$s
[%2$s]',str_replace('_', ' ', $this->getClassName()), $this->id));
return $this->id;
}
else
{
$row['id'] = $this->id; //or load_from_array
will fail
$this->load_from_array($row); //[dv] TODO: not
sure if this is needed
AddUserLog('Action', MakeUserLog('Saved %1$s
[%2$s]',str_replace('_', ' ', $this->getClassName()), $this->id));
return true;
}
}
else
return false;
}
/**
* Delete the corresponding class object ID from the database.
*
* Note: 'delete' is a reserved word in PHP
*
* @access public
* @return boolean
* @author Daevid Vincent [dae...@]
* @version 1.1
* @date 10/10/07
*/
public function delete()
{
if( intval( $this->id ) < 1 )
return( false );
if (SQL_QUERY("DELETE FROM ".$this->db.".".$this->table." WHERE
id = '".$this->id."' LIMIT 1"))
{
AddUserLog('Action', MakeUserLog('Deleted %1$s
[%2$s]',str_replace('_', ' ', $this->getClassName()), $this->id));
foreach($this as $key => $value)
$this->$key = null;
return true;
}
else
return false;
}
/**
* Shows all exposed variables in this class
*
* @access public
* @return array
* @param boolean $print to print out each value
* @author Daevid Vincent [dae...@]
* @version 1.0
* @date 08/13/07
*/
public function iterateVisible($print = false)
{
if ($print) echo
"\n<BR><B>".$this->getClassName()."::iterateVisible:</B><BR>\n";
$tmp = array();
foreach($this as $key => $value)
{
$tmp[$key] = $value;
if ($print) print $key." => ".$value."<BR>\n";
}
return $tmp;
}
/**
* returns the name of this class as a string
*
* @access public
* @return string
* @author Daevid Vincent [dae...@]
* @version 1.0
*/
public function getClassName()
{
//return __CLASS__;
return get_class($this);
}
/**
* Provides generic getters and setters
*
* @access public
* @param string $method The method name.
* @param array $arguments The arguments passed to the method.
* @return mixed
* @author Daevid Vincent [dae...@]
* @date 08/21/2007
* @version 1.1
* @see __get(), __set()
*/
public function __call( $method, $arguments )
{
$prefix = strtolower( substr( $method, 0, 3 ) );
$property = strtolower( substr( $method, 4 ) );
if ( empty($prefix) || empty($property) ) return;
if ( 'get' == $prefix )
{
if ( property_exists($this, $property) )
return $this->$property;
else
return $this->__get($property);
}
elseif ( 'set' == $prefix )
{
if ( property_exists($this, $property) )
return $this->$property = $arguments[0];
else
return $this->__set($property, $arguments[0]);
}
echo "<p><font color='#ff0000'>Attempted to '".$method."' variable
in class '".$this->getClassName()."'.</font><p>\n";
backtrace();
}
/**
* magic function to handle any accessing of undefined variables.
* Since PHP is "lax" this will help prevent stupid mistakes.
*
* @access public
* @return void
* @param mixed $var name of the variable
* @author Daevid Vincent [dae...@]
* @version 1.0
* @date 08/13/07
* @see __set(), __call()
*/
public function __get($var)
{
echo "<p><font color='#ff0000'>Attempted to __get() variable
'".$var."' in class '".$this->getClassName()."'.</font><p>\n";
backtrace();
}
/**
* magic function to handle any setting of undefined variables.
* Since PHP is "lax" this will help prevent stupid mistakes.
*
* @access public
* @return void
* @param mixed $var name of the variable
* @param mixed $val value of the variable
* @author Daevid Vincent [dae...@]
* @version 1.0
* @date 08/13/07
* @see __get(), __call()
*/
public function __set($var, $val)
{
echo "<p><font color='#ff0000'>Attempted to __set() variable
'".$var."' to '".$val."' in class '".$this->getClassName()."'.</font><p>\n";
backtrace();
}
/**
* The destructor method will be called as soon as all references to a
particular object are removed
* or when the object is explicitly destroyed.
*
* This End User method will save the $_SESSION first
* http://www.php.net/session-set-save-handler
*
* @access public
* @author Daevid Vincent [dae...@]
* @since 1.0
* @version 1.0
* @date 08/13/07
*/
/*
function __destruct()
{
session_write_close();
parent::__destruct();
}
*/
}
?>
<?php
#-------------------------------------------------------------------
#
# Confidential - Property of Lockdown Networks, Inc.
# Do not copy or distribute.
# Copyright 2002-2008 Lockdown Networks, Inc. All rights reserved.
#
#-------------------------------------------------------------------
// I use this 'db.inc.php' for other modules, so that's why the host/user/pw
are not in the globals.php file
// also, it's silly to use define() here since this is the only spot they're
ever used.
$GLOBALS['__DB_HANDLE'] = false;
require_once('ironbars.php'); //[dv] why is this here? what uses it?
//TODO: [dv] we REALLY should make these all $SQLOPTION[] and update them in
ALL files to avoid confusion and collisions...
$OPTION['host'] = '';
$OPTION['username'] = 'root';
$OPTION['password'] = '';
$OPTION['noHTML'] = false;
$OPTION['fullQuery'] = false;
$OPTION['useLogger'] = true;
$OPTION['profile'] = 0;
//$OPTION['outfile'] = false; //set this to a filename, and use $showSQL in
your queries and they'll go to this file.
define ('MAX_SQL_ERRORS', 10);
if (!array_key_exists('autoConnect',$OPTION)) $OPTION['autoConnect'] = true;
if ($OPTION['autoConnect']) SQL_CONNECT("localhost");
/*
* We are (currently) trying to prevent just one trivial type of sql injection.
* Namely, the one that attempts to end query with a ; and then add an extra
query
* to the end. This is a common technique, and the one that is easiest to
detect.
*
* First, we watch for unbalanced quotes. If any are found, the query is
invalid anyway
* and thus will not be allowed to run.
*
* Second, I can't think of a single valid use of a semicolon outside the
literals
* enclosed into ''. Semicolons will be alloedd in those literals, but not
outside.
*
* Single quotes that are in the literals and have been SQL_ESCAPE()'d are
treated properly,
* that is as a single character within the literal. So are the
backslashed-escaped chars.
*
* Any other additions are welcome, but this is at least a good start.
*
* @author Vlad Krupin [v...@]
*/
function IS_SAFE_SQL_QUERY($q){
$len = strlen($q);
$inside = false; // inside a literal (enclosed by '')
$ret = true; // query assumed good unless we can prove otherwise.
for($i = 0; $i < $len; $i++)
{
$more = ($i < ($len - 1)); // we have at least one more
character
// CR3940 - we can't use the $q[$i] here because the bracket
operator doesn't
// currently work with multibyte strings. Yuck.
switch( substr( $q, $i, 1 ) )
{
case "\\":
//[krogebry] Why would there be a test for '$inside'
here?
// anything after a \
should be an escaped char, that's what \ does.
#if($inside && $more)
#{
$i++; // whatever follows MUST be an escaped
character.
#continue;
#}
break;
case "'":
// we are inside the string and came up with a
properly-escaped quote
#if($inside && $more && ($q[$i+1] == "'")){
if($inside && $more && substr( $q, $i, $i - 1 ) == "\\"
){
$i++;
continue;
}
$inside = !$inside;
break;
case ";":
// semicolons outside literals are not permitted.
if(!$inside) return "Possible chain query via
semi-colon injection";
//case "-":
// //testing for -- comments
// if (substr( $q, $i, 2 ) == '--') return "Possible '--
comment' injection.";
// break;
}// switch()
}
if($inside) $ret = "Unbalanced single quotes";
#print "Ret: [$ret]<br/>\n";
return $ret;
}
/**
* Make a connection to a mysql db.
* @access public
* @author Daevid Vincent [dae...@]
*/
function SQL_CONNECT($server = 'localhost')
{
global $OPTION;
$GLOBALS['__CONNECTED_SERVER'] = $server;
if (!$OPTION['username']) $OPTION['username'] = 'root';
if (!$OPTION['password']) $OPTION['password'] = '';
$tries = 5;
for($i = 1; $i <= $tries; $i++)
{
switch ( strtolower($server) )
{
case "1":
case "localhost":
default:
$GLOBALS['__DB_HANDLE'] = @mysql_pconnect("localhost",
$OPTION['username'], $OPTION['password']);
if (is_resource($GLOBALS['__DB_HANDLE'])) break
2;
}
echo translate("Unable to connect to database. Retrying
[%1\$s/%2\$s] in 5 seconds.\n", $i, $tries);
sleep(5);
}
if (!is_resource($GLOBALS['__DB_HANDLE']))
{
echo translate("Could not connect to %1\$s server.
Aborting.\n", $GLOBALS['__CONNECTED_SERVER']);
return false;
}
// Set our connection, results, and client charsets to UTF-8
SQL_QUERY('SET NAMES utf8');
//echo translate("Got __DB_HANDLE %1\$s", $GLOBALS['__DB_HANDLE']);
return $GLOBALS['__DB_HANDLE'];
}
/**
* Save the SQL connection object to a global area
* @access public
* @author Evan Webb [e...@]
*/
function SQL_SAVE_CONN() {
if(!isset($GLOBALS['__DB_HANDLES'])) {
$GLOBALS['__DB_HANDLES'] = array();
}
array_push($GLOBALS['__DB_HANDLES'],$GLOBALS['__DB_HANDLE']);
}
/**
* Reuse a stored connection
* @access public
* @author Evan Webb [e...@]
*/
function SQL_RESTORE_CONN($con=null) {
if(is_null($con)) {
$GLOBALS['__DB_HANDLE'] = array_pop($GLOBALS['__DB_HANDLES']);
} else {
$GLOBALS['__DB_HANDLE'] = $con;
}
}
/**
* Select a db
* @access public
* @author Daevid Vincent [dae...@]
*/
function SQL_DB($dbname, $exit = true)
{
if ( @mysql_select_db($dbname,$GLOBALS['__DB_HANDLE']) )
{
$GLOBALS['__CURRENT_DB'] = $dbname;
return true;
}
else
{
if ($exit == true)
exit("Could not connect to the '".$dbname."'
Database.");
else
return false; //this is in case you want to do your own
error handling.
}
}
/**
* Outputs the SQL to /tmp/SQL_profile.txt in detail.
*
* profile SQL statements in varying detail levels.
* Detail Levels:
* 1 = Y-m-d/ h:i:s
* 2 = SQL timing
* 3 = filename
*
* @access public
* @return boolean on success or failure.
* @param string $sql The SQL query to be executed, this can be SELECT,
INSERT, UPDATE or DELETE amongst others.
* @param int $detail the detail level as an integer 1-3.
* @author Daevid Vincent [dae...@]
* @since 3.11
* @version 1.1
* @date 05/11/05
*/
function SQL_PROFILE($sql, $detail = 3 )
{
if ($detail == 0) return false;
if (!isset($sql)) return false;
if (!$handle = fopen("/tmp/SQL_profile.txt", 'a'))
{
echo "unable to open file /tmp/SQL_profile.txt\n";
return false;
}
//not really required, as they're handled inherently
//if ($detail > 4) $detail = 4;
//if ($detail < 1) $detail = 1;
$text = date("[Y-m-d h:i:s ");
if ($detail >= 2) //start timer
{
list($usec, $sec) = explode(" ",microtime());
$sql_start = ((float)$usec + (float)$sec);
}
$result = @mysql_query($sql, $GLOBALS['__DB_HANDLE']);
if ($detail >= 2) //end timer
{
list($usec, $sec) = explode(" ",microtime());
$text .= number_format( (((float)$usec + (float)$sec) -
$sql_start), 4 ).'s';
}
//we do this here so as not to upset the timer too much
if ($detail >= 3)
{
$text .= ' '.$_SERVER['SCRIPT_FILENAME'];
$traceArray = debug_backtrace();
$text .= ' '.$traceArray[1]['file'].'
('.$traceArray[1]['line'].')';
$text = str_replace('/lockdown/', '', $text);
}
$sql = str_replace("\n", ' ', $sql);
$sql = preg_replace('/\s+/',' ', $sql);
if (!fwrite($handle, $text.'] '.$sql."\n"))
{
echo "unable to write to file /tmp/SQL_profile.txt\n";
return false;
}
@fclose($handle);
return $result;
} //SQL_PROFILE
/**
* Output the HTML debugging string in color coded glory for a sql query
* This is very nice for being able to see many SQL queries
* @access public
* @return void. prints HTML color coded string of the input $query.
* @param string $query The SQL query to be executed.
* @author Daevid Vincent [dae...@]
* @since 4.0
* @version 1.0
* @date 04/05/05
* @todo highlight SQL functions.
*/
function SQL_DEBUG( $query )
{
if( $query == '' ) return 0;
global $SQL_INT;
if( !isset($SQL_INT) ) $SQL_INT = 0;
//[dv] I like my version better...
//require_once('classes/geshi/geshi.php');
//$geshi = new GeSHi($query, 'sql');
//echo $geshi->parse_code();
//return;
//TODO: [dv] I wonder if a better way to do this is to split the string
into array chunks and examine them each individually?
//TODO: [dv] I think we'd get better results if we normalize the $query
string by stripping out any \n\r characters:
$query = str_replace( array("\n", "\r", ' '), ' ', $query);
//[dv] this has to come first or you will have goofy results later.
//[dv] UGH this number one is causing me lots of grief... why can't i
figure out the regex to use?
//highlight numbers
//$query = preg_replace("/[\s=](\d+)\s/", "<FONT
COLOR='#FF6600'>$1</FONT>", $query, -1);
//highlight strings between quote marks
$query = preg_replace("/(['\"])([^'\"]*)(['\"])/i", "$1<FONT
COLOR='#FF6600'>$2</FONT>$3", $query, -1);
//highlight functions
$query = preg_replace("/(\w+)\s?\(/", "<FONT
COLOR='#CC00FF'>".strtoupper('\\1')."</FONT>(", $query, -1);
//underline tables/databases
$query = preg_replace("/(\w+)\./", "<U>$1</U>.", $query, -1);
$query = str_ireplace(
array (
'*',
'SELECT
',
'UPDATE
',
'DELETE
',
'INSERT
',
'INTO ',
'VALUES
',
'FROM ',
'LEFT ',
'JOIN ',
'WHERE
',
'LIMIT
',
'ORDER
BY ',
'AND ',
'OR ',
//[dv] note the space. otherwise you match to 'colOR' ;-)
' DESC',
' ASC',
' ON ',
' AS '
),
array (
"<FONT
COLOR='#FF6600'><B>*</B></FONT>",
"<FONT
COLOR='#00AA00'><B>SELECT </B></FONT>",
"<FONT
COLOR='#00AA00'><B>UPDATE </B></FONT>",
"<FONT
COLOR='#00AA00'><B>DELETE </B></FONT>",
"<FONT
COLOR='#00AA00'><B>INSERT </B></FONT>",
"<FONT
COLOR='#00AA00'><B>INTO </B></FONT>",
"<FONT
COLOR='#00AA00'><B>VALUES </B></FONT>",
"<FONT
COLOR='#00AA00'><B>FROM </B></FONT>",
"<FONT
COLOR='#00CC00'><B>LEFT </B></FONT>",
"<FONT
COLOR='#00CC00'><B>JOIN </B></FONT>",
"<FONT
COLOR='#00AA00'><B>WHERE </B></FONT>",
"<FONT
COLOR='#00AA00'><B>LIMIT </B></FONT>",
"<FONT
COLOR='#00AA00'><B>ORDER BY</B> </FONT>",
"<FONT
COLOR='#0000AA'><B>AND</B> </FONT>",
"<FONT
COLOR='#0000AA'><B>OR</B> </FONT>",
"<FONT
COLOR='#0000AA'> <B>DESC</B></FONT>",
"<FONT
COLOR='#0000AA'> <B>ASC</B></FONT>",
"<FONT
COLOR='#00DD00'> <B>ON</B> </FONT>",
"<FONT
COLOR='#0000AA'> <B>AS</B> </FONT>"
),
$query
);
echo "<FONT COLOR='#0000FF'><B>DEBUG SQL[".$SQL_INT."]:</B>
".$query."<FONT COLOR='#FF0000'>;</FONT></FONT><BR>\n";
$SQL_INT++;
} //SQL_DEBUG
/**
* A wrapper around the mysql_query function.
*
* Handles the $db handle, errors and echoing of the SQL query itself
* and stores any errors in the global variable errorString;
*
* @access public
* @return result set handle pointer suitable for.
* @param string $sql The SQL query to be executed, this can be SELECT,
INSERT, UPDATE or DELETE amongst others.
* @param boolean $showSQL output the $sql to the display (for debugging
purposes usually). false by default.
* @param boolean $showErrors output any errors encountered to the
display (for debugging purposes usually). true by default.
* @param boolean $execute useful for debuging when you don't want the
SQL command to actually execute, but you may want to see the query passed i.e.
SQL_QUERY($sql, true, true, false); true by default.
* @param boolean $noHTML when using the function in console scripts to
strip off HTML tags.
* @param int $profile detail level (1-3) to output the SQL to
/tmp/SQL_profile.txt.
* @param int $count The counter used for recursion
* @param string $errorOutput What format the error message, if any,
should be returned as. Can be txt, xml or html (default).
* @author Daevid Vincent [dae...@]
* @since 3.0
* @version 1.4
* @date 06/04/07
*/
function SQL_QUERY($sql, $showSQL = false, $showErrors = true, $execute = true,
$noHTML = false, $profile = 0, $count = 0, $errorOutput = 'html')
{
global $OPTION;
if ($showSQL)
{
//[dv] the preg_replace will magically strip out the spaces,
newlines, tabs and other funky chars to make one nice string.
$sql = preg_replace("/\s+/",' ', (preg_replace("/\s/",'
',$sql)) );
if ($OPTION['outfile'])
file_put_contents($OPTION['outfile'], 'SQL:
'.$sql."\n", (FILE_APPEND | LOCK_EX) );
elseif ($noHTML || $OPTION['noHTML'])
echo "SQL: ".$sql."\n";
else
SQL_DEBUG( $sql );
}
if ($execute)
{
//[dv] added to remove all comments (which may help with SQL
injections as well.
//$sql = preg_replace("/#.*?[\r\n]/s", '', $sql);
//$sql = preg_replace("/--.*?[\r\n]/s", '', $sql);
//$sql = preg_replace("@/\*(.*?)\*/@s", '', $sql);
// execute query only if it appears to be safe.
if ( ($error_str = IS_SAFE_SQL_QUERY($sql)) === TRUE )
{
if ($OPTION['profile'] > 0) $profile =
$OPTION['profile'];
if ($profile > 0)
$result = SQL_PROFILE($sql, $profile);
else
$result =
@mysql_query($sql,$GLOBALS['__DB_HANDLE']);
} else {
$error = "Malformed query (".$error_str."). Execution
blocked.";
$result = FALSE; // indicate that we failed
}
if (!$result)
{
if(!isset($GLOBALS['SQL_ErrorString']))
$GLOBALS['SQL_ErrorString'] = "";
// if error has not been set, then we have a 'regular'
mysql error. Otherwise it is a potentially malicious query.
if(!isset($error)){
$error = mysql_error($GLOBALS['__DB_HANDLE']);
$errno = mysql_errno($GLOBALS['__DB_HANDLE']);
if(($errno == 2013)||($errno == 2006)) {
if($count > 20) {
logger("Maximum number of reconnect attempts exceeded,
giving up.");
} else {
sleep(2);
if ($errno == 2013)
{
SQL_CONNECT($GLOBALS["__CONNECTED_SERVER"]);
}
elseif ($errno == 2006)
{
SQL_CLOSE();
SQL_CONNECT($GLOBALS["__CONNECTED_SERVER"]);
SQL_DB($GLOBALS['__CURRENT_DB'], false);
}
return SQL_QUERY($sql, $showSQL, $showErrors,
$execute, $noHTML, $profile, $count + 1, $errorOutput);
}
}
}
else $errno = 0; // not 'regular' mysql error? well, we
need some error code anyway.
// get rid of needlessly verbose MySQL error string
$error = preg_replace( '/^You have an error in your SQL
syntax;.*?near\s*/i', 'Syntax error near ', $error );
// trim to size if necessary
if(!$OPTION['fullQuery'] && strlen( $error ) > 100 )
$error = substr( $error, 0, 100 ) . "...";
if ($showErrors && $errorOutput == 'xml' )
{
$GLOBALS['SQL_ErrorString'] .= '<errorcode>' .
$errno . '</errorcode>' . "\r\n";
$GLOBALS['SQL_ErrorString'] .= '<errormsg>' .
mb_htmlentities($error) . '</errormsg>' . "\r\n";
$GLOBALS['SQL_ErrorString'] .= '<errorsql>' .
mb_htmlentities($sql) . '</errorsql>' . "\r\n";
$GLOBALS['SQL_ErrorString'] .=
'<errorbacktrace>' . mb_htmlentities(backtrace(false)) . '</errorbacktrace>' .
"\r\n";
echo $GLOBALS['SQL_ErrorString'];
}
elseif ($showErrors && ( $errorOutput == 'text' ||
$errorOutput == 'txt' ) )
{
$GLOBALS['SQL_ErrorString'] .= 'Error Code:
' . $errno . "\n";
$GLOBALS['SQL_ErrorString'] .= 'Error Message:
' . $error . "\n\n";
$GLOBALS['SQL_ErrorString'] .= 'Error SQL:
' . $sql . "\n\n";
$GLOBALS['SQL_ErrorString'] .= 'Backtrace:
' . backtrace( false, $errorOutput ) . "\n";
echo $GLOBALS['SQL_ErrorString'];
}
elseif ($showErrors)
{
$GLOBALS['SQL_ErrorString'] .= "<B><U>SQL
ERROR</U> ::</B> ".$errno." <B>::</B> ".$error." <BR><FONT
SIZE='-3'><I>".$sql."</I></FONT>\n".backtrace(false);
//TODO: [dv] is there a way to determine if
we're in a CGI vs. Web page?
if ($noHTML or $OPTION['noHTML'])
echo
strip_tags($GLOBALS['SQL_ErrorString'])."\n";
else
echo "<PRE STYLE='text-align: left;
border: thin solid Red; padding: 5px;'><FONT
CLASS='error'>".$GLOBALS['SQL_ErrorString']."</FONT></PRE><BR>\n";
if ($OPTION['outfile'])
{
//echo "Dumping error to outfile:
".$OPTION['outfile']."\n";
file_put_contents($OPTION['outfile'],
strip_tags($GLOBALS['SQL_ErrorString']."\n"), (FILE_APPEND | LOCK_EX) );
}
if ($OPTION['useLogger'])
logger(strip_tags($GLOBALS['SQL_ErrorString']));
} //if ($showErrors)
} //if (!$result)
return $result;
}
return true;
}
/**
* @return int Number of rows in the result set
* @access public
* @author Daevid Vincent [dae...@]
*/
function SQL_NUM_ROWS($rslt)
{
if ($rslt)
return @mysql_num_rows($rslt);
else
return false;
}
/**
* A wrapper around the SQL_QUERY function to return an array of key/value pairs.
*
* This is very useful for those tables that are simply a key/value and you'd
like it in an array
* then you can just reference the array and save yourself a JOIN perhaps.
*
* @access public
* @return array of key/value pairs.
* @param string $sql The SQL query to be executed, this can be SELECT,
INSERT, UPDATE or DELETE amongst others.
* @param boolean $showSQL output the $sql to the display (for debugging
purposes usually). false by default.
* @param boolean $showErrors output any errors encountered to the
display (for debugging purposes usually). true by default.
* @param boolean $execute useful for debuging when you don't want the
SQL command to actually execute, but you may want to see the query passed i.e.
SQL_QUERY($sql, true, true, false); true by default.
* @param boolean $noHTML when using the function in console scripts to
strip off HTML tags.
* @param int $profile detail level (1-3) to output the SQL to
/tmp/SQL_profile.txt.
* @param int $count The counter used for recursion
* @param string $errorOutput What format the error message, if any,
should be returned as. Can be txt, xml or html (default).
* @author Daevid Vincent [dae...@]
* @since 3.0
* @version 1.0
* @date 07/29/04
*/
function SQL_QUERY_ARRAY_PAIR($sql, $showSQL = false, $showErrors = true,
$execute = true, $noHTML = false, $profile = 0, $count = 0, $errorOutput =
'html')
{
$rslt = SQL_QUERY($sql, $showSQL, $showErrors, $execute, $noHTML,
$profile, $count, $errorOutput);
if ($rslt)
{
while(list($key,$value) = SQL_ROW($rslt))
$tmpArray[$key] = $value;
return $tmpArray;
}
return false;
}
/**
* @return array Single element assoc. array
* @access public
* @author Daevid Vincent [dae...@]
*/
function SQL_ASSOC_ARRAY($rslt)
{
if ($rslt)
return @mysql_fetch_assoc($rslt);
else
return false;
}
/**
* @return array Single element array
* @access public
* @author Daevid Vincent [dae...@]
*/
function SQL_ROW($rslt)
{
if ($rslt)
return @mysql_fetch_row($rslt);
else
return false;
}
/**
* @return string Returns the correct view for the current locale
* @param string $locale The locale to look up
* @param bool $check Whether to check if the table/view exists. If
not, use default table
* @access public
*/
function SQL_VIEW_LOCALE($table, $locale = null, $check = true)
{
$view = $table;
if (is_null($locale))
{
if(isset($_SESSION['oplocale']))
$locale = $_SESSION['oplocale'];
}
switch($locale)
{
case 'en':
case 'en_US':
case 'en_US.utf8':
$view .= '_en_US';
break;
case 'en_GOV':
case 'en_GOV.utf8':
$view .= '_en_GOV';
break;
case 'ja':
case 'ja_JP':
case 'ja_JP.utf8':
$view .= '_ja_JP';
break;
}
//important: Either a DB resource must already be set, or the database
needs to
//be in the table name for this to work correctly;
if ($check)
{
$try = SQL_QUERY('SELECT 1 FROM '.$view.' LIMIT 1', false,
false);
if (!$try) $view = $table; //set back to default if view does
not exist
}
return $view;
}
/**
* @access public
* @author Daevid Vincent [dae...@]
*/
function SQL_RESULT($rslt, $row = 0)
{
if ($rslt)
return @mysql_result($rslt, $row);
else
return false;
}
/**
* @return int Insert ID of last insert action
* @access public
* @author Daevid Vincent [dae...@]
*/
function SQL_INSERT_ID()
{
return @mysql_insert_id($GLOBALS['__DB_HANDLE']);
}
/**
* @return int Number of affected rows
* @access public
* @author Daevid Vincent [dae...@]
*/
function SQL_AFFECTED_ROWS()
{
return @mysql_affected_rows($GLOBALS['__DB_HANDLE']);
}
/**
* Free up a mysql pointer
* @access public
* @author Daevid Vincent [dae...@]
*/
function SQL_FREE($rslt)
{
if ($rslt)
return @mysql_free_result($rslt);
else
return false;
}
/**
* @access public
* @author Daevid Vincent [dae...@]
*/
function SQL_ESCAPE($s, $trim = true)
{
if( is_array( $s ) )
foreach( $s as $k => $v )
$escaped[$k] = SQL_ESCAPE( $v, $trim );
else
$escaped = mysql_real_escape_string( $trim ? trim( $s ) : $s );
return( $escaped );
}
/**
* Seek the pointer
* @access public
* @author Daevid Vincent [dae...@]
*/
function SQL_DATA_SEEK($rslt, $row = 0)
{
return mysql_data_seek($rslt, $row);
}
/**
* @return int MySQL error number
* @access public
* @author Daevid Vincent [dae...@]
*/
function SQL_ERROR_NUM()
{
return @mysql_errno($GLOBALS['__DB_HANDLE']);
}
/**
* @return int MySQL error message
* @access public
* @author Daevid Vincent [dae...@]
*/
function SQL_ERROR()
{
return @mysql_error($GLOBALS['__DB_HANDLE']);
}
/**
* Close out the connection to the SQL server
* @access public
* @author Daevid Vincent [dae...@]
*/
function SQL_CLOSE()
{
return @mysql_close($GLOBALS['__DB_HANDLE']);
}
/**
* This returns error 1007 if it exists already, SQL_ERROR supressed
* @access public
* @author Daevid Vincent [dae...@]
*/
function SQL_CREATE_DB($name)
{
//[dv] depricated and not even included in our build of PHP!?
//http://us2.php.net/manual/en/function.mysql-create-db.php
//return mysql_create_db($name, $db);
//[dv] this is not a good way to do this, as it doesn't tell you if it
succeeded or not.
//return SQL_QUERY("CREATE DATABASE IF NOT EXISTS ".$name);
//this returns error 1007 if it exists already, SQL_ERROR supressed
return SQL_QUERY("CREATE DATABASE ".$name, false, false);
}
/**
* Returns the value of the given field in the database.
*
* it's annoying to have to do this to find out the username given their ID,
* or other tedious times when you simply need a quick value in a lookup table
*
* @access public
* @return the number of rows in the SELECT box.
* @param $id the record id for which to retrieve the data
* @param $pk the column to use the $id in. usually the primary key.
* @param $column the column name's value to retrieve.
* @param $dbtable which table (or db.table) does this reside in.
* @author Daevid Vincent [dae...@]
* @since 3.0
* @version 1.0
* @date 07/12/04
*/
function SQL_getField($id, $pk, $column, $dbtable)
{
$sth = SQL_QUERY("SELECT ".$column." FROM ".$dbtable." WHERE ".$pk." =
'".$id."' LIMIT 1");
if ($sth)
{
$r = SQL_ASSOC_ARRAY($sth);
return $r[$column];
}
return false;
}
/**
* Dynamically generates a select box from a SQL query.
*
* The SELECT must return between one and three items.
* first is the VALUE the second is the text to display and optional third is
shown in parenthesis
* if there is only a VALUE, then that is used as the display text too.
* <SELECT><OPTTION VALUE=''></SELECT> form element prefilled in
*
* Tooltips do NOT work in IE. sorry. blame Microsoft for not following W3
standards...
*
* @access public
* @return the number of rows in the SELECT box or false.
* @param $size usually 1, but the select box can be any height.
* @param $name the NAME='$name' parameter of a SELECT tag.
* @param $sql The actual SQL SELECT query that returns between 2 and 3
columns.
* @param $blank add the extra 'empty' <OPTION VALUE=''>.
* @param $auto onChange will cause a form submit if true.
* @param $MatchToThis sometimes it is useful to match $name to something
like $_GET['name'] instead. it is array safe too!
* @param $extratags Any extra CLASS='' or MULTIPLE or whatever to put in
the <SELECT ...> tag.
* @param $locale_domain The gettext domain for localization
* @author Daevid Vincent [dae...@]
* @since 3.0
* @version 1.4
* @date 07/26/06
*/
function SelectBoxSQL($size, $name, $sql, $blank = false, $auto = false,
$MatchToThis = false, $extratags = false, $locale_domain = 'db_gui')
{
global $$name;
$items = 0;
if (intval($size) < 1) $size = 1;
if ($MatchToThis === false) $MatchToThis = $$name;
if ( $qry = SQL_QUERY($sql) )
{
echo "\n<SELECT SIZE='".$size."' NAME=\"".$name."\"
ID=\"".$name."\"";
if ($auto) echo " onChange=\"this.form.submit(); return
true;\"";
if ($extratags) echo " ".$extratags;
//if ($size > 1) echo ' onmouseover="selectBoxTitle(this);"';
echo ">\n";
if (SQL_NUM_ROWS($qry) > 0)
{
if ($blank && is_bool($blank) ) { echo "<OPTION
VALUE=''></OPTION>"; }
elseif ($blank && is_string($blank)) { echo "<OPTION
VALUE=''>".mb_htmlentities(dctranslate($blank, $locale_domain))."</OPTION>"; }
while (@list($key, $text, $description) = SQL_ROW($qry))
{
$items++;
// Check for selectbox sub-headings.
if ( 0 == strncmp( $text, "---", 3 ) )
{
echo "<OPTION VALUE='' DISABLED
CLASS='selectbox-ghosted'>".mb_htmlentities(dctranslate($text, $locale_domain));
}
else
{
echo "\t<OPTION
VALUE='".mb_htmlentities($key)."'";
if (SELECTED_IfInArray($MatchToThis,
$key) || ($key == $MatchToThis)) echo " SELECTED";
//if ($size > 1) echo "
title='".mb_htmlentities(stripslashes($val))."'";
if ($size > 1) echo '
onmouseover="this.title = this.text;"';
echo ">";
echo mb_htmlentities(dctranslate(
(($text)?$text:$key) , $locale_domain));
if ($description) echo "
(".mb_htmlentities(dctranslate($description, $locale_domain)).")";
echo "</OPTION>\n";
}
}
}
echo "\t</SELECT>\n";
SQL_FREE($qry);
return $items;
}
else echo "select box cannot be built because of an invalid SQL
query.\n";
SQL_FREE($qry);
return false;
} // end SelectBoxSQL
/**
* returns a string that can be appended to an SQL statement to form the ORDER
BY portion.
*
* if you want to sort by 'service' in descending order, then simply use
'service_DESC',
* conversely, 'service_ASC' would sort in ascending order. The order of the
elements in the array
* will determine the order they are appended together.
*
* @access public
* @return string of the form ' ORDER BY element[1], element[2],
element[3]'...
* @param $orderBy false, string, or array of elements like so: [sort_by]
=> Array ( [1] => service_DESC [2] => protocol [3] => port )
* @param $default a string to use as the default ORDER BY column
* @since Alcatraz
* @version 1.1
* @date 01/18/05
*/
function parseOrderByArray($orderBy = false, $default = false)
{
$sql = ' ORDER BY ';
if (!is_array($orderBy))
{
//[dv] is_string() is not enough, as empty values are coming
across as strings according to var_dump()
if (strlen($orderBy) > 1)
return $sql.$orderBy;
elseif (is_string($default))
return $sql.$default;
else
return false;
}
foreach ($orderBy as $o)
$tmp[] = str_replace('_', ' ', $o);
return $sql.implode(', ',$tmp);
}
/**
* returns an array of ENUM values from a table/column.
*
* @access public
* @return array of enum values
* @param string $Table the name of the table to query
* @param string $Column the name of the enum column to query
* @param boolean $sorted by default the results are sorted otherwise
they are in the order of the enum schema
* @param boolean $indexed by default the key/value are the same string.
if true, then key is an integer.
* @since 4.2
* @version 1.0
* @date 01/26/06
* @see SelectBoxArray()
*/
function SQL_getEnumValues($Table, $Column, $sorted = true, $indexed = false)
{
if ($dbQuery = SQL_QUERY("SHOW COLUMNS FROM ".$Table." LIKE
'".$Column."'"))
{
$EnumArray = array();
$dbRow = SQL_ASSOC_ARRAY($dbQuery);
$EnumValues = $dbRow['Type'];
$EnumValues = substr($EnumValues, 6, strlen($EnumValues)-8);
$EnumValues = str_replace("','",",",$EnumValues);
if ($indexed)
{
$EnumArray = explode(",",$EnumValues);
if ($sorted) sort($EnumArray);
}
else
{
$tmp = explode(",",$EnumValues);
foreach($tmp as $k => $v) $EnumArray[$v] = $v;
if ($sorted) ksort($EnumArray);
}
return $EnumArray;
}
return false;
}
//these functions are more ENUM related ones that are currently unused, but may
be useful at a later date...
/*
function SelectBoxEnum($table)
{
$describe=SQL_QUERY("describe ".$table);
while ($ligne=SQL_ASSOC_ARRAY($describe))
{
extract($ligne);
if (substr($Type,0,4)=='enum')
{
echo $Type;
$liste=substr($Type,5,strlen($Type));
$liste=substr($liste,0,(strlen($liste)-2));
$enums=explode(',',$liste);
if (sizeof($enums) > 0)
{
echo "<select name='enum'>\n";
for ($i=0; $i < sizeof($enums);$i++)
{
$elem=strtr($enums[$i],"'"," ");
echo "<option
value='".mb_htmlentities($elem)."'>".mb_htmlentities($elem)."</option>\n";
}
echo "</select>";
}
}
}
}
function SSM_inputEnumDBField( $myName, $myTable, $myField, $myDefault="",
$visible=true )
{
// query the DB to extract the enum values
$qqq = "DESCRIBE $myTable $myField";
$result = SQL_QUERY( $qqq );
$arow = SQL_ASSOC_ARRAY( $result );
$myArr = explode( ",", trim( strstr( $arow['Type'], "(" ), "()")) ;
// now format the values as required by SSM_inputSelect()
$idx = 0;
$cnt = count($myArr);
while($idx < $cnt)
{
$myArr[$idx] = trim( $myArr[$idx], "'" );
$idx++;
}
sort( $myArr );
$myList = implode( "|", $myArr );
return SSM_inputSelect( $myName, $myList, $myDefault );
}
*/
/**
* Generates an HTML formatted backtrace to pinpoint exactly where code STB.
*
* taken from the PHP user supplied functions as adodb_backtrace()
* shows the functions, file:// and line #
* this is not database specific, i only include it here for convenience as this
is included on every page,
* and more often than not, your SQL is what barfs, moreso than any other
function...
*
* @access public
* @return an HTML formatted string complete with file, function and line
that barfed
* @param $print defaults to true, but can be false if you just want the
returned string.
* @param $output The type of output that is returned. Default is HTML.
* @author [[email protected]]
* @since 3.0
* @version 1.1
* @date 09/15/04
*/
function backtrace($print = true, $output = 'html')
{
global $SQL_ERROR_COUNT;
if( !isset($SQL_ERROR_COUNT) ) $SQL_ERROR_COUNT = 0;
$s = '';
$MAXSTRLEN = 64;
$s = ( $ouptut == 'html' ? "\n<pre align=left CLASS='error'><B><U>" :
'' ) . 'BACKTRACE' . ( $ouptut == 'html' ? "</U></B>" : '' ) . " ::\n";
$traceArr = debug_backtrace();
array_shift($traceArr);
$tabs = sizeof($traceArr)-1;
foreach ($traceArr as $arr)
{
for ($i=0; $i < $tabs; $i++) $s .= ( $output == 'html' ? '
' : ' ' );
$tabs -= 1;
//$s .= "<FONT CLASS='error'>";
if (isset($arr['class'])) $s .= $arr['class'].'.';
if (isset($arr['args']) && is_array($arr['args']))
foreach($arr['args'] as $v)
{
if (is_null($v)) $args[] = 'null';
else if (is_array($v)) $args[] =
'Array['.sizeof($v).']';
else if (is_object($v)) $args[] =
'Object:'.get_class($v);
else if (is_bool($v)) $args[] = $v ? 'true' :
'false';
else {
$v = (string) @$v;
$str = ( $output == 'html' ?
htmlspecialchars(substr($v,0,$MAXSTRLEN)) : substr($v,0,$MAXSTRLEN) );
if (strlen($v) > $MAXSTRLEN) $str .=
'...';
$args[] = $str;
}
}
if (isset($arr['args']) && is_array($args))
$s .= ( $output == 'html' ? '<B>' : '' ) .
$arr['function'] . '(' . ( $output == 'html' ? '</B>' : '' ) . implode( ', ',
$args ) . ( $output == 'html' ? '<B>' : '' ) . ')' . ( $output == 'html' ?
'</B>' : '' );
//$s .= ( $output == 'html' ? '</FONT>' : '' );
if( $output == 'html' )
$s .= sprintf("<FONT COLOR='#808080' SIZE='-3'> :: line
#%d,"." file: <a
href=\"file:/%s\">%s</a></font>",$arr['line'],$arr['file'],$arr['file']);
else
$s .= sprintf(" :: line #%d, file: %s", $arr['line'],
$arr['file'] );
$s .= "\n";
}
$s .= ( $output == 'html' ? "</pre>\n" : '' );
if ($print) print $s;
if($SQL_ERROR_COUNT++ > MAX_SQL_ERRORS) exit( ( $output == 'html' ?
'<CENTER><H1>' : '' ) . '*** More than ' . MAX_SQL_ERRORS . ' SQL errors.
Aborting script. ***' . ( $output == 'html' ? '</H1></CENTER>' : '' ) );
return $s;
} //backtrace()
/**
* @access public
*/
function update_plugin_cache($company){
update_unsafe_tests($company);
}
/**
* @access public
*/
function update_unsafe_tests($company) {
SQL_QUERY("REPLACE INTO $company.testset SELECT * from V2_Data.testset
WHERE id < 1000");
SQL_QUERY("DELETE
FROM $company.testset_test
WHERE testset_id = 3");
SQL_QUERY("INSERT
INTO $company.testset_test
(SELECT NULL, 3, scan_id
FROM Swordfish.pluginlist
WHERE category IN (3,5,8) OR
scan_id IN (11475) OR
name LIKE '%crashes%' OR
summary LIKE '%crashes%')");
}
/**
* @access public
*/
function guideTableDB($Key, $Attribute="", $Type="4")
{
$Key = trim($Key, "/");
$query = SQL_QUERY("SELECT html FROM
".SQL_VIEW_LOCALE('Swordfish.ld_guide')." WHERE gui_key = '".$Key."' AND
gui_key_type = '".$Type."' LIMIT 1");
if ($query) $data = SQL_ROW($query);
$content = $data[0];
if ($content != '')
{
$content = eregi_replace("h[0-9]>", "b>", $content);
$content = eregi_replace("[0-9]+\..nbsp;", "<li>", $content);
$content = eregi_replace(".nbsp;", " ", $content);
$content = eregi_replace("</b>[\r\n ]+<p ", "</b><ol>\r\r<p ",
$content);
$content = $content."</ol>";
?>
<P>
<!-- HELP EXCERPT -->
<TABLE CLASS="info" ALIGN="CENTER" cellpadding="3" <?= $Attribute ?>>
<TR><TD
CLASS="tableHeadline"><?=translate('Instructions')?></TD></TR>
<TR>
<TD><?= $content ?></TD>
</TR>
<TR>
<TD>
<ul><b><?=translate('Note:')?></b><?=translate('These
instructions may disappear once data is populated on this page. To view this
again, click the <IMG SRC=\'/images/gui/help_button.png\'> button in the
upper-right corner of the screen.')?></ul>
</TD>
</TR>
</TABLE>
<!-- /HELP EXCERPT -->
</P>
<?php
}
}
/**
* Prepare a sql query
* @author krogebry (krogebry@)
* @param string $sql SQL query
* @returns dbObject object
*/
function SQL_PREPARE( $sql, $debug=false )
{
return new dbObject($sql, $debug);
}// SQL_PREPARE()
/**
* Prepared DB object.
* This is what gets passed back from SQL_PREPARE()
* Usage:
* Use just about the same way that perl DBI, or any other high level
* DB abstraction layer works. Use '?' as the replacement key.
* Example:
* $sql = "SELECT * FROM blah WHERE id=?";
* $ptr = SQL_PREPARE( $sql );
* for( $i=0; $i<10; $i++ ){
* $ro = $ptr->execute( array($i) );
* print_x( $ro );
* }
*
* Notes:
* execute simply replaces ? with it's value enclosed in "",
* then returns SQL_QUERY(QUERY)
*/
class dbObject
{
/** Variables */
/** @var $sql SQL query */
private $sql = "";
/** @var $sql SQL query */
private $numMatches = 0;
private $debug = 0;
private $ptr = "";
/** End Variables */
public function __construct($sql, $debug=false) {
$num = preg_match_all( "/\?/", $sql, $matches );
$this->numMatches = $num;
$this->sql = $sql;
$this->debug = $debug;
}// __construct()
/**
* Execute the prepared sql query
* @param array $array Array of replacement values
*/
public function execute( $array, $debug=false ) {
if( sizeof($array) != $this->numMatches )
throw new SQL_EXCEPTION("Invalid matching params for
query [".sizeof($array) ."]::[". $this->numMatches ."]");
$sql = $this->sql;
foreach($array as $a ) {
$sql = preg_replace( "/\?/", "\"". $a ."\"", $sql, 1
);
}
if( $debug==true || $this->debug==true ){
SQL_DEBUG( $sql );
}
if( !$ptr = SQL_QUERY($sql,$this->debug) ){
throw new SQL_EXCEPTION( $sql );
}else{
$this->ptr = $ptr;
return $ptr;
}
}// execute()
public function numRows(){ return SQL_NUM_ROWS( $this->ptr ); }
public function fetchrow(){ return SQL_ASSOC_ARRAY( $this->ptr ); }
}// dbObject
class SQL_EXCEPTION extends Exception
{
private $sql = "";
private $debug = "";
private $die = false;
public function __construct( $sql="", $debug="", $die=false )
{
$this->sql = $sql;
$this->debug = $debug;
$this->die = $die;
}
public function getQuery(){ return $this->sql; }
public function getDebug(){ return $this->debug; }
public function dump( $verbosity=1 ){
$trace = $this->getTrace();
#print_x( $trace );
?>
<style type="text/css">
queryFailure{
display: table;
}
info{
display: table-row;
}
query{
display: table-cell;
}
trace{
display: table;
}
call{
display: table-row;
}
</style>
<queryFailure>
<info>
<query><?=$this->getQuery()?></query>
<debug><?=$this->getDebug()?></debug>
</info>
<trace>
<call></call>
</trace>
</queryFailure>
<?php
}
}// SQL_EXCEPTION
//found here http://us2.php.net/manual/en/function.split.php
function quotesplit( $s, $splitter = ' ', $restore_quotes = 0 )
{
$s = str_replace('""', "'", $s);
$getstrings = explode('"', $splitter.$s.$splitter);
$delimlen = strlen($splitter);
$instring = 0;
while (list($arg, $val) = each($getstrings))
{
if ($instring==1)
{
if( $restore_quotes )
$result[count($result)-1] =
$result[count($result)-1].'"'.$val.'"';
else
$result[] = $val;
$instring = 0;
}
else
{
if ((strlen($val)-$delimlen) >= 1)
{
$temparray = split($splitter, substr($val, $delimlen,
strlen($val)-$delimlen-$delimlen ) );
while(list($iarg, $ival) = each($temparray))
{
$result[] = trim($ival);
}
}
$instring = 1;
}
}
return $result;
}
?>
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
