Database Class Information « Development Information

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.

Comments (0)

May 5, 2009

Database Class

Filed under: Database Class Information,Tutorials — Tags: Class — 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.

Comments (0)