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

[BB <adamnt42@xxxxxxxxx>]

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