Saturday, November 2, 2013

PHP Database Programming: Introducing 'ormclass'

To begin with, I have a few problems with the traditional web stack. Suppose I wanted to write a feature-rich, user friendly web application -- this requires that I know at least five programming languages:

It doesn't seem right that we need five languages for a singular application, but that aside, the fact that SQL injection is still in the top ten reasons that anything is compromised is pathetic. We are in the year 2013, SQL injection shouldn't much exist anymore. SQL programming can also be a bit cumbersome, for multiple reasons. Enter the ORM. ORM's are designed for two purposes: making SQL data more easily accessible for a programmer from a chosen programming language, in addition to improving overall application security. The problem I have with most ORM's is simple: I still find myself having to write some form of sql-like statements -- even if it isn't traditional SQL itself. For example, in PHP's doctrine ORM, if I wanted to select an article by id 1, the syntax would look something like:

   $article = Doctrine_Query::Create()->select('*')->from('article')->where('id=?')->execute($id)->fetchOne();

The syntax may have changed since I last used Doctrine, but you can see there is still a lot of SQL-like code going on (even if its not direct SQL itself). In this case I have to ask, why didn't we just use the mysql PDO library? At this point, we've added a lot of extra bloat to the application in the form of doctrine ORM; yet we still find ourselves writing SQL (or something similar). For all of that code and RAM consumption, that's not much of an improvement for a developer who just wants to hack out a quick application.

So, I've made my own quick and dirty ORM (available at github). It automatically handles sanitizing for the developer, as well as automatically handling object mapping. Of course, this isn't the best ORM in the world (and I will never make that claim), but it certainly helps for getting some code out quickly and effectively. Its also very tiny. Many improvements can be made to its design, and I will continue to develop this off-and-on as needed for my own applications. The purpose is to effectively eliminate the need to write SQL during (simple) application development.

The ormclass needs a configuration file to be included before it. The configuration is expected to look like:

    $dbhost   = 'localhost';  //Database server hostname
    $database = '';           //Database name
    $dbuser   = '';           //Database username
    $dbpass   = '';           //Database password

    $dbl      = @mysql_connect($dbhost,$dbuser,$dbpass);
    @mysql_select_db($database,$dbl) or die("I'm not configured properly!");

Obviously, you'll have to fill those values in for yourself. I wanted an ORM that would let me do something like the following:

    $article  = new article($_GET['id']);
    # or 
    $article  = new article($_GET['title']);
    # or
    $articles = new article($array_of_ids);
    # or 
    $articles = new article($array_of_titles);
    # or 
    $articles = new article($nested_mixed_array_of_titles_and_ids);    

I also wanted to be able to simply assign properties to the object and save and delete it, or even create new objects. This would also need the capacity for searches, both exact and wildcard. This would (mostly) eliminate the need for writing actual SQL in my application, but also handle some of the tedium of sanitizing for me. Again, I'm aware that this can certainly be done better and if you'd like to contribute to the project, submit a pull request to github. This is a quick and dirty implementation of such an ORM, that allows the programmer some leeway to write logical code in stead of tedious code. There are definitely some places that need work. I've hacked out a version that uses the traditional MySQL library, and I'm working on a version that uses the MySQL PDO library.

The methods and features included in the library include a few subsets of SQL query tedium removal. The following methods are inherited by all classes extending the ORM's class:

  • __construct($arg = null)
  • search($property,$string,$limit = 10, $offset = 0)
  • search_exact($property,$value, $limit = 10, $offset = 0)
  • unsafe_attr($field,$value)
  • fetchAll()
  • fetchRecent($limit = 10)
  • delete()
  • save()

The constructor will automatically check to determine if a method called construct() exists in its child class. If so, it will invoke the function after it has preloaded all of the relevant data into the object. This is how relations can be maintained. Its a bit hackier than most ORM's (there's no configuration file in which you simply state the relations), but it gets the job done and allows the programmer to have control over whether or not relations are followed and child objects are created by default. The ORM requires that every table have an 'id' column. The 'name' column is optional. Here is an example relation:

    class article extends ormclass {
        function construct() {
            $this->author = new author($this->author_id);
  • In this example, you could later:
     $article = new article($id);
     echo $article->author->name; # or other author property.

When you want to create a new record, you can simply pass '0' as the ID for the object, and it will automatically have an ID on instantiation:

    $article = new article(0);

Alternatively, its possible to just call save after a null instantiation (you'd do this if you don't need it to have an ID for relation purposes before the object has attributes):

    $article = new article();

Similarly to the constructor hook for construct(), there is also a hook for creation of a new record. If you wanted to do something when a new object is inserted into the database, you could add a function called creation() to the class, and it would be called any time a new record is created in the database.

The difference between unsafe_attr() and save() is relatively simple. If there is HTML allowed in a field, for example $article->body, then you'd want to use the unsafe_attr() function to save that particular field (save() will autosanitize against XSS). When using unsafe_attr(), because this uses the normal SQL library (and not PDO), you will need to make sure that your html contains exclusively single quotes or exclusively double quotes, it doesn't particularly matter which. The function does do checks to ensure you aren't using both to prevent sql injection, and returns false if both are in use. This bug is the primary reason I'm developing a PDO version separately (besides standards, we cant forget those). This ORM also has a performance/feature trade off. Because I wanted it to be able to handle nested arrays, the collection function runs an arbitrarily large amount of SQL queries. I can provide a version that doesn't do this (but will also be unable to handle nested arrays) on request, since I'm sure people will not want the performance hit; however because I am working on a PDO version, I'd rather make that a loader option in that rendition for how collections are handled. This also currently only auto-sanitizes strings and integers; better sanitizing will come in the PDO version (hence my describing this as "Quick and Dirty").

This ORM does not have any scaffolding. This means that you will have to create the database and the associated tables yourself before this ORM can access the data. It does not auto-generate tables or class files. If you have an existing database and you'd like to auto-generate the class files, something like the following line of bash should suffice:

mysql dbname -e 'show tables'|grep -v dbname|awk '{print "<?php\nclass "$0" extends ormclass {\n\n}\n?>"}' > objects.php

In closing, the point of this was simply to prove that SQL statements can actually be eliminated from the high-level code entirely; and to provide some easily accessible API. The PDO version should be able to handle a few more complex tasks, like table scans and complex joins to create meta-objects from multiple tables. I also plan to extend the compatibility to include PostgreSQL and perhaps even port this to additional programming languages. At any rate, please enjoy your newfound ability to kick back and lazily write database powered applications. Happy hacking.

1 comment:

  1. It is nice information shared that is why many services hire PHP developer for effective web development application.