28

I'm using a bit(1) field to store boolean values and writing into the table using PDO prepared statements.

This is the test table:

CREATE TABLE IF NOT EXISTS `test` ( `SomeText` varchar(255) NOT NULL, `TestBool` bit(1) NOT NULL DEFAULT b'0' ) ENGINE=MEMORY DEFAULT CHARSET=latin1;

This is the test code:

$pdo = new PDO("connection string etc") ; $statement = $pdo->prepare('INSERT INTO `test` (SomeText,TestBool) VALUES (?,?)') ; $statement->execute(array("TEST",0)) ;

Running that code gives me a row with value 1 under TestBool. And the same thing using bindValue() and bindParm(). I also tried named placeholders (instead of ?) with the same result.

Then I tried:

$statement = $pdo->prepare('INSERT INTO `test` (SomeText,TestBool) VALUES ("TEST",0)') ; $statement->execute() ;

Which worked properly (TestBool has value 0). Punching in the SQL directly into MySQL also works.

Note that inserting 1 always works.

So why would placeholders fail to insert the value 0? (and how do I actually do it?)

Improve this question
3
  • 1
    You are already using PDO, that's good. Why not take advantage of the named placeholders feature of PDO? See a tutorial: phpeveryday.com/articles/… Commented May 10, 2012 at 19:00
  • For the purpose of this question, I tried it and it makes no difference. For general inquiry, is there any advantage to using it other then convenience? (This is part of a DAL so it'll get generated anyway) Commented May 10, 2012 at 19:24
  • Yes, you don't need to remember the order of your variables. You increase your code abstraction. Commented May 10, 2012 at 19:26

6 Answers 6

Reset to default
39

BIT column is a binary type in mysql (though it's documented as numeric type - that's not precisely true) and I advise to avoid it due to problems with client libraries (which PDO issue proves). You will spare yourself a lot of trouble if you modify type of column to TINYINT(1)

TINYINT(1) will of course consume full byte of storage for every row, but according to mysql docs BIT(1) will do as well.

from: http://dev.mysql.com/doc/refman/5.1/en/storage-requirements.html

bit storage requirement is: approximately (M+7)/8 bytes which suggests that BIT(M) column is also byte-aligned.

Also I found this: https://bugs.php.net/bug.php?id=50757

So you could check if following code works as you expect:

$pdo = new PDO("connection string etc") ; $statement = $pdo->prepare('INSERT INTO `test` (SomeText,TestBool) VALUES (:someText,:testBool)') ; $statement->bindValue(':someText', "TEST"); $statement->bindValue(':testBool', 0, PDO::PARAM_INT); $statement->execute();

You may also try with different type hints than PARAM_INT, still even if you make it work I advice to change to TINYINT.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

That worked. Incidentally, I just found that using true/false (instead of 1/0) also seems to work for my case.
Note that when binding a string as a value, even if you use PDO::PARAM_INT it will not work as expected. In addition to PDO::PARAM_INT, you also need to cast the string to an integer: $statement->bindValue(':testBool', (int)$_POST['zero'], PDO::PARAM_INT); or else the value will always be 1.
5

pdo by default doesnt use prepared statements for the mysql driver, it emulates them by creating dynamic sql behind the scenes for you. The sql sent to mysql ends up being a single quoted 0 like '0', which mysql interprets as a string, not a number.

$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

It should work now, and you also will be actually using real prepared statements.

Improve this answer

1 Comment

I tried it and it still doesn't work. Is it possible that the mysql driver simply don't support native prepared statements? I'm using php 5.4.0 if that makes a difference.
4

Because prepare adds ' to your parameter, You have only to add b before parameter name

$statement = $pdo->prepare('INSERT INTO `test` (SomeText,TestBool) VALUES (?, b?)'); $statement->execute(array("TEST", 1 /* or TRUE */));

Note: you can use 1, 0 or TRUE, FALSE.

Improve this answer

2 Comments

Is it possible to achieve similar with a named placeholders? The b:fieldName doesn't seem to work.
@NeverEndingQueue, with a named placeholders you can use true or false and it working fine. Eg. $statement->execute(array("TEST", true /* 1 */));
1

you could try this without parameter

if($_POST['bool'] == 1) { $bool = "b'1'"; } else { $bool = "b'0'"; } $statement = $pdo->prepare("INSERT INTO `test` (SomeText,TestBool) VALUES (?,$bool)") ; $statement->execute(array("TEST")) ;

and no security problem

Improve this answer

Comments

0

Just add (bool) before your bit variable, like this:

$id = 1234; $active = 0; $statement = $pdo->prepare('INSERT INTO `test_table` (id, active) VALUES (?, ?)'); $statement->execute([$id, (bool) $active]);
Improve this answer

Comments

-1

PDOStatement::execute() always converts all arguments to string as shown in the latest php 8.2.0 RC3 source:

https://github.com/php/php-src/blob/615b8006c42715b5ea9ec61f9368582eeef8467f/ext/pdo/pdo_stmt.c#L411-L425

So what actually happens, is that the whole array values are converted to string which causes the SQL statement to fail.

$statement = $pdo->prepare('INSERT INTO `test` (SomeText, TestBool) VALUES (?, ?)'); $statement->execute([ 'TEST', false, // converted to string ]);

It can be fixed using PDOStatement::bindValue() as bind value allows to set the argument type.

$statement = $pdo->prepare('INSERT INTO `test` (SomeText, TestBool) VALUES (?, ?)'); $statement->bindValue('SomeText', 'TEST', PDO::PARAM_STR); $statement->bindValue('TestBool', false, PDO::PARAM_BOOL); $statement->execute();

As manual binding is cumbersome, libraries such as Nette make the process much easier as shown in the sample below:

$database = new Nette\Database\Connection("mysql:host={$params['host']};dbname={$params['database']};charset=utf8", $params['user'], $params['pass']); $staff = [ [ 'birthday' => new DateTime('1995-05-01'), 'name' => 'Sharon', 'salary' => '200', 'management' => true, ], ]; $database->query('INSERT INTO test', $staff);

OLD ANSWER:

Obviously, it's more cumbersome than passing the array to execute that's why I created a quick and dirty helper to simplify the task.

Note: As mentioned in the comments, do not use it on not validated user input as it can cause havoc with queries like:

$sql = <<<SQL DELETE FROM table WHERE email = :email SQL;

:email is meant to be a string, but if instead false is passed, it will delete all emails from the table.

$values = [ 'SomeTest' => 'TEST', 'testBool' => false, ]; bind($query, $values)->execute(); /** * Variable to PDO type * * @param mixed $value * * @return int PDO type */ function typeToParam(mixed $value) : int { switch ($type = gettype($value)) { case 'boolean': return PDO::PARAM_BOOL; case 'integer': return PDO::PARAM_INT; case 'NULL': return PDO::PARAM_NULL; case 'string': return PDO::PARAM_STR; default: throw new Exception("unsupported type - {$type}"); } } /** * Bind values to PDO statement * * @param PDOStatement $statement * @param array $data * * @return PDOStatement */ function bind(PDOStatement $statement, array $data) : PDOStatement { foreach ($data as $key => $value) { $statement->bindValue($key, $value, typeToParam($value)); } return $statement; }
Improve this answer

4 Comments

just accidentally run this code with WHERE email=0 and have all your table deleted.
Maybe you would care to explain how this is subject to SQL injection?
this is subject of your quick and dirty helper. setting type automatically is only safe when it sets everything as string. But all other typems mus be set explicitly but never automatically
Thank you for bringing the problem to my attention, I've updated the answer accordingly.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.