src/UniqueCondition.php

   1 <?php
   2
   3 declare(strict_types=1);
   4
   5 namespace PhpMyAdmin;
   6
   7 use PhpMyAdmin\SqlParser\Components\Expression;
   8
   9 use function bin2hex;
  10 use function count;
  11 use function mb_strlen;
  12 use function preg_replace;
  13 use function strcasecmp;
  14 use function trim;
  15
  16 class UniqueCondition
  17 {
  18     private string $whereClause;
  19     private bool $clauseIsUnique = true;
  20     /** @var array<string, string> */
  21     private array $conditionArray = [];
  22
  23     /**
  24      * Function to generate unique condition for specified row.
  25      *
  26      * @param FieldMetadata[] $fieldsMeta      meta information about fields
  27      * @param array           $row             current row
  28      * @param bool            $forceUnique     generate condition only on pk or unique
  29      * @param string|bool     $restrictToTable restrict the unique condition to this table or false if none
  30      * @param Expression[]    $expressions     An array of Expression instances.
  31      * @psalm-param array<int, mixed> $row
  32      */
  33     public function __construct(
  34         array $fieldsMeta,
  35         array $row,
  36         bool $forceUnique = false,
  37         string|bool $restrictToTable = false,
  38         array $expressions = [],
  39     ) {
  40         $fieldsCount = count($fieldsMeta);
  41         $primaryKey = '';
  42         $uniqueKey = '';
  43         $nonPrimaryCondition = '';
  44         $preferredCondition = '';
  45         $primaryKeyArray = [];
  46         $uniqueKeyArray = [];
  47         $nonPrimaryConditionArray = [];
  48
  49         foreach ($fieldsMeta as $i => $meta) {
  50             // do not use a column alias in a condition
  51             if ($meta->orgname === '') {
  52                 $meta->orgname = $meta->name;
  53
  54                 foreach ($expressions as $expression) {
  55                     if (
  56                         $expression->alias === null || $expression->alias === ''
  57                         || $expression->column === null || $expression->column === ''
  58                     ) {
  59                         continue;
  60                     }
  61
  62                     if (strcasecmp($meta->name, $expression->alias) == 0) {
  63                         $meta->orgname = $expression->column;
  64                         break;
  65                     }
  66                 }
  67             }
  68
  69             // Do not use a table alias in a condition.
  70             // Test case is:
  71             // select * from galerie x WHERE
  72             //(select count(*) from galerie y where y.datum=x.datum)>1
  73             //
  74             // Also, do not use the original table name if we are dealing with
  75             // a view because this view might be updatable.
  76             // (The isView() verification should not be costly in most cases
  77             // because there is some caching in the function).
  78             if (
  79                 $meta->table !== $meta->orgtable
  80                 && ! DatabaseInterface::getInstance()->getTable(Current::$database, $meta->table)->isView()
  81             ) {
  82                 $meta->table = $meta->orgtable;
  83             }
  84
  85             // If this field is not from the table which the unique clause needs
  86             // to be restricted to.
  87             if ($restrictToTable && $restrictToTable != $meta->table) {
  88                 continue;
  89             }
  90
  91             // to fix the bug where float fields (primary or not)
  92             // can't be matched because of the imprecision of
  93             // floating comparison, use CONCAT
  94             // (also, the syntax "CONCAT(field) IS NULL"
  95             // that we need on the next "if" will work)
  96             if ($meta->isType(FieldMetadata::TYPE_REAL)) {
  97                 $conKey = 'CONCAT(' . Util::backquote($meta->table) . '.'
  98                     . Util::backquote($meta->orgname) . ')';
  99             } else {
 100                 $conKey = Util::backquote($meta->table) . '.'
 101                     . Util::backquote($meta->orgname);
 102             }
 103
 104             $condition = ' ' . $conKey . ' ';
 105
 106             [$conVal, $condition] = $this->getConditionValue(
 107                 $row[$i] ?? null,
 108                 $meta,
 109                 $fieldsCount,
 110                 $conKey,
 111                 $condition,
 112             );
 113
 114             if ($conVal === null) {
 115                 continue;
 116             }
 117
 118             $condition .= $conVal . ' AND';
 119
 120             if ($meta->isPrimaryKey()) {
 121                 $primaryKey .= $condition;
 122                 $primaryKeyArray[$conKey] = $conVal;
 123             } elseif ($meta->isUniqueKey()) {
 124                 $uniqueKey .= $condition;
 125                 $uniqueKeyArray[$conKey] = $conVal;
 126             }
 127
 128             $nonPrimaryCondition .= $condition;
 129             $nonPrimaryConditionArray[$conKey] = $conVal;
 130         }
 131
 132         // Correction University of Virginia 19991216:
 133         // prefer primary or unique keys for condition,
 134         // but use conjunction of all values if no primary key
 135         if ($primaryKey !== '') {
 136             $preferredCondition = $primaryKey;
 137             $this->conditionArray = $primaryKeyArray;
 138         } elseif ($uniqueKey !== '') {
 139             $preferredCondition = $uniqueKey;
 140             $this->conditionArray = $uniqueKeyArray;
 141         } elseif (! $forceUnique) {
 142             $preferredCondition = $nonPrimaryCondition;
 143             $this->conditionArray = $nonPrimaryConditionArray;
 144             $this->clauseIsUnique = false;
 145         }
 146
 147         $this->whereClause = trim((string) preg_replace('|\s?AND$|', '', $preferredCondition));
 148     }
 149
 150     public function getWhereClause(): string
 151     {
 152         return $this->whereClause;
 153     }
 154
 155     public function isClauseUnique(): bool
 156     {
 157         return $this->clauseIsUnique;
 158     }
 159
 160     /** @return array<string, string> */
 161     public function getConditionArray(): array
 162     {
 163         return $this->conditionArray;
 164     }
 165
 166     /**
 167      * Build a condition and with a value
 168      *
 169      * @param string|int|float|null $row          The row value
 170      * @param FieldMetadata         $meta         The field metadata
 171      * @param int                   $fieldsCount  A number of fields
 172      * @param string                $conditionKey A key used for BINARY fields functions
 173      * @param string                $condition    The condition
 174      *
 175      * @return array<int,string|null>
 176      * @psalm-return array{string|null, string}
 177      */
 178     private function getConditionValue(
 179         string|int|float|null $row,
 180         FieldMetadata $meta,
 181         int $fieldsCount,
 182         string $conditionKey,
 183         string $condition,
 184     ): array {
 185         if ($row === null) {
 186             return ['IS NULL', $condition];
 187         }
 188
 189         $conditionValue = '';
 190         $isBinaryString = $meta->isType(FieldMetadata::TYPE_STRING) && $meta->isBinary();
 191         // 63 is the binary charset, see: https://dev.mysql.com/doc/internals/en/charsets.html
 192         $isBlobAndIsBinaryCharset = $meta->isType(FieldMetadata::TYPE_BLOB) && $meta->charsetnr === 63;
 193         if ($meta->isNumeric) {
 194             $conditionValue = '= ' . $row;
 195         } elseif ($isBlobAndIsBinaryCharset || ($row != 0 && $isBinaryString)) {
 196             // hexify only if this is a true not empty BLOB or a BINARY
 197
 198             // do not waste memory building a too big condition
 199             $rowLength = mb_strlen((string) $row);
 200             if ($rowLength > 0 && $rowLength < 1000) {
 201                 // use a CAST if possible, to avoid problems
 202                 // if the field contains wildcard characters % or _
 203                 $conditionValue = '= CAST(0x' . bin2hex((string) $row) . ' AS BINARY)';
 204             } elseif ($fieldsCount === 1) {
 205                 // when this blob is the only field present
 206                 // try settling with length comparison
 207                 $condition = ' CHAR_LENGTH(' . $conditionKey . ') ';
 208                 $conditionValue = ' = ' . $rowLength;
 209             } else {
 210                 // this blob won't be part of the final condition
 211                 $conditionValue = null;
 212             }
 213         } elseif ($meta->isMappedTypeGeometry && $row != 0) {
 214             // do not build a too big condition
 215             if (mb_strlen((string) $row) < 5000) {
 216                 $condition .= '= CAST(0x' . bin2hex((string) $row) . ' AS BINARY)';
 217             } else {
 218                 $condition = '';
 219             }
 220         } elseif ($meta->isMappedTypeBit) {
 221             $conditionValue = "= b'" . Util::printableBitValue((int) $row, $meta->length) . "'";
 222         } else {
 223             $conditionValue = '= ' . DatabaseInterface::getInstance()->quoteString((string) $row);
 224         }
 225
 226         return [$conditionValue, $condition];
 227     }
 228 }