FreeTDS Development Group

[Frediano Ziglio <freddy77 AT gmail.com>]

2016-06-12 18:59 GMT+01:00 Nem W Schlecht <nem AT emptec.com>:
> Hello all,
>
> First off, I just want to thank all of the developers in this community for
> continuing work on such a useful and excellent application.  I cannot thank
> and appreciate you all enough!  Thank you, Thank You, THANK YOU!!
>
>
> I recently grabbed the last stable release and came across an issue that
> seems to constantly be plaguing me and has been around for a long time.  I
> had reported this back in July of 2014, but the proposed solution
> (stripping all but one space) would still screw up some of my stored
> procedures (that have lots of spaces in strings for formatting purposes).
>
> My original post and reply:
>   https://lists.ibiblio.org/sympa/arc/freetds/2014q3/028964.html
>   https://lists.ibiblio.org/sympa/arc/freetds/2014q3/028966.html
>
> The issue is with lines 7311-7314 in dblib.c:
>
>    7311                 case NTBSTRINGBIND: /* strip trailing blanks, null
> term */
>    7312                     while (srclen && src[srclen - 1] == ' ') {
>    7313                         --srclen;
>    7314                     }
>
> Looks like something good to do, right?  Get rid of any blanks that we
> don't need.  However, this keeps messing up my output when I use "defncopy"
> to dump out some of my stored procedures.
>

Well... this is the expected behaviour of NTBSTRINGBIND. The problem is not in
dblib.c but in defncopy that expects a different behaviour.

> If it just so happens that you have a large stored procedure or large
> string in that stored procedure *and* there are one or more spaces exactly
> at a multiple of byte 4000, dblib.c will strip out the spaces, causing
> either data corruption or an SQL syntax error in the stored procedure.
>
> I'm attaching an example to reproduce the issue.
>
> Load in 'dbo.big_proc-load.sql' and then immediately run 'defncopy' to dump
> it back out.  Each digit should have 40 spaces between it, but on line 96,
> it only contains 8 spaces.  dblib grabbed the first 4000 bytes, saw there
> there were spaces, and truncated it back to the last non-space character.
>
> I've actually had this happen (just by pure chance) in actual code as well,
> where a line like this:
>
>      SELECT some_field_name AS some_field_alias
>
> has been turned into:
>
>      SELECT some_field_nameAS some_field_alias
>
> Thus causing an error the next time the stored procedure is run, since the
> field name is now incorrect.
>
> I've modified my copy of dblib.c and commented out lines 7312-7314 (as I
> did so 2 years ago and I've had to negative effects), but would like to
> hear thoughts/opinions of the developers and would really like this issue
> fixed.
>
> Again, thank you all so much for all of your hard work on FreeTDS!
>

Seems that all bindings with varlen != 0 (as you don't want an overflow too):
- does blank padding;
- are limited;
- strip blanks.
All stuff you don't want for your usage. The possible solution is to not use
binding but dbdata and family.

Frediano