Ошибка mixed named and positional parameters

Question

У меня есть метод который формирует запрос на вставку:

public function multipleInsert(array $messages, string $connection): void
{
    $values = [];
    foreach ($messages as $message) {
        $message['content'] = pg_escape_string($message['content']);
        $message['url'] = pg_escape_string($message['url']);
        $message['post_author'] = pg_escape_string($message['post_author']);
        $message['from_id'] = !empty($message['from_id']) ? $message['from_id'] : 0;
        $message['created_at'] = now()->format(Carbon::DEFAULT_TO_STRING_FORMAT);
        $message['updated_at'] = now()->format(Carbon::DEFAULT_TO_STRING_FORMAT);

        $values[] = "({$message['telegram_channel_id']},
        {$message['message_id']},
        '{$message['content']}',
        to_tsvector('{$message['content']}'),
        {$message['views']},
        to_timestamp({$message['date_published']}),
        '{$message['url']}',
        {$message['from_id']},
        '{$message['created_at']}',
        '{$message['updated_at']}',
        '{$message['post_author']}')";
    }


    $sql = 'insert into telegram_messages
(telegram_channel_id, message_id, content, content_tsvector, views, date_published, url, from_id, created_at, updated_at, post_author) values '
        . implode(', ', $values) . ' on conflict do nothing';

    DB::connection($connection)->statement($this->clearSql($sql));
}

и метод clearSql:

  protected function clearSql(string $sql)
  {
        return preg_replace('/([\:])([a-z])/m', '&#58;$2', $sql);
  }

этим методом я очищаю текст запроса от двоеточий, которые postgresql может воспринять как переменные, однако время от времени получаю ошибку

SQLSTATE[HY093]: Invalid parameter number: mixed named and positional parameters

Так же непонятный момент - когда я пробую скопировать уже готовый запрос из логов и вставить в базу данных напрямую то вставка происходит без проблем.

У меня есть подозрение что проблемы из за того что в текстах есть смайлики, эмоджи, спецсимволы а так же есть тексты на разных языках, например арабском или китайском

Answer 1

этим методом я очищаю текст запроса от двоеточий, которые postgresql может воспринять как переменные

Не может. В postgresql нет синтаксиса параметров с двоеточиями. PostgreSQL использует $1 синтаксис параметров запроса и только если запрос приходит через вызов prepare.

SQLSTATE[HY093]: Invalid parameter number: mixed named and positional parameters

Эта ошибка от PDO за то что у вас в тексте запроса используются одновременно позиционные плейсхолдеры ? и именованные :чтото. До базы, соответственно, запрос не доходит вовсе.

Выводите результат своего $this->clearSql($sql) и смотрите, откуда в нём берутся упоминания параметров. Парсер запросов в PDO, конечно, довольно глупый, но раз говорит "есть параметры" - значит что-то на них похожее там действительно есть. В целом странная идея пытаться самостоятельно форматировать текст запроса, но прогонять его всё равно через prepare. Используемая у вас обёртка над PDO не даёт возможность использовать вызов query?

Ну и на самом деле - замените весь этот код полностью. Используйте prepared statements в виде:

$conn->begin()
$stmt = $conn->prepare('insert ...')
foreach ($messages as $message)
   $stmt->execute($message)
$conn->commit()

Либо сформируйте строку содержащую структуру запроса с плейсхолдерами и отдельно массив параметров и выполните через один prepare-execute.

В обоих этих случаях PDO обеспечит корректную транспортировку значений к СУБД, так как сам будет понимать где текст запроса, а где параметры.

Но не пытайтесь подставлять параметры в сам текст запроса, тем более смешивая воедино разные драйверы доступа к СУБД (pg для pg_escape_string и pdo). Некорректная реализация записи - оно как-то всегда хуже корректной независимо от производительности. Для производительности записи вам и вовсе COPY протокол нужен, а не insert. API в PDO для него, впрочем, неудобный, да и on conflict у COPY нет.

БЛОГ НА HUSL

Ошибка mixed named and positional parameters

Ответы (1 шт):