Why do we need to specify the parameter type in bindParam()?

pdo::param_str
pdo bindvalue
bindparam not working
bindparam vs bindvalue
pdo param float
bind param php mysqli
pdo execute
bind param in php w3schools

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));

PDOStatement::bindParam - Manual, Length of the data type. To indicate that a parameter is an OUT parameter from a stored procedure, you must explicitly set the length. driver_options  When binding an output parameter to a bigint type, if the value may end up outside the range of an integer, using PDO::PARAM_INT with PDO::SQLSRV_PARAM_OUT_DEFAULT_SIZE may result in a "value out of range" exception. Therefore, use the default PDO::PARAM_STR instead and provide the size of the resulting string, which is at most 21.

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.

PDOStatement::bindValue - Manual, PDOStatement::bindValue — Binds a value to a parameter where sender=:me or recipient=:me"), you'll have to give them different names otherwise your query will return You can specify the type of the value in advance with $typeArray. BINDPARAM binds statement parameters to program variables. On EXECUTE, SQL iPlug will take the value of the program variables, to set the values for the parameters on Input or Input/Output. It will change the value of the program variables bound to parameter on Output or Input/Output.

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).

PDO Prepared Statement - PHP, Is this a secure way to pass the parameters into the prepared statement or does this method the PDO data type) // add additional parameters if needed // $stmt-​>bindValue(2, $param2, If one of the values is an integer for example do i need to specify that for PDO? Did you check the return result of $stmt->bindParam()?​. Now the bindParam method is a little different. It is a boolean it will return TRUE is the parameter given matches and is bound to the variable. Basically if it is the wrong variable type it will return False.

MySQL Stored Procedure Programming: Building High-Performance Web , bindParam() requires that you specify the data type of re-issue the execute( ) call: we do not have to call bindParam( ) whenever the parameter values change. The bindParam binds a parameter exclusively to a specified variable name which is bound as a reference while the bindValue binds a value which could be a variable, an integer or string to a parameter. Let’s see code examples to make the difference clearer. Both bind a variable to a placeholder’s parameter.

MySQL Cookbook, One difference between JDBC and the other APIs is that you don't specify a calls, a helper function bindParam() can be defined that takes a Statement object, third argument can be of different types, so we need one function for each type. Bind variables for the parameter markers in the SQL statement that was passed to mysqli_prepare. If data size of a variable exceeds max. allowed packet size (max_allowed_packet), you have to specify b in types and use mysqli_stmt_send_long_data to send the data in packets.

Better PHP Development, The way to do this is - you guessed it - to construct the string yourself. PDO, but it's always better to specify the type of every parameter you bind. You have probably noticed that we have specified the first parameter to bindvalue () as 1. The data type of a parameter determines the type and range of values that are accepted for the parameter when the procedure is called. For example, if you define a parameter with a tinyint data type, only numeric values ranging from 0 to 255 are accepted when passed into that parameter.

Comments
  • In the old mysql_x functions, if we used $id without doing (int)$id; and even if we used mysql_real_escape_string($id) we can still get SQL injection. But is it same in PDO?
  • Prepared statements help to avoid MySQL injections by preparing your query server side and then binding your parameters to it. Your way of doing it wasn't wrong, I was just showing you a simpler way to do it, at least in my opinion.
  • I understand both ways are correct. What I want to know is that for example if an attacker supplies the value of id like: $_POST['id']='1 or 3=3'. This will be true always, but if we do this: $id=(int)$id; The above attack cannot happen. So what I want to know is that in PDO can I just put $_POST['id'] in the bindParam() function? without specifying that that 'id' is an interger?
  • As I said above, it's the whole point of a prepared statement that avoids SQL injection. The way you bind your parameters to your query doesn't really matter. Just to be sure, I tried to inject my login form on my website, which I modified to use bindParam() without specifying the type. It didn't work, so you're safe even if you're not specifying the type :)
  • Thank you, I ran some tests too locally and yes the injection doesn't work. Thank you!