bindValue and bindParam in mysqli and PDO ignore variable type

Question

I'm having problems in understanding a part of the meaning of binding certain variable types in PDO and mysqli if the type given, in my case, seems to be meaningless. In the following code, the type bound (like i or s) gets ignored. The table row "wert_sortierung" in the database is INT(11). Regardingless if $val_int is really integer or not and if I bind it via i,s / PDO::PARAM_INT or _STR, the query always works, no break, no error or warning, that the types in the binding and database or variable itself don't fit.

<?
class PDOTest {
    protected $pdo;

    function __construct(){

        $usr="usr";        
        $pwd="pwd";     
        $host="localhost";    
        $db="db";
        $val_int="I'm a string";
        $val_str="OP";

        $querystring="SELECT wert_langtext FROM TB_wert WHERE wert_sortierung = ? AND wert_CD = ?";

        try {

            $db_info = "mysql:host=$host;dbname=$db";  // usually provided via require_once and during construction

            $this->pdo = new PDO($db_info, $usr, $pwd);
            $this->pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); 
            $this->pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

            $stmt = $this->pdo->prepare($querystring);

            $stmt->bindValue(1,$val_int,PDO::PARAM_INT);
            $stmt->bindValue(2,$val_str,PDO::PARAM_STR);

            $stmt->execute(); 

            $row_return = $stmt->fetchAll(PDO::FETCH_ASSOC);
            $this->varprint($row_return);
            $this->pdo = NULL;
            }

        catch (PDOException $ex) {
            printf ('Es spricht:');
            $this->printerror("Fehla! (" .  $ex->getMessage() . ")");
            $this->pdo = NULL;
            exit();
        }

        printf("<br />-------<br />");      
        //Added for comparison
        $mysqli = new mysqli($host, $usr, $pwd, $db);

        $m_stmt = $mysqli->prepare($querystring);
        $m_stmt->bind_param('is',$val_int, $val_str);

        $m_stmt->execute();
        $m_stmt->bind_result($row_return);
        $m_stmt->fetch();

        $this->varprint($row_return);

        $m_stmt->close();
        $mysqli->close(); 

    }

    private function printerror($txt) {
            printf("<p><font color=\"#ff0000\">%s</font></p>\n",
            htmlentities($txt));  
    }

    private function varprint($var) {
        echo "<br />";
        echo "<pre>";
        echo var_dump($var);
        echo "</pre>";  
    }
}

new PDOTest();

?>

Please can anyone point out my error in reasoning.

I thought one of the advantages of binding certain variable types is, that the process shouldn't work if those variable types don't fit together. For example I'm binding a string "blabla" with `i` in a query on an INT row - and no warning or what so ever occurs. — sbrue, Aug 14 '13 at 15:48

score 0 · Answer 1 · edited May 23 '17 at 11:56

It is actually Mysql's loose-typing that that deceived you.

As a matter of fact, regular Mysql queries can accept strings for the numberic values all right:

SELECT wert_langtext FROM TB_wert WHERE wert_sortierung = '1' AND wert_CD = '1';

while prepared statement just following this behavior.

However, a contrary situation is not that harmless. Addressing a string value with a number

SELECT wert_langtext FROM TB_wert WHERE wert_sortierung = 1;

will cause infinite number of warnings in case of wert_sortierung being of string type and some unexpected behavior, like matching for ALL the rows.

So, as a general advise I would suggest to always use 's' by default. The only drawback is PDO's emulated prepares and it can be easily worked around.

So, to answer your question explicitly - prepared statements just allow the same behavior as regular queries, adding nothing to it. Everything that possible with a regular query, is possible with prepared statement as well. And no, binding do not validate your data (however it should. Imn my class I test integer placeholders and throw an exception if no numeric value given)

bindValue and bindParam in mysqli and PDO ignore variable type

1 Answers1