SQL injection problem

[aleborg]

I am trying to do a classfield extension to punbb but have problems to do it secure enough for sql injections.

 

$search_string = "";
if($w != ""){
    $words = explode(",", $w);
    $words = trimarray($words);
    //$words = array_merge(array(""), $words);
    $string_of_words = implode(" ", $words);
    $search_string = " AND MATCH (a.body, a.header)
    AGAINST (
    '" . $string_of_words . "'
    IN BOOLEAN
    MODE
    )";
}

if($cat != "0"){
    $search_string .= " AND a.category = " . $cat;
}

if($district != "0"){
    $search_string .= " AND a.lan = " . $district;
}

if($type != "2"){
    $search_string .= " AND a.ad_type = " . $type;
}

////////////////////////////PAGING

// Fetch ads count
$result = $db->query('SELECT COUNT(id) FROM '.$db->prefix.'ads AS a WHERE a.active = 1 '.$search_string.'') or die(mysql_error());
$num_ads = $db->result($result);

// Determine the user offset (based on $_GET['p'])
$num_pages = ceil($num_ads / $posts_per_page);
$p = (!isset($_GET['p']) || $_GET['p'] <= 1 || $_GET['p'] > $num_pages) ? 1 : $_GET['p'];
$start_from = $posts_per_page * ($p - 1);
$paging_links = $lang_common['Pages'].': '.paginate($num_pages, $p, 'viewads.php?w='.urlencode($w).'&cat='.$cat.'&dist='.$district.'&type='.$type.'&search=Submit');


define('PUN_ALLOW_INDEX', 1);
require PUN_ROOT.'header.php';

?>
<?php 
    if($pun_user['is_guest']){
        echo "Login to add an classfield";
    }
    else{
        echo "<a href='newad.php'>Add classfield</a>";
    }
?>
<br /><br />
<div class="blockform">
    <h2><span>Search for an ad</span></h2>
    <div class="box">
        <form id="adlist" method="get" action="viewads.php">
        <div class="inform">

            <fieldset>
                <legend>Search</legend>
                <div class="infldset">
                    <label class="conl">Search word<br /><input type="text" name="w" value="<?php echo $w != '' ? $w : ''; ?>" size="25" maxlength="25" /><br /></label>
                    <label class="conl">Category
                    <br /><select name="cat">
                        <option value="0" selected="selected">All categorys</option>
                        <?php
                        $query = ('SELECT id, description FROM '.$db->prefix.'ad_categories ORDER BY description');
                        $result = $db->query($query) or die (mysql_error());
                        while ($categories = $db->fetch_assoc($result)):?>
                         <option value="<?php echo $categories['id']; ?>" <?php echo $cat == $categories['id'] ? "selected='selected'" : ""; ?>><?php echo utf8_decode($categories['description']); ?></option>
                        <?php endwhile; ?>
                    </select>
                    <br /></label>
                    <label class="conl">Ort
                    <br /><select name="dist">
                        <option value="0" selected="selected">Hela Sverige</option>
                        <?php
                        $query = ('SELECT id, description FROM '.$db->prefix.'district ORDER BY description');
                        $result = $db->query($query) or die (mysql_error());
                        while ($districts = $db->fetch_assoc($result)):?>
                         <option value="<?php echo $districts['id']; ?>" <?php echo $district == $districts['id'] ? "selected='selected'" : ""; ?>><?php echo utf8_decode($districts['description']); ?></option>
                        <?php endwhile; ?>
                    </select>
                    <br /></label>
                    <label class="conl">Ad type
                    <br /><select name="type">
                        <option value="2" <?php echo $type == 2 ? "selected='selected'" : ""; ?>>All</option>
                        <option value="0" <?php echo $type == 0 ? "selected='selected'" : ""; ?>>For sale</option>                        
                        <option value="1" <?php echo $type == 1 ? "selected='selected'" : ""; ?>>Want to buy</option>
                    </select>

                    <br /></label>
                    <p class="clearb">Use , if you want to search for more example "cars, bikes"</p>
                </div>
            </fieldset>
        </div>
        <p><input type="submit" name="search" value="Submit" accesskey="s" /></p>
    </form>
    </div>
</div>


<div class="linkst">
    <div class="inbox">
        <p class="pagelink"><?php echo $paging_links ?></p>
    </div>
</div>

<div id="users1" class="blocktable">
    <h2><span>Found</span></h2>
    <div class="box">
        <div class="inbox">
        <table cellspacing="0">
        <thead>
            <tr>
                <th width="30%" align="left" scope="col">Rubrik</th>
                <th width="20%" align="left" scope="col">Startad</th>
                <th width="20%" align="left" scope="col">Ort</th>
                <th width="20%" align="left" scope="col">Kategori</th>
                <th align="left" scope="col">Pris</th>
            </tr>
        </thead>
        <tbody>
<?php

// Grab the ads
$query = ('SELECT a.id, a.posted, a.header, d.description AS lan, a.ad_type, a.image_url, c.description AS category, a.price FROM '.$db->prefix.'ads AS a, district d, ad_categories c WHERE d.id = a.lan AND c.id = a.category AND a.active = 1 '.$search_string.' ORDER BY a.posted DESC LIMIT '.$start_from.', '.$posts_per_page.'');
//print $query;
$result = $db->query($query) or die (mysql_error());
if ($db->num_rows($result))
{
    while ($ad_data = $db->fetch_assoc($result))
    {
        $ad_title_field = get_title($ad_data);


$today = date("Y-m-d");
$date = date("Y-m-d", $ad_data['posted']);
$s_time = "";
if ($date == $today){
    $s_time = strftime("Idag %H:%M", $ad_data['posted']);
}
else if (strtotime($date) == strtotime("$today -1 day")){
    $s_time = strftime("Igår %H:%M", $ad_data['posted']);
}
else{
    $s_time = strftime("%e %b %H:%M", $ad_data['posted']);
}

?>
                <tr>
                    <td width="30%" align="left" scope="col"><img src="<?php echo $ad_data['image_url'] != "" ? "img/icon_image.gif" : "img/icon_image_none.gif";?>" align="middle" /> <a href="ad.php?id=<?php echo $ad_data['id']; ?>"><?php echo "<strong>"; echo $ad_data['ad_type'] == 1 ? "K: " : "S: "; echo "</strong>"; echo $ad_data['header'] ?></a></th>
                    <td width="20%" align="left" scope="col">
<?php echo $s_time; ?></th>
                    <td width="20%" align="left" scope="col"><?php echo $ad_data['lan']  ?></th>
                    <td width="20%" align="left" scope="col"><?php echo $ad_data['category'] ?></th>
                    <td align="left" scope="col"><?php echo $ad_data['price'] ?>:-</th>
                </tr>
<?php

    }
}
else
    echo "\t\t\t".'<tr>'."\n\t\t\t\t\t".'<td class="tcl" colspan="5">No match</td></tr>'."\n";

?>
            </tbody>
            </table>
        </div>
    </div>
</div>

<div class="linksb">
    <div class="inbox">
        <p class="pagelink"><?php echo $paging_links ?></p>
    </div>
</div>
<?php

require PUN_ROOT.'footer.php';

 

If I for example write ' or '1'='1 I get error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'or '1'='1' IN BOOLEAN MODE )' at line 3

[thcx]

Use mysql_real_escape_string() for the search string and intval() for the numeric data (districts, what page they're on etc).

 

(Är du svensk?)

 

[aleborg]

Japp, Kan du visa mer exakt hur man gör detta?

[thcx]

Yep, you put this stuff right before the code that you pasted:

 

$w = mysql_real_escape_string($w);
$cat = intval($cat);
$district = intval($district);
$type = intval($type);

Then you find this line:

 

$p = (!isset($_GET['p']) || $_GET['p'] <= 1 || $_GET['p'] > $num_pages) ? 1 : $_GET['p'];

..and insert one line afterwards:

 

$p = intval($p);