PHP pdo bindParam type management

Question

This is the post form:

 $funcname->name= htmlentities($_POST['name']);
 $funcname->insert();

this will be the function insert on class funcname which will insert the data to column named name

$this->conn->beginTransaction();
$stmt = $this->conn->prepare("INSERT INTO nameTBL (name) values (:name)";
$stmt->bindParam(':name', $this->name, PDO::PARAM_INT);
if ($stmt->execute()) {
         $this->conn->commit(); //This will save your changes
         $this->conn->exec('UNLOCK TABLES ' . self::$table_name);
         header('Location: ../');
         exit();
} else {
         $this->conn->rollBack(); //This will undo your changes
         $this->conn->exec('UNLOCK TABLES ' . self::$table_name);
         header('Location: ../');
         exit();
}

Now question is i have set PDO::PARAM_INT which should not allow characters but only integer why i am able to post text to database(table)?

is there any how i can highly restrict the data type on bindParam here.

thanks in advance.

Answer 1

You've got several mistakes in your code.

However, let's go over what the types PDO::PARAM_INT, PDO::PARAM_STR and PDO::PARAM_NULL are telling MySQL to do.

Those values are telling PDO how to treat the input, not to disallow input. If you send text, but the column is int then MySQL will attempt to coerce the data into int. It won't tell you "you entered abcd but expected value was integer". You must do this check on your own before passing data to PDO.

Now onto other problems:

• Don't use bindParam. bindParam accepts the value by reference. This is intended for when you invoke stored procedures and variable is supposed to be modified based on procedure's output. Use bindValue. If you tried to do the following with bindParam, it wouldn't work and you'd get an error:

  $stmt->bindParam(':my_column', 1, PDO::PARAM_INT); // It fails and yields an error

• Don't lock tables. You're already using transactions, no need to lock the table, MySQL handles concurrency and access for you.

Bottom line - perform validation before using PDO for inserting. PDO helps you clean the input based on connection information (among other things). It won't perform validation.