The Cache: Technology Expert's Forum
 
*
Welcome, Guest. Please login or register. September 21, 2019, 01:53:33 AM

Login with username, password and session length


Pages: [1]
  Print  
Author Topic: MySQL dbConnection Class Group Project  (Read 3276 times)
m0nkeymafia
Expert
****
Offline Offline

Posts: 240


Check it!


View Profile
« on: January 24, 2008, 02:45:58 AM »

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

Code:
<?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->__passwordtrue);
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];
}
}

?>
« Last Edit: January 24, 2008, 12:25:07 PM by perkiset » Logged

I am Tyler Durden
perkiset
Olde World Hacker
Administrator
Lifer
*****
Offline Offline

Posts: 10096



View Profile
« Reply #1 on: January 24, 2008, 09:00:54 AM »

Ope! We're over here now...

Things that should be done to that class straight away:
  • It should be PHP5'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.  D'oh!

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

It is now believed, that after having lived in one compound with 3 wives and never leaving the house for 5 years, Bin Laden called the U.S. Navy Seals himself.
m0nkeymafia
Expert
****
Offline Offline

Posts: 240


Check it!


View Profile
« Reply #2 on: January 24, 2008, 09:06:13 AM »

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 Smiley

I am at a bit of a disadvantage here, not really used PHP5's OOP stuff much, why would you want to serialize it and such? What is sleep and wakeup? Sounds crrraazzy
Logged

I am Tyler Durden
perkiset
Olde World Hacker
Administrator
Lifer
*****
Offline Offline

Posts: 10096



View Profile
« Reply #3 on: January 24, 2008, 10:17:40 AM »

PHP5's OO model is more normal than the kludgy, bandaid style OO that came with PHP4. 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 base class and what should be added in decendents.
Logged

It is now believed, that after having lived in one compound with 3 wives and never leaving the house for 5 years, Bin Laden called the U.S. Navy Seals himself.
nutballs
Administrator
Lifer
*****
Offline Offline

Posts: 5627


Back in my day we had 9 planets


View Profile
« Reply #4 on: January 24, 2008, 11:21:11 AM »

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)
Logged

I could eat a bowl of Alphabet Soup and shit a better argument than that.
perkiset
Olde World Hacker
Administrator
Lifer
*****
Offline Offline

Posts: 10096



View Profile
« Reply #5 on: January 24, 2008, 11:47:40 AM »

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?
« Last Edit: January 24, 2008, 11:52:00 AM by perkiset » Logged

It is now believed, that after having lived in one compound with 3 wives and never leaving the house for 5 years, Bin Laden called the U.S. Navy Seals himself.
nutballs
Administrator
Lifer
*****
Offline Offline

Posts: 5627


Back in my day we had 9 planets


View Profile
« Reply #6 on: January 24, 2008, 12:14:05 PM »

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.
Logged

I could eat a bowl of Alphabet Soup and shit a better argument than that.
m0nkeymafia
Expert
****
Offline Offline

Posts: 240


Check it!


View Profile
« Reply #7 on: January 24, 2008, 12:17:17 PM »

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!
Logged

I am Tyler Durden
perkiset
Olde World Hacker
Administrator
Lifer
*****
Offline Offline

Posts: 10096



View Profile
« Reply #8 on: January 24, 2008, 12:38:10 PM »

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...
Logged

It is now believed, that after having lived in one compound with 3 wives and never leaving the house for 5 years, Bin Laden called the U.S. Navy Seals himself.
m0nkeymafia
Expert
****
Offline Offline

Posts: 240


Check it!


View Profile
« Reply #9 on: January 25, 2008, 02:10:08 AM »

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->DoQuery($filthy_string);
Logged

I am Tyler Durden
Pages: [1]
  Print  
 
Jump to:  

Perkiset's Place Home   Best of The Cache   phpMyIDE: MySQL Stored Procedures, Functions & Triggers
Politics @ Perkiset's   Pinkhat's Perspective   
cache
mart
coder
programmers
ajax
php
javascript
Powered by MySQL Powered by PHP Powered by SMF 1.1.2 | SMF © 2006-2007, Simple Machines LLC
Seo4Smf v0.2 © Webmaster's Talks


Valid XHTML 1.0! Valid CSS!