Php: How To Prevent Sql Injection From $_post

i have some php script and i think this have a lot of mistake. because of my limited knowledge in concatenation and SQL injection. At 1st time i'm not have any trouble because this

Solution 1:

Looks as if it could run, so if it fails please describe how.

There is, however, one immediately visible mistake in it: In the lines

$sQuery = "SELECT COUNT(*) FROM (SELECT LINE_NAME, MODEL_ONLY, VER_ONLY, PROD_NO,
                                         LOT_SIZE, START_SERIAL, SERIAL_NO_LOW, SERIAL_NO_UP, PROD_DATE 
                                  FROM DOC_TO'.$sWhere.$sOrder.$sLimit.')";

you are starting a string with double quotes and you try to interrupt the string with single quotes, which will not work. So the code must be:

$sQuery = "SELECT COUNT(*) FROM (SELECT LINE_NAME, MODEL_ONLY, VER_ONLY, PROD_NO,
                                         LOT_SIZE, START_SERIAL, SERIAL_NO_LOW, SERIAL_NO_UP, PROD_DATE 
                                  FROM DOC_TO".$sWhere.$sOrder.$sLimit.")";

Depending on your environment in addition there is an important security flaw in your code: For example in the line

$sLimit = " FIRST ".$_POST['iDisplayStart']." SKIP ".$_POST['iDisplayLength'];

you are using $_POST to put it directly into an SQL query, which opens a so-called SQL injection security hole.

Solution 2:

i have found the answer from AndreKR's guidance:

$sLimit = "";
        if ( isset( $_POST['iDisplayStart'] ) )
        {
                settype($iDisplayStart,'integer');
                $iDisplayStart = $_POST['iDisplayStart'];
                $iDisplayLength = $_POST['iDisplayLength'];
                $sLimit = sprintf(" FIRST ".$iDisplayStart." SKIP ".$iDisplayLength);
        }

and change all $_POST:

'%".$_POST['sSearch_0']."%'into''%".$_POST['sSearch_0']."%''//this to prevent injection For databases Like Oracle, DB2, MS SQL, Firebird

and change :

$sQuery = "SELECT COUNT(*) FROM(......)
into
$sQuery = "SELECT COUNT(*) AS DCOUNT FROM DOC_TO".$sWhere.$sLimit.";"; //delete $sOrder$aResultFilterTotal = ibase_fetch_assoc($rResultFilterTotal);
$iFilteredTotal = $aResultFilterTotal['DCOUNT'];  //change from ......[0]

and add at the last php page:

ibase_free_result($aResultTotal);
?>