PHP PDO破坏了我的$ _POST值？

Question

EDIT: Edit of the original code: (I changed the if to "if($_POST['Break'] != "")" to test it and it doesnt work, neither do any of the other varients that i've tried.

if($_SERVER['REQUEST_METHOD'] != 'POST')
{
    echo '<form method="post" action="">
        Category name: <input type="text" name="cat_name" />
        Category description: <textarea name="cat_description" /></textarea>
        <input type="checkbox" value="Break">Is Table Break?<br>
        <input type="submit" value="Add category" />
     </form>';
}


$sql= 'INSERT INTO categories(cat_name, cat_description, isheader) VALUES (:cat_name, :cat_description, :isheader)';
         $stmt = $DBH->prepare($sql);
          if($_POST['Break'] != ""){
          $isbreak = true;
          }
          else{
          $isbreak = false;
          }         
        $stmt->bindParam(':cat_name', $_POST['cat_name']); 
        $stmt->bindParam(':cat_description', $_POST['cat_description']);
        $stmt->bindParam(':isheader', $isbreak);
        try{
           $stmt->execute();
           header('Location: /testpage.php');
        }
        catch(PDOexception $e){
          $e->getMessage();
        }

The above code should insert into my database with Column "Break" being set to "True"(or 1) when a checkbox is checked. It doesnt. I've tried the following if statements and none fixed it:

if(isset($_POST['Break']) == 1)
if(($_POST['Break']) == "Break")  - ("Break" being the name of my checkbox.
if(($_POST['Break']) === "Break") 
if(($_POST['Break']) == 'Break') 

Now i know this code SHOULD work because before i converted to PDO php it was working. Heres what my previous code looked like. This was 100% working how i wanted it to:

if(isset($_POST['Break']) == 1){
   $isbreak = true;
}
else{
    $isbreak = false;
}
$sql = "INSERT INTO categories(cat_name, cat_description, isheader)
           VALUES('" . mysql_real_escape_string($_POST['cat_name']) . "',
                 '" . mysql_real_escape_string($_POST['cat_description']) . "', ". $isbreak.")";

 $result = mysql_query($sql);
    if(!$result)
    {
        echo 'Error' . mysql_error();
    }
    else
    {
        header('Location: /testpage.php');
    };

I know some of the $_POST data works because my database is filled with the correct "cat_name" and "cat_description" with the PDO code. I've had this problem for EVERY page on my site converting it. I've managed to find dumb little work around specific to each page, but i cant figure this one out. I'd rather just know why this is acting the way it is.

What's more is that when i do print_r($_POST) and my check box is checked it returns the value "Break". I dont understand it.

Answer 1

why i use string values for $isbreak. It gets put into the sql statement as a string anyway so it doesnt matter.

Yes, it does matter.

The string 'true' in an integer context has the value 0 in MySQL. Any string-to-integer conversion that happens implicitly takes the leading digit characters from the string, and if there are none, the string has the value zero.

Whereas the keyword true is exactly equal to the integer 1.

Here's a demo of the conversion. I'm adding + 0 to force the values to be converted to integers.

mysql> select 'true' + 0;
+------------+
| 'true' + 0 |
+------------+
|          0 |
+------------+

mysql> select true + 0;
+----------+
| true + 0 |
+----------+
|        1 |
+----------+

In your old code, you put the true keyword into your INSERT statement, so the MySQL server ended up seeing the following:

INSERT INTO categories(cat_name, cat_description, isheader)
    VALUES('name', 'description', true)

When true is inserted into an integer column, the value inserted is 1.

But when passing strings as parameters, they are sent as strings, so it works similar* to the following:

INSERT INTO categories(cat_name, cat_description, isheader)
    VALUES('name', 'description', 'true')

When 'true' is inserted into an integer column, the value inserted is 0.

---

Re your comment:

It makes no difference if you're using PDO or non-PDO. If you interpolate an unquoted string into an SQL statement, it is parsed as a keyword. If you pass a string as a parameter, it's similar to interpolating a quoted string into the SQL statement, and therefore 'true' becomes 0 in an integer context.

---

I worked up a more thorough test script. I guess you got your answer, it was actually an HTML form problem, not an SQL problem. But I'll post my test script here anyway for future reference.

<?php

$pdo = new PDO(..., array(PDO::ATTR_ERRMODE=>PDO::ERRMODE_EXCEPTION));
$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

$stmt = $pdo->prepare("INSERT INTO foo (test, boolcol) VALUES ('test 1: literal true', true)");
$stmt->execute();

$stmt = $pdo->prepare("INSERT INTO foo (test, boolcol) VALUES ('test 2: literal \'true\'', 'true')");
$stmt->execute();

$stmt = $pdo->prepare("INSERT INTO foo (test, boolcol) VALUES ('test 3: literal 1', 1)");
$stmt->execute();

$stmt = $pdo->prepare("INSERT INTO foo (test, boolcol) VALUES ('test 4: literal \'1\'', '1')");
$stmt->execute();

$stmt = $pdo->prepare("INSERT INTO foo (test, boolcol) VALUES ('test 5: param true', ?)");
$stmt->execute(array(true));

$stmt = $pdo->prepare("INSERT INTO foo (test, boolcol) VALUES ('test 6: param \'true\'', ?)");
$stmt->execute(array('true'));

$stmt = $pdo->prepare("INSERT INTO foo (test, boolcol) VALUES ('test 7: param 1', ?)");
$stmt->execute(array(1));

$stmt = $pdo->prepare("INSERT INTO foo (test, boolcol) VALUES ('test 8: param \'1\'', ?)");
$stmt->execute(array('1'));

Here's the result:

+------------------------+---------+
| test                   | boolcol |
+------------------------+---------+
| test 1: literal true   |       1 |
| test 2: literal 'true' |       0 |
| test 3: literal 1      |       1 |
| test 4: literal '1'    |       1 |
| test 5: param true     |       1 |
| test 6: param 'true'   |       0 |
| test 7: param 1        |       1 |
| test 8: param '1'      |       1 |
+------------------------+---------+

---

* Parameters are never combined with the SQL syntax, they're combined with an internal representation of the query logic during execution, but after the SQL has already been parsed. That's why I say "similar."