jQuery Grid Plugin - jqGridForum

Topic RSS

Nav Bar Search Predicate Construction

31/12/2008
01:06

Tq46

New Member

Members

Forum Posts: 2

Member Since:
31/12/2008

Offline

First off, congratulations Tony, nice plug-in!

A few of your demo files (e.g., server.php) have warning comments regarding the way the SQL WHERE clause variable ($wh) is constructed - for demo purposes only, it says. Are you refering only to that, or all the way through the SWITCH statements?

Also, could you be more specific and cite what's wrong with it, or perhaps better, what would properly take its place?

Sorry, I should be more specific myself: I'm trying to get the Find Records to work from the Navigation bar. I got the impression from the documentation that the search was based on the subset of records that were displayed in the table, not a new query against my database.

Thanks

(Here's an excerpt from server.php):

// search options
// IMPORTANT NOTE!!!!!!!!!!!!!!!!!!!!!!!!!!!!
// this type of constructing is not recommendet
// it is only for demonstration
//!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
$wh = "";
$searchOn = Strip($_REQUEST['_search']);
if($searchOn=='true') {
    $fld = Strip($_REQUEST['searchField']);
    if( $fld=='id' || $fld =='invdate' || $fld=='name' || $fld=='amount' || $fld=='tax' || $fld=='total' || $fld=='note' ) {
        $fldata = Strip($_REQUEST['searchString']);
        $foper = Strip($_REQUEST['searchOper']);
        // costruct where
        $wh .= " AND ".$fld;
        switch ($foper) {
            case "bw":
                $fldata .= "%";
                $wh .= " LIKE '".$fldata."'";
                break;
            case "eq":
                if(is_numeric($fldata)) {
                    $wh .= " = ".$fldata;
                } else {
                    $wh .= " = '".$fldata."'";
                }
                break;
            case "ne":
                if(is_numeric($fldata)) {
                    $wh .= " <> ".$fldata;
                } else {
                    $wh .= " <> '".$fldata."'";
                }
                break;
            case "lt":
                if(is_numeric($fldata)) {
                    $wh .= " < ".$fldata;
                } else {
                    $wh .= " < '".$fldata."'";
                }
                break;
            case "le":
                if(is_numeric($fldata)) {
                    $wh .= " <= ".$fldata;
                } else {
                    $wh .= " <= '".$fldata."'";
                }
                break;
            case "gt":
                if(is_numeric($fldata)) {
                    $wh .= " > ".$fldata;
                } else {
                    $wh .= " > '".$fldata."'";
                }
                break;
            case "ge":
                if(is_numeric($fldata)) {
                    $wh .= " >= ".$fldata;
                } else {
                    $wh .= " >= '".$fldata."'";
                }
                break;
            case "ew":
                $wh .= " LIKE '%".$fldata."'";
                break;
            case "ew":
                $wh .= " LIKE '%".$fldata."%'";
                break;
            default :
                $wh = "";
        }
    }
}
//echo $fld." : ".$wh;
// connect to the database

05/01/2009
02:33

tony

Sofia, Bulgaria

Moderator

Members

Moderators

Forum Posts: 7721

Member Since:
30/10/2007

Offline

Hello,

The warrning is that the posted data must be checked deeper to prevent SQL injection. By exmple if you expect a integer data (when it is posted) it is a good idea to do: (integer)$fldata. More about SQL injection you can find on the web.

Yes the search is only by one field only. You can use session variable to to make it multiple.

Regards

Tony

For professional UI suites for Java Script and PHP visit us at our commercial products site - guriddo.net - by the very same guys that created jqGrid.

05/01/2009
17:31

Tq46

New Member

Members

Forum Posts: 2

Member Since:
31/12/2008

Offline

Thanks for the reply, Tony.

The grid is being presented as read-only, so no injection worries, but the user form that feeds it uses something like:

$query = sprintf ("
INSERT INTO `my_table` (`id`,`a`,`b`)
VALUES
('%d','%s','%s')",
$id,
mysql_real_escape_string($_POST['a'], $conn),
mysql_real_escape_string($_POST['b'], $conn)
);

Otherwise, this seems to work fine:

if ($_GET['_search'] == "true") {

$where_clause_1 = "";

$ss = $_GET['searchString'];

$eq = "= '".$ss."'";
$ne = "<> '".$ss."'";
$lt = "< '".$ss."'";
$le = "<= '".$ss."'";
$gt = "> '".$ss."'";
$ge = ">= '".$ss."'";
$bw = "LIKE '".$ss."%'";
$ew = "LIKE '%".$ss."'";
$cn = "LIKE '%".$ss."%'";
$ge = ">= ".$ss;

$search_ar = array(
'eq' => $eq, 'ne' => $ne, 'lt' => $lt, 'le' => $le, 'gt' => $gt,
'ge' => $ge, 'bw' => $bw, 'ew' => $ew, 'cn' => $cn
);

$where_clause_2 = "
WHERE `".$_GET['searchField']."` ".$search_ar[$_GET['searchOper']]."
";

} else {
$where_clause_1 = "";
$where_clause_2 = "";
}

-Gary (aka Tq46)

All RSS

Forum Timezone: Europe/Sofia

Most Users Ever Online: 715

Currently Online:
51 Guest(s)