PHP分页安全

Question

I have simple jTable jQuery webpage. Since I didn't recieve much response in my previous topic: Safe MySQL password on shared hosting, I've started to research on my own. I'm using PHP based script for MySQL connection and for Pagination. From what I've researched, queries with no input data from user can't be harmed by hacker unless the PHP file is directly accessed and/or replaced. Therefore I decided to put it above my web root. I've added pagination feature for jTable, which uses SQL query with some input data from JS script. For heaven's sake I decided to protect this query from any malicious SQL Injections.

The query:

"SELECT * FROM people LIMIT " . $_GET["jtStartIndex"] . "," . $_GET["jtPageSize"] . ";"

What I did is I've added casts to int, for ex:

"SELECT * FROM people LIMIT " . (int)$_GET["jtStartIndex"] . "," . (int)$_GET["jtPageSize"] . ";"

Is it safe enough? As far as I remember, any string that will go there will be parsed to 0 by (int) cast.

Answer 1

Some people think that prepared statement is the only way to be safe about sql injection, it is not true, doing a manual filter is also effective (but verbose).

In this case is fine the solution but it could be wrapper (for example for capturing errors)

<?php
// this function is not needing unless it is used recurrently.
function safe_get_int($value) { 
     return intval(@$_GET[$value]); 
}
$init= safe_get_int("jtStartIndex") ;
$page= safe_get_int("jtPageSize") ;
if (($init>0 && $init<=9999) && ($page>0 && $page<100)) {
   $sql = "SELECT * FROM people LIMIT {$init},{$page};";
} else {
   $sql = "SELECT * FROM people LIMIT 0,20;"; // standard value
}
?>

results:

entering a number = return the number.

entering a text = return zero.

entering a special character = return zero.

missing the value = return zero. (it is why i added the @).

entering a negative = it is considered as a zero value.

(added), entering a big number is also restricted. For example, a big page size could crash some server.