Anything wrong with my database class?

[Tomoso]

I just coded this, this morning (yes work is slow at the moment) but haven't had a chance to actually test it out (PC at work doesn't have anything set up to run my php). Basically is this class doing what I think it's doing? It's aim was to take any sql syntax away from other parts of my code so error catching would be easier, and just generally make it more readable.

At the top of the code are examples of how it can be used.

Also are there any security risks im missing? I'm really quite new to php and mysql so just wanted to make sure I have this sorted before I move on and get other class' relying on this one coded. Thanks for your time.

 

<?php
/*
EXAMPLE - Basic Usage
$db = new database("debug");

# Get a row
$row = $db->getRow("accounts", "id name age email", "50 $name $age [email][email protected][/email]";
echo $row['id'] . $row['name'] . $row['age'] . $row['email']; 

# Insert a row
$db->insertRow("accounts", "name email", "Jimmy $email");

# Update a row (table, set_what?, set_value, where_what?, where_value)
$db->updateRow("accounts", "name email", "Noob [email][email protected][/email]", "name", "Tommy");
*/

class DATABASE {
# Constructor
function __construct($mode) {
	# Set error display mode 
	$this->_mode = $mode;

	# Connect to MySQL & database
	$this->connectMySQL();
	$this->connectDB();
}

#Destructor
function __destruct() {
	# Close connection
	if ($this->_link)
		mysql_close($this->_link);
}

# Variables
var $_host = "localhost";
var $_user = "root";
var $_pass = "abc123";
var $_db = "game";
var $_link = null;
var $_mode; # when in debug mode, more specific error messages are given

# Attempts to connect to MySQL using stored variables
function connectMySQL() {
	# Attempt to create a connection to MySQL
	$this->_link = mysql_connect($this->_host, $this->_user, $this->_pass);

	# Check to see whether a connection was made
	if (!$this->_link) {
		if ($this->_mode == "debug")
			die ("Could not connect to MySQL. 
Error: " . mysql_error() . "
");
		return false;
	}

	# Display message if we are in debug mode so we know a successful connection was made
	$this->printError("Connected to MySQL.", "");

	return true;
}

# Attempts to connect to a database using stored variable
function connectDB () {
	$db_selected = mysql_select_db($this->_db, $this->_link);

	# Check to see whether database was succesfully connected to
	if (!$db_selected) {
		if ($this->_mode == "debug")
			die ("Could not connect to Database. 
Error: " . mysql_error() . "
");
		return false;
	}

	$this->printError("Connected to database.", "");

	return true;
}

# Returns an array of rows from the database, or false if no rows exist
function getRows($table, $where_list, $where_value_list) {

	if (!$where_query = $this->getFieldQuery($where_list, $where_value_list, "WHERE"))
		return false;

	# Assemble query, escaping dangerous variables
	$query = sprintf("SELECT * FROM $table WHERE $where_query");

	$result = mysql_query($query, $this->_link);

	# Check if anything was returned at all
	if (!$result) {
		$this->printError("[b]Error Occured:[/b] No rows found in table: " . $table . " where: " . $where_key . " = " . $where_value, 
			"An error occured while getting information from the database.");
		return false;
	}

	# Check if any rows were found
	if (mysql_num_rows($result) < 1) {
		$this->printError("[b]Error Occured:[/b] No rows found in table: " . $table . " where: " . $where_key . " = " . $where_value, 
			"An error occured while getting information from the database.");
		return false;
	}

	# Rows were found, let's put them into an array
	$row_count = 0; # For the row_array index
	while ($row = mysql_fetch_array($result)) {
		$row_array[$row_count] = $row;
		$row_count++;
	}

	return $row_array;
}

# Returns a single row from a table. WARNING: Should only be used if you are expecting there to be only 1 of what you are looking for to exist
function getRow($table, $where_list, $where_value_list) {
	# Get an array of rows
	if ($row_array = $this->getRows($table, $where_list, $where_value_list)) {

		# Since we are only expecting to find one row, that should be the size of the array
		if (count($row_array) > 1) {
			$this->printError("[b]Error Occured:[/b] More than 1 row found in table: " . $table . " where: " . $where_key . " = " . $where_value, 
				"An error occured while getting information from the database.");
			return false;
		} else {
			# 1 Row found, return it.
			return $row_array[0];
		}
	}

	# No rows found
	return false;
}

# Insert row into table where Example call - $object->insertRow("a_table", "name,email,pass", $value_array) - Value array must be made before hand
function insertRow($table, $field_list, $field_value_list) {

	if (!$field_query = $this->getFieldQuery($field_list, $field_value_list, "INSERT"))
		return false;

	#Assemble full query
	$query = sprintf("INSERT INTO $table $field_query");
	$result = mysql_query($query, $this->_link);
	if (!$result) {
		$this->printError("[b]Error Occured:[/b] INSERTING fields" . $field_list . " with VALUES " . $field_value_list . " Field Query: " . $field_query, 
		"An error occured while inserting information into the database.");
		return false;
	}

	return true;
}

# Updates a row within the table
function updateRow($table, $field_list, $field_value_list, $where_list, $where_value_list) {

	# Assemble both parts of query SET and WHERE
	if (!$set_query = $this->getFieldQuery($field_list, $field_value_list, "SET"))
		return false;

	if (!$where_query = $this->getFieldQuery($where_list, $where_value_list, "WHERE"))
		return false;

	# Assemble query
	$query = sprintf("UPDATE $table SET $set_query WHERE $where_query");
	$result = mysql_query($query, $this->_link);
	if (!$result) {
		$this->printError("[b]Error Occured:[/b] UPDATING fields" . $set_query . " with WHERE " . $where_query, 
		"An error occured while updating information within the database.");
		return false;
	}

	return true;

}

# Prints an error message to the screen. If we are in debug mode that error is printed, if not the other more user friendly message is printed.
function printError($debug_error_msg, $clean_error_msg) {
	if ($this->_mode == "debug")
		echo "
" . $debug_error_msg . "
";
	else
		echo "
" . $clean_error_msg . "
";
}

# Returns an escaped array of items within a field list. Query type determines how this information is formatted so it can be added to a query
function getFieldQuery($field_list, $field_value_list, $query_type) {
	$field_list_query = "";

	$field_list_array = split(" ", $field_list); 		# Example $field_list = "where_key1 where_key2 where_key3"
	$field_value_array = split(" ", $field_value_list); # Example $field_value_list = "where_value1 where_value2 where_value3"

	# Ensure there are the same amount of field keys as there are values
	if (!$this->compareFieldArrays($field_list_array, $field_value_array))
		return false; # Error occured (compareFieldArrays will display the error)

	# SELECT * FROM table WHERE field_key = 'field_value' AND field_key = 'field_value' #
	if ($query_type == "WHERE") {
		# Within the loop we will assemble the array into something that can be put into a query. We will also escape each element of
		# both arrays to ensure they are safe.
		for ($i = 0; $i < count($field_list_array); $i++) {
			# Escape both array elements
			$field_list_array[$i] = mysql_real_escape_string($field_list_array[$i]);
			$field_value_array[$i] = mysql_real_escape_string($field_value_array[$i]);

			# Assemble the query
			# Example:						 "field_key = 'field_value'" 
			# and for more than one element: " AND field_key = 'field_value'"
			if ($i > 0)
				$field_list_query .= " AND ";

			$field_list_query .= $field_list_array[$i] . "='" . $field_value_array[$i] ."'";
		}
		# Returned string should look something like this: "field_key = 'field_value' AND field_key = 'field_value'"
		return $field_list_query;
	}

	# INSERT INTO table(field_key, field_key) VALUES('field_value', 'field_value') #
	if ($query_type == "INSERT") {
		# Assemble query
		# Example query Keys:		"(field_key,field_key,field_key)"
		# Example query Values:		" 'field_value','field_value','field_value'"
		$field_list_query = "(";
		$field_value_query = "VALUES (";
		for ($i = 0; $i < count($field_list_array); $i++) {
			# Escape both array elements
			$field_list_array[$i] = mysql_real_escape_string($field_list_array[$i]);
			$field_value_array[$i] = mysql_real_escape_string($field_value_array[$i]);

			# Assemble both querys
			if ($i > 0) {
				$field_list_query .= ",";
				$field_value_query .= ", ";
			} else {
				$field_list_query .= $field_list_array[$i];
				$field_value_query .= "'" . $field_value_array[$i] . "'";
			} 

			# Tidy up end if field querys and add them both together
			$field_list_query .= ") ";
			$field_value_query .= ")";

			# Returned string should look something like this:
			# "(field_key,field_key,field_key) VALUES ('field_value','field_value','field_value')"
			return ($field_list_query .= $field_value_query); 
		}
	}

	# UPDATE table SET field_key = 'field_value' (plus add a where statement) #
	if ($query_type == "SET") {
		for ($i = 0; $i < count($field_list_array); $i++) {
			# Escape both array elements
			$field_list_array[$i] = mysql_real_escape_string($field_list_array[$i]);
			$field_value_array[$i] = mysql_real_escape_string($field_value_array[$i]);

			# Assemble the query
			# Example:						 "field_key = 'field_value'" 
			# and for more than one element: " AND field_key = 'field_value'"
			if ($i > 0)
				$field_list_query .= ", ";

			$field_list_query .= $field_list_array[$i] . "='" . $field_value_array[$i] ."'";
		}
			# Returned string should look something like this: "field_key = 'field_value', field_key = 'field_value'"
			return $field_list_query;
	}

	$this->printError("[b]Error Occured:[/b] Query Type: " . $query_type . "not found.", 		
	"An error occured while compiling a query for the database.");

	return false;
}

# Compares 2 "field" arrays to ensure they are the same size.
function compareFieldArrays($field_list_array, $field_value_array) {
	# Check if amount of fields and values being inserted match up
	if (count($field_list_array) != count ($field_value_array)) {
		$this->printError("[b]Error Occured:[/b] Field list: " . $field_list . "does not have the same amount of values as: " . $field_value_list, 
			"An error occured while compiling a query for the database.");
		return false;
	}
	return true;
}

}

?>

 

Ps, if this does indeed work, anyone can use it. I don't care if you don't mention I made it :P it didn't take long ^_^

[Vali]

Re: Anything wrong with my database class?

Hey

You should use php 5 for this class, it will be allot cleaner.

Also, can you post some usage examples?

[Tomoso]

Re: Anything wrong with my database class?

Damn I thought that was php5 :P Can't remember where I picked that syntax up now, I think I followed some tutorial and the rest jsut copy be example >.< I'll look into the manual and sort it out.

I put some examples in the previous code but I will try to make a "real world" one here.

The following code will:-

- add a new user to the table

- fetch and display his information

- update his email address

 

MySQL - User Account Table

Fields: id, username, password, email_address

 

<?php
require "database.class.php";

#
# Imagine the user has just clicked "register" on a very unsecure site!
#

# Capture user input
$username = $_POST['username'];
$password = $_POST['password'];
$email = $_POST['email'];

# Since we will be working with the database, create an object to make it "easier" to work with

$db = new database(""); # We keep debug off as we want friendly error messages displayed

# Insert new row for user
$db->insertRow("accounts", "username password email", "$username $password $email");

#
# ... Some time later the user want's to view his information
#

# This function will only ever return one row from the database. If there is no row, or more than 
# one row found it will through up an error.
if (!$user_info = $db->getRow("accounts", "name", "$name")) {
   echo "Error!";
}

# We now have an array of user information, print it to the screen - 
echo "Welcome back " . $user_info['username'] . "! 
";
echo "Your Email address is " . $user_info['email'] . "! 
";

#
# The user wants to change his email. Imagine the user just submitted his new one via a form.
#

$new_email = $_POST['new_email'];

# Update user information
$db->updateRow("accounts", "email", "$new_email", "username", "$username")

# If the user had some information like age, weight etc... we could also update that in the same call
$db->updateRow("accounts", "email age weight", "$new_email $age $weight", "username", "$username")
?>

 

Well I hope that explains it very roughly.

ps, Don't accept user info without some validation :P

[Vali]

Re: Anything wrong with my database class?

That's php4, php 5 doesn't use 'var' in classes and so on.

You class is a good start, but look into "magical functions", namely the __get and __set

With it, you can do something like this:

<?php
# Load user ID 5
$user = new User(5);

# Print some user data
echo $user->name;
echo $user->email;

# Set some data
$user->name = 'my new name';
$user->email = '[email protected]';

# Save the user
$user->save();

#
# Create a new user (not loaded)
#
$newUser = new User();

# Set some data
$newUser->name = 'my new name';
$newUser->email = '[email protected]';

# Save the user
$newUser->save();
?>

 

Now, tell me that's not much leaner to work with?

I'll let you figure out how to create the class for this, it shouldn't be that complicated for you, and I think you'll learn a few things.

[Tomoso]

Re: Anything wrong with my database class?

The demonstration was for the database class only. I intend to make a user class function like you said (instead setting variablse with a function :P) but inside your save function you would still have that messy sql to mess about with. I want to encapsulate all calls to the database inside my class so for instance my user class save function would look like:

 

<?php
public function save() {
# Not sure if I could do "$this->var" inside a string so I will set them outside
$username = $this->username;
$first_name = $this->first_name;
$last_name = $this->last_name;
$email = $this->email;

       # Assume the database class is part of the user class so we can use it here
$this->db->updateRow("accounts", "username first_name last_name email", "$username $first_name $last_name $email");
}
?>

 

Hope that makes more sense about what I'm trying to do. I will def look into magical functions though and see what they can offer.

[Floydian]

Re: Anything wrong with my database class?

One word of caution, the more a query is "abstracted" and each component part (select/from/join/where/order by/group by/limit) is separated out, the harder it's going to be to secure those queries. Just sayin...

[Tomoso]

Re: Anything wrong with my database class?

Is what I have at the moment safe enough? I'm pretty new to all this. I wasn't sure whether inputting all this as one string then splitting it was a good idea. Actually now that I think about it, if one of those variables was "word1 word2" it would take that as 2 values because it splits by space... I'll have to rethink this I think >.<

[Vali]

Re: Anything wrong with my database class?

 

but inside your save function you would still have that messy sql to mess about with.

You should only have 3 or 4 places with SQL in your code in my example, all in the same abstract class (extended by your game classes).

#1 constructor: SELECT * FROM $table WHERE $PK = '$id'

#2 & #3 save

- On insert: "INSERT INTO $table SET [changed fields joined by ',', in the KEY=escaped(VALUE)]

- On UPDATE: "UPDATE $table SET [changed fields joined by ',', in the KEY=escaped(VALUE)] WHERE $pk = '$id'

Notice that the "[changed fields joined by ',', in the KEY=escaped(VALUE)]" is always the same, so you don't have much SQL around.

#4 some "find" function.

Once you have that abstract class, your user class should look like this:

<?php
class User extends myAbstractClass {

}
?>

 

If you want to do some "magic" when you change some field in the user, say score when you build a soldier, you do this:

<?php
class User extends myAbstractClass {

# Overload the setter
public function __set($var, $val) {
 # We get 10 points for every soldier
 if ($var == 'soldier') {
   $this->score = $this->score + ($var - $this->soldier) * 10;
 }
 # Call the parent's setter
 parent::__set($var,$val);
}

}
?>

 

If you want to set some variables to the user that are not in the database, say the "age" of a profile.

<?php
class User extends myAbstractClass {

# Overload constructor
public function __construct($id = null) {
 # Load the object
 parent::__construct($id);

 # Make sure we have a register date (in case it's a new object)
 if ($this->register_date == null) {
   $this->register_date = now();
 }

 # Calculate the age in days, there are 86400 seconds in a day
 $this->age = $this->register_date / 86400;
}

}
?>

 

Notice the lack of SQL queries.

Ps: this code was posted as an example, so you should probably find a better way to implement it.

[Floydian]

Re: Anything wrong with my database class?

I don't know about anyone else, but my db class doesn't guarantee security. The class can be misused and it can be used correctly.

The point: the class performs a job for you. It should make things easier. But using proper database escaping and variable typing in your queries is always going to be a function of a case by case basis.

I.e., it's how you use the class that determines if it is secure.