I am a bit confuse as to why we need to specify the type of data that we pass in the bindParam() function in PDO in Php. For example this query:
$calories = 150;
$colour = 'red';
$sth = $dbh->prepare('SELECT name, colour, calories
FROM fruit
WHERE calories < ? AND colour = ?');
$sth->bindParam(1, $calories, PDO::PARAM_INT);
$sth->bindParam(2, $colour, PDO::PARAM_STR, 12);
$sth->execute();
Is there a security risk if I do not specify the 3rd parameter. I mean if I just do in the bindParam():
$sth->bindParam(1, $calories);
$sth->bindParam(2, $colour);
Using bindParam() with types could be considered safer, because it allows for stricter verification, further preventing SQL injections. However, I wouldn't say there is a real security risk involved if you don't do it like that, as it is more the fact that you do a prepared statement that protects from SQL injections than type verification. A simpler way to achieve this is by simply passing an array to the execute() function instead of using bindParam(), like this:
$calories = 150;
$colour = 'red';
$sth = $dbh->prepare('SELECT name, colour, calories
FROM fruit
WHERE calories < :calories AND colour = :colour');
$sth->execute(array(
'calories' => $calories,
'colour' => $colour
));
You're not obligated to use a dictionary, you can also do it just like you did with questionmarks and then put it in the same order in the array. However, even if this works perfectly, I'd recommend making a habit of using the first one, since this method is a mess once you reach a certain number of parameters. For the sake of being complete, here's what it looks like:
$calories = 150;
$colour = 'red';
$sth = $dbh->prepare('SELECT name, colour, calories
FROM fruit
WHERE calories < ? AND colour = ?');
$sth->execute(array($calories, $colour));
If you do not specify a type, the parameter will be bound as a string by default. You may then end up with a query equivalent to:
SELECT foo FROM bar WHERE baz = '42'
This may or may not be a problem. Just like PHP and other programming languages, databases have types and rules for casting between types. MySQL will typically implicitly cast numeric strings to numbers as needed. Some databases are stricter than others, requiring the right type for certain operations. In most cases, it makes little difference. But if you really need to pass 42 as 42 and not '42', you need to explicitly bind it as an INT.
I know this is an old question, but I wanted to do a little bit of digging in since I always forget the inner workings of the data type parameter when binding values/parameters. Also I have significantly more experience with MySQL, so this answer will be more MySQL-related.
MySQL automatically converts numbers to strings as necessary, and vice versa, so if you do:
SELECT * FROM tablename WHERE columnname = 42
or
SELECT * FROM tablename WHERE columnname = '42'
MySQL knows what column type columnname should be and will automatically type cast if the wrong type is provided as the value.
Now, looking at the source code for PDOStatement:
if (PDO_PARAM_TYPE(param->param_type) == PDO_PARAM_STR && param->max_value_len <= 0 && !Z_ISNULL_P(parameter)) {
if (Z_TYPE_P(parameter) == IS_DOUBLE) {
char *p;
int len = zend_spprintf_unchecked(&p, 0, "%.*H", (int) EG(precision), Z_DVAL_P(parameter));
ZVAL_STRINGL(parameter, p, len);
efree(p);
} else {
convert_to_string(parameter);
}
} else if (PDO_PARAM_TYPE(param->param_type) == PDO_PARAM_INT && (Z_TYPE_P(parameter) == IS_FALSE || Z_TYPE_P(parameter) == IS_TRUE)) {
convert_to_long(parameter);
} else if (PDO_PARAM_TYPE(param->param_type) == PDO_PARAM_BOOL && Z_TYPE_P(parameter) == IS_LONG) {
convert_to_boolean(parameter);
}
You'll notice from the above code that when the third parameter to the bind statements is either omitted or PDO_PARAM_STR (default value), PHP will type cast the value to a string. When it is provided as PDO_PARAM_INT, unlike what you may actually expect, it does not get type cast to an integer, but instead PHP casts it to a long! So if you do:
$stmt->bindValue("integer_column", "41.9", PDO_PARAM_INT);
The database will actually receive the long value 41.9 to be used in the query, which is then rounded (at least by MySQL) to the closest integer, so the final value used in the above query for a column of type INTEGER will be 42.
mysql> SELECT CAST(41.9 AS UNSIGNED);
+------------------------+
| CAST(41.9 AS UNSIGNED) |
+------------------------+
| 42 |
+------------------------+
The same is true if you have a non-numeric string, such as "41.9asdf":
$sth->bindValue("integer_column_value", "41.9asdf", PDO_PARAM_INT);
It will first be type cast to a long by PHP (41.9), and then that value will be rounded to an integer by MySQL (42).
Like I mentioned earlier, MySQL automatically converts strings to numbers, so if you give MySQL the string "123", it will have no problems converting this to an integer, or any other numeric type. However, keep in mind that when MySQL converts from a string to an integer, it truncates instead of rounds, so this could be an actual difference between providing the type when binding.
mysql> SELECT CAST('123.6' AS UNSIGNED);
+---------------------------+
| CAST('123.6' AS UNSIGNED) |
+---------------------------+
| 123 |
+---------------------------+
mysql> SELECT CAST(123.6 AS UNSIGNED);
+-------------------------+
| CAST(123.6 AS UNSIGNED) |
+-------------------------+
| 124 |
+-------------------------+
So here are the values an integer column will contain in PHP for the following:
$stmt->bindValue("integer_column", "123.6", PDO_PARAM_INT); // 124
$stmt->bindValue("integer_column", "123.6"); // 123
$stmt->bindValue("integer_column", (int) "123.6"); // 123
$stmt->bindValue("integer_column", round("123.6")); // 124
Note, as you may notice from the above, PHP casts a string to integer differently than how MySQL does. MySQL rounds floats, but PHP will use the floor value:
var_dump((int) "123.6"); // int(123)
var_dump((int) 123.6); // int(123)
Now, to actually answer the security aspect of your question, there is no security benefit whatsoever in providing the third parameter to the prepared statements. Prepared statements are sufficient in and of themselves to mitigate SQL injection when done correctly (see this link for some obscure edge-cases).
