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?