Right now, our page scripts (and probably some of our classes) interact with a database directly with embedded SQL statements. For example, a page script might have some logic like this:
page_script.php
1 <?php
2 $db = new Db($db_host, $db_user, $db_pass);
3 $post_id = $_GET['post_id'];
4 $stm = "SELECT * FROM comments WHERE post_id = $post_id";
5 $rows = $db->query($stm);
6 foreach ($rows as $row) {
7 // output each row
8 }
9 ?>
The problems with using embedded SQL strings are numerous. Among other things, we want to:
Test the SQL interactions in isolation from the rest of the code
Reduce the number of repeated SQL strings throughout the codebase
Collect related SQL commands for generalization and reuse
Isolate and remove security flaws such as SQL injection
These problems and more lead us to the conclusion that we need to extract all our SQL-related code to an SQL layer, and replace the embedded SQL logic with calls to our SQL-related class methods. We will do...