谁会赢：男人-创造或反斜杠的王冠？

 

自动转换工具通常负责将信息系统从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 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.