Quem vai ganhar: o homem - a coroa da criação ou barra invertida?

 

As ferramentas de conversão automática costumam ser responsáveis ​​pela maior parte da migração dos sistemas de informação do Oracle para o PostgreSQL. Mas entre a quantidade gigantesca de código coberto por essas soluções, também existem histórias excepcionais com as quais você tem que improvisar. Nesses casos, o primeiro passo, é claro, é localizar o problema, estabelecer sua causa e, para encontrar a solução certa, pensar se existem situações semelhantes reais ou fictícias que poderiam ter uma natureza semelhante. Depois disso, você geralmente precisa refatorar o código original no Oracle, modificar os processos de conversão e a gramática ou implementar uma funcionalidade incomparável do Oracle no PostgreSQL. Uma vez, fomos desafiados por um pedido aparentemente primitivo com um erro, que exigia uma investigação inteira para ser resolvido.

Protetor de tela

— , . -, :

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 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 :

, : 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.