{"id":8169,"date":"2021-07-27T22:31:53","date_gmt":"2021-07-28T03:31:53","guid":{"rendered":"https:\/\/www.rushworth.us\/lisa\/?p=8169"},"modified":"2021-08-19T10:35:26","modified_gmt":"2021-08-19T15:35:26","slug":"fortify-on-demand-remediation-sql-injection","status":"publish","type":"post","link":"https:\/\/www.rushworth.us\/lisa\/?p=8169","title":{"rendered":"Fortify on Demand Remediation – SQL Injection"},"content":{"rendered":"

This vulnerability occurs when you accept user input and then use that input in a SQL query. The basic remediation is to use oci_bind_by_name <\/a>to bind variables into placeholders.<\/p>\n

A query using an equivalence clause<\/h5>\n

The simplest case is a query with an equivalence clause.<\/p>\n

The code:<\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0$strQuery\u00a0=\u00a0\"SELECT\u00a0DISTINCT\u00a0EXCHANGE_CARRIER_CIRCUIT_ID,\u00a0CIRCUIT_DESIGN_ID FROM\u00a0circuit$strDBLink WHERE\u00a0EXCHANGE_CARRIER_CIRCUIT_ID = '$strECCKT' ORDER\u00a0BY\u00a0CIRCUIT_DESIGN_ID\";\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0$stmt\u00a0=\u00a0oci_parse($kpiprd_conn,\u00a0$strQuery);\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 oci_set_prefetch($stmt,\u00a0300);\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0oci_execute($stmt);<\/pre>\n
Becomes:<\/p>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0$strQuery\u00a0=\u00a0\"SELECT\u00a0DISTINCT\u00a0EXCHANGE_CARRIER_CIRCUIT_ID,\u00a0CIRCUIT_DESIGN_ID FROM\u00a0circuit$strDBLink WHERE\u00a0EXCHANGE_CARRIER_CIRCUIT_ID\u00a0IN :ecckt ORDER\u00a0BY\u00a0CIRCUIT_DESIGN_ID\";\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0$stmt\u00a0=\u00a0oci_parse($kpiprd_conn,\u00a0$strQuery);\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 oci_bind_by_name($stmt,\u00a0':ecckt', $strECCKT);\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0oci_set_prefetch($stmt,\u00a0300);\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0oci_execute($stmt);<\/pre>\n
The same placeholder can be used with the like query. Use “select something from table where columnname like :placeholdername” followed by an oci_bind_by_name($stmt, “:placeholdername”, $strPlaceholderVariable).<\/p>\n
A query using an IN clause<\/h5>\n
\u2013\u00a0is a little tricker. You could<\/em> iterate through the array of values and build :placeholder1, :placeholder2, …, :placeholdern<\/em> and then iterate through the array of values again to bind each value to its corresponding placeholder. A cleaner approach is to use an Oracle collection ($coll in this example) and binding the collection to a single placeholder.<\/p>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 $arrayCircuitNames = array('L101 \/T1\u00a0\u00a0\u00a0 \/ELYROHU0012\/ELYROHXA32C','111\u00a0 \/ST01\u00a0 \/CHMPILCPF01\/CHMPILCPHH3','C102 \/OC12\u00a0 \/PHLAPAFG-19\/PHLAPAFGW22')\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 $strQuery = \"SELECT CIRCUIT_DESIGN_ID, EXCHANGE_CARRIER_CIRCUIT_ID\u00a0 FROM circuit$strDBLink\u00a0 WHERE EXCHANGE_CARRIER_CIRCUIT_ID in (SELECT column_value FROM table(:myIds))\";\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 $stmt = oci_parse($kpiprd_conn, $strQuery);\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 $coll = oci_new_collection($kpiprd_conn, 'ODCIVARCHAR2LIST','SYS');\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 foreach ($arrayCircuitNames as $key) {\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 $coll->append($key);\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 }\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 oci_bind_by_name($stmt, ':myIds', $coll, -1, OCI_B_NTY);\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 oci_set_prefetch($stmt, 300);\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 oci_execute($stmt);<\/pre>\n
Queries with multiple LIKE conditions<\/h5>\n
Queries with an OR’d group of LIKE clauses can be handled in a similar fashion \u2013 either iterate through the array twice or create a collection with strings that include the wildcard characters, then bind that collection to a single placeholder. Create a semi-join using an EXISTS predicate<\/p>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 $arrayLocs\u00a0=\u00a0array('ERIEPAXE%',\u00a0'HNCKOHXA%',\u00a0'LTRKARXK%');\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 $strQuery\u00a0=\u00a0\"select\u00a0location_id,\u00a0clli_code\u00a0from\u00a0network_location$strDBLink\u00a0where\u00a0exists\u00a0(select\u00a01\u00a0from\u00a0TABLE(:likelocs)\u00a0where\u00a0clli_code\u00a0like\u00a0column_value)\u00a0order\u00a0by\u00a0clli_code\";\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 $stmt\u00a0=\u00a0oci_parse($kpiprd_conn,\u00a0$strQuery);\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 $coll\u00a0=\u00a0oci_new_collection($kpiprd_conn,\u00a0'ODCIVARCHAR2LIST','SYS');\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 foreach\u00a0($arrayLocs\u00a0as\u00a0$strLocation)\u00a0{\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0$coll->append($strLocation);\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 }\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 oci_bind_by_name($stmt,\u00a0':likelocs',\u00a0$coll,\u00a0-1,\u00a0OCI_B_NTY);\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 oci_execute($stmt);<\/pre>\n
A query using DUAL<\/h5>\n
Queries where values are selected from DUAL \u2013 In some of my recursive queries, I need to include the original input in the result set (particularly, this query finds all equipment mounted under<\/em> a specific equipment ID\u00a0\u2013 I want to include the input equipment ID as well).\u00a0Having a bunch of ‘select 12345 from dual’ is fine until I need to use placeholders. This is another place where the collection can be leveraged:<\/p>\n
     select column_value equipment_id from TABLE(sys.ODCIVARCHAR2LIST('12345CDE', '23456BCD', '34567ABC') );<\/pre>\n
Adds each of the values to my result set.<\/p>\n
\"\"<\/a><\/p>\n
Which means I can use a query like “select column_value as equipment_id from TABLE(:myIDs)” and bind the collection to :myIDs.<\/p>\n","protected":false},"excerpt":{"rendered":"
This vulnerability occurs when you accept user input and then use that input in a SQL query. The basic remediation is to use oci_bind_by_name to bind variables into placeholders. A query using an equivalence clause The simplest case is a query with an equivalence clause. The code: \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0$strQuery\u00a0=\u00a0“SELECT\u00a0DISTINCT\u00a0EXCHANGE_CARRIER_CIRCUIT_ID,\u00a0CIRCUIT_DESIGN_ID FROM\u00a0circuit$strDBLink WHERE\u00a0EXCHANGE_CARRIER_CIRCUIT_ID = ‘$strECCKT’ ORDER\u00a0BY\u00a0CIRCUIT_DESIGN_ID”; \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0$stmt\u00a0=\u00a0oci_parse($kpiprd_conn,\u00a0$strQuery); \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 …<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[33],"tags":[45,1437,595,1440,35,69,1329],"class_list":["post-8169","post","type-post","status-publish","format-standard","hentry","category-coding","tag-coding","tag-fortify-on-demand","tag-oracle","tag-oracle-db","tag-php","tag-security","tag-web-coding"],"_links":{"self":[{"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/posts\/8169","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=8169"}],"version-history":[{"count":2,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/posts\/8169\/revisions"}],"predecessor-version":[{"id":8190,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/posts\/8169\/revisions\/8190"}],"wp:attachment":[{"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=8169"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=8169"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=8169"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}