Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Sign In with Google Sign In with OpenID

Backticks in database queries

edited August 2013 in Framework

I want to generate a query of this form:

select b.* from brands b 
    inner join products p on b.id = p.brand_id 
    where p.category_id = 'category' and p.subcategory_id = 'subcategory'
    group by p.brand_id;

If I use

public static function brands_by_subcategory ($category_id, $subcategory_id) {
    return self::query ('b.*')
        ->from ('brands b inner join products p on b.id = p.brand_id')
        ->where ('p.category_id', $category_id)
        ->where ('p.subcategory_id', $subcategory_id)
        ->group ('p.brand_id')
        ->fetch ();
}

The generated query looks like

select b.* from brands b
    inner join products p on b.id = p.brand_id 
    where `p.category_id` = 'category' and `p.subcategory_id` = 'subcategory'
    group by `p.brand_id`

...which, apparently because of the backticks, generates unknown column errors in MySQL (p.category_id, p.subcategory_id, p.brand_id).

Since there's only one brand_id in the two tables, I can make it work using

public static function brands_by_subcategory ($category_id, $subcategory_id) {
    return self::query ('b.*')
        ->from ('brands b inner join products p on b.id = p.brand_id')
        ->where ("p.category_id = '$category_id'")
        ->where ("p.subcategory_id = '$subcategory_id'")
        ->group ('brand_id')
        ->fetch ();
}

Is there a better approach?

Comments

  • Looks like the backticks shouldn't be including the . character. Here's a quick fix for Model::backticks(), let me know if it fixes it.

    /**
     * Add backticks to a name or list of names to prevent clashing with
     * reserved words in SQL.
     */
    public static function backticks ($item) {
        if (is_array ($item)) {
            foreach ($item as $k => $v) {
                if (strpos ($v, '`') !== 0) {
                    $item[$k] = '`' . str_replace ('.', '`.`', $v) . '`';
                } else {
                    $item[$k] = $v; // Already has backticks
                }
            }
        } elseif (strpos ($item, '`') !== 0) {
            $item = '`' . str_replace ('.', '`.`', $item) . '`';
        }
        return $item;
    }
    

    Also a quick tip: you can rewrite your where clauses to avoid SQL injection like this:

    ->where ("p.category_id = ?", $category_id)
    

    This allows for more complex expressions while still being secure :)

  • Works great. And thanks for the tip -- I knew there must be a better way!

  • Awesome, just pushed the fix to Github!

  • edited December 2013

    Is there any way to do a LIKE query and avoid SQL injections? Something like $query->where ('name LIKE %?%', $_GET['q']), but that renders as name LIKE %'search string'% instead of name LIKE '%search string%'.

    Maybe there's a method to call? Something like $query->where ("name LIKE '%".DB::method($_GET['q'])."%'")

  • How about this?

    $query->where ('name LIKE ?', '%' . $_GET['q'] . '%')
    
  • Wouldn't that be vulnerable to SQL injection?

  • Never mind, I see what you did. I'll try it.

  • It works. :)

Sign In or Register to comment.