suwako / LittleBeast

master
ブランチ master
LittleBeast / src / Std / Lib / Db / Pgsql.php
36.9 KB、1006行 生 Blame 履歴
1<?php
2/*************************************************************
3# 076 License
4
5Copyright (c) テクニカル諏訪子
6
7Permission is hereby granted to any person obtaining a copy of the software
8Little Beast (the "Software") to use, modify, merge, copy, publish, distribute,
9sublicense, and/or sell copies of the Software, subject to the following conditions:
10
11 1. **Origin Attribution**:
12 - You must not misrepresent the origin of the Software; you must not claim
13 you created the original Software.
14 - If the Software is used in a product, you must either:
15 a. Provide clear attribution in the product's documentation, user interface,
16 or other visible areas, **OR**
17 b. Pay the original developers a fee they specify in writing.
18 2. **Usage Restriction**:
19 - The Software, or any derivative works, dependencies, or libraries
20 incorporating it, must not be used for censorship or to suppress freedom of
21 speech, expression, or creativity. Prohibited uses include, but are not
22 limited to:
23 - Censorship of so-called "hate speech", visuals, non-mainstream opinions,
24 ideas, or objective reality.
25 - Tools or systems designed to restrict access to information or
26 artistic works.
27 3. **Notice Preservation**:
28 - This license and the above copyright notice must remain intact in all copies
29 of the source code.
30
31THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED,
32INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A
33PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT
34HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF
35CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE
36OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.
37 */
38namespace Std\Lib\Db;
39
40use Std\Lib\Tls;
41use Std\Lib\Tls\TlsException;
42
43/**
44 * PHP純正のPostgreSQLドライバ（プロトコル3.0）。pgsql・pdo_pgsql・opensslのモジュールが不要。
45 *
46 * 対応：TCP・UNIXソケット、TLS（Std\Lib\Tls）、認証（SCRAM-SHA-256、TLSならチャネルバインディング付きの
47 * SCRAM-SHA-256-PLUS、MD5、パスワード、trust）、単純・拡張クエリ、複数の結果、トランザクション、通知（NOTICE）、
48 * 型の変換（整数・浮動小数点数・真偽値・bytea・配列）
49 * 未対応：COPY（データは無視する）、LISTEN・NOTIFYの待ち受け、GSSAPI・SSPI・Kerberos、取り消し要求
50 *
51 * プレースホルダは「?」（$1、$2…に変換する、「??」は「?」そのもの）又は「$1」。
52 * PostgreSQLにはAUTO_INCREMENTのIDが無いので、insertIdは常に0（RETURNINGを使って下さい）。
53 *
54 * 普通はStd\Lib\Db経由で使う。直接使う場合：
55 * $pg = new Pgsql(['host' => '127.0.0.1', 'username' => 'lb', 'password' => 'ひみつ', 'dbname' => 'lb']);
56 * $pg->connect();
57 * $id = $pg->prepare('SELECT * FROM users WHERE id = ?');
58 * $res = $pg->execute($id, [1]); // $res['rows']
59 * $pg->demolish($id);
60 */
61class Pgsql implements DbInterface {
62 // 型のOID
63 public const OID_BOOL = 16;
64 public const OID_BYTEA = 17;
65 public const OID_INT8 = 20;
66 public const OID_INT2 = 21;
67 public const OID_INT4 = 23;
68 public const OID_TEXT = 25;
69 public const OID_OID = 26;
70 public const OID_JSON = 114;
71 public const OID_FLOAT4 = 700;
72 public const OID_FLOAT8 = 701;
73 public const OID_VARCHAR = 1043;
74 public const OID_DATE = 1082;
75 public const OID_TIMESTAMP = 1114;
76 public const OID_TIMESTAMPTZ = 1184;
77 public const OID_NUMERIC = 1700;
78 public const OID_UUID = 2950;
79 public const OID_JSONB = 3802;
80
81 // 配列の型 => 要素の型
82 private const ARRAYS = [
83 1000 => 16, 1001 => 17, 1005 => 21, 1007 => 23, 1016 => 20, 1009 => 25, 1015 => 1043, 1014 => 1042,
84 1021 => 700, 1022 => 701, 1231 => 1700, 1182 => 1082, 1115 => 1114, 1185 => 1184, 2951 => 2950, 199 => 114, 3807 => 3802,
85 ];
86
87 private const PROTOCOL = 196608; // 3.0
88 private const SSL_REQUEST = 80877103;
89
90 /** @var resource|null */
91 private $socket = null;
92 /** @var resource|null TLSの下のソケット（タイムアウトの確認用） */
93 private $raw = null;
94 private bool $connected = false;
95 private bool $secure = false;
96 private bool $debug = false;
97 private array $packetLog = [];
98 private string $out = '';
99 public string $username;
100 private string $password;
101 public string $dbname;
102 public string $host;
103 private int $port;
104 private string $socketPath;
105 private string $charset;
106 private bool|array $tls;
107 private string $channelBinding;
108 private bool $allowCleartext;
109 private int $timeout;
110
111 private string $status = '';
112 private array $params = [];
113 private int $pid = 0;
114 private string $authMethod = '';
115 private array $notices = [];
116 private array $prepared = [];
117 private int $nextStmt = 1;
118
119 /**
120 * コンストラクタ
121 *
122 * 接続に必要な設定を初期化します。$infoを省略するとDBINFOを使います。
123 *
124 * @param array|null $info host、port（5432）、socket（UNIXソケットのファイル又はディレクトリ）、username、password、dbname、
125 * charset（UTF8）、tls（true＝証明書を確認するTLS、又はStd\Lib\Tlsのオプションの配列）、
126 * channel_binding（prefer＝TLSなら使う、require、disable）、
127 * allow_cleartext（TLSでないTCPでもパスワードをそのまま送る、既定false）、timeout（秒、10）、debug
128 * @throws \Exception $infoを省略し、DB_ENABLEDが無効な場合
129 */
130 public function __construct(?array $info = null) {
131 if ($info === null && !(defined('DB_ENABLED') && DB_ENABLED)) {
132 throw new \Exception('データベースは無効です。');
133 }
134
135 $info ??= DBINFO;
136 $this->host = (string)($info['host'] ?? '127.0.0.1');
137 $this->port = (int)($info['port'] ?? 5432);
138 $this->socketPath = (string)($info['socket'] ?? '');
139 $this->username = (string)($info['username'] ?? '');
140 $this->password = (string)($info['password'] ?? '');
141 $this->dbname = (string)($info['dbname'] ?? '');
142 $this->charset = (string)($info['charset'] ?? 'UTF8');
143 $this->tls = $info['tls'] ?? false;
144 $this->channelBinding = strtolower((string)($info['channel_binding'] ?? 'prefer'));
145 $this->allowCleartext = (bool)($info['allow_cleartext'] ?? false);
146 $this->timeout = max(1, (int)($info['timeout'] ?? 10));
147 $this->debug = (bool)($info['debug'] ?? false);
148
149 if (!in_array($this->channelBinding, ['prefer', 'require', 'disable'], true)) {
150 throw new \InvalidArgumentException("channel_bindingはprefer、require、disable： {$this->channelBinding}");
151 }
152 // PostgreSQLの「utf8mb4」は無いので、MySQLと同じ設定でも動く様に
153 if (in_array(strtolower($this->charset), ['utf8mb4', 'utf8', 'utf-8'], true)) $this->charset = 'UTF8';
154 }
155
156 public function __destruct() {
157 $this->close();
158 }
159
160 /**
161 * デバッギングの有無（有効にすると送受信したメッセージを出力し、ログに残す）
162 *
163 * @param bool $debug
164 * @return Pgsql
165 */
166 public function setDebug(bool $debug): Pgsql {
167 $this->debug = $debug;
168 return $this;
169 }
170
171 /**
172 * メッセージのログ
173 *
174 * @return array direction、type、length、data、timestamp
175 */
176 public function getPacketLog(): array {
177 return $this->packetLog;
178 }
179
180 /**
181 * サーバーの情報
182 *
183 * @return array サーバーのパラメータ（server_version、client_encoding等）、pid、tls、authMethod
184 */
185 public function getServerInfo(): array {
186 return $this->params + ['pid' => $this->pid, 'tls' => $this->secure && $this->socketPath === '', 'authMethod' => $this->authMethod];
187 }
188
189 /**
190 * 最後のクエリの通知（NOTICE、WARNING等）
191 *
192 * @return array ['severity' => …, 'code' => SQLSTATE, 'message' => …]の配列
193 */
194 public function getNotices(): array {
195 return $this->notices;
196 }
197
198 /**
199 * PostgreSQLサーバーに接続
200 *
201 * @return bool
202 * @throws DbException 接続または認証に失敗した場合
203 */
204 public function connect(): bool {
205 if ($this->connected) return true;
206
207 $unix = $this->socketPath !== '';
208 if ($unix) {
209 $path = @is_dir($this->socketPath) ? rtrim($this->socketPath, '/')."/.s.PGSQL.{$this->port}" : $this->socketPath;
210 $target = "unix://{$path}";
211 } else {
212 $target = 'tcp://'.(str_contains($this->host, ':') ? "[{$this->host}]" : $this->host).":{$this->port}";
213 }
214 $errno = 0;
215 $errstr = '';
216 $socket = @stream_socket_client($target, $errno, $errstr, $this->timeout, STREAM_CLIENT_CONNECT, stream_context_create());
217 if ($socket === false) $this->fail("ソケットに接続に失敗: {$errstr} ({$errno})");
218 stream_set_timeout($socket, $this->timeout);
219 $this->socket = $this->raw = $socket;
220 $this->secure = $unix;
221
222 try {
223 if ($this->tls !== false) $this->startTls();
224 $this->startup();
225 $this->connected = true;
226 } catch (\Throwable $e) {
227 $this->drop();
228 throw $e;
229 }
230 return true;
231 }
232
233 public function close(): void {
234 if ($this->socket && $this->connected) {
235 try {
236 $this->send('X', ''); // Terminate
237 $this->flush();
238 } catch (\Throwable $e) {
239 // 既に切れている
240 }
241 }
242 $this->drop();
243 }
244
245 public function isConnected(): bool {
246 return $this->connected;
247 }
248
249 /**
250 * 接続がまだ生きているか確認する（空のクエリ）
251 *
252 * @return bool
253 */
254 public function ping(): bool {
255 if (!$this->connected) return false;
256 try {
257 $this->query('');
258 return true;
259 } catch (\Throwable $e) {
260 return false;
261 }
262 }
263
264 /**
265 * SQLをそのまま実行する（単純クエリ、セミコロンで区切った複数の文も可）
266 *
267 * @param string $query SQL
268 * @return array fields、rows、affectedRows、insertId（常に0）、warningCount、command（例：「INSERT 0 1」）（、more）
269 * @throws DbException
270 */
271 public function query(string $query): array {
272 $this->requireConnection();
273 $this->notices = [];
274 $this->send('Q', $query."\0");
275 return $this->collect('クエリに失敗');
276 }
277
278 /**
279 * プリペアドステートメントの準備
280 *
281 * @param string $query プレースホルダ（「?」又は「$1」）付きのSQL
282 * @return int ステートメントID
283 * @throws DbException
284 */
285 public function prepare(string $query): int {
286 $this->requireConnection();
287 $id = $this->nextStmt++;
288 $name = "lb{$id}";
289
290 $this->send('P', $name."\0".self::placeholders($query)."\0\0\0");
291 $this->send('D', 'S'.$name."\0");
292 $this->send('S', '');
293
294 $params = [];
295 $error = null;
296 while (true) {
297 [$type, $body] = $this->readMessage();
298 switch ($type) {
299 case '1': // ParseComplete
300 case 'n': // NoData
301 case 'T': // RowDescription（列は実行の時にポータルのDescribeで受け取る）
302 break;
303 case 't': // ParameterDescription
304 $n = unpack('n', $body)[1];
305 $params = $n > 0 ? array_values(unpack("N{$n}", $body, 2)) : [];
306 break;
307 case 'E':
308 $error = $this->parseError($body);
309 if ($this->fatal($error)) $this->failError('準備に失敗', $error);
310 break;
311 case 'Z':
312 $this->status = $body;
313 if ($error) $this->failError('準備に失敗', $error);
314 $this->prepared[$id] = ['name' => $name, 'params' => $params];
315 return $id;
316 default:
317 $this->other($type, $body);
318 }
319 }
320 }
321
322 /**
323 * プリペアドステートメントの実行
324 *
325 * int・float・bool・null・文字列（bytea型には生のバイト）・DateTimeInterface・BackedEnumを送れます。
326 *
327 * @param int $statementId ステートメントID
328 * @param array $params パラメータ（順番通り）
329 * @return array 結果（query()と同じ形）
330 * @throws DbException
331 */
332 public function execute(int $statementId, array $params = []): array {
333 $this->requireConnection();
334 if (!isset($this->prepared[$statementId])) $this->fail("不正なステートメントID: {$statementId}");
335
336 $stmt = $this->prepared[$statementId];
337 $params = array_values($params);
338 $n = count($stmt['params']);
339 if (count($params) !== $n) {
340 $this->fail("パラメータ数が一致しません: 期待 {$n}, 取得 ".count($params));
341 }
342
343 $formats = '';
344 $values = '';
345 foreach ($params as $i => $v) {
346 [$format, $data] = $this->encodeParam($v, $stmt['params'][$i], $i);
347 $formats .= pack('n', $format);
348 $values .= $data === null ? pack('N', 0xFFFFFFFF) : pack('N', strlen($data)).$data;
349 }
350
351 $this->notices = [];
352 $this->send('B', "\0".$stmt['name']."\0".pack('n', $n).$formats.pack('n', $n).$values."\0\0");
353 $this->send('D', "P\0");
354 $this->send('E', "\0".pack('N', 0));
355 $this->send('S', '');
356 return $this->collect('実行に失敗');
357 }
358
359 /**
360 * プリペアドステートメントの解放
361 *
362 * @param int $statementId
363 * @return bool
364 */
365 public function demolish(int $statementId): bool {
366 if (!isset($this->prepared[$statementId])) return false;
367 $name = $this->prepared[$statementId]['name'];
368 unset($this->prepared[$statementId]);
369 if (!$this->connected) return false;
370
371 $this->send('C', 'S'.$name."\0");
372 $this->send('S', '');
373 while (true) {
374 [$type, $body] = $this->readMessage();
375 if ($type === 'Z') {
376 $this->status = $body;
377 return true;
378 }
379 if ($type !== '3' && $type !== 'E') $this->other($type, $body);
380 }
381 }
382
383 public function beginTransaction(): bool {
384 $this->query('BEGIN');
385 return true;
386 }
387
388 /**
389 * トランザクションをコミットする
390 *
391 * 失敗したトランザクション（エラーの後）は、PostgreSQLがCOMMITをROLLBACKにするので、例外にする。
392 *
393 * @return bool
394 * @throws DbException 失敗したトランザクションの場合（SQLSTATEは25P02、ロールバック済み）
395 */
396 public function commit(): bool {
397 $res = $this->query('COMMIT');
398 if ($res['command'] === 'ROLLBACK') {
399 $this->fail('コミットに失敗: トランザクションの中でエラーが起きたので、ロールバックしました', 0, '25P02');
400 }
401 return true;
402 }
403
404 public function rollback(): bool {
405 $this->query('ROLLBACK');
406 return true;
407 }
408
409 /** トランザクション中（失敗したトランザクションも）かどうか */
410 public function inTransaction(): bool {
411 return $this->connected && ($this->status === 'T' || $this->status === 'E');
412 }
413
414 public function quoteIdentifier(string $name): string {
415 return '"'.str_replace('"', '""', $name).'"';
416 }
417
418 /**
419 * 「?」のプレースホルダを「$1」「$2」…にする
420 *
421 * 文字列（'…'、E'…'）、識別子（"…"）、$tag$…$tag$、コメントの中は変えない。「??」は「?」そのもの（jsonbの演算子等）。
422 *
423 * @param string $sql
424 * @return string
425 */
426 public static function placeholders(string $sql): string {
427 if (!str_contains($sql, '?')) return $sql;
428 $out = '';
429 $n = 0;
430 $len = strlen($sql);
431 for ($i = 0; $i < $len; $i++) {
432 $c = $sql[$i];
433 $prev = $i > 0 ? $sql[$i - 1] : '';
434 $word = fn(string $ch): bool => $ch !== '' && (ctype_alnum($ch) || $ch === '_' || ord($ch) >= 0x80);
435
436 if ($c === "'") {
437 $escape = ($prev === 'E' || $prev === 'e') && !$word($i > 1 ? $sql[$i - 2] : '');
438 $j = $i + 1;
439 while ($j < $len) {
440 if ($escape && $sql[$j] === '\\') {
441 $j += 2;
442 continue;
443 }
444 if ($sql[$j] === "'") {
445 if (($sql[$j + 1] ?? '') === "'") {
446 $j += 2;
447 continue;
448 }
449 break;
450 }
451 $j++;
452 }
453 $out .= substr($sql, $i, $j - $i + 1);
454 $i = $j;
455 } else if ($c === '"') {
456 $j = $i + 1;
457 while ($j < $len && !($sql[$j] === '"' && ($sql[$j + 1] ?? '') !== '"')) $j += $sql[$j] === '"' ? 2 : 1;
458 $out .= substr($sql, $i, $j - $i + 1);
459 $i = $j;
460 } else if ($c === '$' && !$word($prev) && preg_match('/\G\$([A-Za-z_][A-Za-z0-9_]*)?\$/', $sql, $m, 0, $i)) {
461 $end = strpos($sql, $m[0], $i + strlen($m[0]));
462 $j = $end === false ? $len : $end + strlen($m[0]);
463 $out .= substr($sql, $i, $j - $i);
464 $i = $j - 1;
465 } else if ($c === '-' && ($sql[$i + 1] ?? '') === '-') {
466 $j = strpos($sql, "\n", $i);
467 $j = $j === false ? $len : $j;
468 $out .= substr($sql, $i, $j - $i);
469 $i = $j - 1;
470 } else if ($c === '/' && ($sql[$i + 1] ?? '') === '*') {
471 // 入れ子のコメント
472 $depth = 0;
473 $j = $i;
474 while ($j < $len) {
475 if (substr($sql, $j, 2) === '/*') {
476 $depth++;
477 $j += 2;
478 } else if (substr($sql, $j, 2) === '*/') {
479 $depth--;
480 $j += 2;
481 if ($depth === 0) break;
482 } else {
483 $j++;
484 }
485 }
486 $out .= substr($sql, $i, $j - $i);
487 $i = $j - 1;
488 } else if ($c === '?') {
489 if (($sql[$i + 1] ?? '') === '?') {
490 $out .= '?';
491 $i++;
492 } else {
493 $out .= '$'.(++$n);
494 }
495 } else {
496 $out .= $c;
497 }
498 }
499 return $out;
500 }
501
502 /**
503 * 配列のテキスト（例：{1,2,NULL}、{{a,b},{"c,d",e}}）をPHPの配列にする
504 *
505 * @param string $text PostgreSQLの配列の書き方
506 * @param int $elementOid 要素の型（値の変換に使う）
507 * @return array|null
508 */
509 public static function parseArray(string $text, int $elementOid = self::OID_TEXT): ?array {
510 // 範囲の指定（例：[0:1]={1,2}）は無視する
511 if ($text !== '' && $text[0] === '[') $text = substr($text, strpos($text, '=') + 1);
512 $pos = 0;
513 $res = self::parseArrayLevel($text, $pos, $elementOid);
514 return $pos === strlen($text) ? $res : null;
515 }
516
517 private static function parseArrayLevel(string $t, int &$pos, int $oid): ?array {
518 if (($t[$pos] ?? '') !== '{') return null;
519 $pos++;
520 $out = [];
521 $len = strlen($t);
522 if (($t[$pos] ?? '') === '}') {
523 $pos++;
524 return [];
525 }
526 while ($pos < $len) {
527 if ($t[$pos] === '{') {
528 $out[] = self::parseArrayLevel($t, $pos, $oid);
529 } else if ($t[$pos] === '"') {
530 $v = '';
531 for ($pos++; $pos < $len && $t[$pos] !== '"'; $pos++) {
532 if ($t[$pos] === '\\') $pos++;
533 $v .= $t[$pos] ?? '';
534 }
535 $pos++;
536 $out[] = self::castText($v, $oid);
537 } else {
538 $start = $pos;
539 while ($pos < $len && $t[$pos] !== ',' && $t[$pos] !== '}') $pos++;
540 $v = substr($t, $start, $pos - $start);
541 $out[] = strtoupper($v) === 'NULL' ? null : self::castText($v, $oid);
542 }
543 $c = $t[$pos] ?? '';
544 $pos++;
545 if ($c === '}') return $out;
546 if ($c !== ',') return null;
547 }
548 return null;
549 }
550
551 /**
552 * テキストの値をPHPの型にする
553 *
554 * @param string $v 値
555 * @param int $oid 型
556 * @return mixed 整数はint、浮動小数点数はfloat、真偽値はbool、byteaはバイト、配列は配列、その他は文字列
557 */
558 public static function castText(string $v, int $oid): mixed {
559 switch ($oid) {
560 case self::OID_INT2:
561 case self::OID_INT4:
562 case self::OID_INT8:
563 case self::OID_OID:
564 $i = (int)$v;
565 return (string)$i === $v ? $i : $v;
566 case self::OID_FLOAT4:
567 case self::OID_FLOAT8:
568 return match ($v) {
569 'NaN' => NAN,
570 'Infinity' => INF,
571 '-Infinity' => -INF,
572 default => (float)$v,
573 };
574 case self::OID_BOOL:
575 return $v === 't';
576 case self::OID_BYTEA:
577 if (str_starts_with($v, '\\x')) return (string)hex2bin(substr($v, 2));
578 // escape形式（bytea_output = escape）
579 return preg_replace_callback('/\\\\(\\\\|[0-7]{3})/', fn($m) => $m[1] === '\\' ? '\\' : chr(octdec($m[1])), $v);
580 }
581 if (isset(self::ARRAYS[$oid])) return self::parseArray($v, self::ARRAYS[$oid]) ?? $v;
582 return $v;
583 }
584
585 ///////////////
586 // 接続・認証
587
588 /** SSLRequestを送って、TLSを開始する */
589 private function startTls(): void {
590 $this->send(null, pack('NN', 8, self::SSL_REQUEST));
591 $answer = $this->readBytes(1);
592 if ($answer !== 'S') $this->fail('サーバーがTLSに対応していません');
593
594 $opts = is_array($this->tls) ? $this->tls : [];
595 $opts += ['peer_name' => $this->host];
596 try {
597 $this->socket = Tls::enable($this->raw, $opts);
598 } catch (TlsException $e) {
599 $this->fail('TLSの開始に失敗: '.$e->getMessage());
600 }
601 $this->secure = true;
602 }
603
604 /** StartupMessageと認証、ReadyForQueryまで */
605 private function startup(): void {
606 $params = ['user' => $this->username, 'database' => $this->dbname, 'client_encoding' => $this->charset,
607 'application_name' => 'LittleBeast', 'DateStyle' => 'ISO, YMD'];
608 $body = pack('N', self::PROTOCOL);
609 foreach ($params as $k => $v) {
610 if ($v !== '') $body .= $k."\0".$v."\0";
611 }
612 $this->send(null, pack('N', strlen($body) + 5).$body."\0");
613
614 $scram = null;
615 while (true) {
616 [$type, $body] = $this->readMessage();
617 if ($type === 'E') $this->failError('接続に失敗', $this->parseError($body));
618 if ($type === 'Z') {
619 $this->status = $body;
620 return;
621 }
622 if ($type !== 'R') {
623 $this->other($type, $body);
624 continue;
625 }
626
627 $code = unpack('N', $body)[1];
628 switch ($code) {
629 case 0: // AuthenticationOk
630 if ($scram !== null && empty($scram['verified'])) $this->fail('認証に失敗: サーバーがSCRAMを完了しませんでした');
631 if ($this->authMethod === '') $this->authMethod = 'trust';
632 break;
633 case 3: // パスワードをそのまま
634 if (!$this->secure && !$this->allowCleartext) {
635 $this->fail('認証に失敗: サーバーがパスワードをそのまま送る事を求めました（TLS・UNIXソケットでない場合は、allow_cleartextが必要）');
636 }
637 $this->authMethod = 'password';
638 $this->send('p', $this->password."\0");
639 break;
640 case 5: // MD5
641 $this->authMethod = 'md5';
642 $this->send('p', 'md5'.md5(md5($this->password.$this->username).substr($body, 4, 4))."\0");
643 break;
644 case 10: // SASL
645 $scram = $this->scramStart(array_filter(explode("\0", substr($body, 4))));
646 break;
647 case 11: // SASLContinue
648 if ($scram === null) $this->fail('認証に失敗: 想定外のSASLContinue');
649 $scram = $this->scramContinue($scram, substr($body, 4));
650 break;
651 case 12: // SASLFinal
652 if ($scram === null) $this->fail('認証に失敗: 想定外のSASLFinal');
653 $this->scramFinal($scram, substr($body, 4));
654 $scram['verified'] = true;
655 break;
656 default:
657 $this->fail("未対応の認証方式: {$code}（GSSAPI・SSPI・Kerberosは未対応）");
658 }
659 }
660 }
661
662 /** SCRAM-SHA-256（-PLUS）を始める（RFC 5802、RFC 7677） */
663 private function scramStart(array $mechanisms): array {
664 $cbData = $this->secure && $this->socketPath === '' && $this->channelBinding !== 'disable' ? $this->channelBindingData() : null;
665 if (in_array('SCRAM-SHA-256-PLUS', $mechanisms, true) && $cbData !== null) {
666 $mech = 'SCRAM-SHA-256-PLUS';
667 $gs2 = 'p=tls-server-end-point,,';
668 } else if (in_array('SCRAM-SHA-256', $mechanisms, true)) {
669 if ($this->channelBinding === 'require') $this->fail('認証に失敗: チャネルバインディングが使えません（channel_binding = require）');
670 $mech = 'SCRAM-SHA-256';
671 // TLSなのにサーバーが-PLUSを出さない＝クライアントは対応している（ダウングレードを見つける為の「y」）
672 $gs2 = $cbData !== null ? 'y,,' : 'n,,';
673 $cbData = null;
674 } else {
675 $this->fail('未対応のSASLの方式: '.implode(', ', $mechanisms));
676 }
677
678 $nonce = rtrim(strtr(base64_encode(random_bytes(18)), '+/', '-_'), '=');
679 $first = 'n=,r='.$nonce; // ユーザー名はStartupMessageの物を使う
680 $this->authMethod = $mech;
681 $this->send('p', $mech."\0".pack('N', strlen($gs2.$first)).$gs2.$first);
682 return ['nonce' => $nonce, 'first' => $first, 'gs2' => $gs2, 'cb' => $cbData];
683 }
684
685 private function scramContinue(array $scram, string $serverFirst): array {
686 $a = [];
687 foreach (explode(',', $serverFirst) as $kv) {
688 if (strlen($kv) > 2 && $kv[1] === '=') $a[$kv[0]] = substr($kv, 2);
689 }
690 $salt = base64_decode($a['s'] ?? '', true);
691 $iter = (int)($a['i'] ?? 0);
692 if (!isset($a['r']) || !str_starts_with($a['r'], $scram['nonce']) || strlen($a['r']) <= strlen($scram['nonce']) || $salt === false || $iter < 1) {
693 $this->fail('認証に失敗: 不正なSCRAMのメッセージ');
694 }
695
696 $salted = hash_pbkdf2('sha256', self::saslPrep($this->password), $salt, $iter, 32, true);
697 $clientKey = hash_hmac('sha256', 'Client Key', $salted, true);
698 $finalBare = 'c='.base64_encode($scram['gs2'].($scram['cb'] ?? '')).',r='.$a['r'];
699 $authMessage = $scram['first'].','.$serverFirst.','.$finalBare;
700 $proof = $clientKey ^ hash_hmac('sha256', $authMessage, hash('sha256', $clientKey, true), true);
701 $this->send('p', $finalBare.',p='.base64_encode($proof));
702
703 return $scram + ['salted' => $salted, 'auth' => $authMessage];
704 }
705
706 /** サーバーの署名を確認する（偽物のサーバーではない事） */
707 private function scramFinal(array $scram, string $serverFinal): void {
708 if (str_starts_with($serverFinal, 'e=')) $this->fail('認証に失敗: '.substr($serverFinal, 2));
709 $expected = hash_hmac('sha256', $scram['auth'], hash_hmac('sha256', 'Server Key', $scram['salted'], true), true);
710 if (!str_starts_with($serverFinal, 'v=') || !hash_equals($expected, (string)base64_decode(substr($serverFinal, 2), true))) {
711 $this->fail('認証に失敗: サーバーの署名が不正です');
712 }
713 }
714
715 /** tls-server-end-point（RFC 5929）：サーバーの証明書のハッシュ（MD5・SHA-1ならSHA-256） */
716 private function channelBindingData(): ?string {
717 $cert = Tls::info($this->socket)['peer_certificates'][0] ?? null;
718 if ($cert === null) return null;
719 $hash = match ($cert->sigAlg) {
720 '1.2.840.113549.1.1.12', '1.2.840.10045.4.3.3' => 'sha384',
721 '1.2.840.113549.1.1.13', '1.2.840.10045.4.3.4' => 'sha512',
722 default => 'sha256',
723 };
724 return hash($hash, $cert->der, true);
725 }
726
727 /**
728 * パスワードの正規化（SASLprep、RFC 4013の簡略版）
729 *
730 * intlのNormalizerが有ればNFKC、無ければそのまま（ASCIIと普通の日本語は同じ）。
731 */
732 private static function saslPrep(string $pw): string {
733 if (preg_match('/^[\x20-\x7E]*$/', $pw) || !mb_check_encoding($pw, 'UTF-8')) return $pw;
734 // 色々なスペースはスペース、ソフトハイフン等は消す
735 $pw = preg_replace(['/[\x{00A0}\x{1680}\x{2000}-\x{200B}\x{202F}\x{205F}\x{3000}]/u', '/[\x{00AD}\x{034F}\x{1806}\x{180B}-\x{180D}\x{200C}\x{200D}\x{2060}\x{FE00}-\x{FE0F}\x{FEFF}]/u'], [' ', ''], $pw);
736 return class_exists('Normalizer') ? (\Normalizer::normalize($pw, \Normalizer::FORM_KC) ?: $pw) : $pw;
737 }
738
739 ///////////////
740 // 結果
741
742 /**
743 * ReadyForQueryまでの結果を集める
744 *
745 * @param string $what 失敗した時のメッセージ
746 * @return array
747 * @throws DbException
748 */
749 private function collect(string $what): array {
750 $results = [];
751 $cur = ['fields' => [], 'rows' => []];
752 $error = null;
753
754 while (true) {
755 [$type, $body] = $this->readMessage();
756 switch ($type) {
757 case 'T': // RowDescription
758 $cur = ['fields' => $this->parseRowDescription($body), 'rows' => []];
759 break;
760 case 'D': // DataRow
761 $cur['rows'][] = $this->parseDataRow($body, $cur['fields']);
762 break;
763 case 'C': // CommandComplete
764 $tag = rtrim($body, "\0");
765 $results[] = $cur + ['command' => $tag, 'affectedRows' => $this->affected($tag)];
766 $cur = ['fields' => [], 'rows' => []];
767 break;
768 case 'I': // EmptyQueryResponse
769 $results[] = ['fields' => [], 'rows' => [], 'command' => '', 'affectedRows' => 0];
770 break;
771 case 's': // PortalSuspended
772 $results[] = $cur + ['command' => '', 'affectedRows' => 0];
773 break;
774 case '2': // BindComplete
775 case 'n': // NoData（行を返さない文）
776 break;
777 case 'E':
778 $error = $this->parseError($body);
779 if ($this->fatal($error)) $this->failError($what, $error);
780 break;
781 case 'G': // CopyInResponse：断る
782 $this->send('f', "COPYは未対応です\0");
783 break;
784 case 'H': // CopyOutResponse：データは無視する
785 case 'd':
786 case 'c':
787 break;
788 case 'Z':
789 $this->status = $body;
790 if ($error) $this->failError($what, $error);
791 if (empty($results)) $results[] = ['fields' => [], 'rows' => [], 'command' => '', 'affectedRows' => 0];
792 $warnings = count($this->notices);
793 foreach ($results as &$r) $r += ['insertId' => 0, 'warningCount' => $warnings];
794 unset($r);
795 $first = array_shift($results);
796 if (!empty($results)) $first['more'] = $results;
797 return $first;
798 default:
799 $this->other($type, $body);
800 }
801 }
802 }
803
804 /** 「INSERT 0 5」「UPDATE 3」等から行数 */
805 private function affected(string $tag): int {
806 return preg_match('/^(?:INSERT \d+|UPDATE|DELETE|SELECT|MOVE|FETCH|COPY|MERGE) (\d+)$/', $tag, $m) ? (int)$m[1] : 0;
807 }
808
809 private function parseRowDescription(string $b): array {
810 $n = unpack('n', $b)[1];
811 $pos = 2;
812 $fields = [];
813 for ($i = 0; $i < $n; $i++) {
814 $end = strpos($b, "\0", $pos);
815 $name = substr($b, $pos, $end - $pos);
816 $f = unpack('Ntable/ncolumn/Ntype/nsize/Nmodifier/nformat', $b, $end + 1);
817 $pos = $end + 19;
818 $fields[] = ['name' => $name, 'table' => $f['table'], 'column' => $f['column'], 'type' => $f['type'],
819 'size' => $f['size'] >= 0x8000 ? $f['size'] - 0x10000 : $f['size'],
820 'modifier' => $f['modifier'] >= 0x80000000 ? $f['modifier'] - 0x100000000 : $f['modifier'], 'format' => $f['format']];
821 }
822 return $fields;
823 }
824
825 private function parseDataRow(string $b, array $fields): array {
826 $n = unpack('n', $b)[1];
827 $pos = 2;
828 $row = [];
829 for ($i = 0; $i < $n; $i++) {
830 $len = unpack('N', $b, $pos)[1];
831 $pos += 4;
832 $f = $fields[$i] ?? ['name' => (string)$i, 'type' => self::OID_TEXT];
833 if ($len === 0xFFFFFFFF) {
834 $row[$f['name']] = null;
835 continue;
836 }
837 $row[$f['name']] = self::castText(substr($b, $pos, $len), $f['type']);
838 $pos += $len;
839 }
840 return $row;
841 }
842
843 /**
844 * パラメータを送る形にする
845 *
846 * @return array [形式（0＝テキスト、1＝バイナリ）, データ（nullならNULL）]
847 */
848 private function encodeParam(mixed $v, int $oid, int $i): array {
849 if ($v instanceof \BackedEnum) $v = $v->value;
850 if ($v instanceof \DateTimeInterface) $v = $v->format('Y-m-d H:i:s.uP');
851 if ($v === null) return [0, null];
852 if (is_bool($v)) {
853 $int = in_array($oid, [self::OID_INT2, self::OID_INT4, self::OID_INT8], true);
854 return [0, $int ? ($v ? '1' : '0') : ($v ? 't' : 'f')];
855 }
856 if (is_int($v)) return [0, (string)$v];
857 if (is_float($v)) {
858 if (is_nan($v)) return [0, 'NaN'];
859 if (is_infinite($v)) return [0, $v > 0 ? 'Infinity' : '-Infinity'];
860 return [0, var_export($v, true)];
861 }
862 if (is_scalar($v) || $v instanceof \Stringable) {
863 // byteaには生のバイト（バイナリ形式）を送る（テキストだと「\」が特別な意味になる）
864 return $oid === self::OID_BYTEA ? [1, (string)$v] : [0, (string)$v];
865 }
866 $this->fail("パラメータ{$i}の型は未対応です: ".get_debug_type($v));
867 }
868
869 ///////////////
870 // メッセージ
871
872 /** 接続の処理中に来る、結果と関係の無いメッセージ */
873 private function other(string $type, string $body): void {
874 switch ($type) {
875 case 'S': // ParameterStatus
876 [$k, $v] = explode("\0", $body) + ['', ''];
877 $this->params[$k] = $v;
878 return;
879 case 'K': // BackendKeyData
880 $this->pid = unpack('N', $body)[1];
881 return;
882 case 'N': // NoticeResponse
883 $e = $this->parseError($body);
884 $this->notices[] = ['severity' => $e['V'] ?? $e['S'] ?? '', 'code' => $e['C'] ?? '', 'message' => $e['M'] ?? ''];
885 return;
886 case 'A': // NotificationResponse（LISTEN）：無視する
887 return;
888 }
889 $this->fail("想定外のメッセージ: {$type}");
890 }
891
892 /** ErrorResponse・NoticeResponseのフィールド（S、V、C、M、D、H等） */
893 private function parseError(string $b): array {
894 $out = [];
895 foreach (explode("\0", rtrim($b, "\0")) as $f) {
896 if ($f !== '') $out[$f[0]] = substr($f, 1);
897 }
898 return $out;
899 }
900
901 /** FATAL・PANICのエラーの後はサーバーが接続を切る */
902 private function fatal(array $e): bool {
903 return in_array($e['V'] ?? $e['S'] ?? '', ['FATAL', 'PANIC'], true);
904 }
905
906 /**
907 * サーバーのエラーで例外を投げる
908 *
909 * @return never
910 */
911 private function failError(string $what, array $e): never {
912 if ($this->fatal($e)) $this->drop();
913 $code = $e['C'] ?? '';
914 $msg = "{$what}: {$code} - ".($e['M'] ?? '不明なエラー');
915 if (!empty($e['D'])) $msg .= "（{$e['D']}）";
916 $this->fail($msg, 0, $code);
917 }
918
919 private function readMessage(): array {
920 $head = $this->readBytes(5);
921 $type = $head[0];
922 $len = unpack('N', $head, 1)[1];
923 if ($len < 4 || $len > 0x40000000) {
924 $this->drop();
925 $this->fail("不正なメッセージの長さ: {$len}");
926 }
927 $body = $len > 4 ? $this->readBytes($len - 4) : '';
928 if ($this->debug) $this->log('RECV', $type, $body);
929 return [$type, $body];
930 }
931
932 private function readBytes(int $len): string {
933 if (!$this->socket) $this->fail('接続していません');
934 if ($this->out !== '') $this->flush();
935 $buf = '';
936 while (strlen($buf) < $len) {
937 $chunk = @fread($this->socket, min($len - strlen($buf), 1048576));
938 if ($chunk === false || $chunk === '') {
939 $timedOut = Tls::timedOut($this->socket) || (bool)(stream_get_meta_data($this->raw ?? $this->socket)['timed_out'] ?? false);
940 if (!$timedOut && $chunk === '' && !feof($this->socket)) continue;
941 $this->drop();
942 $this->fail($timedOut ? "メッセージの読み込みがタイムアウト（{$this->timeout}秒）" : 'サーバーとの接続が切れました');
943 }
944 $buf .= $chunk;
945 }
946 return $buf;
947 }
948
949 /**
950 * メッセージを送る（バッファに溜めて、次に読む時にまとめて送る）
951 *
952 * Parse・Bind・Execute・Syncを別々に送ると、Nagleと遅延ACKで1回40ms程待たされる為。
953 *
954 * @param string|null $type 種類（StartupMessage・SSLRequestはnull）
955 * @param string $body 中身
956 */
957 private function send(?string $type, string $body): void {
958 if (!$this->socket) $this->fail('接続していません');
959 $this->out .= $type === null ? $body : $type.pack('N', strlen($body) + 4).$body;
960 if ($this->debug) $this->log('SEND', $type ?? '-', $body);
961 }
962
963 /** 溜めたメッセージを送る */
964 private function flush(): void {
965 $data = $this->out;
966 $this->out = '';
967 for ($w = 0, $len = strlen($data); $w < $len; ) {
968 $n = @fwrite($this->socket, substr($data, $w, 1048576));
969 if ($n === false || $n === 0) {
970 $this->drop();
971 $this->fail('メッセージの送信に失敗');
972 }
973 $w += $n;
974 }
975 }
976
977 private function log(string $dir, string $type, string $body): void {
978 $this->packetLog[] = ['direction' => $dir, 'type' => $type, 'length' => strlen($body), 'data' => $body, 'timestamp' => microtime(true)];
979 $show = preg_replace('/[^\x20-\x7E]/', '.', substr($body, 0, 200));
980 echo "=== {$dir} {$type} (長さ: ".strlen($body).") === {$show}\n";
981 }
982
983 private function requireConnection(): void {
984 if (!$this->connected) $this->fail('接続していません（先にconnect()を呼んで下さい）');
985 }
986
987 private function drop(): void {
988 if ($this->socket) @fclose($this->socket);
989 if ($this->raw && is_resource($this->raw)) @fclose($this->raw);
990 $this->socket = $this->raw = null;
991 $this->out = '';
992 $this->connected = false;
993 $this->secure = false;
994 $this->status = '';
995 $this->prepared = [];
996 }
997
998 /**
999 * @return never
1000 * @throws DbException
1001 */
1002 private function fail(string $msg, int $code = 0, string $sqlState = ''): never {
1003 if (function_exists('logger')) logger(\LogType::Db, $msg);
1004 throw new DbException($msg, $code, $sqlState);
1005 }
1006}