CiviCRM Community Forums (archive)

Hi guys,

I'm trying to design my extension as I mean to go on, and make sure that my beautiful PHP code isn't littered with clunky SQL files.

I attempted to use file_get_contents, but it didn't seem to work.

My questions are:

1) Where in the extension folder should the file be?
2) What php function should I use to call it, bearing in mind security?

Agree with Erik's comments. To add a little more:

2a) If the goal is to execute a long SQL file (e.g. as part of the installation or upgrade process), you should use CRM_Utils_File::sourceSQLFile -- e.g. https://github.com/civicrm/civicrm-core/blob/master/CRM/Utils/File.php#L255

2b) If the goal is to write a single SQL statement without fear of SQL injections, you can use CRM_Core_DAO::executeQuery ans pass in arguments. A common formulation you see in Civi's code is:

<?php
$sql = "SELECT id, first_name, last_name FROM civicrm_contact WHERE external_id = %1";
$params = array(
  1 => array("987-65-4321", "String")
);
$query = CRM_Core_DAO::executeQuery($sql, $params);

2c) If the goal is provide a little bit of structure so that SQL queries (like 2b) aren't littering the codebase, you might put the code in a BAO helper function, e.g.

<?php
class CRM_Myextension_BAO_Myentity {
  static function findByExternalId($externalId) {
    $sql = "SELECT * FROM civicrm_my_entity WHERE external_id = %1";
    $params = array(
      1 => array($externalId, "String")
    );
    $query = CRM_Core_DAO::executeQuery($sql, $params);
    // ...
  }
}