Who will win: man - the crown of creation or backslash?

 

Automatic conversion tools are often responsible for the bulk of the migration of information systems from Oracle to PostgreSQL. But among the gigantic amount of code covered by such solutions, there are also exceptional stories that you have to improvise with. In such cases, the first step, of course, is to localize the problem, establish its cause and, in order to find the right solution, think about whether there are any similar real or fictional situations that could have a similar nature. After that, you usually have to refactor the original code in Oracle, modify the conversion processes and grammar, or implement unparalleled Oracle functionality in PostgreSQL. Once we were challenged by a seemingly primitive request with an error, which required an entire investigation to solve.

Screensaver

— , . -, :

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.