Commerce Price - PDOException: Numeric value of out range.

Problem/Motivation

When creating a new product, or updating an existing one, the user is able to enter any numeric price value they wish up to a length of 128 characters.

The existing validation only checks for a numeric value; this leaves a potential issue where a PDO exception is thrown for large input values. For example in a typical MySQL database setup, a price input greater than 21474836 will cause this error:

PDOException: SQLSTATE[22003]: Numeric value out of range: 1264 Out of range value for column 'commerce_price_amount' at row 1: INSERT INTO {field_data_commerce_price} (entity_type, entity_id, revision_id, bundle, delta, language, commerce_price_amount, commerce_price_currency_code, commerce_price_data) VALUES (:db_insert_placeholder_0, :db_insert_placeholder_1, :db_insert_placeholder_2, :db_insert_placeholder_3, :db_insert_placeholder_4, :db_insert_placeholder_5, :db_insert_placeholder_6, :db_insert_placeholder_7, :db_insert_placeholder_8); Array ( [:db_insert_placeholder_0] => commerce_product [:db_insert_placeholder_1] => 1 [:db_insert_placeholder_2] => 28 [:db_insert_placeholder_3] => product [:db_insert_placeholder_4] => 0 [:db_insert_placeholder_5] => und [:db_insert_placeholder_6] => 2222222200 [:db_insert_placeholder_7] => GBP [:db_insert_placeholder_8] => a:1:{s:10:"components";a:0:{}} ) in field_sql_storage_field_storage_write() (line 514 of /var/www/html/sites/lush_content/docroot/modules/field/modules/field_sql_storage/field_sql_storage.module).

Proposed resolution

(Description of the proposed solution, the rationale behind it, and workarounds for people who cannot use the patch.)

// Convert the decimal amount value entered to an integer based
// amount value.
$integer_amount = commerce_currency_decimal_to_amount($element['amount']['#value'], $element['currency_code']['#value']);

// Get the maximum possible integer amount.
$max_possible_integer = _commerce_price_amount_max_int();

// Check if the integer amount exceeds the maximum value allowed
// in the database table.
if ($integer_amount > $max_possible_integer) {
  form_error($element['amount'], t('%title: you have exceeded the maximum price amount. Make sure the price amount is below %max_price.', array('%title' => $element['amount']['#title'], '%max_price' => number_format($max_possible_integer / 100, 2))));
}
else {
  form_set_value($element['amount'], $integer_amount, $form_state);
}

/**
 * Get the maximum allowable integer value for the commerce_price amount.
 */
function _commerce_price_amount_max_int() {
  // Get the field info.
  $commerce_price_field_info = field_info_field('commerce_price');

  // Determine the max value based on if the amount field is signed or not.
  if (isset($commerce_price_field_info['columns']['amount']['unsigned'])) {
    $max_int_value = db_query('SELECT ~0 >> 32 AS max_int_unsigned')->fetchField();
  }
  else {
    $max_int_value = db_query('SELECT ~0 >> 33 AS max_int_signed')->fetchField();
  }

  // Fallback value.
  if (!$max_int_value) {
    $max_int_value = 2147483647;
  }

  return $max_int_value;
}