2

I'm using JQuery Datatables with PHP server-side script which fetches data from MySQL. I took PHP script example from Datatables site and changed it a bit in order to change it from old mysql to mysqli. The script accepts several papameters such as Paging, Ordering and Filtering. There is no concern about the first two as those are always digits and could be sinitized with intval function before passing it to a MySQL request. But the Filtering supposed to have text value and I want to use mysqli prepared statements to sanitize it. Here is the code:

require_once "mysqli_conection.php"

$aColumns = array( 'engine', 'browser', 'platform', 'version', 'grade' );

/*
 * Paging
 */
$sLimit = "";
if ( isset( $_GET['iDisplayStart'] ) && $_GET['iDisplayLength'] != '-1' )
{
    $sLimit = "LIMIT ".intval( $_GET['iDisplayStart'] ).", ".
        intval( $_GET['iDisplayLength'] );
}

/*
 * Ordering
 */
$sOrder = "";
if ( isset( $_GET['iSortCol_0'] ) )
{
    $sOrder = "ORDER BY  ";
    for ( $i=0 ; $i<intval( $_GET['iSortingCols'] ) ; $i++ )
    {
        if ( $_GET[ 'bSortable_'.intval($_GET['iSortCol_'.$i]) ] == "true" )
        {
            $sOrder .= $aColumns[ intval( $_GET['iSortCol_'.$i] ) ]."
                ".($_GET['sSortDir_'.$i]==='asc' ? 'asc' : 'desc') .", ";
        }
    }

    $sOrder = substr_replace( $sOrder, "", -2 );
    if ( $sOrder == "ORDER BY" )
    {
        $sOrder = "";
    }
}


/*
 * Filtering
 */
$sWhere = "";
if ( isset($_GET['sSearch']) && $_GET['sSearch'] != "" )
{
    $sWhere = "WHERE (";
    for ( $i=0 ; $i<count($aColumns) ; $i++ )
    {
        if ( isset($_GET['bSearchable_'.$i]) && $_GET['bSearchable_'.$i] == "true" )
        {
            $sWhere .= $aColumns[$i]." LIKE '%".mysql_real_escape_string( $_GET['sSearch'] )."%' OR ";
        }
    }
    $sWhere = substr_replace( $sWhere, "", -3 );
    $sWhere .= ')';
}

/*
 * SQL queries
 */
$sQuery = "
    SELECT SQL_CALC_FOUND_ROWS ".str_replace(" , ", " ", implode(", ", $aColumns))."
    FROM   $sTable
    $sWhere
    $sOrder
    $sLimit
";
$rResult = $mysqli->query( $sQuery, $gaSql['link'] );

/* Data set length after filtering */
$sQuery = "
    SELECT FOUND_ROWS()
";
$rResultFilterTotal = $mysqli->query( $sQuery, $gaSql['link'] );
$aResultFilterTotal = $rResultFilterTotal->fetch_array(MYSQLI_NUM);
$iFilteredTotal = $aResultFilterTotal[0];

/* Total data set length */
$sQuery = "
    SELECT COUNT(".$sIndexColumn.")
    FROM   $sTable
";
$rResultTotal = $mysqli->query( $sQuery, $gaSql['link'] );
$aResultTotal = $rResultTotal->fetch_array(MYSQLI_NUM);
$iTotal = $aResultTotal[0];


/*
 * Output
 */
$output = array(
    "sEcho" => intval($_GET['sEcho']),
    "iTotalRecords" => $iTotal,
    "iTotalDisplayRecords" => $iFilteredTotal,
    "aaData" => array()
);

while ( $aRow = $rResult->fetch_array(MYSQLI_NUM) )
{
    $row = array();
    for ( $i=0 ; $i<count($aColumns) ; $i++ )
    {
        if ( $aColumns[$i] != ' ' )
        {
            $row[] = $aRow[ $aColumns[$i] ];
        }
    }
    $output['aaData'][] = $row;
}

echo json_encode( $output );

The problem is that Filtering is optional parameter and I can't figure out how to do prepared statement on that. If that request would be always present I would do:

$stmt = $mysqli->prepare( $dataQuery );
$stmt->bind_param("sssss", "%".$_GET['sSearch']."%", "%".$_GET['sSearch']."%", "%".$_GET['sSearch']."%", "%".$_GET['sSearch']."%", "%".$_GET['sSearch']."%")
$stmt->execute();
$rResult = $stmt->get_result();

I would put it just before the /* Output */ section. Should I just switch between prepared or usual statements depending on sSearch request set or not like:

if ( isset($_GET['sSearch']) && $_GET['sSearch'] != "" ) {
    $stmt = $mysqli->prepare( $dataQuery );
    $stmt->bind_param("%".$_GET['sSearch']."%", "%".$_GET['sSearch']."%", "%".$_GET['sSearch']."%", "%".$_GET['sSearch']."%", "%".$_GET['sSearch']."%")
    $stmt->execute();
    $rResult = $stmt->get_result();
} else {
    $rResult = $mysqli->query($dataQuery);
}

 /*
 * Output
 */
//etc.

Would that be safe?

kurast
  • 1,660
  • 3
  • 17
  • 38
user164863
  • 580
  • 1
  • 12
  • 29
  • I have read that question and I understand the theory, my question is about how to apply this here. – user164863 Nov 12 '13 at 14:29
  • 2
    This is a very different question. He is asking how he can parameterize queries with a variable number of parameters. With PDO this would be very simple, but that I know of it is not as simple with mysqli because of how `bind_param` works – Explosion Pills Nov 12 '13 at 14:33
  • Ok, I could switch to PDO... I guess. What would be the difference? How would it look like? – user164863 Nov 12 '13 at 14:40
  • 'sanitize' -- prepared statements does not do that. – sectus Nov 13 '13 at 00:24

0 Answers0