Database Abstraction Switcharoo

A database abstraction layer is a piece of software which helps to manage the interaction between a script, such as PHP, and a database, like MySQL. One of the advantages of such a tool is that it can help boost the security of interactions with the database, particularly when using user input from a web form to populate the database query. This introduces the danger of SQL injection, where a user tries to interact with the database inappropriately by writing SQL code in the form fields instead of the information the form is soliciting.

Another advantage of abstraction layers is that it is usually relatively easy to change the database back end on a project without having to substantially overhaul the code responsible for interacting with the database. Examples of abstraction layers include PEAR MDB2 and PDO. I was introduced to abstraction layers through MDB2, although recently I was forced to switch from MDB2 to PDO on a project when I changed the back end database to SQLite3. MDB2 couldn't interact with this iteration of SQLite.

This week I switched another project over to PDO even though the backend was remaining in MySQL. I was improving the security of my code by no longer passing completed SQL statements but instead assembling them using the abstraction layer and a technique using placeholders. However, I just couldn't get it to work in MDB2. It took offense when I used placeholders, named or unnamed. The error reporting pretty much told me nothing but it seemed as though the MDB2 connection was being lost or something like that. So in a short space of time I did the switcharoo to PDO and it worked like a charm.

Securely handling interactions between a script and a database, especially when user input is included, is an essential skill. It's well worth investing time in learning to utilize such a tool before you need to use it in a hurry.