SQL Injection Prevention in Database Class

Question

I am creating a database class and I think it would be nice to include some form of SQL injection prevention (duh!). Here is the method that runs the 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 to the class than that, but I cut it just for that. The problem I am facing is that if I just use mysql_real_escape_string($sql, $this->link);, then it escapes the entire query and leads to a SQL syntax error. How can I dynamically find variables that need to be escaped? I want to avoid using mysql_real_escape_string()code in my main blocks, I would rather use it in a function.

Thank.

Answer 1

, , SQL-, , - PHP.

, . .

- , , , db, .

; , , .

, :

$user = new DbUser();
$user->create();
$user->set_email('[e-mail]');
$user->write();

:

$user = new DbUser();
$user->set_email('[e-mail]');
if ($user->load_from_fields())
{
}

:

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

Answer 2

SQL-: .

Blacklist , . . " ", SQL. , , , , , , , , , . , , , .

SQL , , .

Answer 3

, DB, WHERE ( , ), - :

$db->where(x,y);

.

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

corpus -

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

, WHERE.

Answer 4

, sql sql, . . :

select * from users where username='test' and password='itisme' or '4'='4'

sql, SQL :

"select * from users where username='test' and password='" . "itisme' or '4'='4". "'"

, , .

Answer 5

SQL- - SQL. , , SQL, ?

, SQL query(). . , , SQL.

SQL, . , , SQL.

PDO bindParam() Zend_DB query(), , .

Answer 6

, , .

Answer 7

Me, , . , codeigniter , .

:

$result = Database::query('INSERT INTO table (column1,column2,column3) VALUES(?,?,?)',array($value1,$value2,$value3));



public static $bind_marker = '?';
public static function query($query, $binds = FALSE)
    {
        if($binds !== FALSE)
        {
            $query = self::compile_binds($query,$binds);
        }
        // $query now should be safe to execute
}

private static function compile_binds($query, $binds)
    {
        if(strpos($query, self::$bind_marker) === FALSE)
        {
            return $query;
        }

        if(!is_array($binds))
        {
            $binds = array($binds);
        }

        $segments = explode(self::$bind_marker, $query);

        if(count($binds) >= count($segments))
        {
            $binds = array_slice($binds, 0, count($segments)-1);
        }

        $result = $segments[0];
        $i = 0;
        foreach($binds as $bind)
        {
            if(is_array($bind))
            {
                $bind = self::sanitize($bind);
                $result .= implode(',',$bind);
            }
            else
            {
                $result .= self::sanitize($bind);
            }

            $result .= $segments[++$i];
        }

        return $result;
    }

public static function sanitize($variable)
{
    if(is_array($variable))
    {
        foreach($variable as &$value)
        {
            $value = self::sanitize($value);
        }
    }
    elseif(is_string($variable))
    {
        mysql_real_escape_string($variable);
    }
    return $variable;
}

, codeigniter, , "IN":

$parameters = array
    (
        'admin',
        array(1,2,3,4,5)
    );

$result = Database::query("SELECT * FROM table WHERE account_type = ? AND account_id IN (?)",$parameters);

Answer 8

? PDO . , , , .