"Raf Schandevyl" <Raf.Schandevyl@...> wrote in message
news:3C91F521.8030409@......

> Now I found it has nothing to do with the blob I was returning but with=20
> concatination off 2 varchars.
>
> Try creating this procedure. You'll get the error.
>
> CREATE PROCEDURE TEST$VARCHAR
> AS
> DECLARE VARIABLE varTest1 VARCHAR(20000);
> DECLARE VARIABLE varTest2 VARCHAR(32000);
> BEGIN
> /* Procedure body */
> varTest2 =3D 'Test';
> varTest1 =3D 'Concatinated';
> varTest2 =3D varTest2 || varTest1;
> SUSPEND;
> END

Hi, Raf.
There's a valid reason why concatenation was tightened. In your example
above, with RC2 and earlier, you could concatenate other varchar more with
length near 30K if you like. But this is way beyond the maximum defined that
64K-1. Since there was no check, you would get bizarre behavior.

Now, why the limit was set at only 32K: if you try to create the longest
field from DSQL, you can't get past 32K. Now, with this artifact, you are
surpassing that limit, not only inside a procedure, but in a direct SELECT
statement. This means you're able to use "||" to create a field that by
other means it's illegal.

I've bumped the limit to 64K-1 to see what happens. This is the largest
concatenation you can produce:
select cast(1 as varchar(32752))||cast(0 as varchar(32765)) from
rdb$database;

Now, there's a problem: some places might treat lengths as signed short
integers. Don't ask me what would happen with values between 32768 and
65535, because anyone that has doing a bit of programming knows.
Furthermore, even if you can concatenate such long fields, you can't use
a computed field with the same expression. Since DSQL restricts fields to
32K-1, one would expect it to catch the problem, but it doesn't and the core
engine doesn't too. Therefore, we create an invalid table that will be
rejected at run-time:

SQL> create table talud(a varchar(23000), b varchar(32000), d computed by
(a||b||a));
SQL> select * from talud;
Statement failed, SQLCODE = -104
invalid request BLR at offset 38
-Implementation limit exceeded
-block size exceeds implementation restriction

SQL> select d from talud;
Statement failed, SQLCODE = -104
invalid request BLR at offset 38
-Implementation limit exceeded
-block size exceeds implementation restriction

This backdoor definitely stinks. The stored length in rdb$fields is 12464
for the computed fields. Obviously, a wrapping by numeric overflow. Now if
you want to smile, older versions simply will crash while trying to execute
it.

System tables use signed integers to contain field lengths. Hence, in
practical terms, this computed field cannot be expressed in system tables,
unless you force the result to be interpreted as unsigned. This is one of
the nasty effects of setting again the limit for concatenation at 64K-1. You
create invalid tables.

SQL> create view valud(a,b,c) as select a,b,d from talud;
=> It succeeds. Again, we have an invalid view.

SQL> select * from valud;
Statement failed, SQLCODE = -104
invalid request BLR at offset 38
-Implementation limit exceeded
-block size exceeds implementation restriction

SQL> create view valud2(a,z) as select a, a||b||a from talud;
SQL> select * from valud2;
Statement failed, SQLCODE = -904
Implementation limit exceeded
-block size exceeds implementation restriction

Therefore, anything in the range 32K..64K-1 is at your own risk. I will
extend the range to 64K-1, but if you got sometimes strange results, you
will get them again. 64K and above is illegal if you read Borland's
specifications: maximum row size can't reach this value. This is because
lengths are kept in unsigned short ints, but there may be places where
signed variables are used.

It seems that the last resort is to be boring, repetitive & pedant but try
to educate users on how not to stuff themselves. However, hackers by default
act as uneducated and find those gaps for possible vulnerabilities. Hence,
the engine should protect itself.

C.
--
Claudio Valderrama C. - http://www.cvalde.com - http://www.firebirdSql.org
Independent developer
Owner of the Interbase® WebRing