数据库值与数据流不匹配

Question

For some odd reason, some database values do not match the datastream. It is not for every entry although. For instance a line from my CSV file is below.

00-1751,P,1649.95,1649.95,1237.00

And somehow in my DB the same entry reads

00-1751,P,999.99,999.99,999.99

My code for dumping into the DB is below:

function dump($csvFile, $conn){
if(($handle = fopen($csvFile, 'r')) !== false) {

    $header = fgetcsv($handle);
    $id = 0;

    $sql = "TRUNCATE TABLE inv_price";
    $sth = $conn->prepare($sql);
    $sth->execute();


    while(($data = fgetcsv($handle)) !== false) {
        $sql = "INSERT INTO `inv_price` (sku, part_status, msrp, curr_sugg_retail, your_price) 
                VALUES ('$id', '$data[0]', '$data[1]', '$data[2]', '$data[3]')";
        print_r($data);
    try{
        $conn->query($sql);

    }
    catch (PDOException $e) {
        print "Error!: " . $e->getMessage() . "<br/>";
        die();
    }

        $id++;
    }
    fclose($handle);
}
}

The $data printed to the console matches the CSV file. I am not sure how or why certain rows are changing the value to 999.99.

Answer 1

Most likely explanation for this behavior is that the column is defined as datatype DECIMAL(5,2), which allows for only three digits before the decimal point. And the error/warning is being ignored.

The value being stored in the column the maximum value that can be stored in that datatype. We observe similar behavior with integer values as well.

SQL Fiddle Here http://sqlfiddle.com/#!2/a7577f/1

create table foo (col52 DECIMAL(5,2), col72 decimal(7,2));
insert ignore into foo (col52, col72) values (1234.56, 1234.56);

select * from foo;

col52   col72
------  -------
999.99  1234.56

---

On an entirely different note, the code appears to be vulnerable to SQL injection.

Curiously, the TRUNCATE TABLE statement is a prepared statement, but the INSERT is not.

  $sql = 'INSERT INTO `inv_price` (sku, part_status, msrp, curr_sugg_retail, your_price) 
          VALUES (?, ?, ?, ?, ?)';

  $sth=conn->prepare($sql);
  $sth->bindParam(1,$id);
  $sth->bindParam(2,$data[0]);
  $sth->bindParam(3,$data[1]);
  $sth->bindParam(4,$data[2]);
  $sth->bindParam(5,$data[3]);
  $sth->execute();