Database Abstraction with MDB2 from PEAR - Prepared Statements
(Page 4 of 4 )
If you know anything about web application security, you know that SQL injection is one of the most common vulnerabilities. These security holes are created when the program accepts data input from an outside source and places it into the raw text of a query. PHP is particularly notorious for these vulnerabilities because the standard MySQL library does not provide a prepared statement facility. It is left to the programmer to sanitize and escape all data.
MDB2 provides a prepared statement layer which works on top of all supported libraries. If you're not familiar with the concept, you can think of prepared statements as a query template. Rather than building the query out of a series of concatenated strings, the programmer specifies what query they want to run, and the library takes care of replacing the data into the query string in a properly escaped manner.
$db = MDB2::connect($dsn);
if (PEAR::isError($db)) {
die('Database connection error: ' . $db->getMessage());
}
$query = $db->prepare('INSERT INTO news (title, author) VALUES (?, ?)',
MDB2_PREPARE_MANIP);
$result = $query->execute(array("New PEAR Article", "Chris Moyer"));
if (PEAR::isError($result)) {
die('Could not insert row');
}
$query = $db->prepare('SELECT * FROM news WHER author=?',
MDB2_PREPARE_RESULT);
$result = $db->execute(array('Chris Moyer'));
In this example, you'll notice several things. A question mark is used to denote the replaceable data passed in to your query. This data is then passed to the execute statement as an array of replacement values. Execute will take care of converting those to the proper string format for your database. Additionally, you must tell prepare() whether you are preparing a manipulation statement (INSERT, UPDATE, DELETE) or a result (SELECT) as some databases require that parameters to these statements be formatted differently.
Conclusion
The features outlined in this article simply scratch the surface, as MDB2 strives to abstract nearly every feature you'd want from a modern database library. MDB2 may not always be the answer. But, if the question is, "How can I ensure my application runs on three different database platforms with minimal code modification?" MDB2 is a good answer.
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
/ 6 
