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

Question

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

PLPeeters · Accepted Answer · 2013-06-02T08:53:07.263

3

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

edited Jun 02 '13 at 08:53

answered Jun 02 '13 at 08:41

PLPeeters

1,009
12
26

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? – Sameer Zahid Jun 02 '13 at 08:46
[Prepared statements](http://en.wikipedia.org/wiki/Prepared_statement) 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. – PLPeeters Jun 02 '13 at 08:49
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? – Sameer Zahid Jun 02 '13 at 08:55
1

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 :) – PLPeeters Jun 02 '13 at 09:03
Thank you, I ran some tests too locally and yes the injection doesn't work. Thank you! – Sameer Zahid Jun 02 '13 at 09:43
You're welcome. Don't forget to validate my answer if it indeed solved your question :) – PLPeeters Jun 02 '13 at 10:59

score 2 · Answer 2 · answered Jun 02 '13 at 09:12

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.

Mike · Answer 3 · 2018-06-03T00:31:19.200

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

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

3 Answers3

Linked

Related