Backticks in database queries

twheel · August 2013

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?

jbroadway · August 2013

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 :)

twheel · August 2013

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

jbroadway · August 2013

Awesome, just pushed the fix to Github!

twheel · 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'])."%'")

jbroadway · December 2013

How about this?

$query->where ('name LIKE ?', '%' . $_GET['q'] . '%')

twheel · December 2013

Wouldn't that be vulnerable to SQL injection?

twheel · December 2013

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

twheel · December 2013

It works. :)

Howdy, Stranger!

Categories

Backticks in database queries

Comments