/** @var resource $pgsql database resource */
protected $pgsql = null;
- protected $bytea_oid = null;
protected $last_error_reporting; // To handle pgsql driver default verbosity
$connection = "host='$this->dbhost' $port user='$this->dbuser' password='$pass' dbname='$this->dbname'";
}
+ // ALTER USER and ALTER DATABASE are overridden by these settings.
+ $options = array('--client_encoding=utf8', '--standard_conforming_strings=on');
+ // Select schema if specified, otherwise the first one wins.
+ if (!empty($this->dboptions['dbschema'])) {
+ $options[] = "-c search_path=" . addcslashes($this->dboptions['dbschema'], "'\\");
+ }
+
+ $connection .= " options='".implode(' ', $options)."'";
+
ob_start();
if (empty($this->dboptions['dbpersist'])) {
$this->pgsql = pg_connect($connection, PGSQL_CONNECT_FORCE_NEW);
throw new dml_connection_exception($dberr);
}
- $this->query_start("--pg_set_client_encoding()", null, SQL_QUERY_AUX);
- pg_set_client_encoding($this->pgsql, 'utf8');
- $this->query_end(true);
-
- $sql = '';
- // Only for 9.0 and upwards, set bytea encoding to old format.
- if ($this->is_min_version('9.0')) {
- $sql = "SET bytea_output = 'escape'; ";
- }
-
- // Select schema if specified, otherwise the first one wins.
- if (!empty($this->dboptions['dbschema'])) {
- $sql .= "SET search_path = '".$this->dboptions['dbschema']."'; ";
- }
-
- // Find out the bytea oid.
- $sql .= "SELECT oid FROM pg_type WHERE typname = 'bytea'";
- $this->query_start($sql, null, SQL_QUERY_AUX);
- $result = pg_query($this->pgsql, $sql);
- $this->query_end($result);
-
- $this->bytea_oid = pg_fetch_result($result, 0, 0);
- pg_free_result($result);
- if ($this->bytea_oid === false) {
- $this->pgsql = null;
- throw new dml_connection_exception('Can not read bytea type.');
- }
-
// Connection stabilised and configured, going to instantiate the temptables controller
$this->temptables = new pgsql_native_moodle_temptables($this);
return array('description'=>$info['server'], 'version'=>$info['server']);
}
- /**
- * Returns if the RDBMS server fulfills the required version
- *
- * @param string $version version to check against
- * @return bool returns if the version is fulfilled (true) or no (false)
- */
- private function is_min_version($version) {
- $server = $this->get_server_info();
- $server = $server['version'];
- return version_compare($server, $version, '>=');
- }
-
/**
* Returns supported query parameter types
* @return int bitmask of accepted SQL_PARAMS_*
if (is_bool($value)) { // Always, convert boolean to int
$value = (int)$value;
- } else if ($column->meta_type === 'B') { // BLOB detected, we return 'blob' array instead of raw value to allow
- if (!is_null($value)) { // binding/executing code later to know about its nature
- $value = array('blob' => $value);
+ } else if ($column->meta_type === 'B') {
+ if (!is_null($value)) {
+ // standard_conforming_strings must be enabled, otherwise pg_escape_bytea() will double escape
+ // \ and produce data errors. This is set on the connection.
+ $value = pg_escape_bytea($this->pgsql, $value);
}
} else if ($value === '') {
}
protected function create_recordset($result) {
- return new pgsql_native_moodle_recordset($result, $this->bytea_oid);
+ return new pgsql_native_moodle_recordset($result);
}
/**
$this->query_end($result);
// find out if there are any blobs
- $numrows = pg_num_fields($result);
+ $numfields = pg_num_fields($result);
$blobs = array();
- for($i=0; $i<$numrows; $i++) {
- $type_oid = pg_field_type_oid($result, $i);
- if ($type_oid == $this->bytea_oid) {
+ for ($i = 0; $i < $numfields; $i++) {
+ $type = pg_field_type($result, $i);
+ if ($type == 'bytea') {
$blobs[] = pg_field_name($result, $i);
}
}
$id = reset($row);
if ($blobs) {
foreach ($blobs as $blob) {
- // note: in PostgreSQL 9.0 the returned blobs are hexencoded by default - see http://www.postgresql.org/docs/9.0/static/runtime-config-client.html#GUC-BYTEA-OUTPUT
- $row[$blob] = $row[$blob] !== null ? pg_unescape_bytea($row[$blob]) : null;
+ $row[$blob] = ($row[$blob] !== null ? pg_unescape_bytea($row[$blob]) : null);
}
}
if (isset($return[$id])) {
$this->query_end($result);
$return = pg_fetch_all_columns($result, 0);
+
+ if (pg_field_type($result, 0) == 'bytea') {
+ foreach ($return as $key => $value) {
+ $return[$key] = ($value === null ? $value : pg_unescape_bytea($value));
+ }
+ }
+
pg_free_result($result);
return $return;
}
$cleaned = array();
- $blobs = array();
foreach ($dataobject as $field=>$value) {
if ($field === 'id') {
continue;
}
$column = $columns[$field];
- $normalised_value = $this->normalise_value($column, $value);
- if (is_array($normalised_value) && array_key_exists('blob', $normalised_value)) {
- $cleaned[$field] = '@#BLOB#@';
- $blobs[$field] = $normalised_value['blob'];
- } else {
- $cleaned[$field] = $normalised_value;
- }
- }
-
- if (empty($blobs)) {
- return $this->insert_record_raw($table, $cleaned, $returnid, $bulk);
+ $cleaned[$field] = $this->normalise_value($column, $value);
}
- $id = $this->insert_record_raw($table, $cleaned, true, $bulk);
-
- foreach ($blobs as $key=>$value) {
- $value = pg_escape_bytea($this->pgsql, $value);
- $sql = "UPDATE {$this->prefix}$table SET $key = '$value'::bytea WHERE id = $id";
- $this->query_start($sql, NULL, SQL_QUERY_UPDATE);
- $result = pg_query($this->pgsql, $sql);
- $this->query_end($result);
- if ($result !== false) {
- pg_free_result($result);
- }
- }
-
- return ($returnid ? $id : true);
+ return $this->insert_record_raw($table, $cleaned, $returnid, $bulk);
}
$columns = $this->get_columns($table, true);
- // Make sure there are no nasty blobs!
- foreach ($columns as $column) {
- if ($column->binary) {
- parent::insert_records($table, $dataobjects);
- return;
- }
- }
-
$fields = null;
$count = 0;
$chunk = array();
}
/**
- * Insert records in chunks, no binary support, strict param types...
+ * Insert records in chunks, strict param types...
*
* Note: can be used only from insert_records().
*
$columns = $this->get_columns($table);
$cleaned = array();
- $blobs = array();
foreach ($dataobject as $field=>$value) {
$this->detect_objects($value);
if (!isset($columns[$field])) {
continue;
}
- if ($columns[$field]->meta_type === 'B') {
- if (!is_null($value)) {
- $cleaned[$field] = '@#BLOB#@';
- $blobs[$field] = $value;
- continue;
- }
- }
-
- $cleaned[$field] = $value;
- }
-
- $this->insert_record_raw($table, $cleaned, false, true, true);
- $id = $dataobject['id'];
-
- foreach ($blobs as $key=>$value) {
- $value = pg_escape_bytea($this->pgsql, $value);
- $sql = "UPDATE {$this->prefix}$table SET $key = '$value'::bytea WHERE id = $id";
- $this->query_start($sql, NULL, SQL_QUERY_UPDATE);
- $result = pg_query($this->pgsql, $sql);
- $this->query_end($result);
- if ($result !== false) {
- pg_free_result($result);
- }
+ $column = $columns[$field];
+ $cleaned[$field] = $this->normalise_value($column, $value);
}
- return true;
+ return $this->insert_record_raw($table, $cleaned, false, true, true);
}
/**
$columns = $this->get_columns($table);
$cleaned = array();
- $blobs = array();
foreach ($dataobject as $field=>$value) {
if (!isset($columns[$field])) {
continue;
}
$column = $columns[$field];
- $normalised_value = $this->normalise_value($column, $value);
- if (is_array($normalised_value) && array_key_exists('blob', $normalised_value)) {
- $cleaned[$field] = '@#BLOB#@';
- $blobs[$field] = $normalised_value['blob'];
- } else {
- $cleaned[$field] = $normalised_value;
- }
+ $cleaned[$field] = $this->normalise_value($column, $value);
}
$this->update_record_raw($table, $cleaned, $bulk);
- if (empty($blobs)) {
- return true;
- }
-
- $id = (int)$dataobject['id'];
-
- foreach ($blobs as $key=>$value) {
- $value = pg_escape_bytea($this->pgsql, $value);
- $sql = "UPDATE {$this->prefix}$table SET $key = '$value'::bytea WHERE id = $id";
- $this->query_start($sql, NULL, SQL_QUERY_UPDATE);
- $result = pg_query($this->pgsql, $sql);
- $this->query_end($result);
-
- pg_free_result($result);
- }
-
return true;
}
$columns = $this->get_columns($table);
$column = $columns[$newfield];
- $normalised_value = $this->normalise_value($column, $newvalue);
- if (is_array($normalised_value) && array_key_exists('blob', $normalised_value)) {
- // Update BYTEA and return
- $normalised_value = pg_escape_bytea($this->pgsql, $normalised_value['blob']);
- $sql = "UPDATE {$this->prefix}$table SET $newfield = '$normalised_value'::bytea $select";
- $this->query_start($sql, NULL, SQL_QUERY_UPDATE);
- $result = pg_query_params($this->pgsql, $sql, $params);
- $this->query_end($result);
- pg_free_result($result);
- return true;
- }
+ $normalisedvalue = $this->normalise_value($column, $newvalue);
- if (is_null($normalised_value)) {
- $newfield = "$newfield = NULL";
- } else {
- $newfield = "$newfield = \$".$i;
- $params[] = $normalised_value;
- }
+ $newfield = "$newfield = \$" . $i;
+ $params[] = $normalisedvalue;
$sql = "UPDATE {$this->prefix}$table SET $newfield $select";
$this->query_start($sql, $params, SQL_QUERY_UPDATE);
}
/**
- * Delete one or more records from a table which match a particular WHERE clause.
+ * Delete one or more records from a table which match a particular WHERE clause, lobs not supported.
*
* @param string $table The database table to be checked against.
* @param string $select A fragment of SQL to be used in a where clause in the SQL call (used to define the selection criteria).
if (strpos($param, '%') !== false) {
debugging('Potential SQL injection detected, sql_like() expects bound parameters (? or :named)');
}
- if ($escapechar === '\\') {
- // Prevents problems with C-style escapes of enclosing '\',
- // E'... bellow prevents compatibility warnings.
- $escapechar = '\\\\';
- }
// postgresql does not support accent insensitive text comparisons, sorry
if ($casesensitive) {
} else {
$LIKE = $notlike ? 'NOT ILIKE' : 'ILIKE';
}
- return "$fieldname $LIKE $param ESCAPE E'$escapechar'";
+ return "$fieldname $LIKE $param ESCAPE '$escapechar'";
}
public function sql_bitxor($int1, $int2) {
$table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
$table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
+ $table->add_field('onebinary', XMLDB_TYPE_BINARY, 'big', null, null, null);
$table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
$dbman->create_table($table);
- $DB->insert_record($tablename, array('course' => 1));
- $DB->insert_record($tablename, array('course' => 3));
- $DB->insert_record($tablename, array('course' => 2));
- $DB->insert_record($tablename, array('course' => 6));
+ $binarydata = '\\'.chr(241);
+
+ $DB->insert_record($tablename, array('course' => 1, 'onebinary' => $binarydata));
+ $DB->insert_record($tablename, array('course' => 3, 'onebinary' => $binarydata));
+ $DB->insert_record($tablename, array('course' => 2, 'onebinary' => $binarydata));
+ $DB->insert_record($tablename, array('course' => 6, 'onebinary' => $binarydata));
$fieldset = $DB->get_fieldset_sql("SELECT * FROM {{$tablename}} WHERE course > ?", array(1));
$this->assertInternalType('array', $fieldset);
$this->assertEquals(2, $fieldset[0]);
$this->assertEquals(3, $fieldset[1]);
$this->assertEquals(4, $fieldset[2]);
+
+ $fieldset = $DB->get_fieldset_sql("SELECT onebinary FROM {{$tablename}} WHERE course > ?", array(1));
+ $this->assertInternalType('array', $fieldset);
+
+ $this->assertCount(3, $fieldset);
+ $this->assertEquals($binarydata, $fieldset[0]);
+ $this->assertEquals($binarydata, $fieldset[1]);
+ $this->assertEquals($binarydata, $fieldset[2]);
}
public function test_insert_record_raw() {
$this->assertEquals($clob, $DB->get_field($tablename, 'onetext', array('id' => 1)), 'Test CLOB set_field (full contents output disabled)');
$this->assertEquals($blob, $DB->get_field($tablename, 'onebinary', array('id' => 1)), 'Test BLOB set_field (full contents output disabled)');
+ // Empty data in binary columns works.
+ $DB->set_field_select($tablename, 'onebinary', '', 'id = ?', array(1));
+ $this->assertEquals('', $DB->get_field($tablename, 'onebinary', array('id' => 1)), 'Blobs need to accept empty values.');
+
// And "small" LOBs too, just in case.
$newclob = substr($clob, 0, 500);
$newblob = substr($blob, 0, 250);
