Page 1 of 1
Properly handling data that goes into the database.
Posted: Fri Mar 06, 2009 3:33 pm
by kaisellgren
Hi,
I have been lately rewriting my project and trying to improve the database class mainly. Currently I am trying to figure out the most convenient way to handle data that goes into the database.
I am using prepared statements. To be exact, I am using MySQLi since it is the fastest extension at the moment. Faster than MySQL or PDO and I only need MySQL backend. Further more, MySQL -extension does not even support prepared statements.
Code: Select all
$db -> prepare("INSERT INTO members (name,email,ip,some_number) VALUES (?,?,?,?);");
And then I am binding the values, which is what I am trying to think about of doing properly:
Code: Select all
$db -> bind($name,'s',255);
$db -> bind($email,'s',255);
$db -> bind($ip,'s',16);
$db -> bind($nro,'i',65535);
But I am not sure how I should do the binding... The database runs as in STRICT_ALL_TABLES mode, so, MySQL will not truncate the data, thus, I have to implement the length sanitizion into it, right? In addition, if the value of the data type is "i", then I would do (int) to cast it into an integer, if it's a string, then I add quotes around the value, etc.
Do you think that is a good approach? What are you doing yourself? Suggestions?
Also, that approach above is a bit painful to write. I mean, so much code for stupid binding?
I am trying to find a convenient way to bind. Thank you for the time you spend on reading/replying ladies and gentlemen.

Re: Properly handling data that goes into the database.
Posted: Sun Mar 08, 2009 8:23 pm
by Benjamin
Shouldn't the data types and lengths be checked in your data validation code which would be in the model layer?
Re: Properly handling data that goes into the database.
Posted: Sun Mar 08, 2009 8:28 pm
by kaisellgren
astions wrote:Shouldn't the data types and lengths be checked in your data validation code which would be in the model layer?
I'm not using MVCs btw.
Re: Properly handling data that goes into the database.
Posted: Mon Mar 09, 2009 12:34 am
by Christopher
Are you wrapping bind_param() with your bind() method? You should call real_escape_string() on the values.
kaisellgren wrote:I'm not using MVCs btw.
Even if you are not using MVC, you should be trying to separate out the Domain Model into a separate layer. Even N-Tier does that. The layer separation between the Domain layer and Presentation layer is the first and most important separation. MVC just adds a secondary View-Controller separation within the Presentation layer.
This Domain/Presentation separation does not have to be separate classes or files. It can be all in one file, but you should somehow separate/delineate the Domain code and minimize its dependencies on Presentation code.
Re: Properly handling data that goes into the database.
Posted: Mon Mar 09, 2009 6:45 am
by kaisellgren
arborint wrote:Are you wrapping bind_param() with your bind() method? You should call real_escape_string() on the values.
Even if you are not using MVC, you should be trying to separate out the Domain Model into a separate layer. Even N-Tier does that. The layer separation between the Domain layer and Presentation layer is the first and most important separation. MVC just adds a secondary View-Controller separation within the Presentation layer.
This Domain/Presentation separation does not have to be separate classes or files. It can be all in one file, but you should somehow separate/delineate the Domain code and minimize its dependencies on Presentation code.
Weird, the _param disappeared at some point

. Anyway, am I no way near finish and there's a lot to do and that code was just a scratch on the surface actually.
May I ask you how do you handle data usually? For instance, let us say that you are passing $name into an SQL query that must be at most 200 bytes in size and must be a type of string and be enclosed within single quotes. I am actually asking you to show some real code if you do not mind.
Re: Properly handling data that goes into the database.
Posted: Mon Mar 09, 2009 8:30 am
by inghamn
I do all my database interaction in Model classes. I use java-style setters to filter the data. Validation happens on save. All queries are run as prepared statements with bound parameters. That covers all the security problems and keeps all the SQL in one file.
As a small example, here's a race table. The table only has two fields: id and name.
Code: Select all
<?php
class Race
{
private $id;
private $name;
/**
* This will load all fields in the table as properties of this class.
* You may want to replace this with, or add your own extra, custom loading
*/
public function __construct($id=null)
{
if ($id) {
$PDO = Database::getConnection();
$query = $PDO->prepare('select * from races where id=?');
$query->execute(array($id));
$result = $query->fetchAll(PDO::FETCH_ASSOC);
if (!count($result)) {
throw new Exception('races/unknownRace');
}
foreach ($result[0] as $field=>$value) {
if ($value) {
$this->$field = $value;
}
}
}
else {
// This is where the code goes to generate a new, empty instance.
// Set any default values for properties that need it here
}
}
/**
* Throws an exception if anything's wrong
* @throws Exception $e
*/
public function validate()
{
if (!$this->name) {
throw new Exception('missingName');
}
}
/**
* This generates generic SQL that should work right away.
* You can replace this $fields code with your own custom SQL
* for each property of this class,
*/
public function save()
{
$this->validate();
$fields = array();
$fields['name'] = $this->name;
// Split the fields up into a preparedFields array and a values array.
// PDO->execute cannot take an associative array for values, so we have
// to strip out the keys from $fields
$preparedFields = array();
foreach ($fields as $key=>$value) {
$preparedFields[] = "$key=?";
$values[] = $value;
}
$preparedFields = implode(",",$preparedFields);
if ($this->id) {
$this->update($values,$preparedFields);
}
else {
$this->insert($values,$preparedFields);
}
}
private function update($values,$preparedFields) {
$PDO = Database::getConnection();
$sql = "update races set $preparedFields where id={$this->id}";
$query = $PDO->prepare($sql);
$query->execute($values);
}
private function insert($values,$preparedFields) {
$PDO = Database::getConnection();
$sql = "insert races set $preparedFields";
$query = $PDO->prepare($sql);
$query->execute($values);
$this->id = $PDO->lastInsertID();
}
//----------------------------------------------------------------
// Generic Getters
//----------------------------------------------------------------
public function getId() { return $this->id; }
public function getName() { return $this->name; }
//----------------------------------------------------------------
// Generic Setters
//----------------------------------------------------------------
public function setName($string) { $this->name = trim($string); }
//----------------------------------------------------------------
// Custom Functions
// We recommend adding all your custom code down here at the bottom
//----------------------------------------------------------------
public function __toString() { return $this->name; }
}
Re: Properly handling data that goes into the database.
Posted: Mon Mar 09, 2009 8:42 am
by kaisellgren
A simple SQL query turned into a class, neat

, but not very handy... why would you prefer that over simple SQL queries?
Re: Properly handling data that goes into the database.
Posted: Mon Mar 09, 2009 9:07 am
by inghamn
Not just a single SQL query. All of the SQL in the entire application that ever reads or writes data to the Race table will be here, in this one file. Over the course of developing an application, that's a lot of SQL.
This is just the start. As you develop, and maybe need to add new fields, you have a single file in your application to change. No more search and replace to make sure you got all the SQL queries that might be affected by a database change.
It's also the one place to make sure database security is taken care of. As I handle POSTs, I have simply call the setter for each field and pass the raw data the user POSTed. So, it simplifies front-end development.
Re: Properly handling data that goes into the database.
Posted: Mon Mar 09, 2009 9:12 am
by kaisellgren
That sample of yours barely filters data.
If I ever go on that road (using classes), I would need to generate the PHP code with PHP, because I simply cannot know what to expect from other coders. Maybe someone has already invented this approach, but I am not familiar with the terms. Anyone?
Re: Properly handling data that goes into the database.
Posted: Mon Mar 09, 2009 9:24 am
by inghamn
The example is only doing a simple trim() as a filter in the setName() function. If you want to go the route of a Filter class - those rules would go into the setters in my example. For a name field, though...in my experience, there's really not much filtering to apply. The bound parameters will make sure the name gets saved in the database correctly, and safely. People have wierd names sometimes, like Smith, O'Conner, or B!0*R - who am I to judge? An email or phone number, or digit field would get different filtering. I was just trying to keep the example simple.
kaisellgren wrote:
I would need to generate the PHP code with PHP, because I simply cannot know what to expect from other coders.
Other coders on a project means establishing some coding standards that everyone agrees to. Database interaction is something you decide how to do upfront, and everyone must agree to it. The alternative is chaos and spaghetti code - no one wants that.
And I do actually generate PHP code with PHP - by reading directly from the database. It gives me a good starting point for development. There are other ways to do it as well, but I like having my SQL code explicitly in the models, thats just me.
Re: Properly handling data that goes into the database.
Posted: Mon Mar 09, 2009 9:38 am
by kaisellgren
inghamn wrote:The example is only doing a simple trim() as a filter in the setName() function. If you want to go the route of a Filter class - those rules would go into the setters in my example. For a name field, though...in my experience, there's really not much filtering to apply. The bound parameters will make sure the name gets saved in the database correctly, and safely. People have wierd names sometimes, like Smith, O'Conner, or B!0*R - who am I to judge? An email or phone number, or digit field would get different filtering. I was just trying to keep the example simple.
Name is a very simple detail. I would apply data size, data content and escaping (which is done by the prepared statements, though) filters. Ok, got it, it was a simplistic demonstration.
inghamn wrote:And I do actually generate PHP code with PHP - by reading directly from the database. It gives me a good starting point for development. There are other ways to do it as well, but I like having my SQL code explicitly in the models, thats just me.
So I would first need to generate an application that generates PHP scripts. Maybe a C++ application? Could be quite handy.
By the way, do you put all SQL into classes? Session, member, forum, shopping cart, wtvr, everything? How do you categorize those files...? It would look bad if I had over two thousand files on one folder, so, I would need to create some sort of hiearchial file system structure for them and a loader too...

Re: Properly handling data that goes into the database.
Posted: Mon Mar 09, 2009 10:35 am
by inghamn
kaisellgren wrote:
By the way, do you put all SQL into classes? Session, member, forum, shopping cart, wtvr, everything? How do you categorize those files...? It would look bad if I had over two thousand files on one folder, so, I would need to create some sort of hiearchial file system structure for them and a loader too..
Organize them however you think makes sense inside some directory. You're the one writing the autoload for them. (You are using autoload or spl_autoload, right?

)
http://www.php.net/autoload
http://www.php.net/spl_autoload
Re: Properly handling data that goes into the database.
Posted: Mon Mar 09, 2009 10:41 am
by kaisellgren
inghamn wrote:kaisellgren wrote:
By the way, do you put all SQL into classes? Session, member, forum, shopping cart, wtvr, everything? How do you categorize those files...? It would look bad if I had over two thousand files on one folder, so, I would need to create some sort of hiearchial file system structure for them and a loader too..
Organize them however you think makes sense inside some directory. You're the one writing the autoload for them. (You are using autoload or spl_autoload, right?

)
http://www.php.net/autoload
http://www.php.net/spl_autoload
Yea I run spl autoload register. I found it irritating that you couldn't do it like "class :: method", instead I had to do it without the spaces "class::method"!
