Insert empty value using Bindparam without error Incorrect Integer value

Question

I want to insert some data into my MySql table using PDO. If it's not empty it will have Integer datatype so i use PDO::PARAM_INT, but the problem is this data is not required so sometimes it's empty.

It make me have an error:

PDOException: SQLSTATE[HY000]: General error: 1366 Incorrect integer value.

So is it anyway I can pass this empty value variable into my database without having error when it have empty value? here's my code snippet:

$stmt = $db->prepare("INSERT INTO table (column) VALUES (:column)");
$stmt->bindParam(':column',$_POST['value'], PDO::PARAM_INT);
$stmt->execute();

Answer 1

An empty string is incorrect integer value all the same. Although been permissive in the past, nowadays MySQL tends to be more strict with types, enforcing the STRICT MODE by default, hence not accepting an empty string for the integer field anymore.

Besides, PDO::PARAM_INT doesn't cast your values.

Therefore, you have to cast them manually

In case you want to retain a possible NULL value, convert your input like this

$value = is_null($_POST['value']) ? null : (int)$_POST['value'];

If you want to just convert to integer unconditionally, then just make it

$value = (int)$_POST['value'];

Both snippets will convert an empty string to 0 which will be gladly accepted by MySQL