How to become an author
Search
Write a publication
Pull to refresh
0
Rating
БАРС Груп
Создаем технологии. Меняем жизнь.

Кто победит: человек — венец творения или обратный слэш?

Reading time10 min
Views3.3K
БАРС Груп corporate blogPHP*Oracle*PostgreSQL*

 
За основную часть перехода информационных систем с Oracle на PostgreSQL часто отвечают инструменты автоматической конвертации. Но среди гигантского объёма кода, покрываемого такими решениями, есть и исключительные истории, с которыми приходится импровизировать. В таких случаях первым делом, конечно, необходимо локализовать проблему, установить её причину и, чтобы подобрать правильное решение, подумать, нет ли аналогичных реальных или вымышленных ситуаций, которые могли бы иметь похожую природу. После этого обычно приходится рефакторить оригинальный код на Oracle, дорабатывать процессы конвертации и грамматику или реализовывать на PostgreSQL не имеющую аналогов функциональность Oracle. Однажды нам бросил вызов, на первый взгляд, примитивный запрос с ошибкой, для решения которой пришлось проводить целое расследование.


Заставка


История начинается довольно тривиально — появляется задача о том, что в отчёте совсем не печатаются данные. Воспроизводим проблемный тест-кейс, получаем следующую ошибку:


ERROR:  syntax error at or near ":"
LINE X:  WHERE strpos((concat(concat(';', (:ID)::varchar), ';'...
                                           ^

Уровень 0


Смотрим повнимательнее на часть запроса с ошибкой. Циркумфлекс явно указывает на :ID, но интуиция подсказывает, что это всё-таки следствие, а не причина ошибки — для начала лучше посмотреть по сторонам. Конструкция с instr, превратившаяся в конструкцию со strpos, встречается по проекту достаточно часто, и сама по себе проблем вызывать не должна. С concat, пожалуй, тоже должно быть всё в порядке — функция есть в обеих рассматриваемых СУБД, и используется вместо оператора ||, потому что отлично справляется с NULL-значениями. Очень похоже, что разработчик при реализации конкретно этого отчёта допустил какую-то ошибку при связывании переменных или в самом запросе. Но и там на удивление оказалось, что всё гладко. Тогда будем искать проблемную часть простейшим способом — удаляем поочерёдно части запроса и переменные связывания, постепенно сужая круг «подозреваемых». После нескольких итераций сокращённый проблемный вариант оригинального Oracle-запроса выглядел примерно так:


select '\' || t.SOME_FIELD || '\' SLASHED_FIELD
  from SOME_TABLE t
 where instr(';' || :ID || ';', ';' || t.ID || ';') > 0

Конвертируется он в такой запрос для PostgreSQL:


SELECT concat(concat('\', t.some_field), '\')  "slashed_field"
  FROM some_table t
 WHERE strpos((concat(concat(';', (:ID)::varchar), ';')), (concat(concat(';', (t.id)::varchar), ';'))))::numeric > 0

Проблем на уровне отчётной формы не оказалось, оба запроса валидны для соответствующих СУБД. Ошибка не повторяется при выполнении запроса из популярных IDE или с помощью psql, если установить валидное значение для переменной :ID. Но почему всё-таки возникает именно синтаксическая ошибка, а не, например, предупреждение о неправильном количестве/типе переменных связывания? Благодаря множеству опытов выясняется, что для воспроизведения ошибки нужна особенная комбинация строкового литерала с обратным слэшем, конкатенации и переменной связывания.


Изменение Результат
перемещаем выражение с переменной связывания в разные части select ошибка
переименовываем переменную связывания ошибка
пробуем разные значения переменной связывания ошибка
«схлопываем» вложенные вызовы функции конкатенации в один вызов со всеми аргументами сразу ошибка
вместо функции конкатенации пробуем использовать оператор конкатенации ошибка
вместо функции конкатенации пробуем использовать другие строковые функции запрос работает, но результат не тот, что нам нужен
меняем содержимое литерала на случайный текст, двойной/четверной обратный слэш, экранированные управляющие конструкции запрос работает, но результат не тот, что нам нужен
меняем порядок аргументов в функции конкатенации запрос работает, но результат не тот, что нам нужен

После этих наблюдений получаем по-настоящему минимальный проблемный пример:


select concat('\', concat(:ID::varchar, '\')) SLASHED_ID

На этом этапе интуиция подсказывает, что, скорее всего, дело в неправильном экранировании или, наоборот, его отсутствии где-то на более низких слоях абстракции, вследствие которых на исполнение в СУБД отправляется невалидный запрос. Проверим это, посмотрев на логи PostgreSQL.


# Логи запроса, прошедшего по оригинальному пути
ERROR:  syntax error at or near ":" at character 20
STATEMENT:  select concat('\', :ID::varchar, '\') SLASHED_ID;

# Логи того же запроса при вызове из psql с предварительным \set
LOG:  statement: select concat('\', 12345678::varchar, '\') SLASHED_ID;
LOG:  duration: 0.936 ms

При вызове запроса из приложения значение переменной связывания по каким-то причинам не подставляется. Пришло время спуститься на следующий уровень абстракции, вдруг проблема в ядре приложения?


Уровень 1


Фреймворк написан на чистом PHP, в нём реализован свой фасад для работы с СУБД. Версия для Oracle «под капотом» использует oci8, а для PostgreSQL — вполне привычный PDO. Типичный сценарий работы с классом-фасадом (назовём его Query) выглядит просто:


  1. в конструктор передаётся текст запроса, там же выполняется его подготовка (PDO::prepare());
  2. для каждой переменной связывания вызывается метод Query->Bind(), который запоминает имена, значения и типы переменных в приватный массив для подстановки непосредственно перед выполнением;
  3. вызывается функция Query->Execute(), которая выполняет предварительную подстановку значений с помощью PDOStatement::bindParam(), а затем запускает PDOStatement::execute().
  4. при необходимости вернувшиеся кортежи обрабатываются с помощью разных методов Query->Fetch*().

Дебаг очень быстро показал, что с первыми двумя шагами всё отлично, а вот на третьем, где и происходит подстановка, возникает следующая ситуация. Функция PDOStatement::bindParam() в качестве результата подстановки возвращает булево значение, в нашем случае это FALSE. В документации и комментариях к ней не оказалось информации на этот счёт, только скупое Возвращает TRUE в случае успешного завершения или FALSE в случае возникновения ошибки. Штатные средства PDO для диагностики ошибок тоже не хотят делиться информацией о том, почему переменную не удалось подставить. В PDO::errorInfo() уже известная нам информация об ошибке:


array(3) {
  [0]=>
  string(5) "42601"
  [1]=>
  int(7)
  [2]=>
  string(136) "ERROR:  syntax error at or near ":"
LINE 1: select concat('\', concat(:ID::varchar, '\')) SLASHED_ID
                                  ^"
}

В PDO::debugDumpParams() тоже ничего нового — только фактический текст запроса и количество параметров:


SQL: [56] select concat('\', concat(:ID::varchar, '\')) SLASHED_ID
Params:  0

Уровень 2


Чтобы с полной уверенностью исключить влияние окружения и фреймворка, переходим на следующий уровень — используем наш минимальный запрос из чистого PDO:


<?php

$connectionString = getenv('CONNECTION_STRING');
$connection = new \PDO($connectionString);
if ($connection) {
    $stmt = $connection->prepare("select concat('\\', concat(:ID::varchar, '\\')) SLASHED_ID");
    if (!$stmt) {
        print "Statement preparation has failed\n";
    } else {
        $value = '12345678';
        if ($stmt->bindParam(':ID', $value)) {
            print "Bound :ID with value of {$value}\n";
        } else {
            print "Bind attempt for :ID with value of {$value} has failed\n";
        }
        if ($stmt->execute()) {
            print "Query successfully executed\n";
        } else {
            $info = $stmt->errorInfo();
            print "Query execution has failed, reason: {$info[2]}\nDebug dump: ";
            $stmt->debugDumpParams();
            print "\n";
        }
    }
} else {
    print "Connection attempt to {$connectionString} has failed, check the CONNECTION_STRING environment variable\n";
}

В результате узнаем единственную новость — фреймворк, кажется, не виноват. В остальном всё по-старому: значение не подставилось и запрос завершился с синтаксической ошибкой:


Bind attempt for :ID with value of 12345678 has failed
Query execution has failed, reason: ERROR:  syntax error at or near ":"
LINE 1: select concat('\', concat(:ID::varchar, '\')) SLASHED_ID
                                  ^
Debug dump: SQL: [56] select concat('\', concat(:ID::varchar, '\')) SLASHED_ID
Params:  0

В PDO есть два режима подготовки выражений: нативная подготовка в драйвере и эмуляция в самом PDO. Режим зависит от атрибута соединения под названием ATTR_EMULATE_PREPARES. В документации нет информации о значении по умолчанию. Уже чувствуется запах победы: наверняка, в режиме эмуляции есть какой-то баг, из-за которого не происходит связывание. Что ж, попробуем оба варианта в явном виде и посмотрим, изменится ли что-нибудь. Указав FALSE, получим точно такой же результат, что и в примере выше, а вот с TRUE — кое-что новое:


Bound :ID with value of 12345678
Query execution has failed, reason: ERROR:  syntax error at or near ":"
LINE 1: select concat('\', concat(:ID::varchar, '\')) SLASHED_ID
                                  ^
Debug dump: SQL: [56] select concat('\', concat(:ID::varchar, '\')) SLASHED_ID
Params:  1
Key: Name: [3] :ID
paramno=-1
name=[3] ":ID"
is_param=1
param_type=2

Параметр якобы был использован, но всё же не подставился, и потому запросу это, к сожалению, не помогло. Вспоминаем способы связывания переменных в PDO и пробуем их по очереди:


Способ Результат
PDOStatement::bindParam() [42601]: syntax error at or near ":"
PDOStatement::bindValue() [42601]: syntax error at or near ":"
PDOStatement::execute($parameters) [HY093]: Invalid parameter number

Результат немного разный, но с одним и тем же смыслом: все 6 комбинаций этих способов с разными режимами ATTR_EMULATE_PREPARES завершаются неудачей. Явное указание размера и типа переменной в тех методах, которые это позволяют, также ни на что не влияет.


Уровень 3


Пробуем выполнить запрос с помощью libpq напрямую, в надежде получить больше информации об ошибке, если она возникнет. Тут стоит отметить, что сама по себе libpq не предоставляет средств для работы с именованными параметрами, только с позиционными. Значит, если такая возможность есть в ваших среде/языке, стоит поблагодарить автора за такой удобный инструмент.


#include <libpq-fe.h>
#include <stdio.h>
#include <stdlib.h>
#include <string.h>

static void graceful_failure(PGconn * conn) {
    PQfinish(conn);
    exit(1);
}

int main(int argc, char ** argv) {
    const char * conninfo;
    const char * stmtName = "TEST_STATEMENT";
    PGconn * conn;
    PGresult * res;

    if (argc > 1) {
        conninfo = argv[1];
    } else {
        fprintf(stderr, "Please provide a connection string as the first argument");
    }

    conn = PQconnectdb(conninfo);

    if (PQstatus(conn) != CONNECTION_OK) {
        fprintf(stderr, "Connection to database failed: %s",
            PQerrorMessage(conn));
        graceful_failure(conn);
    }

    res = PQprepare(
        conn,
        stmtName,
        "select concat('\\', $1::varchar, '\\') SLASHED_ID",
        1,
        NULL
    );

    if (PQresultStatus(res) != PGRES_COMMAND_OK) {
        fprintf(stderr, "Statement preparation has failed: %s", PQerrorMessage(conn));
        PQclear(res);
        graceful_failure(conn);
    }

    const char * paramValues[1];
    int paramLengths[1];

    paramValues[0] = "12345678";
    paramLengths[0] = strlen(paramValues[0]);

    res = PQexecPrepared(conn,
        stmtName,
        1,
        paramValues,
        paramLengths,
        NULL,
        0
    );

    if (PQresultStatus(res) != PGRES_TUPLES_OK) {
        fprintf(stderr, "Query execution has failed: %s", PQerrorMessage(conn));
        PQclear(res);
        graceful_failure(conn);
    } else {
        fprintf(stdout, "Got the following result: %s", PQgetvalue(res, 0, 0));
    }

    PQclear(res);
    PQfinish(conn);

    return 0;
}

Компилируем, запускаем и осознаём, что ошибки нет:


$ gcc libpqtest.c -I /usr/include/postgresql -o libpqtest -lpq && ./libpqtest "$CONNECTION_STRING"

Got the following result: \12345678\

Можно сделать вывод, что проблема всё-таки где-то в PDO.


Зовём друга


Есть ещё один скриптовый высокоуровневый язык программирования, который всегда под рукой. Почему бы из интереса не проверить, работает ли такой запрос в нём? Вдруг проблема не в PDO, а в версии или реализации протокола, который предоставляется libpq?


import os
import psycopg2
conn = psycopg2.connect(os.getenv("CONNECTION_STRING"))
cursor = conn.cursor()

cursor.execute("select concat('\\', %(ID)s, '\\') SLASHED_ID", {"ID": "12345678"})
for row in cursor:
    print(row)

cursor.close()
conn.close()

И тут тоже сработало:


('\\12345678\\',)

И снова уровень 2


На самом деле закончить играть можно было ещё на втором уровне, но я понял это незадолго до публикации статьи. Чтобы окончательно убедиться, что проблема в PDO, а не где-то глубже, достаточно было использовать какой-то альтернативный способ для работы с PostgreSQL из PHP, например, старое доброе расширение pgsql. Оно не предоставляет отдельных функций для связывания параметров и поддерживает только позиционные псевдопеременные:


<?php

$connectionString = getenv('CONNECTION_STRING');
$connection = pg_connect($connectionString);
if ($connection) {
    $stmt = pg_prepare($connection, "query_with_slashes", "select concat('\\', concat($1::varchar, '\\')) SLASHED_ID");
    if (!$stmt) {
        print "Statement preparation has failed";
    } else {
        if (pg_execute($connection, "query_with_slashes", ['12345678'])) {
            print "Query successfully executed\n";
        } else {
            $info = pg_last_error();
            print "Query execution has failed, reason: {$info}";
        }
    }
} else {
    print "Connection attempt to {$connectionString} has failed, check the CONNECTION_STRING environment variable\n";
}

В ответ увидим простое и понятное сообщение Query successfully executed, после которого не останется никаких сомнений по поводу места возникновения проблемы.


Финальная сцена


Разбираться с такими проблемами, конечно, сложно и интересно, но клиенту нужен рабочий отчёт. Подлатаем временным решением: из множества вариантов (например, очевидный вынос конкатенции из запроса на другой слой приложения) выберем самый простой — заменим строковый литерал на вызов нативной функции, которая вернёт нам нужный символ. Для этого узнаем ASCII-код нужного символа с помощью одноимённой функции, а сам символ из кода получим с помощью функции chr:


select chr(92) || t.SOME_FIELD || chr(92) SLASHED_FIELD
  from SOME_TABLE t
 where instr(';' || :ID || ';', ';' || t.ID || ';') > 0

Ура, всё работает! Но это ещё не всё: нужно проверить, не встречаются ли где-то в других местах такие проблемные конструкции, хоть это и крайне маловероятно. И, к счастью, тщательный поиск по проекту не показал больше потенциальных кандидатов для аналогичной ошибки.


Титры


Можно сказать, что в борьбе с обратным слэшем пока победила дружба. Наблюдения превратятся в отдельную задачу в бэклоге, к которой мы однажды вернёмся. Наверняка, накопленной информации уже достаточно, чтобы сообщить о баге в PHP, но на https://bugs.php.net море багов в статусе Open, и решения придётся ждать очень долго, учитывая, что есть простые обходные пути, а условия воспроизведения весьма редки и специфичны. Несмотря на это, сообщить о нём там стоит всё равно.


Чтобы подвести черту, напоследок парочка напоминаний о вещах, благодаря которым этой статьи могло бы не быть:


  • Предоставляйте детальную информацию об ошибке, соответствующую установленному уровню логирования — просто FALSE в качестве результата или сообщения в духе "Произошла ошибка" сами по себе никак не помогут человеку, столкнувшемуся с ошибкой;
  • Участвуйте в open source проектах — это прекрасный способ помочь тысячам коллег и миллионам пользователей, а также подтянуть свои навыки, расширить кругозор и заявить о себе в профессиональном сообществе. Сейчас как раз удачное время, чтобы начать — на дворе уже Preptember, а за ним Hacktoberfest.

Информация для желающих присоединиться


К такого рода публикациям принято прикладывать информацию об окружении, в этом случае были испробованы два варианта:


  • PHP 7.3.18, libpq-dev 10.12;
  • PHP 7.4.5, libpq-dev 11.7.
Tags:
Hubs:
+7
Comments2
БАРС Груп
Создаем технологии. Меняем жизнь.
3
Karma
0
Rating
Дмитрий Лебедев @nscyclone

User

Send message

Articles

Change theme settings

Information

Website
bars.group
Registered
Founded
Employees
1,001–5,000 employees
Location
Россия

Your account

Sections

Information

Services

Support
© 2006–2024, Habr