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

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

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