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

Why use LIKE instate of using loop in PHP ?

We know LIMIT counts from the first row of the results. However, by including two numbers after the
LIMIT keyword, separated by a comma, you can specify both the row from which to start returning
results, as well as the number of results to return:

mysql> SELECT id, username FROM members LIMIT 1, 2;

| id | username |
-------------------
 2    ron
 3    anjan

Notice that the start row counts from zero, so 1 is actually the second row (ron).

You might be wondering what the point of LIMIT is, because you can always just loop through the result set in PHP to extract only the rows you’re interested in. The main reason to use LIMIT is that it reduces the amount of data that has to flow between MySQL and your PHP script.

Imagine that you want to retrieve the first 100 rows of a million-row table of users. If you use LIMIT
100, only 100 rows are sent to your PHP script. However, if you don’t use a LIMIT clause (and your
query also contains no WHERE clause), all 1,000,000 rows of data will be sent to your PHP script, where they will need to be stored inside a PDOStatement object until you loop through them to extract the first 100. Storing the details of a million users in your script will quickly bring the script to a halt, due to the large amount of memory required to do so.
LIMIT is particularly useful when you’re building a paged search function in your PHP application. For example, if the user requests the second page of search results, and you display 10 results per page, you can use SELECT ... LIMIT 10, 10 to retrieve the second page of results.

Some Important Attributes in Mysql

There have some most important attributes in mysql which can solve/get the solution of our faced scenery. So, we should keep in mind that those we can do by using default attribute of mysql.


BINARY Attribute

All character data types have a collation that is used to determine how characters in the field are
compared. By default, a character field’s collation is case insensitive. This means that, when you sort the column alphabetically (which you learn to do shortly), “a” comes before both “b” and “B”. It also means that queries looking for the text “banana” will match the field values “banana” and “Banana”.

However, by adding the BINARY attribute after the data type definition, you switch the field to a binary collation, which is case sensitive; when sorting, “a” comes before “b”, but “B” comes before “a” (because, generally speaking, uppercase letters come before lowercase letters in a character set). Furthermore, this means that matches are case sensitive too; “banana” will only match “banana”, not “Banana”. In this case, you created the username field of the members table with the BINARY attribute, making it case sensitive:

username VARCHAR(30) BINARY NOT NULL UNIQUE,

This ensures that there’s no ambiguity over the case of the letters in each user ’s username; for example,
“shaon” is a different username than “Shaon”. This is important because many people choose usernames where the case of the username’s characters is significant to them. If they created their account with a username of “shaon”, and later found out they could also login using “Shaon”, they might wonder if they were working with one account or two!

ENUM Data Type

Enumeration columns is a  type of string column where only predefined string values are allowed in the field .
Suppose we want user can input only 'm' or 'f' for their gender then we have to use:

gender ENUM ('m', 'f'),

or their favorite subject to read:

subject ENUM( 'JAVA','PHP','CEH'),

TIMESTAMP Data Type

Another most important data type is TIMESTAMP data type which can get the result exactly what you need. It is a bit different from the other date/time types in that it can automatically record the time that certain event occur. For example, when you add a new row to a table containing a TIMESTAMP column, the field stores the time that the insertion took place. Similarly, whenever a row is updated, the TIMESTAMP field is automatically updated with the time of the update.

The other point to remember about TIMESTAMP fields is that they store the date and time in the UTC
(Universal Coordinated Time) time zone, which is essentially the same as the GMT time zone. This
probably won’t affect you much, because MySQL automatically converts TIMESTAMP values between UTC and your server ’s time zone as required. However, bear in mind that if you store a TIMESTAMP value in a table, and you later change the server ’s time zone, the value that you get back from the TIMESTAMP field will be different.

A TIMESTAMP field is great for tracking things such as when a record was last created or updated,
because you don’t have to worry about setting or changing its value; it happens automatically. In this
example, you created a TIMESTAMP field in the accessLog table to track when the last access was made:

lastAccess TIMESTAMP NOT NULL,