I am creating a database class and thought it would be smart to incorporate some type of SQL injection prevention (duh!). Here's the technique that runs a database query:

class DB
{
    var $db_host    = 'localhost';
    var $db_user    = 'root';
    var $db_passwd  = '';
    var $db_name    = 'whatever';

    function query($sql)
    {
        $this->result = mysql_query($sql, $this->link);
        if(!$this->result)
        {
           $this->error(mysql_error());
        } else {
            return $this->result;
        }
    }
}

There's more within the class than that but I am performing lower for this purpose. The issue I am facing is that if I simply use mysql_real_escape_string($sql, $this->link); it escapes the whole query and creates a SQL syntax error. How do i dynamically discover the variables that should be steered clear of? I wish to stay away from mysql_real_escape_string() during my primary code blocks, i'd favour it inside a function.

Thanks.

However , when you've built an SQL query it's past too far to have the ability to prevent injection by finding variables - otherwise it'd be included in PHP.

The getting away must be done much earlier whenever you build the queries. You could utilize a question building class.

However I would suggest another approach - which would be to possess a layer that delivers a database table being an object, here's a good example user object which comes from the base db entity class which supplies an entire interface to some database using active record pattern, and also the iterator pattern.

I'll illustrate this with a few good examples the neat factor here's iterators as possible abstract away much more and also have some quite generic classes further on down the road to extract the information.

To produce a user record while using above approach:

$user = new DbUser();
$user->create();
$user->set_email('test@example.com');
$user->write();

To see a person record:

$user = new DbUser();
$user->set_email('text@example.com');
if ($user->load_from_fields())
{
}

To iterate through records:

$user_iterator = DbUser::begin();
if ($user_iterator->begin())
{
    do
    {
         $user = $user_iterator->current();
         echo $user->get_email();
    } while ($user_iterator->next());
}

You will find two methods to prevent an SQL injection attack: Blacklist based approach and Whitelist based approach.

Blacklist approach suggests you need to look into the whole query string and identify undesirable code and take away it. This really is hell large amount of difficult. Rather, make use of the whitened-list approach by utilizing parametrized SQL. By doing this you'll be certain the only real query that'll be performed would be the the one that you deliberately built making use of your code, and then any injection attempt will fail as all of the injection queries will take part in parameter and therefore will not be performed through the database. You are searching for a means of stopping the injection following the query was already developed, which not directly means a blacklist based approach.

Use parametrized SQL inside your code, its among the secure coding concepts modified globally.

Either parametrized or try building the DB class to be able to pass all of the values for WHERE ( and whatever could use values ) utilizing a something similar to :

$db->where(x,y);

ie.

$db->where('userid','22');

As well as in the course corpus use something similar to

function where(var x, var y) // method
{
    $this->where .= x . ' = '.mysql_real_escape_string(y);
}

Obviously this needs cleaning to aid multiple WHERE inputs.