Connection lost when running SQL query with three quotes per string
Some SQL queries will cause my connection to stall and drop after about 20 seconds. This happens in both 11g database and 12c database.
So far, the only thing that comes across with offensive queries is that everyone has multiple groups of quotes, one of the groups has three quotes in a row, that is '''
, and there are pipes or hyphens between the groups (e.g. ||||
, --
).
Sample output from sqlplus:
> exec dbms_output.put_line(q'[ '''--''' ]');
BEGIN dbms_output.put_line(q'[ '''--''' ]'); END;
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 143349
Session ID: 284 Serial number: 51992
Elapsed: 00:00:18.93
I expect to see '''--'''
, but the connection is lost instead.
I have replicated the error in sqlplus 11.2.0.1.0, SQL Developer 4.1.3.20 and Toad 12.6.
This causes problems when working in packages. A large number of packages has the following code: '''' || l_varchar2_variable || '''';
. When these packets are launched, the connection is lost.
An example of a list of requests that cause a connection to be dropped:
exec dbms_output.put_line(q'['''--''']');
exec dbms_output.put_line(q'['''---''']');
exec dbms_output.put_line(q'['''----''']');
exec dbms_output.put_line(q'['''||||''']');
exec dbms_output.put_line(q'['''||||' ' ']');
exec dbms_output.put_line(q'['''||||' ' ']');
exec dbms_output.put_line(''''||''||'''');
execute immediate(''''||||'''');
execute immediate('''' |||| '''');
exec dbms_output.put_line(q'#'''|| ||'''#');
exec dbms_output.put_line(q'[ ||'''||--''' ]');
An example of a list of requests that have no problems:
exec dbms_output.put_line(q'[ '''' '''' ]');
exec dbms_output.put_line(q'[ ''' || ''' ]');
exec dbms_output.put_line(q'[ ''' | | | | ''' ]');
exec dbms_output.put_line(q'[ ''' - - ''' ]');
exec dbms_output.put_line(q'[ ''' ||| ''' ]');
exec dbms_output.put_line(q'[ ' ' ' |||| ' ' ' ]');
exec dbms_output.put_line(q'[ ''' |||| ]');
exec dbms_output.put_line(q'[ |||| ''' ]');
select ''''||''||'''' from dual;
select '''''||||''''' from dual;
select ''''||'--'||'''' from dual;
Setting in sqlnet.ora
TRACE_LEVEL_CLIENT=16
TRACE_DIRECTORY_CLIENT=c:\_temp
TRACE_FILE_CLIENT=client
TRACE_UNIQUE_CLIENT=ON
TRACE_TIMESTAMP_CLIENT=ON
DIAG_ADR_ENABLED =OFF
This is the result after I hit Return to execute the request:
[10-JUL-2017 13:58:33:476] nioqsn: entry
[10-JUL-2017 13:58:33:477] nioqsn: exit
[10-JUL-2017 13:58:33:477] nioqrc: entry
[10-JUL-2017 13:58:33:477] nsbasic_bsd: entry
[10-JUL-2017 13:58:33:477] nsbasic_bsd: tot=0, plen=287.
[10-JUL-2017 13:58:33:477] nttfpwr: entry
[10-JUL-2017 13:58:33:477] nttfpwr: socket 528 had bytes written=287
[10-JUL-2017 13:58:33:477] nttfpwr: exit
[10-JUL-2017 13:58:33:477] nsbasic_bsd: packet dump
[10-JUL-2017 13:58:33:477] nsbasic_bsd: 01 1F 00 00 06 00 00 00 |........|
[10-JUL-2017 13:58:33:477] nsbasic_bsd: 00 00 03 5E 45 21 00 04 |...^E!..|
[10-JUL-2017 13:58:33:477] nsbasic_bsd: 00 00 00 00 00 FE FF FF |........|
[10-JUL-2017 13:58:33:477] nsbasic_bsd: FF FF FF FF FF 93 00 00 |........|
[10-JUL-2017 13:58:33:477] nsbasic_bsd: 00 FE FF FF FF FF FF FF |........|
[10-JUL-2017 13:58:33:477] nsbasic_bsd: FF 0D 00 00 00 FE FF FF |........|
[10-JUL-2017 13:58:33:477] nsbasic_bsd: FF FF FF FF FF FE FF FF |........|
[10-JUL-2017 13:58:33:477] nsbasic_bsd: FF FF FF FF FF 00 00 00 |........|
[10-JUL-2017 13:58:33:477] nsbasic_bsd: 00 01 00 00 00 00 00 00 |........|
[10-JUL-2017 13:58:33:477] nsbasic_bsd: 00 00 00 00 00 00 00 00 |........|
[10-JUL-2017 13:58:33:477] nsbasic_bsd: 00 00 00 00 00 00 00 00 |........|
[10-JUL-2017 13:58:33:477] nsbasic_bsd: 00 00 00 00 00 FE FF FF |........|
[10-JUL-2017 13:58:33:477] nsbasic_bsd: FF FF FF FF FF 00 00 00 |........|
[10-JUL-2017 13:58:33:477] nsbasic_bsd: 00 00 00 00 00 FE FF FF |........|
[10-JUL-2017 13:58:33:477] nsbasic_bsd: FF FF FF FF FF FE FF FF |........|
[10-JUL-2017 13:58:33:477] nsbasic_bsd: FF FF FF FF FF FE FF FF |........|
[10-JUL-2017 13:58:33:477] nsbasic_bsd: FF FF FF FF FF 00 00 00 |........|
[10-JUL-2017 13:58:33:477] nsbasic_bsd: 00 00 00 00 00 FE FF FF |........|
[10-JUL-2017 13:58:33:477] nsbasic_bsd: FF FF FF FF FF FE FF FF |........|
[10-JUL-2017 13:58:33:477] nsbasic_bsd: FF FF FF FF FF 00 00 00 |........|
[10-JUL-2017 13:58:33:477] nsbasic_bsd: 00 00 00 00 00 00 00 00 |........|
[10-JUL-2017 13:58:33:477] nsbasic_bsd: 00 00 00 00 00 00 00 00 |........|
[10-JUL-2017 13:58:33:477] nsbasic_bsd: 00 00 00 00 00 00 00 00 |........|
[10-JUL-2017 13:58:33:477] nsbasic_bsd: 00 31 42 45 47 49 4E 20 |.1BEGIN.|
[10-JUL-2017 13:58:33:478] nsbasic_bsd: 64 62 6D 73 5F 6F 75 74 |dbms_out|
[10-JUL-2017 13:58:33:478] nsbasic_bsd: 70 75 74 2E 70 75 74 5F |put.put_|
[10-JUL-2017 13:58:33:478] nsbasic_bsd: 6C 69 6E 65 28 71 27 5B |line(q'[|
[10-JUL-2017 13:58:33:478] nsbasic_bsd: 27 27 27 2D 2D 27 27 27 |'''--'''|
[10-JUL-2017 13:58:33:478] nsbasic_bsd: 5D 27 29 3B 20 45 4E 44 |]');.END|
[10-JUL-2017 13:58:33:478] nsbasic_bsd: 3B 0A 00 01 00 00 00 01 |;.......|
[10-JUL-2017 13:58:33:478] nsbasic_bsd: 00 00 00 00 00 00 00 00 |........|
[10-JUL-2017 13:58:33:478] nsbasic_bsd: 00 00 00 00 00 00 00 00 |........|
[10-JUL-2017 13:58:33:478] nsbasic_bsd: 00 00 00 00 00 00 00 08 |........|
[10-JUL-2017 13:58:33:478] nsbasic_bsd: 00 00 00 00 00 00 00 00 |........|
[10-JUL-2017 13:58:33:478] nsbasic_bsd: 00 00 00 00 00 00 00 00 |........|
[10-JUL-2017 13:58:33:478] nsbasic_bsd: 00 00 00 00 00 00 00 |....... |
[10-JUL-2017 13:58:33:478] nsbasic_bsd: exit (0)
[10-JUL-2017 13:58:33:478] nsbasic_brc: entry: oln/tot=0
[10-JUL-2017 13:58:33:478] nttfprd: entry
[10-JUL-2017 13:58:52:377] ntt2err: entry
[10-JUL-2017 13:58:52:377] ntt2err: exit
[10-JUL-2017 13:58:52:377] nttfprd: exit
[10-JUL-2017 13:58:52:377] nserror: entry
[10-JUL-2017 13:58:52:377] nserror: nsres: id=0, op=68, ns=12535, ns2=12560; nt[0]=505, nt[1]=60, nt[2]=0; ora[0]=0, ora[1]=0, ora[2]=0
[10-JUL-2017 13:58:52:377] nsbasic_brc: exit: oln=0, dln=0, tot=0, rc=-1
[10-JUL-2017 13:58:52:377] nioqrc: wanted 1 got 0, type 0
[10-JUL-2017 13:58:52:377] nioqper: error from nioqrc
[10-JUL-2017 13:58:52:377] nioqper: ns main err code: 12535
[10-JUL-2017 13:58:52:377] nioqper: ns (2) err code: 12560
[10-JUL-2017 13:58:52:377] nioqper: nt main err code: 505
[10-JUL-2017 13:58:52:377] nioqper: nt (2) err code: 60
[10-JUL-2017 13:58:52:377] nioqper: nt OS err code: 0
[10-JUL-2017 13:58:52:377] nioqer: entry
[10-JUL-2017 13:58:52:377] nioqer: incoming err = 12151
[10-JUL-2017 13:58:52:377] nttaddr2bnd: entry
[10-JUL-2017 13:58:52:377] snlinGetNameInfo: entry
[10-JUL-2017 13:58:52:377] snlinGetNameInfo: exit
Note the change in time from 13:58:33 to 13:58:52 is where the connection is lost.
Thank you for your time!
+3
source to share
No one has answered this question yet
Check out similar questions: