Mysql dynamic query [sql injection]-CodePudding

I usually use prepared statements but on the particular page I am using a dynamic query and I can't find a reliable way to prevent SQL injection.

if(isset($_GET['sub_cat'])){

    if($_GET['sub_cat'] != '')
        $conditions[] = 'ad_sub_cat='.$_GET['sub_cat'].'';
}

if(isset($_GET['ad_brand'])){

    if($_GET['ad_brand'] != '')
        $conditions[] = "`ad_brand` LIKE CONCAT('%','".$_GET['ad_brand']."','%') ";
}

if(isset($_GET['min_range'])){

    if($_GET['min_range'] != '')
        $conditions[] = 'ad_price >='.$_GET['min_range'].'';
}

if(isset($_GET['max_range'])){

    if($_GET['max_range'] != '')
        $conditions[] = 'ad_price <='.$_GET['max_range'].'';
}

if(isset($_GET['for_r_s'])){
    
    if($_GET['for_r_s'] != '')
        $conditions[] = 'for_r_s ='.$_GET['for_r_s'].'';
}

$query = "SELECT posts.ID, posts.ad_title, posts.ad_price, posts.ad_location, posts.ad_sub_cat FROM `posts` WHERE ". implode(' AND ', $conditions) ."";

CodePudding user response：

You prevent SQL injection the same way you would with any other query: by separating the query, which should always be based on hard-coded values, from the data. The only difference is that you need to build up both the query and the list of data parameters conditionally.

So instead of:

if(isset($_GET['sub_cat'])){

    if($_GET['sub_cat'] != '')
        $conditions[] = 'ad_sub_cat='.$_GET['sub_cat'].'';
}

You should write:

if(isset($_GET['sub_cat'])){

    if($_GET['sub_cat'] != '') {
        $sql_conditions[] = 'ad_sub_cat=:sub_cat';
        $parameters['sub_cat'] = $_GET['sub_cat'];
    }
}

Or if using positional rather than named parameters:

if(isset($_GET['sub_cat'])){

    if($_GET['sub_cat'] != '') {
        $sql_conditions[] = 'ad_sub_cat=?';
        $parameters[] = $_GET['sub_cat'];
    }
}

And so on for all your conditions.

Then at the end, you build up your SQL from $sql_conditions - which has no user input in it at all, so is safe from injection - and pass $parameters to PDO/mysqli to execute the query.

CodePudding user response：

Use mysqli_real_escape_string before adding the parameter to the query string. For example:

if(isset($_GET['sub_cat']) && $_GET['sub_cat'] != ''){

    $sub_cat = mysqli_real_escape_string($connection, $_GET['sub_cat']);

    $conditions[] = 'ad_sub_cat='.$sub_cat.'';
}