Using Placeholder or parameter markers for preventing sql injection in PHP

public static function getMembers($startRow,$numRows,$order){
$sql = “SELECT SQL_CALC_FOUND_ROWS * FROM “ . TBL_MEMBERS . “ ORDER BY $order LIMIT :startRow, :numRows”;
}

Here in the above statement SQL_CALC_FOUND_ROWS is a special MySQL keyword that computes the total number of rows that would be returned by query , assuming  the LIMIT clause wasn't applied. So if the query would return 20 records, but the LIMIT clause limits the returned rows five, SQL_CAL_FOUND_ROWS return a value of 20. This is useful because it enables you to display the records several pages, as you see in a moment.

:startRow and :numRows are called placeholders or parameter markers.
They serve two purposes.
First of all, they let you prepare — that is, get MySQL to parse — a query once, then run it multiple
times with different values. If you need to run the same query many times using different input
values -- when inserting many rows of data, for instance -- prepared statement can really speed up execution.
Secondly, they reduce the risk of so-called SQL injection attacks. For example, an alternative to using
placeholders might be to write:


$sql = “SELECT SQL_CALC_FOUND_ROWS * FROM “ . TBL_MEMBERS . “ ORDER BY $order LIMIT $startRow, $numRows”;


However, imagine that, due to insufficient checking of user input, a malicious user managed to set
$numRows to “1; DELETE FROM members”.

This would run the query as intended, but it would also run the second statement, which would delete all records from your members table!

When you use placeholders, you pass data to the query via PDO (as you see shortly), not directly into
your query string. This allows PDO to check the passed data to ensure that it only contains what it’s supposed to contain(integers in this case).


Next, the two :startRow and :numRow placeholders you created earlier are populated with the actual data from the $startRow and $numRow variables:

$st->bindValue( “:startRow”, $startRow, PDO::PARAM_INT );
$st->bindValue( “:numRows”, $numRows, PDO::PARAM_INT );

The PDOStatement::bindValue() method takes three arguments:

the name of the placeholder to bind,
the value to use instead of the placeholder,
and the data type of the value (PDO::PARAM_INT, or integer, in this case).

By specifying the data type, PDO can ensure that the correct type of data is passed to MySQL. In addition, PDO automatically escapes any quote marks and other special characters in the data.(Failing to escape special characters is another common cause of SQL injection vulnerabilities.)

Some other common data types that you can use include:

PDO::PARAM_BOOL — A Boolean data type
PDO::PARAM_NULL — The NULL data type
PDO::PARAM_STR — A string data type. (This is the default if you don’t specify a type.)
PDO::PARAM_LOB — A LOB data type, such as BLOB or LONGBLOB