CODEWITHSUNDEEP
abapopensql
guettli
guettli

Reputation: 27806

Quoting arguments in dynamic WHERE in OpenSQL

I found this example how to create a dynamic WHERE:

REPORT ZII_RKP_TEST1.

DATA: cond(72) TYPE c,
      itab LIKE TABLE OF cond.

PARAMETERS: source(10) TYPE c, dest(10) TYPE c.

DATA wa TYPE spfli-cityfrom.

CONCATENATE 'CITYFROM = ''' source '''' INTO cond.
APPEND cond TO itab.

CONCATENATE 'OR CITYFROM = ''' dest '''' INTO cond.
APPEND cond TO itab.

CONCATENATE 'OR CITYFROM = ''' 'BAYERN' '''' INTO cond.
APPEND cond TO itab.

LOOP AT itab INTO cond.
  WRITE cond.
ENDLOOP.

SKIP.

SELECT cityfrom
  INTO wa
  FROM spfli
 WHERE (itab).
   WRITE / wa.
ENDSELECT.

Source: https://wiki.scn.sap.com/wiki/display/ABAP/Dynamic%2Bwhere%2Bclause

Above example uses static values like "BAYERN", but if I use arbitrary values, then I guess things could break for some special values like '''.

Is it necessary to do some quoting to make the dynamic WHERE unbreakable? And if yes, how to do it?

Upvotes: 2

Views: 5181

Answers (3)

mkysoft
mkysoft

Reputation: 5758

You can escape apostrophe in perform before adding them like below

PERFORM escape CHANGING source.
PERFORM escape CHANGING dest.

CONCATENATE 'CITYFROM = ''' source '''' INTO cond.
APPEND cond TO itab.

...

FORM escape CHANGING value TYPE c.
  REPLACE ALL OCCURRENCES OF '''' IN value WITH ''''''.
ENDFORM.

Latest ABAP versions included escape function details are here. But it is not included quote escaping. We can use static escape_quotes method on class cl_abap_dyn_prg like below.

CALL METHOD cl_abap_dyn_prg=>escape_quotes
  EXPORTING
    val    = source
  receiving
    out    = output.

Method making something look like above perform.

Upvotes: 2

guettli
guettli

Reputation: 27806

The method cl_abap_dyn_prg=>quote( name ) should get used.

Example:

DATA(cond) = `country = 'DE' AND name = ` &&
             cl_abap_dyn_prg=>quote( name ).

Source: SQL Injections Using Dynamic Tokens

Thanks to Sandra Rossi for pointing me in the right direction.

Upvotes: 1

VXLozano
VXLozano

Reputation: 317

I wonder why that example is written that way, maybe it's an old piece of code.

1st: if you have access in your system to the "new" string syntax, you can just use something like

WHERE = |CITYFROM = '| && source && |'|.

2nd: the nice thing about string WHERE clauses is that you can just use variables as part of the string, I mean, if you just write something like

WHERE = 'CITYFROM = source'.

ABAP will transform that into proper SQL, as if you were writing your SQL properly.

(I wish I could explain myself properly, do not hesitate to ask if you have any doubt)

Upvotes: 1

Related Questions