Crystal Starting to Form
Martin Rusev is developing a new database query library for PHP 5. The project shows promise, but it has some distance to go before it competes with other available libraries.
Martin’s project is called Crystal. It’s an object-oriented wrapper for the venerable mysql and pg extensions, with the goals of making SQL more human-readable, and providing a library that is lightweight and easy to learn.
Examples on the web site show simple usage, once you set up a configuration file with database connection parameters.
$db = Crystal::db();$posts = $db->select('date, title')->from('posts')->fetch_all();
The Crystal Project website and documentation is easy to read and shows many examples for using the Crystal class interface in most scenarios. It also provides a class interface for defining data validation functions so you can process data before you update or insert it to the database.
However, by simplifying SQL usage, Crystal fails to support some important features of SQL, such as query parameters and transactions. By the same token, there is no support for table aliases, so you would have to work around the interface to do a self-join or to reference columns that appear in more than one joined table.
I’m very worried about the way Crystal tries to protect against SQL injection security risks. It delegates escaping to functions like mysql_real_escape_string() and pg_escape_string() in most cases, but it also uses the same functions when delimiting identifiers (table names and column names). This isn’t safe, because the escaping functions don’t escape the back-tick or double-quote characters used for identifier delimiters. The library does not handle escaping properly for UTF-8 data or risks from multibyte data. I think the developer’s understanding of SQL injection defense is naive for someone developing a database interface library.
The library supports MySQL and PostgreSQL in the current version (the project claims an SQLite adapter is in development). But most of the code is loaded into vendor-specific subdirectories, with little or no common code used by both adapters. This is likely to result in bugs that affect one database brand but not the others. Also, there are no regression tests present in the download, despite the project having an open-source license (MIT License).
There are other naive or unfinished features in this project. For example, a query logging class works by reading the entire contents of the log file into memory using file_get_contents(), then appending the current query to that string, and writing the entire log back out to the log file. This is impractical to use even during development or testing, let alone a production environment with many concurrent requests. The developer should learn about fseek().
The Crystal Project has nice goals, but its development is in its very early stages (the April 2010 release is version 0.4). It has a long way to go before it can be recommended.
Leave a comment
Use the form below to leave a comment:
Responses and Pingbacks
August 18th, 2010 at 5:17 pm
You don’t even need to fseek, just:
$handle = fopen(‘/file.txt’, ‘a’);
August 19th, 2010 at 5:33 am
Or even better: just use error_log function
August 19th, 2010 at 10:28 am
How is this going to be different than the Doctrine ORM? Maybe more lightweight?
August 19th, 2010 at 1:18 pm
Hello,
For you, what’s the best way to protect from Sql injections ?
Bye,
Hervé
August 19th, 2010 at 3:01 pm
@Justin: I’m not sure why the author is designing a new package. It’s just a query builder so far; it doesn’t have many ORM features that a package like Doctrine does.
August 19th, 2010 at 3:06 pm
@Hervé: Thanks for asking! I’ll refer you to my recent book “SQL Antipatterns: Avoiding the Pitfalls of Database Programming.” http://www.pragprog.com/titles/bksqla/sql-antipatterns
The book includes a chapter about defending against SQL Injection.
Also I’ve posted similar advice in my slides for my presentation “SQL Injections Myths and Fallacies” http://www.slideshare.net/billkarwin/sql-injection-myths-and-fallacies
August 19th, 2010 at 3:12 pm
@Daniel, @Sebastian: Yes, good points.
August 19th, 2010 at 7:55 pm
Best!!… PHP is RAD with Crystal.
August 20th, 2010 at 3:03 am
Hello Bill,
Thanks for your answer, I have bought your book but I’m not yet to this chapter.
bye,
Hervé
August 23rd, 2010 at 7:25 pm
Excellent!
August 25th, 2010 at 12:40 pm
Cant wait to get this baby out of incubator 🙂
Looks very promising, something that could really speed up db interaction.
August 25th, 2010 at 10:56 pm
I like PHP DataMapper – http://phpdatamapper.com/
October 6th, 2010 at 12:56 pm
the database module for kohana seems to be better for me
October 5th, 2016 at 3:18 pm
I think this is better than PHP datamapper or kohana.