ID: 44707
Updated by: [EMAIL PROTECTED]
Reported By: regli at yahoo dot com
-Status: Open
+Status: Bogus
Bug Type: PDO related
Operating System: Windows Vista
PHP Version: 5.2.5
New Comment:
What uwe said..
Previous Comments:
------------------------------------------------------------------------
[2008-07-03 19:55:43] [EMAIL PROTECTED]
An *important* addition to
--- Bug 2 --- PDOs SQL parser :
It is your task to teach the SQL parser that a value shall not be
interpreted as a string. Make use of the PDO::PARAM_<type> constants!
PDO can't know what you intend to do. PDO will always fall back to the
safe default of strings and quote even integers unless you tell PDO not
to do so!
------------------------------------------------------------------------
[2008-07-03 15:08:23] [EMAIL PROTECTED]
What a lovely variation of the old theme "The PDO SQL parser breaks
your SQL". This is not a MySQL bug. This is not a driver bug.
You have hit two PDO bugs!
However, you bug report is close to Bogus. Please pay more attention on
your bug report. Well prepared bugs, including reproducible code
examples, have a higher chance to be taken care of then bug reports like
yours.
--- Bug 1 -- Type conversion
Your example is bogus. PDO does not promise not to change the variable
type of a bound variable. PHP is dynamic and loosely typed. Its very
"PHPish" from PDO not to take too much care about it.
However, if you care about types you should read the manually carefully
and try all options on types which the API provides:
bool PDOStatement::bindParam ( mixed $parameter , mixed &$variable
[, int $data_type [, int $length [, mixed $driver_options ]]] )
You can give PDO a type hint when binding parameters. Make use of it:
$id = 1;
$mybool = false;
$stmt = $db->prepare('INSERT INTO test(id, mybool) VALUES (?, ?)');
$stmt->bindParam(1, $id);
$stmt->bindParam(2, $mybool, PDO::PARAM_BOOL);
If you do so, your variable will at least not change its type before
you call execute(). If you omit this additional parameter your variable
will be converted into a string by the call to bindParam().
However, at lastest the type of your variable $mybool will change from
boolean to string due to a bug in ext/pdo/pdo_sql_parser.c upon
execution of the statement. The file pdo_sql_parser.c comes from the PDO
core module. It has nothing to do with MySQL.
The "bug" (PDO does not promise not to change the type of your bound
parameters!) is around line 480. PDO recognizes that it got a boolean
value and converts it into an PHP integer (long). It does this within a
switch-case construct and falls through to the default handling. The
default handling will convert the variable again from integer to string.
You can check this yourself and do a quick hack of the parser using a
"convert_back_to_boolean" flag.
And why do you try to bind a PHP boolean variable to an integer SQL
column? Wouldn't it be more appropriate to use a PHP integer variable
for a SQL integer column? PDO has not automatic PHP to SQL type mapping.
--- Bug 2 --- PDOs SQL parser
PDOs SQL parser is very, very basic. It takes your INSERT statement and
replaces all placeholders with string values!
For example, INSERT INTO test(id, mybool) VALUES (?, ?) will be
converted by the INSERT INTO test(id, mybool) VALUES ('<somevalue>',
'<somevalue>'). Of course MySQL will bail at you, if you send such a SQL
statement!
Search the bug archive for more examples of PDO generating invalid
SQL.
--- What can you do? ---
- use ext/mysqli and be happy
(nothing)
(nothing)
(nothing)
- fix PDO
- use MySQLs native prepared statements and keep your fingers off from
named parameters and the PDO SQL parser
------------------------------------------------------------------------
[2008-04-12 01:30:02] regli at yahoo dot com
Description:
------------
In a prepare/bindParam scenario where the variable is already preset
before the bindParam has been executed, a boolean field gets reset by
the driver. In the case of TRUE it resets it to "1" and in the case of
FALSE it rests it to ''.
Obviously this is highly undesirable as I get the input parameters in a
function and then simply execute the prepare/bindParam. However, as the
database field is defined as tinyint, I naturally get a PDOException. I
tested the same code with IBM DB2 and this issue does not occur and it
works perfectly (obviously the field definition is smallint).
If I reset the field after bindParam to FALSE and then again execute
the statement, it works perfectly as it should. The problem seems
clearly to be in bindParam.
I tested this using the latest php_pdo_mysql.dll php-5.2.1(5_2) from
2008-01-11.
Reproduce code:
---------------
$stmt = db::getInstance()->prepare("INSERT INTO session (sessionName,
privateSessionBool);
$stmt->bindParam(':sessionName', $sessionName);
$privateSessionBool) = false;
echo $privateSessionBool;
// here is where the problem occurs.
$stmt->bindParam(':privateSessionBool', $privateSessionBool);
echo $privateSessionBool;
Expected result:
----------------
The second bindParam statement should NOT change the field value of
privateSessionBool to ''. In fact bindParam should NEVER alter a fields
value. Note that it even changes TRUE booleans to '1' instead of
leaving them alone. I did not observe this behavior in other field
types.
Actual result:
--------------
The first echo results in "false" whereas the second results in ''.
Any insert then results in:
SQLSTATE[HY000]: General error: 1366 Incorrect integer value: '' for
column 'privateSessionBool' at row 1
------------------------------------------------------------------------
--
Edit this bug report at http://bugs.php.net/?id=44707&edit=1
