1-fields.php

<?php

// Analyze the full/active data-model and attempt to infer optimal escaping policy for each field.
//
// Each field may be classified as follows:
// - "sql-clean-type": The field is an integer, float, or similarly unambiguous element.
// - "string-canonical": We have good reason to believe the field is already stored as a canonical string
//   rather than HTML-esque. Ironically, these are often true-HTML fields. In any event, they store content intuitively.
// - "string-htmlesque-?": The field *may* be using HTML-esque encoding. But not necessarily
//   The current filter has obvious counter-examples, eg "civicrm_contact.contact_type" and "civicrm_case_type.definition".
// - "unknown": The field is completely unrecognized.
//
// Run it with `cv scr fields.php`
//
// The attached example was generated circa 2019.
// Key stats: ~1700 fields total. ~1100 are classified as "sql-clean"; ~100 as "string-canonical"; ~500 as "string-htmlesque-?"; 0 as "unknown".

// NOTE: Third-party/uninstalled extensions not visible to us
function categorize($field, $type) {
  $skipList = CRM_Utils_API_HTMLInputCoder::singleton()->getSkipFields();

  if (in_array($field, $skipList)) {
    // This field is expected to store HTML content (e.g. `description` or `msg_bo.
    return 'string-canonical';
  }

  switch ($type) {
    case CRM_Utils_Type::T_INT:
    case CRM_Utils_Type::T_BOOLEAN:
      // case CRM_Utils_Type::T_ENUM: UGH! Same value as T_STRING...
    case CRM_Utils_Type::T_FLOAT:
    case CRM_Utils_Type::T_MONEY:

    case CRM_Utils_Type::T_DATE + CRM_Utils_Type::T_TIME:
    case CRM_Utils_Type::T_DATE:
    case CRM_Utils_Type::T_TIME:
    case CRM_Utils_Type::T_TIMESTAMP:

      return 'sql-clean-type';

    case CRM_Utils_Type::T_CCNUM:
      throw new \Exception('wtf');

    case CRM_Utils_Type::T_BLOB:
    case CRM_Utils_Type::T_EMAIL:
    case CRM_Utils_Type::T_LONGTEXT:
    case CRM_Utils_Type::T_MEDIUMBLOB:
    case CRM_Utils_Type::T_STRING:
    case CRM_Utils_Type::T_TEXT:
    case CRM_Utils_Type::T_URL:
      // This field is an ambiguous string. It is probably not HTML (*HTML fields usually in skipList*).
      // It may have been coereced to HTML-esque - or it may be a basic/canonical string.
      //
      // Note that the $type alone doesn't tell us the encoding. The true
      // encoding depends on how the UI's were written. For example,
      // a page-controller that reads `exportValues()` gets HTML-esqueue data,
      // but a page-controller that reads `CRM_Utils_Request` gets canonical data).
      //
      // You might get some more precision by examining the field-spec (eg
      // `pseudoconstant` and `html.type`...  maybe look at the underlying list-items
      // for any select/radio/checkbox fields?)

      return 'string-htmlesque-?';

    default:
      return 'unknown';
  }
}

$tables = CRM_Core_DAO_AllCoreTables::getCoreTables();
ksort($tables);
$allFields = [];
$allApiEntities = civicrm_api3('Entity', 'get', []);
foreach ($tables as $table => $daoClass) {
  $fields = $daoClass::fields();
  ksort($fields);
  $entity = CRM_Core_DAO_AllCoreTables::getBriefName($daoClass);
  $isEntity = in_array($entity, $allApiEntities['values']);
  foreach ($fields as $fieldName => $field) {
    $allFields[] = [
      'mysql_table' => $table,
      'api3_entity' => $isEntity ? $entity : '*NONE*',
      'field' => $fieldName,
      'type' => CRM_Utils_Type::typeToString($field['type']),
      'current-storage-format' => categorize($fieldName, $field['type']),
    ];
  }
}

fputcsv(STDOUT, array_keys($allFields[0]));
foreach ($allFields as $field) {
  fputcsv(STDOUT, $field);
}

2-fields.csv