m0nkeymafia

I split this topic from the original security thread because it deserves it's own topic /perk

OK, with some grooming by Perk [you big perv] I reckon we could start off with the DB connection class he posted eons ago, here it is for reference:

I'd like to suggest that any comments / suggestions made are updated in this post, and the changes made bold, so we can see what has been changed and relate it to various comments

<?

php

 

class dbConnection
{
var $__connected;
var $__host;
var $__user;
var $__password;
var $__database;
var $__myConnection;
var $__lastQuery;
var $dataSet;
var $row;

function dbConnection($host, $user, $password, $database, $doConnect=true)
{
$this->__host = $host;
$this->__user = $user;
$this->__password = $password;
$this->__database = $database;
$this->__connected = false;
}
function &cloneConnection()
{
if (!$this->__connected) { $this->connect(); }
return new dbConnection($this->__host, $this->__user, $this->__password, $this->__database);
}
function close()
{
if($this->__connected) { mysql_close($this->__myConnection); }
}
function connect()
{
$this->__myConnection = mysql_connect($this->__host, $this->__user, $this->__password, true);
if (!$this->__myConnection) { die('class DBConnection cannot connect'); }
$this->__connected = true;
mysql_select_db($this->__database, $this->__myConnection);
}
function connected() { return $this->__connected; }
function error()
{
if (!$this->__connected) { return ''; }
return mysql_error($this->__myConnection);
}
function fetchArray()
{
// Note that I could have just said "return $this->row" and gotten
// essentially the same result, but I am potentially passing WAY more
// data back than is necessary - especially if there is a TEXT attached to the row
$this->row = mysql_fetch_array($this->dataSet);
if ($this->row) { return true; } else { return false; }
}
function fetchRow()
{
$this->row = mysql_fetch_row($this->dataSet);
if ($this->row) { return true; } else { return false; }
}
function fieldCount() { return mysql_num_fields($this->dataSet); }
function fieldName($idx) { return mysql_field_name($this->dataSet, $idx); }
function lastInsertID() { return $this->singleAnswer('select LAST_INSERT_ID(' . $this__myConnection . ')'); }
function lastQuery() { return $this->__lastQuery; }
function query($queryStr, $ignore=false)
{
if (!$this->__connected) { $this->connect(); }
$this->__lastQuery = $queryStr;
$this->__lastError = '';
$this->dataSet = mysql_query($queryStr, $this->__myConnection) or ($this->__lastError = mysql_error($this->__myConnection));
if (($this->__lastError > ' ') && (!$ignore))
{
print "MySQL Error on query('{$this->__lastQuery}') - {$this->__lastError}";
}
}
        function rowCount()
        {
                return mysql_num_rows($this->dataSet);
        }
function rowToXML(&$xml)
{
$max = mysql_num_fields($this->dataSet);
for ($i=0; $i<$max; $i++)
{
$xml->addChild(mysql_field_name($this->dataSet, $i), $this->row[$i], false);
}
}
function seek($rowNum)
{
return mysql_data_seek($this->dataSet, $rowNum);
}
function selectDB($dbName)
{
if (!$this->__connected) { $this->connect(); }
mysql_select_db($dbName, $this->__myConnection);
}
function singleAnswer($queryStr)
{
if (!$this->__connected) { $this->connect(); }
$this->query($queryStr);
$this->fetchRow();
return $this->row[0];
}
}

?>

perkiset

Ope! We're over here now...

Things that should be done to that class straight away:

  • It should be

    PHP

     5'd so that it can be serialized and such.

  • It should make use of __sleep and __wakeup

  • Although it is currently an all-public class, it should be explicitly outlined so that we have more clarity when we decide to add private and protected methods/properties



Things that I have done personally, which we might consider adding permanently:

  • Adding fetchAssoc() and fetchObject()

  • Adding singleAssoc() and singleObject() (these, particularaly, have served me well)

  • queryArray() - this function takes a single SQL parameter and returns the entire thing as an array. Helpful, but dangerous if you don't watch what you're querying for. I use this sparingly, but it is nice when I do.

  • Adding a forceOpen=false as an optional last parameter to the constructor, then adding if (forceOpen) $this->connect() to it, making the object into a live connection at construction.

  • Changing the error mechanism in the query() to be more exception based than printing the message.  Applause



Thoughts? Most of this is already done and I can post an update right here right now if we like.

m0nkeymafia

Nice one perk, perhaps we talk / discuss it more, then if you want to you can post
At least then we think about it first Applause

I am at a bit of a disadvantage here, not really used

PHP

 5's OOP stuff much, why would you want to serialize it and such? What is sleep and wakeup? Sounds crrraazzy

perkiset

PHP

 5's OO model is more normal than the kludgy, bandaid style OO that came with

PHP

 4. Although not entirely complete and still lushc with magic keywords that are bandaids for the language, it is decent enough to be compelling.

Serialization: this is where the properties of the object are wrapped up into a string for storage. As the class is, there is almost zero benefit for this - but if the class had storage for a data set - for example, we had a new function that executed a query, grabbed every row of that query and stored it in an array in the object, then you serialized the object and stored it in the APC cache, every page pull could simply unserialize the object and the query results would still be in the object, available for access.

The __sleep and __wakeup functions are overrideable magic OO functions that occur when an object is serialized or unserialized. When you serialize an object, the __sleep function is called so that it can do ... whatever might be necessary before it was serialized. The __wakeup happens just after unserialization. This could be very handy to have a db connection class that "remembered" what the last query was, and upon unserialization (__wakeup) it automagically reconnects to the DB and reopens the <persistent query>. If this serialized object was stored in the APC cache it would again be very efficient and begin to look like Java, EJB and JBoss mechanisms.

This is just gear turning stuff, would love some debate on what is a strong addition to the <i>base class</i> and what should be added in decendents.

nutballs

i think the ideal would actually a very simple base class that does the raw SQL and Security stuff, then an extended class for all the bells and whistles. (selfish desire mind you, but also makes sense)

perkiset

I am very comfortable with the notion of a hierarchy rather than big ol' class... do you have some suggestions for how you'd trim/enhance the base and/or how you might want to see the family?

Personal Opinion: if a method enhances the existing functionality but does not involve any OTHER technology, then it is fit for the root class IE., adding a fetchObject() function would fit nicely into either the base or the immediate child, but encodedRowAsJSON() might be best left to a derivative.

Perhaps:

  • <>dbConnectionBase
    Why? Because I might want to build an extremely light, custom applied dbConnection and will not need any fetch or convert functions. This class would be super super light, providing perhaps only the notion of connecting, query and simple fetching. I think this is what you are after NBs.

    • <>dbConnection
      Why? This class would have all the fetch functions, single functions etc - basically a full-featured database connection class for general usage.
      Something I'd also thought about, but hadn't added here yet was row-iterators ie., throw a query, then provide a callback function for each row. Something like iterateQuery('select * from contacts', 'myFunctionName') which would fire myFunctionName for every row in the table. Then handler would look something like
      function myFunctionName($sender, &$array)
      or something, so that I simply do <whatever> with the array pointer - this would make master-detail loops REALLY easy to do.

      • <>dbConnectionWebsafe
        Why? This is where we'd add web-safe querying, which (might) add significant overhead to the query process (converting/cleansing strings before letting them fly).






Thoughts?

nutballs

actually i was thinking along the lines of only 2 classes, but you are right.
In my view the base would be:
connect
disconnect
executeaquery - would either return a recordset, or would return a single value, depending on the query type. not sure if this would need to be 2 seperate functions though.
possibly error handling

that would be it. From there, you would be able to build any other subclass that would always utilize those 3 functions anyway based on the query you write.

m0nkeymafia

i like the classes there perk

to extend further you could have a DBConnectionCache or something, that like you suggested, can serialize stuf
If we used virtual functions and  polymorphism we could get a really powerful modular system that would allow for some great functionality whatever you wanted!

perkiset

The more I look at it, the more I dislike the Websafe child. Here's my thought:

You MAY want to throw a websafe query even if you simply inherit from dbConnectionBase. As it is, you must encur all of the overhead for the entire class to do that... perhaps we include websafe functionality but as functions that are only called if a property is true or false eg.,

// $query = the current query, would look different per function...
if ($this->webSafe) $query = $this->makeWebSafe($query);

The web safe function would not be that heavy and not a real burden to the base class and would add the functionality to all decendants. Additionally, if the makeWebSafe function were protected, then decendents could grow/modify that function as they needed.

Given this thought, then NBs I actually think that 2 classes is more correct...

m0nkeymafia

Sounds good to me.
Probably best to be a switch on / switch off thing
i.e.

$myDB = new DBConnClass($server, $dbname, $user, $pass);
$myDB->SetWebSafe(); //although i guess we'd like this on by default, so youd turn it off if you didnt want it
$myDB->ApplauseoQuery($filthy_string);


Perkiset's Place Home   Politics @ Perkiset's