This update fixed a huge issue with the ‘numRows’ function. Prior to 2.2.1, the ‘numRows’ function relied on the ‘rowCount’ function which is built into the PDOStatement which according to the documentation doesn’t actually grab the number of results grabbed by the ‘SELECT’ query.
May 24, 2009
May 22, 2009
Added 3 additional functions:
- Counting of how many successful database queries were executed
- Keeping track of the amount of time that it took to run all queries
- Giving the opportunity to keep a log of all the database queries and the amount of time the application spent executing them
Error messages now combine on themselves so that you will know about all of the values which fail to validate at the same time instead of if one fails you only see that error message.
Minor update. A new field type was added:
%p can now be used to insert a floating point number (double) into the database.
Major update in security and implementation.
Changed the query function to use the “PDO object” instead of using the “mysql_” set of functions which are built into PHP. In doing this, all of the parameters for the query are now bound to the query as query parameters instead of just using a fair number of functions to add security, we now let the database engines control the values being passed in order to prevent injection.
May 15, 2009
I have received questions about how to connect to your database using the database class. So here is a quick tutorial on how you can connect to the database using the database class.
Firstly we need to start at the top of the PHP page with the following:
require_once ( “/path/to/database/class/database.php”);
For example, “/path/to/database/class” could be nothing meaning that it is in the same directory as the current file, or it could be something like this “include/”.
This will tell PHP that the information about the database class is found on the corresponding file. After the database class is ‘require’d into the page, there are two ways that you can create the connection.
In the “database.php” file modify the lines that say:
$db_host = “hostname”; /* Normally ‘localhost’ */
$db_name = “database_name”;
$db_user = “database_user”;
$db_pass = “database_pass”;
To be the information for connecting to your database.
For example, if you connect to your database through “localhost” you would change “hostname” to “localhost” as well as for all of the other variables ($db_name, $db_user, $db_pass).
After the login information is all set up for the database class, all you will have to do in the original script is add the following:
$db = new Database (); //Change $db with any variable name you would like your database connection to have
This method is a bit easier and will allow you to use any updated versions of the database class without actually having to remember to change the login credentials in the database.php file. All you have to do is the following:
$db = new Database ( array ( “host” => “hostname”, “name” => “database_name”, “user” => “database_user”, “pass” => “database_pass” ) );
Where you fill in each of the credentials as they correspond to the values. For example, if you connect to your database through “localhost” you would change “host” to “localhost” as well as for all of the other values (name, user, pass).
After doing this you should be connected to the database and able to run your queries.
Next Tutorial on how to run queries.
May 5, 2009
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.