I’ve been developing websites with PHP for about 5 years now and I blush to confess that I have only just discovered I can use bind parameters with SQL, instead of painstakingly building up my SQL statements by concatenating strings, while remembering to deal appropriately with all potentially dangerous characters to avoid the risk of crashes or hacks.
Bind parameters are an unreservedly Good Thing. Not only do they make your SQL more readable, maintainable, and portable, but they also protect against the dreaded SQL injection which is the scourge of badly coded PHP websites developed by people who don’t know any better. And as if that wasn’t enough they can improve performance too.
I am no fan of reinventing the wheel, so since I first started using PHP I have used the open-source ADODB database abstraction library for database access. I like it because it’s fast and lightweight compared to its better-known competitor PEAR::DB. Tutorials are thin on the ground though, and I confess I only discovered its support for bind parameters recently, by reading between the lines of the documentation while looking up something else. Since then I’ve been gradually refactoring existing code — whenever I need to edit some code for some other reason I have a look for any embedded SQL and add some data-binding goodness to it. This is simple to do with ADODB. For example:
Before:
$SQL = "SELECT column1, column2, column3 FROM Tablename WHERE column1 = '$myvalue'";
$Result = $DB->GetRow($SQL);
having first carefully cleaned the contents of $myvalue of course!
After:
$SQL = "SELECT column1, column2, column3 FROM Tablename WHERE column1 = ?";
$Result = $DB->GetRow($SQL, array($myvalue));
Note I didn’t need to quote the string in the SQL statement, and the database library deals with any escaping necessary, in the appropriate style for the database being used — no MySQL-specific code here! In addition the SQL statement is pre-compiled — if you are going to execute the same statement a number of times with different values each time (e.g. when doing multiple inserts), this can really boost performance.
So what’s not to like? I don’t know why this technique isn’t better known among PHP developers. For example here is a lengthy thread discussing SQL injection, involving some apparently quite knowledgeable developers, and yet bind parameters are never mentioned; everyone pins their hopes on the stupidly named PHP function mysql_real_escape_string. Perhaps this will change now that support for binding is built into the PDO database access layer in PHP 5. Even if you are still using PHP 4.x you owe it to yourself to try a database abstraction library instead of endlessly hard-coding for one specific database engine.
Of course I should add that none of this means you can dispense with proper data validation before writing it to the database: the golden rule still remains “Never trust user input”!
