Re: Dynamic SQL using parameter markers vs. embedded SQL using host variables

Very good point, I should have mentioned it with my example.

--Do--
if var2 <> 0;
  wSqlWhere += 'and col2 = ?';
endif;

--Do not--
if var2 <> 0;
  wSqlWhere += 'and col2 = ' + %char(var2);
endif;

Charles

On Fri, Sep 12, 2025 at 8:10 AM Daniel Gross <[email protected]> wrote:

> > Am 12.09.2025 um 15:47 schrieb Charles Wilt <[email protected]>:
>
> > One last tidbit, don't overly complicate the static statement just to use
> > static.
> >
> > What I mean is having something like so:
> > exec sql
> >  select <...>
> >    where col1 =
> >        case when :var1 = 0 then col1 else :var1 end
> >     and col2 =
> >        case when :var2 = '' then col2 else :var2 end
> >     <...>
> >
> > To have "optional" filters or sorting applied.  This takes the "static
> is a better choice" too far; greatly increasing the difficulty of the
> optimiser's job.
> >
> > In these cases, dynamic is a better choice.
>
> Definitely true.
>
> The main error, that a lot of people do in this case, is to create the
> whole SQL statement in the string, and don't use parameter marker (?) in
> dynamic SQL. At least I have seen this very often.
>
> I understand the problem, as you might have changing/variable WHERE
> conditions depending on user input - and it gets complicated very fast. So
> embedding user input strings into the dynamic SQL statement seems easy and
> straightforward.
>
> The problem is then, that when parts of the dynamic SQL statement are
> strings from user input, you risk SQL injections:
>
> -> https://xkcd.com/327/
>
> So I have written a piece about avoiding SQL injections with "really"
> dynamic SQL statements:
>
> -> https://blog.qpgmr.de/2025/05/24/avoiding-sql-injections.html
>
> So when using dynamic SQL, I really recommend using ? parameter markers
> and SQL indicators at least for all CHAR or VARCHAR values. And using the
> technique from my blog post, it's only a bit more complicated than
> embedding the strings directly.
>
> HTH
> Daniel
>
>
> >> On Thu, Sep 11, 2025 at 3:14 PM Dan Bale <
> [email protected]>
> >> wrote:
> >>
> >> We have a lot of embedded SQL that uses host variables, e.g.:
> >> Select a, b, c from custmast where c = :hostc;
> >>
> >> It has been suggested to me that this can cause performance issues
> because
> >> the plan cache may need to be "rebuilt"(?), so we should consider
> replacing
> >> these type of queries with dynamic SQL using parameter markers, e.g.
> >>
> >> SQLStatement = 'Select a, b, c from custmast where c = ?';
> >> Exec SQL Prepare P1 from :SQLStatement;
> >> Exec SQL Declare C1 cursor for P1;
> >> Exec SQL Open C1 USING :hostc;
> >>
> >> Is anyone aware of a resource that describes this in detail?  Maybe also
> >> provide test cases that prove performance gains?  (Or not?)
> >>
> >> - Dan Bale
> >> *** CONFIDENTIALITY NOTICE: The information contained in this
> >> communication may be confidential, and is intended only for the use of
> the
> >> recipients named above. If the reader of this message is not the
> intended
> >> recipient, you are hereby notified that any dissemination,
> distribution, or
> >> copying of this communication, or any of its contents, is strictly
> >> prohibited. If you have received this communication in error, please
> return
> >> it to the sender immediately and delete the original message and any
> copy
> >> of it from your computer system. If you have any questions concerning
> this
> >> message, please contact the sender. ***
> >> --
> >> This is the RPG programming on IBM i (RPG400-L) mailing list
> >> To post a message email: [email protected]
> >> To subscribe, unsubscribe, or change list options,
> >> visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
> >> or email: [email protected]
> >> Before posting, please take a moment to review the archives
> >> at https://archive.midrange.com/rpg400-l.
> >>
> >> Please contact [email protected] for any subscription
> related
> >> questions.
> >>
> >>
> > --
> > This is the RPG programming on IBM i (RPG400-L) mailing list
> > To post a message email: [email protected]
> > To subscribe, unsubscribe, or change list options,
> > visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
> > or email: [email protected]
> > Before posting, please take a moment to review the archives
> > at https://archive.midrange.com/rpg400-l.
> >
> > Please contact [email protected] for any subscription
> related questions.
> >
> --
> This is the RPG programming on IBM i (RPG400-L) mailing list
> To post a message email: [email protected]
> To subscribe, unsubscribe, or change list options,
> visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
> or email: [email protected]
> Before posting, please take a moment to review the archives
> at https://archive.midrange.com/rpg400-l.
>
> Please contact [email protected] for any subscription related
> questions.
>
>
-- 
This is the RPG programming on IBM i (RPG400-L) mailing list
To post a message email: [email protected]
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: [email protected]
Before posting, please take a moment to review the archives
at https://archive.midrange.com/rpg400-l.

Please contact [email protected] for any subscription related questions.