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.

Early last month, we sent out an email to customers of php|architect announcing a contest just for them. Participants had to look at last month’s cover and name the 9 websites represented on the visible cards. We want to thank everyone who played, we loved reading some of the responses we got and wish that there had been a prize for most inventive answer.

We have selected 3 winners at random. Each winner will be contacted and given 2 code good for PDF versions of any of php|architect’s books.

The three winners are (in no particular order):

• Susan Campbell
• Joe Devon
• Steven Lloyd Watkin

Congratulations to the three of you. Thank you for being php|architect customers!