C/C++ (or Java) SQL CLI Q: Using SQLBindParameter() or sprintf()? - IBM AS400

This is a discussion on C/C++ (or Java) SQL CLI Q: Using SQLBindParameter() or sprintf()? - IBM AS400 ; Whats the difference is using SSQLBindParameter(...1, ..., somekey, ...) SQLBindParameter(..., 2, ..., someotherkey, ...) SQLExecDirect(..., "SELECT * FROM SOMETABLE WHERE SOMEKEY = ? AND SOMEOTHERKEY = ?", ...) and just plugging in what you need with sprintf() or building a ...

+ Reply to Thread
Results 1 to 3 of 3

Thread: C/C++ (or Java) SQL CLI Q: Using SQLBindParameter() or sprintf()?

  1. C/C++ (or Java) SQL CLI Q: Using SQLBindParameter() or sprintf()?

    Whats the difference is using

    SSQLBindParameter(...1, ..., somekey, ...)
    SQLBindParameter(..., 2, ..., someotherkey, ...)

    SQLExecDirect(..., "SELECT * FROM SOMETABLE WHERE SOMEKEY = ? AND
    SOMEOTHERKEY = ?", ...)

    and just plugging in what you need with sprintf() or building a string
    in C++ ? :

    str = "SELECT * FROM SOMETABLE WHERE SOMEKEY = " + "'" + somekey + "'"
    + " AND SOMEOTHERKEY = " + "'" + someotherkey + "'";
    SQLExecDirect(..., str.c_str(), ...);

    or

    sprintf(str, "SELECT * FROM SOMETABLE WHERE SOMEKEY ='%s' AND
    SOMEOTHERKEY = '%s'", somekey, someotherkey);
    SQLExecDirect(..., str, ...);

    Seems like a lot less drama to do it without having to use
    SQLBindParameter.

    ????

    BTW, I know using c_str() will cause a compiler problem because
    SQLExecDirect() doesn't define that parameter as a const char * but
    that is a moot point for me.

  2. Re: C/C++ (or Java) SQL CLI Q: Using SQLBindParameter() or sprintf()?

    Mr. K.V.B.L. wrote:
    > Whats the difference is using
    >
    > SSQLBindParameter(...1, ..., somekey, ...)
    > SQLBindParameter(..., 2, ..., someotherkey, ...)
    >
    > SQLExecDirect(..., "SELECT * FROM SOMETABLE WHERE SOMEKEY = ? AND
    > SOMEOTHERKEY = ?", ...)
    >
    > and just plugging in what you need with sprintf() or building a string
    > in C++ ? :
    >
    > str = "SELECT * FROM SOMETABLE WHERE SOMEKEY = " + "'" + somekey + "'"
    > + " AND SOMEOTHERKEY = " + "'" + someotherkey + "'";
    > SQLExecDirect(..., str.c_str(), ...);
    >
    > or
    >
    > sprintf(str, "SELECT * FROM SOMETABLE WHERE SOMEKEY ='%s' AND
    > SOMEOTHERKEY = '%s'", somekey, someotherkey);
    > SQLExecDirect(..., str, ...);
    >
    > Seems like a lot less drama to do it without having to use
    > SQLBindParameter.
    >
    > ????
    >
    > BTW, I know using c_str() will cause a compiler problem because
    > SQLExecDirect() doesn't define that parameter as a const char * but
    > that is a moot point for me.


    There is a fair amount of overhead in parsing the SQL statement and
    building an access plan for the query. If you have a statement that's
    run multiple times with slightly different input values each time, with
    parameter markers ("?") you can pay the overhead once by first doing
    SQLPrepare(). Then each time you run the statement, bind specific values
    for the parameter markers and run SQLExecute() passing the statement
    handle of the previously prepared statement. When using SQLExecDirect()
    the RDB server essentially does the "prepare" step behind the scenes as
    well as the execute step. But with SQLExecDirect() the statement can
    only be run once. In that case it's probably simpler (when possible) to
    create a complete statement string w/o parameter markers, and skip doing
    any parameter binds, as you suggest.

    http://webdocs.caspur.it/ibm/db2/8.1...d/t0007071.htm

    --
    Karl Hanson

  3. Re: C/C++ (or Java) SQL CLI Q: Using SQLBindParameter() or sprintf()?

    As well as the performance differences already mentioned, binding the
    parameters protects you from SQL Injection attacks.
    Consider what would happen in the string building example if somekey
    contained "' OR 1=1 " - the query would effectively ignore somekey,
    and only match on someotherkey. If parameters are bound, the dangerous
    ' will be automatically escaped for you and will not modify the SQL
    query.
    You should only use the string building technique where you can
    absolutely guarantee that it is impossible to receive malicious
    parameters.

+ Reply to Thread