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

Escaping SQL in subquery

edited May 2014 in Framework

I have a somewhat complicated query and I'm wondering how to sanitize it against SQL injections. $conditions['q'] and $conditions['type'] below are form submissions. Is there a function like DB::sanitize or similar? I looked through the documentation but missed it if it's there.

    $where_clause = "WHERE latitude BETWEEN '$min_lat' AND '$max_lat' AND longitude BETWEEN '$min_lon' AND '$max_lon'";
    if (isset ($conditions['q'])) {
        $where_clause .= ' AND MATCH (field1, field2, field3) AGAINST ' . DB::sanitize ($conditions['q']);
    }   
    if (isset ($conditions['type'])) {
        $where_clause .= ' AND MATCH (field4, field5, field6) AGAINST ' . DB::sanitize ($conditions['type']);
    }

    return self::query (
        "*, ($R * acos(cos(radians('$latitude')) * cos(radians(latitude)) * cos(radians(longitude) - radians('$longitude')) + sin(radians('$latitude')) * sin(radians(latitude)))) AS distance")
        ->from ("(SELECT * $where_clause) AS firstcut")
        ->group ('distance')
        ->order ('distance')
        ->having ("distance < $radius")
        ->fetch ();
}

Comments

  • I would try inserting them with ? as usual, and manually adding them to the $query_params property of the model object, like this:

    $q = self::query (
        "*, ($R * acos(cos(radians('$latitude')) * cos(radians(latitude)) * cos(radians(longitude) - radians('$longitude')) + sin(radians('$latitude')) * sin(radians(latitude)))) AS distance"
    );
    
    $where_clause = "WHERE latitude BETWEEN '$min_lat' AND '$max_lat' AND longitude BETWEEN '$min_lon' AND '$max_lon'";
    if (isset ($conditions['q'])) {
        $where_clause .= ' AND MATCH (field1, field2, field3) AGAINST ?';
        $q->query_params[] = $conditions['q'];
    }   
    if (isset ($conditions['type'])) {
        $where_clause .= ' AND MATCH (field4, field5, field6) AGAINST ?';
        $q->query_params[] = $conditions['type'];
    }
    
    return $q
        ->from ("(SELECT * $where_clause) AS firstcut")
        ->group ('distance')
        ->order ('distance')
        ->having ("distance < $radius")
        ->fetch ();
    

    I moved the self::query() to the start and stored the object, that way you can use $q->query_params[] where you need to in building the $where_clause. This works because the query building methods are chainable and just return the same instance of the current model.

Sign In or Register to comment.