Development Information

May 12, 2012

Database Object – What the Configuration values mean

Filed under: Database Class Information,Tutorials — judda @ 9:37 am

As mentioned in my previous post, there are a few configuration settings that are used, this post will define the meaning of each of them.

Hostname="localhost"
Username="testing"
Password="testing"
Engine="mysql"
Database="testing"
ErrorReporting=2
ErrorLog="logfile.log"
LogQueries="true"
QueryMode=0

Hostname: (normally localhost) the IP address / name of the host that you will be connecting to

Username: the username to connect to the database with

Password: the password to connect to the database with

Engine: the database engine to use

ErrorReporting: The error reporting level(s) that are desired from the database object.  This can be any combination of the following constants (however if used in an INI file you will have to manually set the value).

ERRORS_IGNORE = 0 – Ignore any error messages that come up

ERRORS_ECHO = 1 – Echo any error messages to the screen

ERRORS_EXCEPTION = 2 – Throw an exception whenever there is an issue

ERRORS_LOGFILE = 4 – Write the exception to the log file (located in the ErrorLog configuration setting)

You are able to mix and match any of these levels (except for ERRORS_IGNORE).  For example, in the PHP code you could do the following:

$config->errorReporting=DatabaseConfiguration::ERRORS_ECHO | DatabaseConfiguration::ERRORS_EXCEPTION;

And you would get the errors both echoed out to the screen AND an exception would be thrown.

ErrorLog: If error logging to a log file is enabled, then the error messages will be written here (need to have write access to it).

LogQueries: Whether or not you want a query log stored in memory with the object (useful for helping to determine long running queries and various other query stats)

QueryMode: One of two settings which will define the way that the queries get parsed for parameters.

QUERY_DEFAULT = 0 – Use the new style parameters (i.e. {0:uf})

QUERY_CLASSIC = 1 – Use the classic style parameters (i.e. %uf)

With these values in mind, you are able to set up the object and start querying your database.

Configuring the Database Object

Filed under: Database Class Information,Tutorials — judda @ 9:23 am

The first step to using the database object is the configuration of it.  Essentially at the configuration process is just the hydrating of the DatabaseConfiguration object.  There are four relatively easy ways to do this.

Next post I’ll explain what each of these mean, and what values they should contain.

They are as follows:

1 – Using an INI file

DatabaseConfiguration::fromINIFile($iniFile, $section=NULL)

If you store your configuration settings in a .ini file, you are able to use that to configure the object.  The ini file should look as follows:

; Configuration INI File for Database Configuration
[Database]
Hostname='localhost'
Username='testing'
Password='testing'
Engine='mysql'
Database='testing'
ErrorReporting=2
ErrorLog='logfile.log'
LogQueries='true'
QueryMode=0

With this as your ini file you can then call the static method DatabaseConfiguration::fromINIFile passing it the full file path to this ini file and if there is a section (i.e. the ‘Database’ in the ini file seen above) then provide it that name, and then it will automatically configure the rest for you.

2 – Using an INI string

DatabaseConfiguration::fromINIString($iniString, $section=NULL)

You may already have the body of an ini file in memory, so this way works just like the previous method but instead of reading the contents of the ini file from a flat file, it will just load it from a string.

3 – Using an array

DatabaseConfiguration::fromArray($config)

If you have an array which contains the following key values (keys the same as seen in the above example) then you can set up the object using that.

$array = array (
    'Hostname' => 'localhost'
    , 'Username' => 'testing'
    , 'Password' => 'testing'
    , 'Engine' => 'mysql'
    , 'Database' => 'testing'
    , 'ErrorReporting' => 2
    , 'ErrorLog' => 'logfile.log'
    , 'LogQueries' => 'true'
    , 'QueryMode' => 0
);

4 – Manually Hydrating the object

If none of the previous options work for you, then you are always able to manually create an instance of the DatabaseConfiguration object and populate each of the values by hand.

$config=new DatabaseConfiguration();
$config->hostname = 'localhost';
$config->engine = 'mysql';
$config->database = 'testing';
$config->username = 'testing';
$config->password = 'testing';
$config->errorReporting = DatabaseConfiguration::ERRORS_EXCEPTION;
$config->errorLogFile = 'logfile.log';
$config->maintainQueryLog = true;
$config->queryMode = DatabaseConfiguration::QUERY_DEFAULT;

May 6, 2012

Database Class v3.0.0

Filed under: Database Class Information,Updates — judda @ 2:45 pm

The first cut of the new database object has been completed.  From what I’ve found, it is a lot easier to use than the older versions of it.  Not only to configure, but some things have been moved around to make it much more intuitive.  I will be adding more posts on here walking through the basics on how to set up your object, as well as how to query in the new fashion.

April 21, 2012

Database Class v 3.0.0 in development

Filed under: Database Class Information,Updates — judda @ 6:44 pm

Hey All,

So I’ve finally decided to take on my overhaul of the database object that I made a few years ago now (my how time flies). I just noticed that I posted the original idea back in November 2010 (so much for doing it within the next few weeks).

Essentially this change will affect several things about the database object. The main one being the syntax used to bind the parameters to the query. You will be able to write queries as follows:

Code:

$query='SELECT `Username` FROM `Users` WHERE `Username` LIKE {0:s} OR `FullName` LIKE {0:s}';
$db->query($query, 'foo');
 
// OR
 
$query='SELECT `Username` FROM `Users` WHERE `UserId` = {u}';
$db->query($query, 'foo');

That said, I am also building into it a “classic” mode where you can use the %s and stuff as you did in the old versions.

While I am at it, I was thinking about renaming some of the type strings. The thing is with this renaming it would also affect the ‘classic’ mode. So I want to know what you guys think about it.

Below is a list of what it currently is, and what I’m thinking of changing it to, or if they are being removed.

Existing:
* %u – unsigned integer – ud
* %d – signed integer – d
* %p – decimal number – f
* %b – binary files – b
* %h – string (HTML permitted) – s
* %s – string (no HTML permitted) – es
* %l – list of values or an array – l

New:
* unsigned decimal number – f

Possible Expansion:
* list of values or an array – l – should there be type checking enabled on these? (i.e. lp would be a list of decimal numbers, ls would be a list of strings, etc.)

Removed:
* %i – IP Address – shouldn’t be storing IP addresses as strings, and it was very inflexible for IPv6
* %f – field

Another thing I could do, is make these keys also configurable so that you can get a ‘true classic’ mode by just switching the type codes back to what they originally were (except for the 2 which I was planning on removing). Would this make the upgrade easier?

While I am in here ripping out the guts of it and rebuilding it, was there anything in it that really annoyed you when using it? If yes, what was it, maybe I can improve it this time around?

There are a few simple things that I’ll be changing as well but I’ll still be leaving it so you can use it the same way as before (but with deprecation notices) so there won’t be much re-work there.

Please let me know what you think by either posting a comment on here, or sending me an email through the contact page.

April 23, 2010

Database Class – Like

Filed under: Database Class Information,Tutorials — judda @ 1:13 pm

It has come to my attention that a few people have been stumbling upon how to use the ‘LIKE’ command with wildcards using this database class.  Because of this, I will provide a brief sample of how to use it.

Normally when someone writes a query which uses ‘LIKE’, it is written as follows:

$query = "SELECT `email` FROM `users` WHERE `username` LIKE '%Tom%'";
 
$res = $db -> query ( $query );

However, because of how this database class currently works, this will cause issues (it looks for the percent sign (%) and then tries to find a parameter type to bind to it).  So instead of doing it this way, you must do the following:

$query = "SELECT `email` FROM `users` WHERE `username` LIKE %s";
 
$res = $db -> query ( $query, '%Tom%' );

This will bind the wildcards along with the actual value into the query and end up doing exactly what you need it to do.

Sorry for the inconvenience.

May 5, 2009

Database Class

Filed under: Database Class Information,Tutorials — Tags: — judda @ 10:30 am

I noticed that there are not many good database classes that people implement nowadays in PHP that help prevent against SQL Injection and allow for the validation of the data as well.  So I decided to make one which is based off of ‘printf’ from various languages (i.e. C).

This class originally started as a wrapper class for the ‘mysql_’ set of functions in PHP (version 1) but then was changed to a wrapper class for the PDO object.

Why not just use the PDO/PDOStatement object directly?  It requires you to do extra leg work to ensure that all data is bound properly as well as the validation for the different data types is fairly simple.

In this class, the way that you write the SQL queries will change slightly in order to add a potential layer of security.  Instead of entering the values you want to add into the query directly, you will swap the value out with one of the variable constants (i.e. %s, %d, etc.) based on the type of informaton that you would like to store.

Your queries will start looking like this:

SELECT * FROM `table_name` WHERE `id` = %d

In order to run the query then (after an instance of the database class has already been created) all that is needed is the following (this example assumes that the Database object has been created and is stored in a variable called ‘db’):

$idNumber = 2;

$results = $db -> query ( “SELECT * FROM `table_name` WHERE `id` = %u”, $idNumber );

If the ‘idNumber’ variable fails to validate against the regular expression for the unsigned integer then the query function will return NULL and if verbose mode is turned on then an error message will be displayed on your page saying that the value failed to validate.  Otherwise, the query function will return the PDOStatement that has been executed.  Which then you can use any of the other functions built into the class to actually retrieve the information that is in the records.

Each of the parameters (i.e. %s, %d, etc.) are changed with a parameter that will be bound to the query at a later time during the execution of the query function.  It’s this binding of the parameters to the query, rather than adding them directly in that adds the layer of security.

Powered by WordPress