On Tue, 2019-12-31 at 06:55 +0100, Matthias Apitz wrote:
> Due to a wrong human input in the GUI of our application our
> application server, from the point of view of the PostgreSQL server it
> is the PostgreSQL client, issues a broken ESQL/C command to the PostgreSQL
> server, here from our own debug logging the command and the error
> message of the server:
>
>
> posDebug: [27.12.2019 15:20:59:043] stmt: SELECT ctid, * from titel_datum WHERE desk>='*2019' AND feldnr IN ( 2 )
ORDERBY desk ASC , feldnr ASC
> posDebug: [27.12.2019 15:20:59:043] ESQL: PREPARE sid_titel_datum FROM :select_anw;
> ==== sqlca ====
> sqlcode: -400
> sqlerrm.sqlerrml: 61
> sqlerrm.sqlerrmc: invalid input syntax for type date: »*2019« on line 918
> ...
>
> All subsequent correct (SELECT ...) statements get rejected with, for example:
>
>
> ...
> posDebug: [27.12.2019 15:20:59:044] stmt: SELECT ctid, * from titel_datum WHERE desk>='31.12.1900' AND feldnr IN ( 2
) ORDER BY desk ASC , feldnr ASC
> posDebug: [27.12.2019 15:20:59:044] ESQL: PREPARE sid_titel_datum FROM :select_anw;
> ==== sqlca ====
> sqlcode: -400
> sqlerrm.sqlerrml: 105
> sqlerrm.sqlerrmc: current transaction is aborted, commands ignored until end of transaction block on line 918
> sqlerrd: 0 0 0 0 0 0
> sqlwarn: 0 0 0 0 0 0 0 0
> sqlstate: 25P02
> ========posSqlError=======
> ...
>
> Note: we are not in some kind TRANSACTION block, like 'EXEC SQL BEGIN TRANSACTION;'
>
> What is the correct way to abort the "transaction" as requested by the PG
> server to return to normal operations?
Then you must have "autocommit" set to "off", and you must catch the error
in your code and issue an explicit rollback.
Maybe you should have a look at your transaction management in general, because
it is very important that COMMIT is issued once any transaction is completed.
COMMIT on an aborted transaction will automatically cause a rollback.
Did you make sure that your code is not vulnerable to SQL injection?
What is the statement if the user enters a single quote for the date?
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com