Wer wird gewinnen: der Mensch - die Krone der Schöpfung oder der Rückschlag?

 

Automatische Konvertierungstools sind häufig für den Großteil der Migration von Informationssystemen von Oracle zu PostgreSQL verantwortlich. Aber unter der gigantischen Menge an Code, die von solchen Lösungen abgedeckt wird, gibt es auch außergewöhnliche Geschichten, mit denen Sie improvisieren müssen. In solchen Fällen besteht das erste natürlich darin, das Problem zu lokalisieren, seine Ursache zu ermitteln und, um die richtige Lösung zu finden, darüber nachzudenken, ob es ähnliche reale oder fiktive Situationen gibt, die eine ähnliche Natur haben könnten. Danach müssen Sie normalerweise den ursprünglichen Oracle-Code umgestalten, die Konvertierungsprozesse und die Grammatik ändern oder die beispiellose Oracle-Funktionalität in PostgreSQL implementieren. Einmal wurden wir von einer scheinbar primitiven Anfrage mit einem Fehler herausgefordert, deren Lösung eine ganze Untersuchung erforderte.

Bildschirmschoner

— , . -, :

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.