Re: Connection.Exec() silently corrupts queries with placeholders &10 and above

[Alarch <alarch@xxxxxxxxx>]

On Mon, 28 Sep 2026 12:05:39 +0930
BB <adamnt42@xxxxxxxxx> wrote:

> Have you tried using {&10}  See the help on SUBST
> 
> b
> 
> On 28/9/26 10:32 am, Alarch wrote:
> > Hi list,
> >
> > We just tracked down a data-corruption bug in production caused by
> > Connection.Exec()'s positional placeholder substitution (&1, &2,
> > ...). It produces no error at all, it just silently substitutes the
> > wrong value, which makes it particularly nasty to catch.
> >
> > Root cause (as far as we can tell from the outside): placeholder
> > substitution appears to match by string prefix rather than by exact
> > token. &1 matches as a prefix of &10, &11, ..., &19, so those get
> > replaced with parameter #1's value, with the placeholder's second
> > digit left behind as literal text in the query. Same for &2
> > inside&20-&29, etc.
> >
> > Minimal reproduction (gb.db + gb.db.sqlite3, no server needed):
> >
> > Public Sub Main()
> > Dim hDB As New Connection
> > Dim hRes As Result
> > hDB.Type = "sqlite3"
> > hDB.Host = "/tmp"
> > hDB.Name = "placeholder_bug_test.sqlite"
> > ' created first with `sqlite3 /tmp/placeholder_bug_test.sqlite
> > "CREATE TABLE x(c1 INT)"` hDB.Open()
> >
> > hDB.Exec("CREATE TABLE t (c1 INT, c2 INT, c3 INT, c4 INT, c5 INT, c6
> > INT, c7 INT, c8 INT, c9 INT, c10 INT, c11 INT, c12 INT)")
> > hDB.Exec("INSERT INTO t (c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12)
> > VALUES (&1,&2,&3,&4,&5,&6,&7,&8,&9,&10,&11,&12)", 1, 2, 3, 4, 5, 6,
> > 7, 8, 9, 999, 888, 777)
> >
> >
> > hRes = hDB.Exec("SELECT * FROM t")
> > Print "c9="; hRes!c9; " c10="; hRes!c10; " c11="; hRes!c11; " c12=";
> > hRes!c12
> >
> > End
> >
> > Expected: c9=9 c10=999 c11=888 c12=777
> > Actual: c9=9 c10=10 c11=11 c12=12
> >
> > (c10 becomes "1" [param #1's value] + "0" [leftover digit] = "10",
> > c11 → "11", c12 → "12", matches the mechanism above exactly.)
> >
> > Confirmed on two separate versions/environments:
> > - Gambas 3.19.5 (git build, local dev machine), gb.db.sqlite3
> > - Gambas 3.20.4-1ubuntu1 (Ubuntu 26.04 package), gb.db.mysql, in a
> > real production worker
> >
> > We'd already independently hit and worked around this once before
> > (2026-09-15, in an invoice-collector query), but only just realized
> > it's the same root cause when it bit us again tonight in a different
> > query with 16 parameters, every row silently got wrong values for
> > parameters 10 and up, no error raised, over roughly a thousand rows
> > before we noticed.
> >
> > Workaround (in case anyone else runs into this): never use more
> > than 9 positional placeholders (&1-&9) in a single Exec()
> > call.Split into an INSERT + a follow-up UPDATE (keyed on the
> > inserted row's id) if you need more columns.
> >
> > Is this a known issue? I couldn't find it mentioned in the wiki
> > (comp/gb.db/_connection/exec) or an existing bugtracker entry,
> > happy to file a proper bug report if useful, just wanted to check
> > first in case there's already a ticket or a simpler documented
> > limit I missed.
> >
> > Thanks,
> >
> > Marc
> >

Thanks, that's exactly it !
Confirmed with a quick test (&{10},&{11},
&{12} all substitute correctly).

Not a bug then, just documentation split across two pages:
comp/gb.db/_connection/exec shows the basic &1/&2 example but doesn't
mention the ambiguity past &9, while the actual explanation and the
&{N} escape live on the Subst$ page ("With the '&10' substitution
pattern, you cannot make the difference between substitute the 10th
argument and substitute the first argument and add a zero"). 

It would probably be a good idea to make a cross-reference from the
Exec documentation page to the Subst$'s one. 

That might prevent someone else from falling into the same trap and asking a
silly question on the list. ;o)

Thanks again for the quick answer.

Marc