Safer queries..?

[eluser]rvent[/eluser]
hello,

I am trying to find out whats safer..

Code:

function checkItem ($table, $column, $value)
    {
        $sql = "SELECT *
                FROM ?
                WHERE ? = ?";
        $result = $this->db->query($sql, array($table, $column, $value));
        
        //Check for a result
        if($result->num_rows() > 0)
            return TRUE;
        else
            return FALSE;
    }

Or

Code:

function checkItem ($table, $column, $value)
    {
        $result = $this->db->get_where($table, array($column => $value));
        
        //Check for a result
        if($result->num_rows() > 0)
            return TRUE;
        else
            return FALSE;
    }

According to the docs both of them are escaped so no difference, uh? Other than the 1st block wont work since it passes '' instead of ``

Thanks

[eluser]TheFuzzy0ne[/eluser]
Exactly. There's no difference, although there may be a slight difference in performance, and as you pointed out, query bindings can only be used for values, not field/table names. Personally, I think the second version looks cleaner. Also, AR gives you more flexibility when it comes to building more complex statements. Sure, a lot of old-school, hardcore programmers build seriously complex SQL queries for breakfast, but I just don't have the time, knowledge or patience, so for me, unless It's something that AR can't do, AR wins hands down every time.

However, an arguably better way of coding your first function would be like this:

Code:

function checkItem ($table, $column, $value)
{
    $result = $this->db->query("
             SELECT *
             FROM `$table`
             WHERE ? = ?",
             array($column, $value)
        );
      
    //Check for a result
    if($result->num_rows() > 0)
        return TRUE;
    else
        return FALSE;
}

Yes, I may have complicated it slightly by getting rid of the variable, but the point is, the $table variable is used in the query, as it doesn't really need escaping. Of course, you should probably validate it anyway with a simple regex to ensure if only contains a certain set of characters. Or of course, just use $this->db->escape().

[eluser]majidmx[/eluser]
I found AR use more memory [not completely sure though], so if you're thinking of a high traffic application you'd better doing the queries manually.

[eluser]TheFuzzy0ne[/eluser]
You're right. It will use a little more memory, because the AR class is instantiated, and also stores the data for the query, as well a other things. If you're going to do manual queries for everything, you should disable the AR class completely.

[eluser]majidmx[/eluser]
Good point TheFuzzy0ne.