Siapa yang akan menang: manusia - mahkota ciptaan atau garis miring terbalik?

 

Alat konversi otomatis sering kali bertanggung jawab atas sebagian besar migrasi sistem informasi dari Oracle ke PostgreSQL. Tetapi di antara sejumlah besar kode yang tercakup oleh solusi semacam itu, ada juga cerita luar biasa yang harus Anda improvisasi. Dalam kasus seperti itu, hal pertama, tentu saja, adalah melokalisasi masalah, menetapkan penyebabnya dan, untuk menemukan solusi yang tepat, pikirkan apakah ada situasi nyata atau fiksi serupa yang mungkin memiliki sifat serupa. Setelah itu, Anda biasanya harus melakukan refactor kode asli di Oracle, mengubah proses konversi dan tata bahasa, atau menerapkan fungsionalitas Oracle yang tak tertandingi di PostgreSQL. Setelah kami ditantang oleh permintaan yang tampaknya primitif dengan kesalahan, yang membutuhkan seluruh penyelidikan untuk menyelesaikannya.

Screen saver

— , . -, :

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.