php[architect] logo

Want to check out an issue? Sign up to receive a special offer.

Crystal Starting to Form

Posted by on August 18, 2010

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.


Tags: ,
 

Responses and Pingbacks

You don’t even need to fseek, just:
$handle = fopen(‘/file.txt’, ‘a’);

Or even better: just use error_log function

How is this going to be different than the Doctrine ORM? Maybe more lightweight?

Hello,

For you, what’s the best way to protect from Sql injections ?

Bye,
Hervé

@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.

@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

@Daniel, @Sebastian: Yes, good points.

Best!!… PHP is RAD with Crystal.

Hello Bill,

Thanks for your answer, I have bought your book but I’m not yet to this chapter.

bye,
Hervé

Excellent!

Cant wait to get this baby out of incubator 🙂
Looks very promising, something that could really speed up db interaction.

I like PHP DataMapper – http://phpdatamapper.com/

the database module for kohana seems to be better for me

I think this is better than PHP datamapper or kohana.

Leave a comment

Use the form below to leave a comment: