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

[Ivan C. <ich...@qu...>]

Thanks, Vincent! So it is obvious I have to invent my own simple escaping
function :)

Ivan.

On Wed, Feb 11, 2015 at 11:42 PM, vincent rogier <vin...@gm...>
wrote:

> 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;
>>>> }
>>>>
>>>