Database Handler and SQL injection prevention

Question

I've written a database model class in PHP and have written a controller class that specifically validates the data before sending it to db. I'm getting criticism that I should handle the data in database model class rather than controller class.

I wanted to write a generic db class which can be used anywhere for CRUD operations. Now can you guys please help me out in sorting this out? Whether I should validate the data in db model class or in db controller class?

Answer 1

You should use PDO (PHP Data Objects) for preventing SQL Injection attacks. It supports 12 different database systems.

Your Generic Class for connecting to MySQL using pdo would look like this:

class Db{
    public function dbConnect(){
        try {
            $conn = new   PDO('mysql:host=host_name/ip;dbname=database_name','user','password');
            $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        }catch(PDOException $e){
            echo $e->getMessage();
         }
    return $conn;
    }
}

You can use this class from anywhere just follow the following steps:

1. include the above php file into your script where you want to use the Db class. For example:

   include_once 'db_connect.php';
   

2. Create an object of Db:

   $conn = new Db(); // $conn is object
   

3. Call to function 'dbConnect' using object($conn):

    $connection =$conn->dbConnect();                                                 
    // $connection is variable which will hold the
    // value returned by the 'dbConnect' function.
   

Using this method you can make more functions inside the Db class for CRUD operation and use those function anywhere you want.

To learn more about PDO visit:

1. http://code.tutsplus.com/tutorials/why-you-should-be-using-phps-pdo-for-database-access--net-12059
2. http://code.tutsplus.com/tutorials/php-database-access-are-you-doing-it-correctly--net-25338

Answer 2

If your database class is responsible for getting data into and out of your actual database, then it should take care of defending against SQL injection attacks or, if you're still building dynamic SQL, escaping character sequences that can be harmful (e.g. single quotes, line breaks (in some cases), etc.)

That way, everything outside of the database class can just with “pure” PHP types and doesn't have to worry about any of that. The database class does the database class' job so that nothing else has to.

That said, it's an entirely different discussion as to whether or not the database class should be doing logical validation, say,

Is this a valid tire size for this particular car?

That's not a database issue; it's “business” logic and it really belongs elsewhere.

Answer 3

Your database validations should happen in your model. Assuming you are not using a framework you can write a generic wrapper class with your CRUD operations and protect against SQL injection etc. here. Data validation can occur within your wrapper and more specific validation (e.g. like Rails like validates_presence_of) on data can occur in models that subclass your wrapper. A pseudo example below should get you kicked off, however, note it is always much better to use a framework (even a micro-framework with ORM) for such tasks as most of this stuff is baked in so it is just a matter of learning the framework itself.

<?php
class DataWrapper
{
    function __construct()
    {
        // connect to database here
    }


    function create($object)
    {
        // do create stuff here
    }

    function read($object)
    {
        // do read stuff here
    }
    ...
}

class Car extends DataWrapper
{
    private $table = 'cars';

    // can do specific validation here
}
?>

Answer 4

I think what you're looking for is a Service class. Controllers should be very thin and do literally nothing but forward the data from the request to a service and respond with the appropriate output.

The service class should take care of validation delegation and dealing with the database abstraction.