Re: [Orclib-users] Crash when using BindArray* and Bind* functions together

[vincent r. <vin...@gm...>]

Hi,

Oracle OCI requires all binds to be arrays when performing bulk inserts but
does not prevent mixing single and array binds. It only complains when
statement is executed by crashing.
OCILIB is built on top of OCI.
I could check any violation of that OCI paradigm. But then I should check
plenty of other ones that may also change depending on oracle version. Thus
it could lead to an important library code complexification. It may also
require runtime overhead checks that require logic dependent on the sql
statement type...
I will check this at least for that paradigm and see if i can prevent any
mix for bulks without performance or code complexification issues.
But keep in mind that what you want to do (mix single bind and array bind
for bulk inserts) is not possible with oracle !

Regards,

Vincent
Le 12 févr. 2015 00:53, "Ivan Chernetsky" <ich...@qu...> a écrit :

> Hi Vincent,
>
> The fact that library allows to use BindInt and BindArrayOfInts with the
> same prepared statement implies that
>
> 1. Either it will use the same int that was bind to :single for each
> element that comes from :array.
> 2. Or it uses only the first element of :array along with :single value.
>
> To crash is not among good options. So one of those functions either
> should return an error code, or the library should do either 1 or 2.
>
> For instance, it's quite common when where is a need for an insert like
> "INSERT INTO t (n1, n2) VALUES (:single, :array)", and data for :single and
> :array comes from a user, and by using both BindInt and BindArrayOfInts I
> can be sure that SQL injection cannot happen. Theoretically it can be done
> with OCI_PrepareFmt and the like, and it works fine, if it's me who
> provides a value for single variable. If single[] = "va'lue", PrepareFmt
> cannot escape it and just return an error code.
>
> In C, int array[N] = {0} means array is to be initialized with N zeros.
> int array[N] = {1} means array is to be initialized with 1 and (N-1) zeros.
>
> #include <ocilib.h>
>
> #define N 100000
>
> int main(void)
> {
>     OCI_Connection *cn = NULL; OCI_Statement *st = NULL;
>     int i = 0, array[N] = {0}; char single[] = "value";
>     const char *host = "db", *user = "user", *password = "pass";
>
>     for (i = 0; i < N; i++) array[i] = i;
>
>     if (!OCI_Initialize(NULL, NULL, OCI_ENV_DEFAULT)) return EXIT_FAILURE;
>     cn = OCI_ConnectionCreate(host, user, password, OCI_SESSION_DEFAULT);
>     if (cn == NULL) return EXIT_FAILURE;
>
>     st = OCI_StatementCreate(cn);
>     if (st == NULL) return EXIT_FAILURE;
>
>     OCI_Prepare(st, "DROP TABLE ns");
>     OCI_Execute(st);
>     OCI_Commit(cn);
>
>     OCI_Prepare(st, "CREATE TABLE ns (s VARCHAR2(64), n NUMBER)");
>     OCI_Execute(st);
>     OCI_Commit(cn);
>
>     if (!OCI_PrepareFmt(st, "INSERT INTO ns (s, n) VALUES (%s, :array)",
> single))
>         return EXIT_FAILURE;
>
>     if (!OCI_BindArraySetSize(st, N)) return EXIT_FAILURE;
>     if (!OCI_BindArrayOfInts(st, ":array", array, N)) return EXIT_FAILURE;
>
>     if (!OCI_Execute(st)) return EXIT_FAILURE;
>     printf("rows affected: %u\n", OCI_GetAffectedRows(st));
>
>     if (!OCI_Commit(cn)) return EXIT_FAILURE;
>
>     OCI_Cleanup();
>     return EXIT_SUCCESS;
> }
>
> On Wed, Feb 11, 2015 at 2:06 PM, vincent rogier <vin...@gm...>
> wrote:
>
>> Hi,
>>
>> When performing bulk insert, all binding host variables must be arrays.
>> you cannot bind an int to n1 and an array to n2.
>> You need to bind array for both n1 and n2.
>> That's the concept of bulk inserts !
>>
>> Look at your array variable "array". you declare it of size N but you
>> only initialize the first element !!! Other elements have non defined
>> values !
>>
>> Regards,
>>
>> Vincent
>>
>> On Wed, Feb 11, 2015 at 9:26 PM, Ivan Chernetsky <ich...@qu...>
>> wrote:
>>
>>> Hello everybody,
>>>
>>> With N sufficiently large (on my machine 100000 is large enough), the
>>> below program segfaults. With N sufficiently small (on my machine 1000 is
>>> small enough) trash values get written into ns table. It is not allowed to
>>> use BindInt with BindArrayOfInts with the same statement, by design, is it?
>>> If so, BindInt should return some error code, instead of silently allowing
>>> it. Again, if so, what is the proper way of binding single variables and
>>> arrays?
>>>
>>> #include <ocilib.h>
>>>
>>> #define N 100000
>>>
>>> int main(void)
>>> {
>>>     OCI_Connection *cn = NULL; OCI_Statement *st = NULL;
>>>     int single = 0, array[N] = {1};
>>>     const char *host = "db", *user = "user", *password = "pass";
>>>
>>>     if (!OCI_Initialize(NULL, NULL, OCI_ENV_DEFAULT))
>>>         return EXIT_FAILURE;
>>>     cn = OCI_ConnectionCreate(host, user, password, OCI_SESSION_DEFAULT);
>>>     if (cn == NULL)
>>>         return EXIT_FAILURE;
>>>
>>>     st = OCI_StatementCreate(cn);
>>>     if (st == NULL)
>>>         return EXIT_FAILURE;
>>>
>>>     OCI_Prepare(st, "DROP TABLE ns");
>>>     OCI_Execute(st);
>>>     OCI_Commit(cn);
>>>
>>>     if (!OCI_Prepare(st, "CREATE TABLE ns (n1 NUMBER, n2 NUMBER)"))
>>>         return EXIT_FAILURE;
>>>     if (!OCI_Execute(st))
>>>         return EXIT_FAILURE;
>>>     if (!OCI_Commit(cn))
>>>         return EXIT_FAILURE;
>>>
>>>     if (!OCI_Prepare(st, "INSERT INTO ns (n1, n2) VALUES (:single,
>>> :array)"))
>>>         return EXIT_FAILURE;
>>>
>>>     if (!OCI_BindArraySetSize(st, N))
>>>         return EXIT_FAILURE;
>>>     if (!OCI_BindInt(st, ":single", &single))
>>>         return EXIT_FAILURE;
>>>     if (!OCI_BindArrayOfInts(st, ":array", array, N))
>>>         return EXIT_FAILURE;
>>>
>>>     if (!OCI_Execute(st))
>>>         return EXIT_FAILURE;
>>>     printf("rows affected: %u\n", OCI_GetAffectedRows(st));
>>>
>>>     if (!OCI_Commit(cn))
>>>         return EXIT_FAILURE;
>>>
>>>     OCI_Cleanup();
>>>     return EXIT_SUCCESS;
>>> }
>>>
>>