PDO Prepared Param - Different Results

[Anonymous]

Below is a test example of the problem I was able to reproduce using prepared statements for a MySQL query. When using a static query it works fine but as soon as I attempt a prepared param the results change. It has something to do with the column schema type I know. See the 2nd code section for more details.

My data in database/table test.test:
clientname,age
"John Doe",55
"Jane Doe",32
"Bob Smith",21
"Rick James",39

The code below only returns John Doe 55, Jane Doe 32, and Rick James 39. It does not return Bob Smith 21 even though that is > 3???

$connector = new PDO('mysql:host=localhost', 'user', 'password');
$comm = $connector -> prepare("SELECT * FROM test.test WHERE age > :age;");
$age = 3;
$comm -> bindParam(':age', $age);
$comm -> execute();
$dataArray = $comm -> fetchAll(PDO::FETCH_ASSOC);
echo "<pre>";
print_r($dataArray);
echo "</pre>";
$comm = null;
$connector = null;

Result = Array(
[0] => Array([pkey] => 1 [clientname] => John Doe [age] => 55)
[1] => Array([pkey] => 2 [clientname] => Jane Doe [age] => 32)
[2] => Array([pkey] => 4 [clientname] => Rick James [age] => 39)
)

Now the column type for age is VARCHAR and when changing it to INT it does resolve the problem and that part makes sense. What does not make sense is if I type the query in in either of the following ways it works with all 4 names returned. What is different about prepared params that cause it to act different with trying to calculate a number using a VARCHAR type column?

$comm = $connector -> prepare("SELECT * FROM test.test WHERE age > 3;");
$comm = $connector -> prepare("SELECT * FROM test.test WHERE age > '3';");

Results = Array(
[0] => Array([pkey] => 1 [clientname] => John Doe [age] => 55)
[1] => Array([pkey] => 2 [clientname] => Jane Doe [age] => 32)
[2] => Array([pkey] => 3 [clientname] => Bob Smith [age] => 21)
[3] => Array([pkey] => 4 [clientname] => Rick James [age] => 39)
)

This also resolves the problem.

$comm -> bindParam(':age', $age, PDO::PARAM_INT);

So I know the solutions do make sense but I like to learn as much as I can and was wondering if anyone knows some details behind the curtain on how prepared params work and would cause it to just not return one row out of four? I would think if it is just a schema problem or cast value type it would not return any rows but it returns 3/4 rows when the problem occurs.

Thanks

 

[Anonymous]

If you use a string, ASCII 3 is larger than ASCII 2

Or '3' is larger than '234567799'

You're comparing a string (text), not numbers.

http://www.asciitable.com/

 

[Anonymous]

Thanks. I get the string vs int thing when doing a WHERE but if I were to use it set as 3' or just 3 in the static query it works differently. My question is why does a prepared bind param change the behavior compared to a static query? This is not a good real world example just found it weird that is behavior would be different.

 

[Anonymous]

This may explain it

http://php.net/manual/en/language.types.type-juggling.php

 

[Anonymous]

This may explain it

http://php.net/manual/en/language.types.type-juggling.php

Thanks that is interesting. When I type '3' into the query I was expecting it to behave the same and only return 3 of the 4 rows since you are asking if age > '3' as a string but apparently MySQL can pretend that '3' is really an integer and the result returns all 4 rows. When using a prepared param though it must handle it a little differently and does not give MySQL the same opportunity as a static query handled the database call and is much more strict about data types. Thanks for the responses as I love this type of dialog. I get bored and curious very easy, and this will not be the last time.

 

[Anonymous]

IMHO this is one of the problems of 'dynamic' typed languages - their fine until you reach the boundaries where types are very important. Some of the automatic conversions make it worse by making your mind up about what you really meant it to do.
If your value is a field coming in from the user, then it will be a string - you can convert it using...

$int = (int)$num;

(From http://stackoverflow.com/questions/8529656/how-do-i-convert-a-string-to-a-number-in-php) which means if you use this new value - your bind variables will then be an int as well.
Main thing is to stick with bind variables - just when you test your application, make sure that you check some of these sorts of issues don't come back to bite you after the client has the system up and running!