PDO Prepared Statement with Int Casting

Question

I am using PDO prepared statements to insert data to database from external xml source, because I do not trust the source 100% I used bindValue on all variables including strings and integers, for example:

SQL:

INSERT INTO table (id, int1, int2, string1, string2)
VALUES (:id, :int1, :int2, :string1, :string2)

In my PDO function:

$sth->bindValue(":id", $id, PDO::PARAM_INT);
$sth->bindValue(":int1", $int1, PDO::PARAM_INT);
$sth->bindValue(":int2", $int2, PDO::PARAM_INT);
$sth->bindValue(":string1", $string1, PDO::PARAM_STR);
$sth->bindValue(":string2", $string2, PDO::PARAM_STR);

Now my question is, if I previousley used int casting to get the integer values, do I still need to use prepared statment for integer values:

$id= (int) $xml->node->attributes()->id

$id will always be an integer, even if the id in the xml file is not an integer the returned value when using (int) will be 0.

Is it safe in this case to just do:

INSERT INTO table (id, int1, int2, string1, string2)
VALUES ($id, $int1, $int2, :string1, :string2)

EDIT (Example of shorter code):

Binding all parameters:

$sql="INSERT INTO table (id, int1, int2, string1, string2)
     VALUES (:id, :int1, :int2, :string1, :string2)";

$pars = array(":id"=>$id,":int1"=>$int1,":int2"=>$int2,":string1"=>$string1,
              ":string2"=>$string2); 

$model->insert($sql,$pars);

Without Intergers Binding:

$sql="INSERT INTO table (id, int1, int2, string1, string2)
      VALUES ($id, $int1, $int2, :string1, :string2)";

$pars = array(":string1"=>$string1,":string2"=>$string2);

$model->insert($sql,$pars);

now imagine this code with 20+ parameters.

Answer 1

Theoretically it's safe. An int cannot contain malicious data, so there's hardly any chance of SQL injection.

However, this depends on you making no mistakes in your source code. The variables $int1 and $int2 may be guaranteed to be integers now, but as you'll be editing your code and move stuff around, they may not be in the future. Using parameter bindings when creating the SQL statement gives you 100% certainty that nothing can cause malformed syntax. This guarantee does not exist if you rely on other parts of the code doing the sanitisation.

Answer 2

This is wrong question to ask. For too many reasons.

Architectural is one of them.
When your app matures, you will separate your DB layer from input processing level. Means DB code will never know which variable intended to be int and which is not.

Means code something like this

$model = new Model($data);
$model->save();

See - there is no trace nor of PDO, nor of SQL. All DB interaction is done behind the scenes. And obviously this DB layer should be able to handle data properly regardless any prior validations. Which makes the latter just useless, if they done only for DB. Frankly, It's DB layer's business, how to format values, not programmer's

Overall sanity is another.
You can make your code as simple as

$sql = "INSERT INTO table VALUES (?, ?, ?, ?, ?)";
$pdo->prepare($sql)->execute($data);

if you have your values already in array
Why bother with inserting variables directly at all?

Another is efficiency.
You are apparently doing multiple inserts. With placeholders for all values you can prepare your query ONCE and then only execute it with new portions of data - so, actually use this neat side effect of prepared statements:

$stmt = $pdo->prepare("INSERT INTO table VALUES (?, ?, ?, ?, ?)");
foreach ($data as $row) {
    $stmt->execute($row);
}